prisma with fts

Pesquisa completa e difusa de texto com Postgres e Prisma

Em algum momento da sua vida de engenharia de software, você pode ter ouvido a frase "precisamos criar um recurso de pesquisa para isso". Grandes conjuntos de dados exigem ser pesquisáveis. Você pode adicionar filtros, sim, mas o usuário final sempre desejará digitar lrd of t e obter a comunhão do anel como um primeiro resultado. Você provavelmente pensou em usar uma solução sofisticada de terceiros, que parece promissora, apenas aponta o banco de dados e a pesquisa é tratada de alguma forma. Mas e se, com um pouquinho de mexer no SQL, você puder chegar ao mesmo resultado, ou até melhor?

O básico do Postgres para pesquisa completa de texto

Quando se trata de análise de texto ou mineração, existem alguns conceitos diferentes que são úteis para entender antes de se aventurar na bela jornada de pesquisa completa de texto. Podemos começar definindo um documento . Um documento é praticamente o que você provavelmente já conhece, um conjunto de frases após algum tipo de estrutura, escrita em um idioma específico. El Cantar del Mío Cid é um documento, e a Sinopsis de um filme de 1930 também é um documento. Quando se trata de Postgres, os documentos podem ser encontrados em uma ou muitas outras colunas. Os documentos geralmente são analisados ​​em tokens , que podem ser palavras e frases, das quais podemos recuperar lexemas , unidades significativas de texto.

O Postgres pega documentos e analisa lexemes deles usando dicionários . Existe um dicionário padrão, dicionários baseados em linguagem e você pode até fornecer o seu. A verdade é que, se você sabe que seu documento está em alemão, provavelmente desejará usar o dicionário alemão para analisar seus lexemas. Usando dicionários específicos, você pode obter melhores lexemes específicos para o alfabeto e as raízes das palavras do seu idioma.

Conheça tsvector

tsvector é um tipo de dados embutido no Postgres ¹ , que representa uma lista classificada de lexemes distintos e normalizados. Se considerarmos o seguinte 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
}

Poderíamos pensar em uma função de pesquisa com base no extrato do filme. Infelizmente, a partir de hoje, o Prisma não tem apoio ao tsvector ² . No entanto, pode -se usar o Decorador @unsupported e aventurar -se em um belo SQL cru.

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

Eu sei, se você está usando o prisma, provavelmente não gosta muito do SQL, mas depois de tentar fazer algo um pouco personalizado ou atingir um estágio produtivo, encontrará qualquer ORM limitante.

Gerando vetores de colunas

Ok, agora temos uma boa coluna vetorial para pesquisa completa de texto. Com um script SQL simples, podemos preencher essa coluna:

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

to_tsvector é uma das muitas funções internas no Postgres ³ que levará seu documento (e um dicionário opcional) e criará um vetor para ele. No entanto, o que acontece se o extrato for atualizado? E se eu adicionar uma nova linha? Bem, precisamos recalcular. E que boa oportunidade para as colunas geradas ⁴ brilharem, certo? Com o SQL, você faria o seguinte.

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

Mas a vida não é tão simples, porque estamos usando o prisma. Embora a equipe PRISMA nos forneça com npx prisma migrate dev --create-only , uma maneira de mexer com o SQL gerado nas migrações, no momento da redação deste artigo, há um bug que nos impede de configurar uma coluna ⁵ gerada. Felizmente, este não é o fim, este é apenas mais um caminho! Ainda podemos alcançar o mesmo resultado usando gatilhos!

 -- 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

Agora você tem sua coluna tsvector configurada, é hora de consultar. E sim, você faz isso com um ts_query . Existem muitas funções úteis que podem ajudá -lo a converter sua consulta de pesquisa de texto em algo que o PostGres entenderá. phraseto_tsquery pode levar um dicionário e websearch_to_tsquery se aproxima do comportamento de algumas ferramentas comuns de pesquisa na Web. Você pode escolher o que atende às suas necessidades ³ . Você também pode ir além e fazer pesquisas difusas. Ao converter sua pesquisa de texto em um tsvector , você pode misturar lexemas e expressões regulares para criar um tsquery difuso:

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

Finalmente, sua consulta Raw SQL Prisma pode ficar assim.

 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
}

Observe que pedimos por ts_rank ⁶ , para que possamos fornecer os melhores resultados correspondentes primeiro!

Conclusão

Em suma, você pode criar um poderoso recurso de pesquisa sem depender de software de terceiros, duplicação de banco de dados e sintaxes complicadas. Você só precisa de Postgres e SQL, coisas que você já tem. Tenho certeza de que outros DBMSs lidam com a pesquisa completa de texto de maneira semelhante. A implementação é simples, direta, flexível e sustentável. E se você estiver usando o Prisma, poderá obter os mesmos resultados com uma abordagem menos elegante, mas ainda funcional.

PS: Não se esqueça de indexar com GIN !

Notas de rodapé

1. tsvector na documentação do Postgres. ↩

2. Problema aberto para o suporte tsvector no repositório de Prisma. ↩

3. Funções e operadores de pesquisa de texto. ↩ ↩ ²

4. Colunas geradas no Postgres. ↩

5. Suporte para colunas geradas. ↩

6. Resultados da pesquisa de classificação ↩