# Full-text search (Prisma ORM v6) (/docs/orm/v6/prisma-client/queries/full-text-search)

> For the complete Prisma documentation index, see [llms.txt](https://www.prisma.io/docs/llms.txt). A markdown version of any docs page is available by appending `.md` to its URL.

This page explains how to search for text within a field.

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

> [!NOTE]
> In Prisma v6, FTS has been promoted to General Availability on MySQL. It still remains in Preview for PostgreSQL and requires using the `fullTextSearchPostgres` preview feature flag.

## Enabling full-text search for PostgreSQL [#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`](https://www.prisma.io/docs/orm/v6/reference/preview-features) block in your schema to include the `fullTextSearchPostgres` preview feature flag:

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

2. Generate Prisma Client:

   
     

#### bun

```bash
bunx prisma generate
```

#### pnpm

```bash
pnpm prisma generate
```

#### yarn

```bash
yarn prisma generate
```

#### npm

```bash
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/orm/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 [#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 [#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 [#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` [#sorting-results-by-_relevance]

> [!WARNING]
> Sorting by relevance is only available for PostgreSQL and MySQL.

In addition to [Prisma Client's default `orderBy` behavior](https://www.prisma.io/docs/orm/v6/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 [#adding-indexes]

### PostgreSQL [#postgresql-1]

Prisma Client does not currently support using indexes to speed up full text search. There is an existing [GitHub Issue](https://github.com/prisma/orm/issues/8950) for this.

### MySQL [#mysql-1]

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 title="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-with-raw-sql]

Full-text search is currently in Preview, and due to a [known issue](https://github.com/prisma/orm/issues/23627), you might experience slow search queries. If so, you can optimize your query using [TypedSQL](https://www.prisma.io/docs/orm/v6/prisma-client/using-raw-sql).

### PostgreSQL [#postgresql-2]

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

  

#### fullTextSearch.sql

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

#### index.ts

```ts
import { fullTextSearch } from "@prisma/client/sql";

const term = `cat`;
const result = await prisma.$queryRawTyped(fullTextSearch(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:

  

#### fullTextSearch.sql

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

#### index.ts

```ts
const term = `cat:*`; // [!code highlight]
const result = await prisma.$queryRawTyped(fullTextSearch(term));
```

### MySQL [#mysql-2]

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

  

#### fullTextSearch.sql

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

#### index.ts

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

## Related pages

- [`Aggregation, grouping, and summarizing`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/aggregation-grouping-summarizing): Use Prisma Client to aggregate, group by, count, and select distinct.
- [`Case sensitivity`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/case-sensitivity): How Prisma Client handles case sensitivity when filtering and sorting.
- [`Computed fields`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/computed-fields): This page explains how to use client extensions to add computed fields to Prisma models.
- [`CRUD`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/crud): How to perform CRUD with Prisma Client.
- [`Custom models`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/custom-models): This page explains how to wrap Prisma Client in custom models