Indexes

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 for more details.

The length argument is available on the @id, @@id, @unique, @@unique and @@index attributes. It is generally available in versions 4.0.0 and later, and available as part of the extendedIndexes preview feature in versions 3.5.0 and later.

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

schema.prisma
1model Id {
2  id String @id @db.VarChar(3000)
3}

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

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:

schema.prisma
1model Id {
2  id String @id(length: 100) @db.VarChar(3000)
3}

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 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 to support indexes and constraints that were previously suppressed and results in better support of existing MySQL databases that are making use of this feature.

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

schema.prisma
1model CompoundId {
2  id_1 String @db.VarChar(3000)
3  id_2 String @db.VarChar(3000)
4
5  @@id([id_1(length: 100), id_2(length: 10)])
6}

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

Configuring the index sort order with sort

The sort argument is available for all databases supported by Prisma. It 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 sort argument is available for all databases on @unique, @@unique and @@index. Additionally, SQL Server also allows it on @id and @@id. It is generally available in versions 4.0.0 and later, and available as part of the extendedIndexes preview feature in versions 3.5.0 and later.

As an example, the following table

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

is now introspected as

schema.prisma
1model Unique {
2  unique Int @unique(sort: Desc)
3}

The sort argument can also be used on compound indexes:

schema.prisma
1model CompoundUnique {
2  unique_1 Int
3  unique_2 Int
4
5  @@unique([unique_1(sort: Desc), unique_2])
6}

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:

schema.prisma
1model Post {
2  title      String   @db.VarChar(300)
3  abstract   String   @db.VarChar(3000)
4  slug       String   @unique(sort: Desc, length: 42) @db.VarChar(3000)
5  author     String
6  created_at DateTime
7
8  @@id([title(length: 100, sort: Desc), abstract(length: 10)])
9  @@index([author, created_at(sort: Desc)])
10}

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. The type argument is generally available in versions 4.0.0 and later. The Hash index access method is available as part of the extendedIndexes preview feature in versions 3.6.0 and later, and the Gist, Gin, SpGist and Brin index access methods are available in preview in versions 3.14.0 and later.

schema.prisma
1model Example {
2  id    Int @id
3  value Int
4
5  @@index([value], type: Hash)
6}

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

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

schema.prisma
1model Example {
2  id    Int  @id
3  value Json
4  //    ^ field type matching the operator class
5
6  @@index([value(ops: JsonbPathOps)], type: Gin)
7  //                  ^ operator class      ^ index type
8}

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

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

schema.prisma
1model Example {
2  id    Int    @id
3  value String @db.Inet
4  //           ^ native type matching the operator class
5
6  @@index([value(ops: InetOps)], type: Gist)
7  //                                   ^ index type
8  //                  ^ operator class
9}

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

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

schema.prisma
1model Example {
2  id    Int    @id
3  value String
4  //    ^ field type matching the operator class
5
6  @@index([value], type: SpGist)
7  //                     ^ index type
8  //       ^ using the default ops: TextOps
9}

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

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

schema.prisma
1model Example {
2  id    Int @id
3  value Int
4  //    ^ field type macthing the operator class
5
6  @@index([value(ops: Int4BloomOps)], type: Brin)
7  //                  ^ operator class      ^ index type
8}

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

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

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. It is generally available in versions 4.0.0 and later, and available as part of the extendedIndexes preview feature in versions 3.13.0 and later.

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

schema.prisma
1model Example {
2  id    Int @id(clustered: false)
3  value Int
4}

This translates to the following SQL commands:

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:

A table can have at most one clustered index.

Upgrading from previous versions

A breaking change can occur in the following situations:

• Existing sort constraints and indexes: earlier versions of Prisma will assume that the desired sort order is ascending if no order is specified explicitly. This means that this is a breaking change if you have existing constraints or indexes that are using descending sort order and migrate your database without first specifying this in your data model.
• Existing length constraints and indexes: in earlier versions of Prisma, indexes and constraints that were length constrained in MySQL could not be represented in the Prisma schema. Therefore prisma db pull was not fetching these and you could not manually specify them. When you ran prisma db push or prisma migrate dev they were ignored if already present in your database. Since you are now able to specify these, migrate commands will now drop them if they are missing from your data model but present in the database.
• Existing indexes other than BTree (PostgreSQL): earlier versions of Prisma only supported the default BTree index type. Other supported indexes (Hash, Gist, Gin, SpGist and Brin) need to be added before migrating your database.
• Existing (non-)clustered indexes (SQL Server): earlier versions of Prisma did not support configuring an index as clustered or non-clustered. For indexes that do not use the default, these need to be added before migrating your database.

In each of the cases above unwanted changes to your database can be prevented by properly specifying these properties in your data model where necessary. The easiest way to do this is to use prisma db pull to retrieve any existing constraints or configuration. Alternatively, you could also add these arguments manually. This should be done before using prisma db push or prisma migrate dev the first time after the upgrade.

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:

schema.prisma
1generator client {
2  provider        = "prisma-client-js"
3  previewFeatures = ["fullTextIndex"]
4}

Examples

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

schema.prisma
1model Post {
2  id      Int    @id
3  title   String @db.VarChar(255)
4  content String @db.Text
5
6  @@fulltext([title, content])
7}

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:

schema.prisma
1generator js {
2  provider        = "prisma-client-js"
3  previewFeatures = ["fullTextIndex"]
4}
5
6datasource db {
7  provider = "mongodb"
8  url      = env("DATABASE_URL")
9}
10
11model Post {
12  id      String @id @map("_id") @db.ObjectId
13  title   String
14  content String
15
16  @@fulltext([title(sort: Desc), content])
17}

Upgrading from previous versions

Earlier versions of Prisma converted full text indexes using the @@index attribute rather than the @@fulltext attribute. After enabling the fullTextIndex preview feature, run prisma db pull to convert these indexes to @@fulltext before migrating again with Prisma Migrate. If you do not do this, the existing indexes will be dropped instead and normal indexes will be created in their place.