Full-text search

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:

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:

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"

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

PostgreSQL

The 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
1generator client {
2  provider        = "prisma-client-js"
3  previewFeatures = ["fullTextSearch", "fullTextIndex"]
4}
5
6model Blog {
7  id      Int    @unique
8  content String
9  title   String
10
11  @@fulltext([content])
12  @@fulltext([content, title])
13}

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 index requires a search on both fields.