prisma with fts

Recherche de texte complète et floue avec Postgres et Prisma

À un moment donné de votre vie d'ingénierie logicielle, vous avez peut-être entendu l'expression "nous devons créer une fonction de recherche pour cela". De grands ensembles de données demandent d'être consultables. Vous pouvez y ajouter des filtres, oui, mais l'utilisateur final voudra toujours taper lrd of t et obtenir la bourse de l'anneau comme premier résultat. Vous avez probablement pensé à utiliser une solution tierce sophistiquée, qui semble prometteuse, il vous suffit de pointer la base de données et la recherche est gérée d'une manière ou d'une autre. Mais que se passe-t-il si, avec un tout petit peu de bricolage SQL, vous pouvez atteindre le même résultat, ou même mieux?

Les bases de Postgres pour la recherche en texte intégral

En ce qui concerne l'analyse de texte ou l'exploitation minière, il existe quelques concepts différents qui sont utiles à saisir avant de s'aventurer dans le beau voyage de la recherche en texte intégral. Nous pouvons commencer par définir un document . Un document est à peu près ce que vous savez probablement déjà, un ensemble de phrases suivant une sorte de structure, écrite dans une langue spécifique. El Cantar del Mío Cid est un document, et la sinopsie d'un film de 1930 est également un document. En ce qui concerne Postgres, des documents peuvent être trouvés dans une ou bien d'autres colonnes. Les documents sont généralement analysés en jetons , qui peuvent être des mots et des phrases, à partir desquels nous pouvons récupérer des lexèmes , des unités de texte significatives.

Postgres prend des documents et des parses lexèmes à l'aide de dictionnaires . Il y a un dictionnaire par défaut, des dictionnaires basés sur la langue et vous pouvez même fournir le vôtre. La vérité est que si vous savez que votre document est en allemand, vous voudrez probablement utiliser le dictionnaire allemand pour analyser vos lexèmes. En utilisant des dictionnaires spécifiques, vous pouvez obtenir de meilleurs lexèmes spécifiques à l'alphabet de votre langue et à des racines de mots et des étymologies.

Rencontrez tsvector

tsvector est un type de données intégré dans Postgres ¹ , qui représente une liste triée de lexèmes normalisés distincts. Si nous considérons le modèle PRISMA suivant

 model Movie {
  id        String  @ id @ default ( cuid ( ) )
  title     String
  year      Int
  extract   String ?
  thumbnail String ?
  genre     String ?

  createdAt DateTime @ default ( now ( ) )
  updatedAt DateTime @ updatedAt
}

Nous pourrions penser à une fonction de recherche basée sur l'extrait du film. Malheureusement, à ce jour, Prisma manque de soutien à tsvector ² . Cependant, on peut utiliser le décorateur @unsupported et s'aventurer dans un magnifique SQL brut.

 model Movie {
  id        String  @ id @ default ( cuid ( ) )
  title     String
  year      Int
  extract   String ?
  thumbnail String ?
  genre     String ?

  createdAt DateTime @ default ( now ( ) )
  updatedAt DateTime @ updatedAt

  search Unsupported ( "tsvector" ) ? @ default ( dbgenerated ( "''::tsvector" ) )
}

Je sais que si vous utilisez Prisma, vous n'aimez probablement pas beaucoup SQL, mais une fois que vous essayez de faire quelque chose d'un peu personnalisé ou de passer une étape productive, vous trouverez une limitation d'ORM.

Générer des vecteurs à partir de colonnes

D'accord, maintenant nous avons une belle colonne vectorielle pour la recherche en texte intégral. Avec un script SQL simple, nous pouvons remplir cette colonne:

 ALTER TABLE " Movie "
SET search = to_tsvector( ' english ' , extract)

to_tsvector est l'une des nombreuses fonctions intégrées de Postgres ³ qui prendra votre document (et un dictionnaire facultatif) et créera un vecteur pour cela. Cependant, que se passe-t-il si l'extrait est mis à jour? Et si j'ajoute une nouvelle ligne? Eh bien, nous devons recalculer. Et quelle belle opportunité pour les colonnes générées ⁴ de briller, non? Avec SQL, vous feriez ce qui suit.

 ALTER TABLE " Movie " ADD COLUMN search tsvector
GENERATED ALWAYS AS (to_tsvector( ' english ' , extract)) STORED;

Mais la vie n'est pas si simple, car nous utilisons Prisma. Même si l'équipe PRISMA nous fournit du npx prisma migrate dev --create-only , un moyen de bricoler avec le SQL généré dans les migrations, au moment de la rédaction, il y a un bug qui nous empêche de mettre en place une colonne générée ⁵ . Heureusement, ce n'est pas la fin, ce n'est qu'un autre chemin! Nous pouvons toujours obtenir le même résultat en utilisant des déclencheurs!

 -- Function to be invoked by trigger

CREATE OR REPLACE FUNCTION update_tsvector_column () RETURNS TRIGGER AS $$
BEGIN
  NEW . search : = to_tsvector( ' english ' , COALESCE( NEW . extract , ' ' ));

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY definer SET search_path = public, pg_temp;

-- Trigger that keeps the TSVECTOR up to date

DROP TRIGGER IF EXISTS " update_tsvector " ON " Movie " ;

CREATE TRIGGER " update_tsvector "
  BEFORE INSERT OR UPDATE ON " Movie "
  FOR EACH ROW
  EXECUTE FUNCTION update_tsvector_column ();

Temps de requête

Vous avez maintenant la configuration de votre colonne tsvector , il est temps de vous interroger. Et oui, vous le faites avec un ts_query . Il existe de nombreuses fonctions pratiques qui peuvent vous aider à convertir votre requête de recherche de texte en quelque chose que Postgres comprendra. phraseto_tsquery peut prendre un dictionnaire et websearch_to_tsquery se rapproche du comportement de certains outils de recherche Web courants. Vous pouvez choisir votre celui qui correspond à vos besoins ³ . Vous pouvez également faire un effort supplémentaire et faire une recherche floue. En convertissant votre recherche de texte en tsvector , vous pouvez mélanger les lexèmes et les expressions régulières pour créer une tsquery floue:

 SELECT to_tsquery(string_agg(lexeme || ' :* ' , ' & ' ORDER BY positions)) AS q FROM unnest(to_tsvector(${searchQuery}))

Enfin, votre requête brute SQL Prisma peut ressembler à ceci.

 const movies = await prisma . $queryRaw < MovieRecord [ ] > `
    WITH query AS (SELECT to_tsquery(string_agg(lexeme || ':*', ' & ' ORDER BY positions)) AS q FROM unnest(to_tsvector( ${ searchQuery } )))
    SELECT
      id, title, genre, year, extract, ts_rank(search, query.q) AS rank
    FROM
      "Movie", query
    ${ searchQuery ? Prisma . sql `WHERE search @@ query.q` : Prisma . empty }
    ORDER BY
      year, rank
    LIMIT 10
  `
/** Direct representation of a row in the Movie table. */
interface MovieRecord {
  id : string
  title : string
  year : number
  genre ?: string
  extract : string
}

Notez que nous commandons par ts_rank ⁶ , afin que nous puissions d'abord fournir les meilleurs résultats correspondants!

Conclusion

Dans l'ensemble, vous pouvez créer une fonction de recherche puissante sans compter sur des logiciels tiers, une duplication de base de données et des syntaxes compliquées. Vous avez juste besoin de Postgres et SQL, des choses que vous avez déjà. Je suis à peu près sûr que d'autres SGBD gèrent la recherche de texte intégral de la même manière. L'implémentation est simple, simple, flexible et maintenable. Et si vous utilisez PRISMA, vous pouvez obtenir les mêmes résultats avec une approche moins élégante mais toujours fonctionnelle.

PS: N'oubliez pas d'indexer avec GIN !

Notes de bas de page

1. tsvector dans la documentation de Postgres. ↩

2. Problème ouvert pour le support tsvector dans le référentiel de Prisma. ↩

3. Fonctions de recherche de texte et opérateurs. ↩ ↩ ²

4. Colonnes générées dans Postgres. ↩

5. Prise en charge des colonnes générées. ↩

6. Classement des résultats de recherche ↩