prisma with fts

完整的，模糊的文字搜索与Postgres和Prisma

在您的软件工程生活中的某个时刻，您可能已经听过“我们需要为此构建搜索功能”一词。大型数据集需要搜索。是的，您可以添加过滤器，但是最终用户将始终想键入lrd of t ，并将戒指的奖学金作为第一个结果。您可能想到了使用一些精美的第三方解决方案，看起来很有希望，您只需将数据库指向其中，并以某种方式处理搜索。但是，如果有一点点的SQL修补，您可以取得相同的结果，甚至更好？

Postgres基础知识用于全文搜索

在文本分析或采矿方面，有一些不同的概念在进行完整的全文搜索之旅之前有用。我们可以从定义文档开始。文档几乎是您可能已经知道的，是用特定语言编写的某种结构的一组句子。 El CantardelMíoCid是一份文件，1930年的电影的中国人也是一份文件。关于Postgres，可以在一个或多个列中找到文档。文档通常被解析为代币，这可能是单词和短语，我们可以从中检索词汇，有意义的文本单位。

Postgres使用词典从中拿出文档，并解析其词汇。有一个默认词典，基于语言的词典，您甚至可以提供自己的词典。事实是，如果您知道自己的文档是德语的，那么您可能想使用德语词典来解析您的词汇。通过使用特定的词典，您可以获得更好的词汇，特定于语言的字母和单词根和词源。

遇到tsvector

tsvector是Postgres ¹中的内置数据类型，代表了独特的归一化词汇的排序列表。如果我们考虑以下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
}

我们可以根据电影的摘录来想到搜索功能。不幸的是，截至今天，Prisma缺乏对tsvector ^2的支持。但是，可以利用@unsupported装饰器，然后冒险进入美丽的原始SQL。

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

我知道，如果您使用的是Prisma，您可能不太喜欢SQL，但是一旦您尝试做一些习惯或达到富有成效的阶段，您就会发现任何ORM限制。

从列生成向量

好的，现在我们为全文搜索提供了一个不错的矢量列。使用简单的SQL脚本，我们可以填充该列：

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

to_tsvector是Postgres ³中众多内置功能之一，它将带您的文档（和一个可选的字典），并将为其创建一个向量。但是，如果提取物更新会发生什么？如果我添加新行怎么办？好吧，我们需要重新计算。对于生成的第⁴列发光的是什么好机会，对吗？使用SQL，您将执行以下操作。

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

但是生活并不是那么简单，因为我们正在使用Prisma。即使Prisma团队为我们提供npx prisma migrate dev --create-only ，这是一种修补迁移中生成的SQL的方式，但在撰写本文时，仍有一个错误使我们无法设置生成的第⁵列。幸运的是，这不是终点，这只是另一个途径！我们仍然可以使用触发器实现相同的结果！

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

是时候查询了

现在，您已经设置了tsvector列，该是时候查询了。是的，您可以使用ts_query做到这一点。有许多方便的功能可以帮助您将文本搜索查询转换为Postgres所理解的内容。 phraseto_tsquery可以采用字典， websearch_to_tsquery近似于某些常见的Web搜索工具的行为。您可以选择适合您需求的人³ 。您也可以加倍努力进行模糊搜索。通过将文本搜索转换为tsvector ，您可以混合词汇和正则表达式以创建模糊的tsquery ：

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

最后，您的原始SQL Prisma查询看起来像这样。

 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
}

请注意，我们通过ts_rank ⁶订购，因此我们可以首先提供最佳的匹配结果！

结论

总而言之，您可以在不依赖第三方软件，数据库重复和复杂语法的情况下构建强大的搜索功能。您只需要Postgres和SQL，即您已经拥有的东西。我很确定其他DBMS以类似的方式处理全文搜索。实施是简单，直接，灵活且可维护的。而且，如果您使用的是Prisma，则可以通过较不优雅但仍然功能齐全的方法获得相同的结果。

PS：别忘了用GIN索引！

脚注

1. Postgres文档中的tsvector 。 ↩

2. 在Prisma的存储库中为tsvector支持的开放问题。 ↩

3. 文本搜索功能和操作员。 ^↩2

4. Postgres中生成的列。 ↩

5. 支持生成的列。 ↩

6. 排名搜索结果↩