Add the ability to create custom PostgreSQL text search configurations

[David-Guillot]

Code of Conduct

• I agree to follow Django's Code of Conduct

Feature Description

Make it easier for Django users to create PostgreSQL text search configurations.

That could happen on various levels:

• It could be as simple as adding some examples to the docs on how to create a custom text search configuration
• It could be adding some helpers in django.contrib.postgres.operations module to generate the correct SQL statements that so many projects have copied/pasted over time (like I did this week, like I did several times in closed-source Django projects ; you can find the open-source probably-less-than-1% of the French ones by searching Github)
• It could be something more sophisticated, like defining classes that represent PostgreSQL text search configurations and mappings, and detect them in order to generate migrations

Problem

PostgreSQL full-text search is awesome. Over the past few years many teams have realized they didn't need ElasticSearch for offering efficient full-text search to their users.

But it comes with one major quirk for many websites in non-English languages: the text search configurations are not unaccented. And of course, users sometimes forgot to type accents, and get frustrated because they just don't find what they expect.

Some nice people have published blog posts explaining how to enable the unaccent extension and how to create a custom text search configuration copying the French/Spanish/you-name-it configuration and just add a rule to unaccent everything. And many people have copied/pasted these SQL statements to their Django migrations. And it works, of course it works, but it all relies on some random blog post that is now impossible to find because of you-know-what.

I think it would be nice to have these few lines of SQL code consolidated somewhere in Django, either in the docs or in the codebase itself.

Request or proposal

proposal

Additional Details

No response

Implementation Suggestions

One possible approach would be to add a CreateUnaccentTextSearchConfiguration() operation to django.contrib.postgres.operations module, that would take as argument the name of a built-in PostgreSQL text search configuration and that would run the infamous SQL:

CREATE TEXT SEARCH CONFIGURATION {config_name}_unaccent ( COPY = {config_name} );
ALTER TEXT SEARCH CONFIGURATION {config_name}_unaccent ALTER MAPPING FOR hword, hword_part, word WITH unaccent, {config_name}_stem;

This operation would have to be added to the migration like:

CreateUnaccentTextSearchConfiguration("french")

But I don't know if it's the most relevant thing to do, you tell me :)

[github-actions]

Thank you David-Guillot for sharing your idea! We have a lot of them so please be patient. You can see the current queue here.

Community instructions

For commenters, please use the emoji reactions on the issue to express support, and/or concern easily. Please use the comments to ask questions or contribute knowledge about the idea. It is unhelpful to post comments of "I'd love this" or "What's the state of this?"

Reaction Guide

• 👍 This is something I would use
• 👎 This is something that would cause problems for me or Django
• 😕 I’m indifferent to this
• 🎉 This is an easy win

[adamchainz]

Some nice people have published blog posts

Can you link to these blog posts? I've not heard of this feature before and would like to read up on the state of the art.

One possible approach would be to add a CreateUnaccentTextSearchConfiguration() operation

This seems reasonable to me, but it should probably be CreateTextSearchConfiguration() with unaccent as an argument. From what I can see, that's just one of several settings you might want to change.

Also, how do you propose that one uses a search configuration?

[pauloxnet]

Some nice people have published blog posts

Can you link to these blog posts? I've not heard of this feature before and would like to read up on the state of the art.

I proposed this approach in the past (2017):
https://stackoverflow.com/a/47248109/755343

I think I've mentioned this approach also in some talks about full-text search and some issues in the Django website repository.

I was also sure I wrote about it in my original article on the subject, but it seems I only wrote about the unaccent extension:
https://www.paulox.net/2017/12/22/full-text-search-in-django-with-postgresql/

One possible approach would be to add a CreateUnaccentTextSearchConfiguration() operation

This seems reasonable to me, but it should probably be CreateTextSearchConfiguration() with unaccent as an argument. From what I can see, that's just one of several settings you might want to change.

Also, how do you propose that one uses a search configuration?

If you create the 'french_unaccent' configuration like this:

CREATE TEXT SEARCH CONFIGURATION french_unaccent( COPY = french );
ALTER TEXT SEARCH CONFIGURATION french_unaccent
ALTER MAPPING FOR hword, hword_part, word
WITH unaccent, french_stem;

Then you can pass the name to the config attribute:

SearchVector('title', 'abstract', config='french_unaccent')
SearchQuery(word, config='french_unaccent')

[David-Guillot]

Thanks for your feedback!

Can you link to these blog posts?

Hm actually I don't think I'm able to find anything more instructive than the official PostgreSQL docs. @pauloxnet was the first to comment about it on StackOverflow back in 2017, and it seems all other publications ( @douglasmiranda 's several Gists, and @kemar 's French blog post ) are mainly copy/pasting of the SQL snippet, sometimes inserting it into a Django migration.

it should probably be CreateTextSearchConfiguration() with unaccent as an argument.

IMO it depends on how many use cases we want Django to be able to address in an easy way for developers. From my perspective, the use cases for custom text-search configurations are:

• Just apply unaccent to an existing configuration (
• Add a synonym dictionary or a thesaurus dictionary to an existing configuration ; synonym and thesaurus work the same way unaccent does, but require more stuff (well, define the dictionaries themselves), so I don't think this use case should be in the scope of this proposed feature (also because it's probably less frequent).

So IMO if we define the API as CreateTextSearchConfiguration(something), we need to think about:

• How to name the something argument, because it's not trivial to me how to abstract what unaccent, synonym and thesaurus are (their implementation on PostgreSQL side are non-standalone "translation" dictionaries, but not sure there is a term for us here)
• How to handle the other use cases (or maybe not at the moment)

Maybe the CreateTextSearchConfiguration(something) operation can exist and be called by CreateUnaccentTextSearchConfiguration() and by some other future shorthands?

how do you propose that one uses a search configuration?

That's where we need to settle on the right abstraction level:

• In the simplest scenario, one defines their text-search configuration in the Django migration (the name being a simple string) and then uses the configuration name in their code ; this might seem a bit fragile but on the bright side it's very thin

• In a more sophisticated scenario, the PostgreSQL objects (text search configurations, dictionaries, dictionary rules) could be represented as Python classes that hold the PostgreSQL configurations and a constant to avoid the fragility of a simple string. That would be something like:

  # django.contrib.postgres.search
  class CustomTextSearchConfiguration:
      base_configuration_name = ""
      name = ""
      mapping = dict()
  
  # django.contrib.postgres.operations
  class CreateCustomTextSearchConfiguration(Operation):
      def __init__(configuration: CustomTextSearchConfiguration):
          self.configuration = configuration  # used by `database_forwards()` to generate the SQL statements
  
  # user code
  class FrenchUnaccentTextSearchConfiguration(CustomTextSearchConfiguration):
      base_configuration_name = "french"
      name = "french_unaccent"
      mapping = {"hword": ["unaccent", "french_stem"], "hword_part": ["unaccent", "french_stem"], "word": ["unaccent", "french_stem"]}
  
  # user migration
  operations = [
      ...
      CreateCustomTextSearchConfiguration(FrenchUnaccentTextSearchConfiguration),
      ...
  ]

What do you think? I'm just worried this would be a bit over-engineered...