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.
Enabling full-text search
The full-text search API is currently a Preview feature. To enable this feature, carry out the following steps:
Update the
previewFeatures
block in your schema to include thefullTextSearch
preview feature flag:schema.prisma1generator client {2 provider = "prisma-client-js"3 previewFeatures = ["fullTextSearch"]4}For MySQL, you will also need to include the
fullTextIndex
preview feature flag:schema.prisma1generator client {2 provider = "prisma-client-js"+ previewFeatures = ["fullTextSearch", "fullTextIndex"]4}Generate Prisma Client:
$
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:
Query | Match? | Description |
---|---|---|
fox & dog | Yes | The text contains 'fox' and 'dog' |
dog & fox | Yes | The text contains 'dog' and 'fox' |
dog & cat | No | The text contains 'dog' but not 'cat' |
!cat | Yes | 'cat' is not in the text |
Yes | The text contains 'fox' or 'cat' | |
No | The text doesn't contain 'cat' or 'pig' | |
fox <-> dog | Yes | 'dog' follows 'fox' in the text |
dog <-> fox | No | '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:
Query | Match? | Description |
---|---|---|
+fox +dog | Yes | The text contains 'fox' and 'dog' |
+dog +fox | Yes | The text contains 'dog' and 'fox' |
+dog -cat | Yes | The text contains 'dog' but not 'cat' |
-cat | Yes | 'cat' is not in the text |
fox dog | Yes | The text contains 'fox' or 'dog' |
-cat -pig | No | The text does not contain 'cat' or 'pig' |
quic* | Yes | The text contains a word starting with 'quic' |
quick fox @2 | Yes | 'fox' starts within a 2 word distance of 'quick' |
fox dog @2 | No | 'dog' does not start within a 2 word distance of 'fox' |
"jumps over" | Yes | The text contains the whole phrase 'jumps over' |
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"
Query | Result | Description |
---|---|---|
fox ~cat | Return 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
Sorting by relevance is only available for PostgreSQL and MySQL.
In addition to Prisma'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
1generator client {2 provider = "prisma-client-js"3 previewFeatures = ["fullTextSearch", "fullTextIndex"]4}56model Blog {7 id Int @unique8 content String9 title String1011 @@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.