prisma with fts

Búsqueda de texto completa y difusa con Postgres y Prisma

En algún momento de su vida de ingeniería de software, es posible que haya escuchado la frase "Necesitamos crear una función de búsqueda para esto". Grandes conjuntos de datos exigen ser de búsqueda. Puede agregarle filtros, sí, pero el usuario final siempre querrá escribir lrd of t y obtener la Comunidad del Anillo como primer resultado. Probablemente pensó en usar una solución de terceros elegante, que parece prometedora, simplemente apunta la base de datos y la búsqueda se maneja de alguna manera. Pero, ¿qué pasa si, con un poquito de retoques SQL, puede llegar al mismo resultado o incluso mejor?

Los conceptos básicos de Postgres para la búsqueda de texto completo

Cuando se trata de análisis o minería de texto, hay algunos conceptos diferentes que son útiles para comprender antes de aventurarse en el hermoso viaje de búsqueda de texto completo. Podemos comenzar definiendo un documento . Un documento es más o menos lo que probablemente ya sepa, un conjunto de oraciones que siguen algún tipo de estructura, escrita en un lenguaje específico. El Cantar del Mío Cid es un documento, y el sinopsis de una película de 1930 también es un documento. Cuando se trata de Postgres, los documentos se pueden encontrar en una o muchas columnas más. Los documentos generalmente se analizan en tokens , que pueden ser palabras y frases, de las cuales podemos recuperar lexemas , unidades de texto significativas.

Postgres toma documentos y analizaciones de ellos utilizando diccionarios . Hay un diccionario predeterminado, diccionarios basados ​​en el idioma e incluso puede proporcionar el suyo. La verdad es que si sabe que su documento está en alemán, es probable que desee usar el diccionario alemán para analizar sus Lexemes. Al usar diccionarios específicos, puede obtener mejores lexemas específicos para el alfabeto de su idioma y las raíces y etimologías de palabras.

Conocer tsvector

tsvector es un tipo de datos incorporado en Postgres ¹ , que representa una lista ordenada de lexemas distintos y normalizados. Si consideramos el siguiente modelo prisma

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

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

Podríamos pensar en una función de búsqueda basada en el extracto de la película. Desafortunadamente, a partir de hoy, Prisma carece de apoyo para tsvector ² . Sin embargo, uno puede hacer uso del decorador @unsupported y aventurarse en un hermoso SQL crudo.

 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" ) )
}

Lo sé, si estás usando Prisma, probablemente no te guste mucho SQL, pero una vez que intentes hacer algo un poco personalizado o alcanzar una etapa productiva, encontrarás cualquier limitación de ORM.

Generando vectores a partir de columnas

Bien, ahora tenemos una buena columna vectorial para la búsqueda de texto completo. Con un simple script SQL, podemos poblar esa columna:

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

to_tsvector es una de las muchas funciones integradas en Postgres ³ que tomará su documento (y un diccionario opcional) y creará un vector para ello. Sin embargo, ¿qué sucede si el extracto se actualiza? ¿Qué pasa si agrego una nueva fila? Bueno, necesitamos recalcular. Y qué buena oportunidad para que brille las columnas generadas ⁴ , ¿verdad? Con SQL, harías lo siguiente.

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

Pero la vida no es tan simple, porque estamos usando prisma. A pesar de que el equipo de Prisma nos proporciona npx prisma migrate dev --create-only , una forma de jugar con el SQL generado en las migraciones, al momento de escribir, hay un error que nos impide configurar una columna ⁵ generada. Afortunadamente, este no es el final, ¡este es solo otro camino! ¡Todavía podemos lograr el mismo resultado usando desencadenantes!

 -- 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 ();

Hora de consultar

Ahora tiene configurado su columna tsvector , es hora de consultar. Y sí, lo haces con un ts_query . Hay muchas funciones útiles que pueden ayudarlo a convertir su consulta de búsqueda de texto en algo que Postgres comprenderá. phraseto_tsquery puede tomar un diccionario y websearch_to_tsquery se aproxima al comportamiento de algunas herramientas de búsqueda web comunes. Puede elegir su que se ajuste a sus necesidades ³ . También puede hacer una milla adicional y hacer una búsqueda difusa. Al convertir su búsqueda de texto en un tsvector , puede mezclar Lexemes y expresiones regulares para crear un tsquery difuso:

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

Finalmente, su consulta RAW SQL Prisma puede verse así.

 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
}

Tenga en cuenta que ordenamos por ts_rank ⁶ , ¡por lo que podemos proporcionar los mejores resultados coincidentes primero!

Conclusión

En general, puede crear una potente función de búsqueda sin depender del software de terceros, la duplicación de la base de datos y las sintaxis complicadas. Solo necesitas Postgres y SQL, cosas que ya tienes. Estoy bastante seguro de que otros DBMS manejan la búsqueda de texto completo de manera similar. La implementación es simple, directa, flexible y mantenible. Y si está utilizando prisma, puede lograr los mismos resultados con un enfoque menos elegante pero aún funcional.

PD: ¡No olvides indexar con GIN !

Notas al pie

1. tsvector en documentación de Postgres. ↩

2. Abra el problema para el soporte tsvector en el repositorio de Prisma. ↩

3. Funciones y operadores de búsqueda de texto. ↩ ↩ ²

4. Columnas generadas en Postgres. ↩

5. Soporte para columnas generadas. ↩

6. Ranking de resultados de búsqueda ↩