PostgreSQL full text search with Prisma

Tuesday, March 5, 2024
Prisma, PostgreSQL

Prisma can be used for full text search with PostgreSQL even though it’s not yet fully supported.

“Prisma Client does not currently support using indexes to speed up full text search. There is an existing issue for this.”

https://www.prisma.io/docs/orm/prisma-client/queries/full-text-search

But we can still..

manually create the search index.

ALTER TABLE todos ADD column searchVector tsvector;
UPDATE todos SET searchVector = to_tsvector('swedish', "title" || ' ' || "description");
CREATE INDEX todos_searchVector ON todos USING gin(searchVector);

Make Prisma client ignore searchVector entirely so npx prisma db push won’t accidentally remove the column.

# schema.prisma
model Todos {
  id                            Int                      @id @default(autoincrement())
  title                         String?                  @db.VarChar(255)
  description                   String?
  searchVector                  Unsupported("tsvector")?
}

Update search index manually when neccessary.

prisma.$queryRaw`UPDATE "todos" SET "searchVector" = to_tsvector('swedish', "title" || ' ' || "description") WHERE "searchVector" IS NULL`,

Query using queryRaw.

const freeText = "the best todo item";
const freeTextQuery = !!freeText
  ? Prisma.sql` AND search_vector @@ websearch_to_tsquery('swedish', ${freeText})`
  : Prisma.empty;
const [totalRows, rows] = await Promise.all([
  prisma.$queryRaw<
    { count: number }[]
  >`SELECT count(id) FROM "todos" WHERE 1=1 ${freeTextQuery}`,
  prisma.$queryRaw<Todo[]>`SELECT * FROM "todos" WHERE 1=1 ${freeTextQuery}`,
]);

Add a comment