prisma with fts

Vollständige, unscharfe Textsuche mit Postgres und Prisma

Irgendwann in Ihrem Software -Engineering -Leben haben Sie den Ausdruck "Wir müssen eine Suchfunktion dafür erstellen" gehört. Große Datensätze erfordern suchbar. Sie können Filter dazu hinzufügen, ja, aber der Endbenutzer möchte immer lrd of t eingeben und das Stipendium des Rings als erstes Ergebnis erhalten. Sie haben wahrscheinlich darüber nachgedacht, eine ausgefallene Lösung von Drittanbietern zu verwenden, die vielversprechend aussieht. Sie richten nur die Datenbank darauf und die Suche wird irgendwie behandelt. Aber was ist, wenn Sie mit einem kleinen Stück SQL -Basteln das gleiche Ergebnis oder noch besser erreichen können?

Die Postgres -Grundlagen für die Volltextensuche

Wenn es um Textanalyse oder Bergbau geht, gibt es einige verschiedene Konzepte, die nützlich zu verstehen sind, bevor es sich auf die schöne Reise der Volltext -Suche wagt. Wir können zunächst ein Dokument definieren. Ein Dokument ist so ziemlich das, was Sie wahrscheinlich bereits kennen, eine Reihe von Sätzen, die einer Art Struktur folgen, die in einer bestimmten Sprache geschrieben wurden. El Cantar del Mío CID ist ein Dokument, und die Sinopsis eines Films von 1930 ist ebenfalls ein Dokument. Wenn es um Postgres geht, finden Sie Dokumente in einer oder vielen weiteren Spalten. Dokumente werden normalerweise in Token analysiert, die Wörter und Phrasen sein können, aus denen wir Lexeme , bedeutungsvolle Texteinheiten abrufen können.

Postgres nimmt Dokumente und Parsen Lexemes mit Wörterbüchern von ihnen. Es gibt ein Standard -Wörterbuch, sprachbasierte Wörterbücher und Sie können sogar Ihre eigenen zur Verfügung stellen. Die Wahrheit ist, wenn Sie wissen, dass Ihr Dokument in Deutsch ist, möchten Sie wahrscheinlich das deutsche Wörterbuch verwenden, um Ihre Lexeme zu analysieren. Durch die Verwendung bestimmter Wörterbücher können Sie bessere Lexemes für das Alphabet und die Wortwurzeln und Etymologien Ihrer Sprache erhalten.

tsvector treffen

tsvector ist ein integrierter Datentyp in Postgres ¹ , der eine sortierte Liste unterschiedlicher, normalisierter Lexeme darstellt. Wenn wir das folgende Prisma -Modell betrachten

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

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

Wir könnten uns eine Suchfunktion vorstellen, die auf dem Auszug des Films basiert. Leider mangelt es in Prisma bis heute keine Unterstützung für tsvector ² . Man kann jedoch den @unsupported Decorator verwenden und sich in den wunderschönen rohen SQL wagen.

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

Ich weiß, wenn Sie Prisma verwenden, mögen Sie SQL wahrscheinlich nicht sehr, aber sobald Sie versuchen, etwas benutzerdefiniert zu machen oder eine produktive Stufe zu treffen, werden Sie eine ORM -Begrenzung finden.

Vektoren aus Spalten erzeugen

Okay, jetzt haben wir uns eine schöne Vektorspalte für die Volltextsuche bekommen. Mit einem einfachen SQL -Skript können wir diese Spalte bevölkern:

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

to_tsvector ist eine der vielen integrierten Funktionen in Postgres ³ , die Ihr Dokument (und ein optionales Wörterbuch) aufnehmen und einen Vektor dafür erstellen. Was passiert jedoch, wenn der Auszug aktualisiert wird? Was ist, wenn ich eine neue Zeile hinzufüge? Nun, wir müssen neu berechnen. Und was für eine nette Gelegenheit für generierte Spalten ^4, um zu glänzen, oder? Mit SQL würden Sie Folgendes tun.

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

Aber das Leben ist nicht so einfach, weil wir Prisma verwenden. Obwohl das Prisma-Team uns npx prisma migrate dev --create-only , eine Möglichkeit zum Basteln an der in Migrationen generierten SQL zum Zeitpunkt des Schreibens zur Verfügung stellt, gibt es einen Fehler, der uns daran hindert, eine generierte Spalte ⁵ einzurichten. Zum Glück ist dies nicht das Ende, dies ist nur ein weiterer Weg! Wir können immer noch das gleiche Ergebnis mit Triggern erzielen!

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

Zeit zu fragen

Sie haben jetzt Ihre tsvector -Spalte eingerichtet, es ist Zeit zu fragen. Und ja, du machst es mit einem ts_query . Es gibt viele praktische Funktionen, die Ihnen helfen können, Ihre Textsuche in etwas zu konvertieren. phraseto_tsquery kann ein Wörterbuch aufnehmen, und websearch_to_tsquery nähert sich dem Verhalten einiger gängiger Web -Such -Tools. Sie können Ihren derjenigen auswählen, die Ihren Anforderungen entsprechen ³ . Sie können auch die Extrameile gehen und Fuzzy -Suche durchführen. Durch die Konvertierung Ihrer Textsuche in einen tsvector können Sie Lexeme und reguläre Ausdrücke mischen, um eine Fuzzy tsquery zu erstellen:

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

Schließlich kann Ihre rohe SQL -Prisma -Abfrage so aussehen.

 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
}

Beachten Sie, dass wir bei ts_rank ⁶ bestellen, damit wir zuerst die besten passenden Ergebnisse liefern können!

Abschluss

Insgesamt können Sie eine leistungsstarke Suchfunktion erstellen, ohne sich auf Software von Drittanbietern, Datenbank-Duplikation und komplizierte Syntaxe zu verlassen. Sie brauchen nur Postgres und SQL, Dinge, die Sie bereits haben. Ich bin mir ziemlich sicher, dass andere DBMSs auf ähnliche Weise die Volltext -Suche verarbeiten. Die Implementierung ist einfach, unkompliziert, flexibel und wartbar. Und wenn Sie Prisma verwenden, können Sie die gleichen Ergebnisse mit einem weniger eleganten, aber dennoch funktionalen Ansatz erzielen.

PS: Vergessen Sie nicht, mit GIN zu indexieren!

Fußnoten

1. tsvector in Postgres -Dokumentation. ↩

2. Offenes Problem für die Unterstützung tsvector im Prisma -Repository. ↩

3. Textsuchfunktionen und -operatoren. ↩ ↩ ²

4. Erzeugte Spalten in Postgres. ↩

5. Unterstützung für generierte Spalten. ↩

6. Ranking Suchergebnisse ↩