Full-Text Search

The Prisma Client supports a full-text search API for Postgres databases. With full-text search enabled, you can add search functionality to your application by searching for text within a database column.

Note: Full text search can only be used in a Postgres database

The full-text Search API is still in Preview. You can enable fullTextSearch in your Prisma Schema:

  1. Update the previewFeatures block in your schema:

    generator client {
    provider = "prisma-client-js"
    previewFeatures = ["fullTextSearch"]
    }
  2. Generate the Prisma Client:

    $npx prisma generate

After you regenerate your client, a new search field will be available on any String fields created on your models.

Example of new search field

// All posts that contain the words cat or dog.
const result = await prisma.posts.findMany({
where: {
body: {
search: 'cat | dog',
},
},
})
// All drafts that contain the words cat and dog.
const result = await prisma.posts.findMany({
where: {
status: 'Draft',
body: {
search: 'cat & dog',
},
},
})

Query Format

The search field uses the database's native querying capabilities under the hood. This means that the Prisma Client supports what the database supports.

To get a sense of how the query format works, consider the following text:

"The quick brown fox jumps over the lazy dog"

Here's how the following queries would match that text:

QueryMatch?Description
fox & dogYesThe text contains fox and dog
dog & foxYesThe text contains dog and fox
dog & catNoThe text doesn't contain cat
!catYesThere is not cat in the text
fox | catYesThe text contains fox or cat
cat | pigNoThe text doesn't contain cat or pig
fox <-> dogYesdog follows fox in the text
dog <-> foxNodog doesn't follow fox in the text

Adding Indexes

To speed up your full-text queries, you should add an index to your database. Prisma Migrate currently doesn't support adding search indices, but you can easily add one yourself.

CREATE INDEX post_body_index ON posts USING GIN (body);

You can continue using Prisma Migrate as you were before, it will ignore indexes that it doesn't know about.

Limitations

  • The search field is only supported in PostgreSQL
  • You'll need to manage indexes yourself.
Edit this page on GitHub