Skip to main content

Full-text search

Prisma Client supports full-text search for PostgreSQL databases in versions 2.30.0 and later, and MySQL databases in versions 3.8.0 and later. With full-text search enabled, you can add search functionality to your application by searching for text within a database column.

Note: There currently is a known issue in the full-text search feature. If you observe slow search queries, you can optimize your query with raw SQL.

Enabling full-text search​

The full-text search API is currently a Preview feature. To enable this feature, carry out the following steps:

  1. Update the previewFeatures block in your schema to include the fullTextSearch preview feature flag:

    schema.prisma
    generator client {
      provider        = "prisma-client-js"
      previewFeatures = ["fullTextSearch"]
    }

    For MySQL, you will also need to include the fullTextIndex preview feature flag:

    schema.prisma
    generator client {
      provider        = "prisma-client-js"
      previewFeatures = ["fullTextSearch", "fullTextIndex"]
    }

  2. Generate 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. For example, the following search will return all posts that contain the word 'cat'.

// All posts that contain the word 'cat'.
const result = await prisma.posts.findMany({
  where: {
    body: {
      search: 'cat',
    },
  },
})

Querying the database​

The search field uses the database's native querying capabilities under the hood. This means that the exact query operators available are also database-specific.

PostgreSQL​

The following examples demonstrate the use of the PostgreSQL 'and' (&) and 'or' (|) operators:

// 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',
    },
  },
})

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?Explanation
fox & dogYesThe text contains 'fox' and 'dog'
dog & foxYesThe text contains 'dog' and 'fox'
dog & catNoThe text contains 'dog' but not 'cat'
!catYes'cat' is not in the text
fox | catYesThe text contains 'fox' or 'cat'
cat | pigNoThe text doesn't contain 'cat' or 'pig'
fox <-> dogYes'dog' follows 'fox' in the text
dog <-> foxNo'fox' doesn't follow 'dog' in the text

For the full range of supported operations, see the PostgreSQL full text search documentation.

MySQL​

The following examples demonstrate use of the MySQL 'and' (+) and 'not' (-) operators:

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

// All posts that contain the words 'cat' and not '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',
    },
  },
})

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 -catYesThe text contains 'dog' but not 'cat'
-catNoThe minus operator cannot be used on its own (see note below)
fox dogYesThe text contains 'fox' or 'dog'
quic*YesThe text contains a word starting with 'quic'
quick fox @2Yes'fox' starts within a 2 word distance of 'quick'
fox dog @2No'dog' does not start within a 2 word distance of 'fox'
"jumps over"YesThe text contains the whole phrase 'jumps over'

Note: The - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a boolean-mode search that contains only terms preceded by - returns an empty result. It does not return “all rows except those containing any of the excluded terms.”

MySQL also has >, < and ~ operators for altering the ranking order of search results. As an example, consider the following two records:

1. "The quick brown fox jumps over the lazy dog"

2. "The quick brown fox jumps over the lazy cat"

QueryResultDescription
fox ~catReturn 1. first, then 2.Return all records containing 'fox', but rank records containing 'cat' lower
fox (<cat >dog)Return 1. first, then 2.Return all records containing 'fox', but rank records containing 'cat' lower than rows containing 'dog'

For the full range of supported operations, see the MySQL full text search documentation.

Sorting results by _relevance​

warning

Sorting by relevance is only available for PostgreSQL and MySQL.

In addition to Prisma Client's default orderBy behavior, full-text search also adds sorting by relevance to a given string or strings. As an example, if you wanted to order posts by their relevance to the term 'database' in their title, you could use the following:

const posts = await prisma.post.findMany({
  orderBy: {
    _relevance: {
      fields: ['title'],
      search: 'database',
      sort: 'asc'
    },
  },
})

Adding indexes​

PostgreSQL​

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

MySQL​

For MySQL, it is necessary to add indexes to any columns you search using the @@fulltext argument in the schema.prisma file. To do this, the "fullTextIndex" preview feature must be enabled.

In the following example, one full text index is added to the content field of the Blog model, and another is added to both the content and title fields together:

schema.prisma
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch", "fullTextIndex"]
}

model Blog {
  id      Int    @unique
  content String
  title   String

  @@fulltext([content])
  @@fulltext([content, title])
}

The first index allows searching the content field for occurrences of the word 'cat':

const result = await prisma.blogs.findMany({
  where: {
    content: {
      search: 'cat',
    },
  },
})

The second index allows searching both the content and title fields for occurrences of the word 'cat' in the content and 'food' in the title:

const result = await prisma.blogs.findMany({
  where: {
    content: {
      search: 'cat',
    },
    title: {
      search: 'food',
    },
  },
})

However, if you try to search on title alone, the search will fail with the error "Cannot find a fulltext index to use for the search" and the message code is P2030, because the search requires an index on both fields.

Full-text search with raw SQL​

Full-text search is currently in Preview, and due to a known issue, you might experience slow search queries. If so, you can optimize your query using TypedSQL.

PostgreSQL​

With TypedSQL, you can use PostgreSQL's to_tsvector and to_tsquery to express your search query.

SELECT * FROM "Blog" WHERE to_tsvector('english', "Blog"."content") @@ to_tsquery('english', ${term});

Note: Depending on your language preferences, you may exchange english against another language in the SQL statement.

If you want to include a wildcard in your search term, you can do this as follows:

SELECT * FROM "Blog" WHERE to_tsvector('english', "Blog"."content") @@ to_tsquery('english', ${term});

MySQL​

In MySQL, you can express your search query as follows:

SELECT * FROM Blog WHERE MATCH(content) AGAINST(${term} IN NATURAL LANGUAGE MODE);