prisma with fts

PostgresとPrismaを使用した完全なファジーテキスト検索

ソフトウェアエンジニアリングライフのある時点で、「これの検索機能を構築する必要がある」というフレーズを聞いたことがあるかもしれません。大規模なデータセットは検索可能である必要があります。ええ、フィルターを追加できますが、エンドユーザーは常にlrd of tを入力し、最初の結果としてリングのフェローシップを取得します。おそらく、有望に見える派手なサードパーティのソリューションを使用することを考えていたので、データベースをそれに向けるだけで、検索は何らかの形で処理されます。しかし、少しのSQLのいじくり回しを使用して、同じ結果に到達できる場合、またはさらに良い場合はどうでしょうか。

全文検索のPostgresの基本

テキスト分析やマイニングに関しては、フルテキスト検索の美しい旅に出る前に把握するのに役立ついくつかの異なる概念があります。ドキュメントを定義することから始めることができます。ドキュメントは、あなたがおそらくすでに知っていること、特定の言語で書かれた何らかの構造に従って一連の文です。 El Cantar DelMíoCidは文書であり、1930年の映画のSinopsisも文書です。 Postgresに関しては、ドキュメントは1つまたはさらに多くの列にあります。ドキュメントは通常、トークンに解析されます。これは単語やフレーズであり、そこから語彙素、意味のあるテキスト単位を取得できます。

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 ²のサポートがありません。ただし、 @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 ³の多くの組み込み関数の1つであり、ドキュメント（およびオプションの辞書）を使用し、そのためのベクトルを作成します。ただし、抽出物が更新されるとどうなりますか？新しい行を追加した場合はどうなりますか？まあ、私たちは再計算する必要があります。そして、生成された列^4が輝くためのなんて素晴らしい機会ですよね？ SQLを使用すると、次のことを行います。

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

しかし、私たちはプリスマを使用しているので、人生はそれほど単純ではありません。 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}))

最後に、RAW 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、すでに持っているものが必要です。他のDBMSSが同様の方法で全文検索を処理すると確信しています。実装は、シンプルで、簡単で、柔軟で保守可能です。また、Prismaを使用している場合は、エレガントではないが機能的なアプローチで同じ結果を達成できます。

PS： GINとのインデックスを忘れないでください！

脚注

1. Postgresドキュメントのtsvector 。 ↩

2. Prismaのリポジトリにおけるtsvectorサポートの開かれた問題。 ↩

3. テキスト検索関数と演算子。 ^↩2

4. Postgresで生成された列。 ↩

5. 生成された列のサポート。 ↩

6. ランキング検索結果↩