# Indexes (/docs/orm/prisma-schema/data-model/indexes)

Location: ORM > Prisma Schema > Data Model > Indexes

Prisma ORM allows configuration of database indexes, unique constraints and primary key constraints. Full text indexes in MySQL and MongoDB are available through the `fullTextIndex` preview feature using the `@@fulltext` attribute.

Index configuration [#index-configuration]

You can configure indexes, unique constraints, and primary key constraints with the following attribute arguments:

* The [`length` argument](#configuring-the-length-of-indexes-with-length-mysql) allows you to specify a maximum length for the subpart of the value to be indexed on `String` and `Bytes` types
  * Available on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes
  * MySQL only

* The [`sort` argument](#configuring-the-index-sort-order-with-sort) allows you to specify the order that the entries of the constraint or index are stored in the database
  * Available on the `@unique`, `@@unique` and `@@index` attributes in all databases, and on the `@id` and `@@id` attributes in SQL Server

* The [`type` argument](#configuring-the-access-type-of-indexes-with-type-postgresql) allows you to support index access methods other than PostgreSQL's default `BTree` access method
  * Available on the `@@index` attribute
  * PostgreSQL only
  * Supported index access methods: `Hash`, `Gist`, `Gin`, `SpGist` and `Brin`

* The [`clustered` argument](#configuring-if-indexes-are-clustered-or-non-clustered-with-clustered-sql-server) allows you to configure whether a constraint or index is clustered or non-clustered
  * Available on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes
  * SQL Server only

* The [`map` argument](#configuring-the-name-of-indexes-with-map) allows you to specify a custom name for the index or constraint in the underlying database
  * Available on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes
  * Supported in all databases

Configuring the length of indexes with length (MySQL) [#configuring-the-length-of-indexes-with-length-mysql]

The `length` argument is specific to MySQL and allows you to define indexes and constraints on columns of `String` and `Byte` types. For these types, MySQL requires you to specify a maximum length for the subpart of the value to be indexed in cases where the full value would exceed MySQL's limits for index sizes. See [the MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html) for more details.

The `length` argument is available on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes.

As an example, the following data model declares an `id` field with a maximum length of 3000 characters:

```prisma title="schema.prisma" showLineNumbers
model Id {
  id String @id @db.VarChar(3000)
}
```

This is not valid in MySQL because it exceeds MySQL's index storage limit and therefore Prisma ORM rejects the data model. The generated SQL would be rejected by the database.

```sql
CREATE TABLE `Id` (
  `id` VARCHAR(3000) PRIMARY KEY
)
```

The `length` argument allows you to specify that only a subpart of the `id` value represents the primary key. In the example below, the first 100 characters are used:

```prisma title="schema.prisma" showLineNumbers
model Id {
  id String @id(length: 100) @db.VarChar(3000)
}
```

Prisma Migrate is able to create constraints and indexes with the `length` argument if specified in your data model. This means that you can create indexes and constraints on values of Prisma schema type `Byte` and `String`. If you don't specify the argument the index is treated as covering the full value as before.

Introspection will fetch these limits where they are present in your existing database. This allows Prisma ORM to support indexes and constraints that were previously suppressed and results in better support of MySQL databases utilizing this feature.

The `length` argument can also be used on compound primary keys, using the `@@id` attribute, as in the example below:

```prisma title="schema.prisma" showLineNumbers
model CompoundId {
  id_1 String @db.VarChar(3000)
  id_2 String @db.VarChar(3000)

  @@id([id_1(length: 100), id_2(length: 10)])
}
```

A similar syntax can be used for the `@@unique` and `@@index` attributes.

Configuring the index sort order with sort [#configuring-the-index-sort-order-with-sort]

The `sort` argument allows you to specify the order that the entries of the index or constraint are stored in the database. This can have an effect on whether the database is able to use an index for specific queries. The behavior and support varies by database:

* In MySQL/MariaDB, you can specify sort order (`ASC`/`DESC`) directly in unique constraints and indexes
* In PostgreSQL, sort order can only be specified on indexes, not on unique constraints
* In SQL Server, sort order is supported on all constraints and indexes including `@id` and `@@id`

For example, in MySQL/MariaDB, the following table using a descending unique constraint:

```sql
CREATE TABLE `Unique` (
  `unique` INT,
  CONSTRAINT `Unique_unique_key` UNIQUE (`unique` DESC)
)
```

would be introspected as

```prisma title="schema.prisma" showLineNumbers
model Unique {
  unique Int @unique(sort: Desc)
}
```

Note that in PostgreSQL, while you cannot specify sort order on unique constraints directly, you can create a unique index with a sort order that will enforce uniqueness:

```sql
-- PostgreSQL approach
CREATE UNIQUE INDEX "unique_index_desc" ON "Unique" ("unique" DESC);
```

The `sort` argument can also be used on compound indexes:

```prisma title="schema.prisma" showLineNumbers
model CompoundUnique {
  unique_1 Int
  unique_2 Int

  @@unique([unique_1(sort: Desc), unique_2])
}
```

Example: using sort and length together [#example-using-sort-and-length-together]

The following example demonstrates the use of the `sort` and `length` arguments to configure indexes and constraints for a `Post` model:

```prisma title="schema.prisma" showLineNumbers
model Post {
  title      String   @db.VarChar(300)
  abstract   String   @db.VarChar(3000)
  slug       String   @unique(sort: Desc, length: 42) @db.VarChar(3000)
  author     String
  created_at DateTime

  @@id([title(length: 100, sort: Desc), abstract(length: 10)])
  @@index([author, created_at(sort: Desc)])
}
```

Configuring the access type of indexes with type (PostgreSQL) [#configuring-the-access-type-of-indexes-with-type-postgresql]

The `type` argument is available for configuring the index type in PostgreSQL with the `@@index` attribute. The index access methods available are `Hash`, `Gist`, `Gin`, `SpGist` and `Brin`, as well as the default `BTree` index access method.

Hash [#hash]

The `Hash` type will store the index data in a format that is much faster to search and insert, and that will use less disk space. However, only the `=` and `<>` comparisons can use the index, so other comparison operators such as `<` and `>` will be much slower with `Hash` than when using the default `BTree` type.

As an example, the following model adds an index with a `type` of `Hash` to the `value` field:

```prisma title="schema.prisma" showLineNumbers
model Example {
  id    Int @id
  value Int

  @@index([value], type: Hash)
}
```

This translates to the following SQL commands:

```sql
CREATE TABLE "Example" (
  id INT PRIMARY KEY,
  value INT NOT NULL
);

CREATE INDEX "Example_value_idx" ON "Example" USING HASH (value);
```

Generalized Inverted Index (GIN) [#generalized-inverted-index-gin]

The GIN index stores composite values, such as arrays or `JsonB` data. This is useful for speeding up querying whether one object is part of another object. It is commonly used for full-text searches.

An indexed field can define the operator class, which defines the operators handled by the index.

> [!WARNING]
> Indexes using a function (such as `to_tsvector`) to determine the indexed value are not yet supported by Prisma ORM. Indexes defined in this way will not be visible with `prisma db pull`.

As an example, the following model adds a `Gin` index to the `value` field, with `JsonbPathOps` as the class of operators allowed to use the index:

```prisma title="schema.prisma" showLineNumbers
model Example {
  id    Int  @id
  value Json
  //    ^ field type matching the operator class

  @@index([value(ops: JsonbPathOps)], type: Gin)
  //                  ^ operator class      ^ index type
}
```

This translates to the following SQL commands:

```sql
CREATE TABLE "Example" (
  id INT PRIMARY KEY,
  value JSONB NOT NULL
);

CREATE INDEX "Example_value_idx" ON "Example" USING GIN (value jsonb_path_ops);
```

As part of the `JsonbPathOps` the `@>` operator is handled by the index, speeding up queries such as `value @> '{"foo": 2}'`.

Supported Operator Classes for GIN [#supported-operator-classes-for-gin]

Prisma ORM generally supports operator classes provided by PostgreSQL in versions 10 and later. If the operator class requires the field type to be of a type Prisma ORM does not yet support, using the `raw` function with a string input allows you to use these operator classes without validation.

The default operator class (marked with ✅) can be omitted from the index definition.

| Operator class | Allowed field type (native types) | Default | Other                         |
| -------------- | --------------------------------- | ------- | ----------------------------- |
| `ArrayOps`     | Any array                         | ✅       | Also available in CockroachDB |
| `JsonbOps`     | `Json` (`@db.JsonB`)              | ✅       | Also available in CockroachDB |
| `JsonbPathOps` | `Json` (`@db.JsonB`)              |         |                               |
| `raw("other")` |                                   |         |                               |

Read more about built-in operator classes in the [official PostgreSQL documentation](https://www.postgresql.org/docs/14/gin-builtin-opclasses.html).

CockroachDB [#cockroachdb]

GIN and BTree are the only index types supported by CockroachDB. The operator classes marked to work with CockroachDB are the only ones allowed on that database and supported by Prisma ORM. The operator class cannot be defined in the Prisma Schema Language: the `ops` argument is not necessary or allowed on CockroachDB.

Generalized Search Tree (GiST) [#generalized-search-tree-gist]

The GiST index type is used for implementing indexing schemes for user-defined types. By default there are not many direct uses for GiST indexes, but for example the B-Tree index type is built using a GiST index.

As an example, the following model adds a `Gist` index to the `value` field with `InetOps` as the operators that will be using the index:

```prisma title="schema.prisma" showLineNumbers
model Example {
  id    Int    @id
  value String @db.Inet
  //           ^ native type matching the operator class
  //                                   ^ index type
  //                  ^ operator class

  @@index([value(ops: InetOps)], type: Gist)
}
```

This translates to the following SQL commands:

```sql
CREATE TABLE "Example" (
  id INT PRIMARY KEY,
  value INET NOT NULL
);

CREATE INDEX "Example_value_idx" ON "Example" USING GIST (value inet_ops);
```

Queries comparing IP addresses, such as `value > '10.0.0.2'`, will use the index.

Supported Operator Classes for GiST [#supported-operator-classes-for-gist]

Prisma ORM generally supports operator classes provided by PostgreSQL in versions 10 and later. If the operator class requires the field type to be of a type Prisma ORM does not yet support, using the `raw` function with a string input allows you to use these operator classes without validation.

| Operator class | Allowed field type (allowed native types) |
| -------------- | ----------------------------------------- |
| `InetOps`      | `String` (`@db.Inet`)                     |
| `raw("other")` |                                           |

Read more about built-in operator classes in the [official PostgreSQL documentation](https://www.postgresql.org/docs/14/gist-builtin-opclasses.html).

Space-Partitioned GiST (SP-GiST) [#space-partitioned-gist-sp-gist]

The SP-GiST index is a good choice for many different non-balanced data structures. If the query matches the partitioning rule, it can be very fast.

As with GiST, SP-GiST is important as a building block for user-defined types, allowing implementation of custom search operators directly with the database.

As an example, the following model adds a `SpGist` index to the `value` field with `TextOps` as the operators using the index:

```prisma title="schema.prisma" showLineNumbers
model Example {
  id    Int    @id
  value String
  //    ^ field type matching the operator class

  @@index([value], type: SpGist)
  //                     ^ index type
  //       ^ using the default ops: TextOps
}
```

This translates to the following SQL commands:

```sql
CREATE TABLE "Example" (
  id INT PRIMARY KEY,
  value TEXT NOT NULL
);

CREATE INDEX "Example_value_idx" ON "Example" USING SPGIST (value);
```

Queries such as `value LIKE 'something%'` will be sped up by the index.

Supported Operator Classes for SP-GiST [#supported-operator-classes-for-sp-gist]

Prisma ORM generally supports operator classes provided by PostgreSQL in versions 10 and later. If the operator class requires the field type to be of a type Prisma ORM does not yet support, using the `raw` function with a string input allows you to use these operator classes without validation.

The default operator class (marked with ✅) can be omitted from the index definition.

| Operator class | Allowed field type (native types)    | Default | Supported PostgreSQL versions |
| -------------- | ------------------------------------ | ------- | ----------------------------- |
| `InetOps`      | `String` (`@db.Inet`)                | ✅       | 10+                           |
| `TextOps`      | `String` (`@db.Text`, `@db.VarChar`) | ✅       |                               |
| `raw("other")` |                                      |         |                               |

Read more about built-in operator classes from [official PostgreSQL documentation](https://www.postgresql.org/docs/14/spgist-builtin-opclasses.html).

Block Range Index (BRIN) [#block-range-index-brin]

The BRIN index type is useful if you have lots of data that does not change after it is inserted, such as date and time values. If your data is a good fit for the index, it can store large datasets in a minimal space.

As an example, the following model adds a `Brin` index to the `value` field with `Int4BloomOps` as the operators that will be using the index:

```prisma title="schema.prisma" showLineNumbers
model Example {
  id    Int @id
  value Int
  //    ^ field type matching the operator class

  @@index([value(ops: Int4BloomOps)], type: Brin)
  //                  ^ operator class      ^ index type
}
```

This translates to the following SQL commands:

```sql
CREATE TABLE "Example" (
  id INT PRIMARY KEY,
  value INT4 NOT NULL
);

CREATE INDEX "Example_value_idx" ON "Example" USING BRIN (value int4_bloom_ops);
```

Queries like `value = 2` will now use the index, which uses a fraction of the space used by the `BTree` or `Hash` indexes.

Supported Operator Classes for BRIN [#supported-operator-classes-for-brin]

Prisma ORM generally supports operator classes provided by PostgreSQL in versions 10 and later, and some supported operators are only available from PostgreSQL versions 14 and later. If the operator class requires the field type to be of a type Prisma ORM does not yet support, using the `raw` function with a string input allows you to use these operator classes without validation.

The default operator class (marked with ✅) can be omitted from the index definition.

| Operator class              | Allowed field type (native types)    | Default | Supported PostgreSQL versions |
| --------------------------- | ------------------------------------ | ------- | ----------------------------- |
| `BitMinMaxOps`              | `String` (`@db.Bit`)                 | ✅       |                               |
| `VarBitMinMaxOps`           | `String` (`@db.VarBit`)              | ✅       |                               |
| `BpcharBloomOps`            | `String` (`@db.Char`)                |         | 14+                           |
| `BpcharMinMaxOps`           | `String` (`@db.Char`)                | ✅       |                               |
| `ByteaBloomOps`             | `Bytes` (`@db.Bytea`)                |         | 14+                           |
| `ByteaMinMaxOps`            | `Bytes` (`@db.Bytea`)                | ✅       |                               |
| `DateBloomOps`              | `DateTime` (`@db.Date`)              |         | 14+                           |
| `DateMinMaxOps`             | `DateTime` (`@db.Date`)              | ✅       |                               |
| `DateMinMaxMultiOps`        | `DateTime` (`@db.Date`)              |         | 14+                           |
| `Float4BloomOps`            | `Float` (`@db.Real`)                 |         | 14+                           |
| `Float4MinMaxOps`           | `Float` (`@db.Real`)                 | ✅       |                               |
| `Float4MinMaxMultiOps`      | `Float` (`@db.Real`)                 |         | 14+                           |
| `Float8BloomOps`            | `Float` (`@db.DoublePrecision`)      |         | 14+                           |
| `Float8MinMaxOps`           | `Float` (`@db.DoublePrecision`)      | ✅       |                               |
| `Float8MinMaxMultiOps`      | `Float` (`@db.DoublePrecision`)      |         | 14+                           |
| `InetInclusionOps`          | `String` (`@db.Inet`)                | ✅       | 14+                           |
| `InetBloomOps`              | `String` (`@db.Inet`)                |         | 14+                           |
| `InetMinMaxOps`             | `String` (`@db.Inet`)                |         |                               |
| `InetMinMaxMultiOps`        | `String` (`@db.Inet`)                |         | 14+                           |
| `Int2BloomOps`              | `Int` (`@db.SmallInt`)               |         | 14+                           |
| `Int2MinMaxOps`             | `Int` (`@db.SmallInt`)               | ✅       |                               |
| `Int2MinMaxMultiOps`        | `Int` (`@db.SmallInt`)               |         | 14+                           |
| `Int4BloomOps`              | `Int` (`@db.Integer`)                |         | 14+                           |
| `Int4MinMaxOps`             | `Int` (`@db.Integer`)                | ✅       |                               |
| `Int4MinMaxMultiOps`        | `Int` (`@db.Integer`)                |         | 14+                           |
| `Int8BloomOps`              | `BigInt` (`@db.BigInt`)              |         | 14+                           |
| `Int8MinMaxOps`             | `BigInt` (`@db.BigInt`)              | ✅       |                               |
| `Int8MinMaxMultiOps`        | `BigInt` (`@db.BigInt`)              |         | 14+                           |
| `NumericBloomOps`           | `Decimal` (`@db.Decimal`)            |         | 14+                           |
| `NumericMinMaxOps`          | `Decimal` (`@db.Decimal`)            | ✅       |                               |
| `NumericMinMaxMultiOps`     | `Decimal` (`@db.Decimal`)            |         | 14+                           |
| `OidBloomOps`               | `Int` (`@db.Oid`)                    |         | 14+                           |
| `OidMinMaxOps`              | `Int` (`@db.Oid`)                    | ✅       |                               |
| `OidMinMaxMultiOps`         | `Int` (`@db.Oid`)                    |         | 14+                           |
| `TextBloomOps`              | `String` (`@db.Text`, `@db.VarChar`) |         | 14+                           |
| `TextMinMaxOps`             | `String` (`@db.Text`, `@db.VarChar`) | ✅       |                               |
| `TextMinMaxMultiOps`        | `String` (`@db.Text`, `@db.VarChar`) |         | 14+                           |
| `TimestampBloomOps`         | `DateTime` (`@db.Timestamp`)         |         | 14+                           |
| `TimestampMinMaxOps`        | `DateTime` (`@db.Timestamp`)         | ✅       |                               |
| `TimestampMinMaxMultiOps`   | `DateTime` (`@db.Timestamp`)         |         | 14+                           |
| `TimestampTzBloomOps`       | `DateTime` (`@db.Timestamptz`)       |         | 14+                           |
| `TimestampTzMinMaxOps`      | `DateTime` (`@db.Timestamptz`)       | ✅       |                               |
| `TimestampTzMinMaxMultiOps` | `DateTime` (`@db.Timestamptz`)       |         | 14+                           |
| `TimeBloomOps`              | `DateTime` (`@db.Time`)              |         | 14+                           |
| `TimeMinMaxOps`             | `DateTime` (`@db.Time`)              | ✅       |                               |
| `TimeMinMaxMultiOps`        | `DateTime` (`@db.Time`)              |         | 14+                           |
| `TimeTzBloomOps`            | `DateTime` (`@db.Timetz`)            |         | 14+                           |
| `TimeTzMinMaxOps`           | `DateTime` (`@db.Timetz`)            | ✅       |                               |
| `TimeTzMinMaxMultiOps`      | `DateTime` (`@db.Timetz`)            |         | 14+                           |
| `UuidBloomOps`              | `String` (`@db.Uuid`)                |         | 14+                           |
| `UuidMinMaxOps`             | `String` (`@db.Uuid`)                | ✅       |                               |
| `UuidMinMaxMultiOps`        | `String` (`@db.Uuid`)                |         | 14+                           |
| `raw("other")`              |                                      |         |                               |

Read more about built-in operator classes in the [official PostgreSQL documentation](https://www.postgresql.org/docs/14/brin-builtin-opclasses.html).

Configuring if indexes are clustered or non-clustered with clustered (SQL Server) [#configuring-if-indexes-are-clustered-or-non-clustered-with-clustered-sql-server]

The `clustered` argument is available to configure (non)clustered indexes in SQL Server. It can be used on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes.

As an example, the following model configures the `@id` to be non-clustered (instead of the clustered default):

```prisma title="schema.prisma" showLineNumbers
model Example {
  id    Int @id(clustered: false)
  value Int
}
```

This translates to the following SQL commands:

```sql
CREATE TABLE [Example] (
  id INT NOT NULL,
  value INT,
  CONSTRAINT [Example_pkey] PRIMARY KEY NONCLUSTERED (id)
)
```

The default value of `clustered` for each attribute is as follows:

| Attribute  | Value   |
| ---------- | ------- |
| `@id`      | `true`  |
| `@@id`     | `true`  |
| `@unique`  | `false` |
| `@@unique` | `false` |
| `@@index`  | `false` |

A table can have at most one clustered index.

Configuring the name of indexes with map [#configuring-the-name-of-indexes-with-map]

The `map` argument allows you to specify a custom name for the index or constraint in the underlying database. This is useful when you want to use a specific naming convention or when the auto-generated name doesn't meet your requirements.

The `map` argument is available on the `@id`, `@@id`, `@unique`, `@@unique` and `@@index` attributes.

As an example, the following model configures a custom name for the index on the `title` field:

```prisma title="schema.prisma" showLineNumbers
model Post {
  id    Int    @id
  title String

  @@index([title], map: "my_custom_index_name")
}
```

This translates to the following SQL command (PostgreSQL example):

```sql
CREATE INDEX "my_custom_index_name" ON "Post" ("title");
```

Without the `map` argument, Prisma would generate a default name like `Post_title_idx`.

The `map` argument can also be used on unique constraints:

```prisma title="schema.prisma" showLineNumbers
model User {
  id    Int    @id
  email String @unique(map: "unique_user_email")
}
```

And on composite indexes and constraints:

```prisma title="schema.prisma" showLineNumbers
model Post {
  id        Int    @id
  title     String
  author    String
  createdAt DateTime

  @@index([author, createdAt], map: "posts_author_date_idx")
  @@unique([title, author], map: "posts_title_author_unique")
}
```

Configuring partial indexes with where [#configuring-partial-indexes-with-where]

The `where` argument allows you to define [partial indexes](https://www.postgresql.org/docs/current/indexes-partial.html) (also known as filtered indexes). A partial index only includes rows that match a specified condition, which reduces the index size and improves both write performance and query performance for the indexed subset of data.

The `where` argument is available on the `@unique`, `@@unique` and `@@index` attributes. It requires the `partialIndexes` Preview feature.

> [!NOTE]
> Partial indexes are supported on **PostgreSQL**, **SQLite**, **SQL Server**, and **CockroachDB**. They are **not** supported on MySQL.

Enabling the partialIndexes Preview feature [#enabling-the-partialindexes-preview-feature]

To use partial indexes, add the `partialIndexes` feature flag to the `generator` block of your `schema.prisma` file:

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

Raw SQL syntax with raw() [#raw-sql-syntax-with-raw]

You can define a partial index with a raw SQL predicate string using the `raw()` function. This approach supports any valid SQL `WHERE` expression that your database accepts:

```prisma title="schema.prisma" showLineNumbers
model User {
  id        Int       @id
  email     String
  status    String
  deletedAt DateTime?

  @@unique([email], where: raw("status = 'active'"))
  @@index([email], where: raw("\"deletedAt\" IS NULL"))
}
```

This generates SQL like:

**PostgreSQL:**

```sql
CREATE UNIQUE INDEX "User_email_key" ON "User" ("email") WHERE (status = 'active');
CREATE INDEX "User_email_idx" ON "User" ("email") WHERE ("deletedAt" IS NULL);
```

**SQLite:**

```sql
CREATE UNIQUE INDEX "User_email_key" ON "User" ("email") WHERE status = 'active';
CREATE INDEX "User_email_idx" ON "User" ("email") WHERE "deletedAt" IS NULL;
```

**SQL Server:**

```sql
CREATE UNIQUE NONCLUSTERED INDEX [User_email_key] ON [dbo].[User]([email]) WHERE ([status]='active');
CREATE NONCLUSTERED INDEX [User_email_idx] ON [dbo].[User]([email]) WHERE ([deletedAt] IS NULL);
```

The `raw()` syntax can be used with any SQL expression your database supports, making it the most flexible option.

Object literal syntax (type-safe alternative) [#object-literal-syntax-type-safe-alternative]

You can also define partial indexes using an object literal syntax, which provides type-safety by validating field names and value types against your Prisma schema:

```prisma title="schema.prisma" showLineNumbers
model Post {
  id        Int      @id
  title     String
  published Boolean

  @@index([title], where: { published: true })
  @@unique([title], where: { published: true })
}
```

The object literal syntax supports the following value types:

| Value type       | Example                                  | Notes                                                  |
| ---------------- | ---------------------------------------- | ------------------------------------------------------ |
| `Boolean`        | `{ active: true }`, `{ deleted: false }` | For `Boolean` fields                                   |
| `String`         | `{ status: "active" }`                   | For `String`, `DateTime`, and `Enum` fields            |
| `Number`         | `{ priority: 1 }`, `{ score: 1.5 }`      | For `Int`, `BigInt`, `Float`, and `Decimal` fields     |
| `null`           | `{ deletedAt: null }`                    | Translates to `IS NULL`. Works with any nullable field |
| `{ not: value }` | `{ deletedAt: { not: null } }`           | Negation. Translates to `IS NOT NULL` or `!= value`    |

You can combine multiple conditions in a single object:

```prisma title="schema.prisma" showLineNumbers
model User {
  id        Int       @id
  email     String
  active    Boolean
  deletedAt DateTime?

  @@unique([email], where: { active: true, deletedAt: null })
}
```

> [!NOTE]
> The object literal syntax validates field types. For example, you cannot use a `Boolean` value for a `String` field. For fields with types that are not supported by the object syntax (such as `Unsupported` or composite types), use `raw()` instead.

Using where with other index arguments [#using-where-with-other-index-arguments]

The `where` argument can be combined with other index arguments such as `name` and `map`:

```prisma title="schema.prisma" showLineNumbers
model User {
  id     Int    @id
  email  String
  status String

  @@unique([email], name: "email_active_unique", map: "idx_email_active", where: raw("status = 'active'"))
}
```

Database-specific behavior [#database-specific-behavior]

| Database    | Migrations    | Introspection | Notes                                                                  |
| ----------- | ------------- | ------------- | ---------------------------------------------------------------------- |
| PostgreSQL  | Full support  | Full support  | Full predicate support                                                 |
| SQLite      | Full support  | Full support  | Full predicate support                                                 |
| SQL Server  | Full support  | Full support  | Filtered indexes via `CREATE INDEX`                                    |
| CockroachDB | Create only   | Not supported | Cannot introspect predicate text; predicate modifications not detected |
| MySQL       | Not supported | Not supported | Partial indexes are not supported by the database                      |

> [!WARNING]
> **CockroachDB limitation**: CockroachDB supports creating partial indexes, but it cannot introspect the predicate text from existing indexes. This means that after initial creation, modifications to the `where` clause (adding, changing, or removing a predicate) will not be detected by Prisma Migrate. The differ skips predicate comparison for CockroachDB to prevent false-positive migrations.

Introspection [#introspection]

When you run `prisma db pull` on a database that contains partial indexes, Prisma ORM will:

1. Automatically add `"partialIndexes"` to the `previewFeatures` list in your generator block
2. Represent the partial index predicate using the `raw()` syntax with the database's normalized form of the SQL expression

For example, a PostgreSQL partial unique index on a single field will be introspected as:

```prisma title="schema.prisma" showLineNumbers
model User {
  id     Int    @id
  email  String @unique(where: raw("(status = 'active'::text)"))
  status String
}
```

> [!NOTE]
> The introspected `raw()` string reflects the database's normalized form of the SQL expression, which may differ from what you originally wrote. For example, PostgreSQL adds parentheses and explicit type casts (e.g., `'active'::text`), SQL Server wraps column names in brackets and adds parentheses (e.g., `([status]='active')`), while SQLite generally preserves the original expression as-is.

Full text indexes (MySQL and MongoDB) [#full-text-indexes-mysql-and-mongodb]

The `fullTextIndex` preview feature provides support for introspection and migration of full text indexes in MySQL and MongoDB. This can be configured using the `@@fulltext` attribute. Existing full text indexes in the database are added to your Prisma schema after introspecting with `db pull`, and new full text indexes added in the Prisma schema are created in the database when using Prisma Migrate.

> [!WARNING]
> For now we do not enable the full text search commands in Prisma Client for MongoDB; the progress can be followed in the [MongoDB](https://github.com/prisma/prisma/issues/9413) issue.

Enabling the fullTextIndex preview feature [#enabling-the-fulltextindex-preview-feature]

To enable the `fullTextIndex` preview feature, add the `fullTextIndex` feature flag to the `generator` block of the `schema.prisma` file:

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

Examples [#examples]

The following example demonstrates adding a `@@fulltext` index to the `title` and `content` fields of a `Post` model:

```prisma title="schema.prisma" showLineNumbers
model Post {
  id      Int    @id
  title   String @db.VarChar(255)
  content String @db.Text

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

On MongoDB, you can use the `@@fulltext` index attribute (via the `fullTextIndex` preview feature) with the `sort` argument to add fields to your full-text index in ascending or descending order. The following example adds a `@@fulltext` index to the `title` and `content` fields of the `Post` model, and sorts the `title` field in descending order:

```prisma title="schema.prisma" showLineNumbers
generator js {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextIndex"]
}

datasource db {
  provider = "mongodb"
  url      = env("DATABASE_URL")
}

model Post {
  id      String @id @map("_id") @db.ObjectId
  title   String
  content String

  @@fulltext([title(sort: Desc), content])
}
```

## Related pages

- [`Database mapping`](https://www.prisma.io/docs/orm/prisma-schema/data-model/database-mapping): Learn how to map model and field names to database tables and columns
- [`External tables`](https://www.prisma.io/docs/orm/prisma-schema/data-model/externally-managed-tables): How to declare and use externally managed tables in Prisma ORM
- [`Models`](https://www.prisma.io/docs/orm/prisma-schema/data-model/models): Learn about the concepts for building your data model with Prisma: Models, scalar types, enums, attributes, functions, IDs, default values and more
- [`Multi-schema`](https://www.prisma.io/docs/orm/prisma-schema/data-model/multi-schema): How to use Prisma ORM with multiple database schemas
- [`Relations`](https://www.prisma.io/docs/orm/prisma-schema/data-model/relations): A relation is a connection between two models in the Prisma schema. This page explains how you can define one-to-one, one-to-many and many-to-many relations in Prisma