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 (FTS) enabled, you can add search functionality to your application by searching for text within a database column. 

:::info

In Prisma v6, FTS has been [promoted to General Availability on MySQL](/orm/more/upgrade-guides/upgrading-versions/upgrading-to-prisma-6#fulltextsearch). It still remains in Preview for PostgreSQL and requires using the [`fullTextSearchPostgres`](/orm/more/upgrade-guides/upgrading-versions/upgrading-to-prisma-6#full-text-search-on-postgresql) Preview feature flag.

:::

## Enabling full-text search for PostgreSQL

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

1. Update the [`previewFeatures`](/orm/reference/preview-features) block in your schema to include the `fullTextSearchPostgres` preview feature flag:

   ```prisma file=schema.prisma showLineNumbers
   generator client {
     provider        = "prisma-client"
     output          = "./generated"
     previewFeatures = ["fullTextSearchPostgres"]
   }
   ```


2. Generate Prisma Client:

   ```terminal copy
   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'.

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

> **Note**: There currently is a [known issue](https://github.com/prisma/prisma/issues/23627) in the full-text search feature for PostgreSQL. If you observe slow search queries, you can [optimize your query with raw SQL](#full-text-search-with-raw-sql).

## 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:

```ts
// 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? | Explanation                             |
| :-------------------------------------- | :----- | :-------------------------------------- |
| `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                |
| `fox \| cat` | Yes    | The text contains 'fox' or 'cat'        |
| `cat \| pig` | 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](https://www.postgresql.org/docs/12/functions-textsearch.html).

### MySQL

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

```ts
// 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`         | No     | The minus operator cannot be used on its own  (see note below)         |
| `fox dog`      | Yes    | The text contains 'fox' or 'dog'                       |
| `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'        |

> **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"**

| 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](https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html).

## Sorting results by `_relevance`

:::warning

Sorting by relevance is only available for PostgreSQL and MySQL.

:::

In addition to [Prisma Client's default `orderBy` behavior](/orm/reference/prisma-client-reference#orderby), 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:

```ts
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](https://github.com/prisma/prisma/issues/8950) 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. 

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:

```prisma file=schema.prisma showLineNumbers
generator client {
  provider        = "prisma-client"
  output          = "./generated"
}

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':

```ts
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`:

```ts
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](https://github.com/prisma/prisma/issues/23627), you might experience slow search queries. If so, you can optimize your query using [TypedSQL](/orm/prisma-client/using-raw-sql).

### PostgreSQL

With [TypedSQL](/orm/prisma-client/using-raw-sql), you can use PostgreSQL's `to_tsvector` and `to_tsquery` to express your search query.

<TabbedContent code>

  <TabItem value="fullTextSearch.sql">

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

  </TabItem>

  <TabItem value="index.ts">

  ```ts
  import { fullTextSearch } from "@prisma/client/sql"
  
  const term = `cat`
  const result = await prisma.$queryRawTyped(fullTextSearch(term))
  ```

  </TabItem>

</TabbedContent>

> **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:

<TabbedContent code>

  <TabItem value="fullTextSearch.sql">

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

  </TabItem>

  <TabItem value="index.ts">

  ```ts
  //highlight-next-line
  const term = `cat:*`
  const result = await prisma.$queryRawTyped(fullTextSearch(term))
  ```

  </TabItem>

</TabbedContent>

### MySQL

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

<TabbedContent code>

  <TabItem value="fullTextSearch.sql">

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

  </TabItem>

  <TabItem value="index.ts">

  ```ts
  const term = `cat`
  const result = await prisma.$queryRawTyped(fullTextSearch(term))
  ```

  </TabItem>

</TabbedContent>

---

# Custom validation