prisma with fts

Pencarian teks penuh dan fuzzy dengan postgres dan prisma

Pada titik tertentu dalam kehidupan rekayasa perangkat lunak Anda, Anda mungkin pernah mendengar frasa "Kami perlu membangun fitur pencarian untuk ini". Dataset besar menuntut agar dapat dicari. Anda dapat menambahkan filter ke dalamnya, ya, tetapi pengguna akhir akan selalu ingin mengetik lrd of t dan mendapatkan persekutuan cincin sebagai hasil pertama. Anda mungkin berpikir untuk menggunakan beberapa solusi pihak ketiga mewah, yang terlihat menjanjikan, Anda hanya mengarahkan database ke sana dan pencarian ditangani entah bagaimana. Tetapi bagaimana jika, dengan sedikit bermain -main SQL, Anda bisa mendapatkan hasil yang sama, atau bahkan lebih baik?

Dasar -dasar postgres untuk pencarian teks lengkap

Ketika datang ke analisis atau penambangan teks, ada beberapa konsep berbeda yang berguna untuk dipahami sebelum bertualang dalam perjalanan yang indah dari pencarian teks lengkap. Kita dapat mulai dengan mendefinisikan dokumen . Sebuah dokumen adalah apa yang mungkin sudah Anda ketahui, satu set kalimat mengikuti semacam struktur, yang ditulis dalam bahasa tertentu. El Cantar del Mío Cid adalah dokumen, dan Sinopsis dari film 1930 juga merupakan dokumen. Ketika datang ke postgres, dokumen dapat ditemukan di satu atau banyak kolom lagi. Dokumen biasanya diuraikan menjadi token , yang bisa menjadi kata -kata dan frasa, dari mana kita dapat mengambil leksem , unit teks yang bermakna.

Postgres mengambil dokumen dan parse leksem dari mereka menggunakan kamus . Ada kamus default, kamus berbasis bahasa dan Anda bahkan dapat menyediakan sendiri. Kebenarannya adalah, jika Anda tahu dokumen Anda dalam bahasa Jerman, Anda mungkin ingin menggunakan kamus Jerman untuk menguraikan leksem Anda. Dengan menggunakan kamus tertentu, Anda bisa mendapatkan leksem yang lebih baik khusus untuk alfabet dan akar kata dan etimologi bahasa Anda.

Temui tsvector

tsvector adalah tipe data bawaan di Postgres ¹ , yang mewakili daftar leksem yang dinormalisasi yang diurutkan. Jika kami mempertimbangkan model prisma berikut

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

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

Kita bisa memikirkan fungsi pencarian berdasarkan ekstrak film. Sayangnya, hingga hari ini, Prisma tidak memiliki dukungan untuk tsvector ² . Namun, orang dapat memanfaatkan dekorator @unsupported , dan menjelajah ke SQL mentah yang indah.

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

Saya tahu, jika Anda menggunakan prisma, Anda mungkin tidak terlalu menyukai SQL, tetapi begitu Anda mencoba melakukan sesuatu yang sedikit adil atau mencapai tahap produktif, Anda akan menemukan ORM yang membatasi.

Menghasilkan vektor dari kolom

Oke, sekarang kita mendapatkan kolom vektor yang bagus untuk pencarian teks lengkap. Dengan skrip SQL sederhana, kita dapat mengisi kolom itu:

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

to_tsvector adalah salah satu dari banyak fungsi bawaan di Postgres ³ yang akan mengambil dokumen Anda (dan kamus opsional) dan akan membuat vektor untuk itu. Namun, apa yang terjadi jika ekstrak diperbarui? Bagaimana jika saya menambahkan baris baru? Nah, kita perlu menghitung ulang. Dan kesempatan yang bagus untuk kolom ⁴ yang dihasilkan untuk bersinar, bukan? Dengan SQL, Anda akan melakukan hal berikut.

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

Tetapi hidup tidak sesederhana itu, karena kita menggunakan prisma. Meskipun tim PRISMA memberi kita npx prisma migrate dev --create-only , cara bermain-main dengan SQL yang dihasilkan dalam migrasi, pada saat penulisan, ada bug yang mencegah kita dari menyiapkan kolom ⁵ yang dihasilkan. Untungnya, ini bukan akhir, ini hanyalah jalan lain! Kami masih dapat mencapai hasil yang sama menggunakan pemicu!

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

Saatnya bertanya

Anda sekarang mengatur kolom tsvector Anda, saatnya untuk meminta. Dan ya, Anda melakukannya dengan ts_query . Ada banyak fungsi praktis yang dapat membantu Anda mengubah kueri pencarian teks Anda menjadi sesuatu yang akan dipahami Postgres. phraseto_tsquery dapat mengambil kamus dan websearch_to_tsquery mendekati perilaku beberapa alat pencarian web umum. Anda dapat memilih yang sesuai dengan kebutuhan Anda ³ . Anda juga bisa bekerja ekstra dan melakukan pencarian fuzzy. Dengan mengubah pencarian teks Anda menjadi tsvector , Anda dapat mencampur leksem dan ekspresi reguler untuk membuat tsquery fuzzy:

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

Akhirnya, kueri SQL Prisma mentah Anda bisa terlihat seperti ini.

 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
}

Perhatikan bahwa kami memesan oleh ts_rank ⁶ , sehingga kami dapat memberikan hasil yang cocok terlebih dahulu!

Kesimpulan

Secara keseluruhan, Anda dapat membangun fitur pencarian yang kuat tanpa mengandalkan perangkat lunak pihak ketiga, duplikasi basis data, dan sintaksis yang rumit. Anda hanya perlu Postgres dan SQL, hal -hal yang sudah Anda miliki. Saya cukup yakin bahwa DBMS lain menangani pencarian teks lengkap dengan cara yang sama. Implementasinya sederhana, langsung, fleksibel dan dapat dipelihara. Dan jika Anda menggunakan Prisma, Anda dapat mencapai hasil yang sama dengan pendekatan yang kurang elegan tetapi masih fungsional.

PS: Jangan lupa untuk indeks dengan GIN !

Catatan kaki

1. tsvector dalam dokumentasi Postgres. ↩

2. Masalah terbuka untuk dukungan tsvector di repositori Prisma. ↩

3. Fungsi dan Operator Pencarian Teks. ↩ ↩ ²

4. Kolom yang dihasilkan di postgres. ↩

5. Dukungan untuk kolom yang dihasilkan. ↩

6. Peringkat hasil pencarian ↩