Indexes
Prisma ORM allows configuration of database indexes, unique constraints and primary key constraints. This is in General Availability in versions 4.0.0
and later. You can enable this with the extendedIndexes
Preview feature in versions 3.5.0
and later.
Version 3.6.0
also introduces support for introspection and migration of full text indexes in MySQL and MongoDB through a new @@fulltext
attribute, available through the fullTextIndex
Preview feature.
If you are upgrading from a version earlier than 4.0.0, these changes to index configuration and full text indexes might be breaking changes if you have a database that already uses these features. See Upgrading from previous versions for more information on how to upgrade.
Index configuration
You can configure indexes, unique constraints, and primary key constraints with the following attribute arguments:
-
The
length
argument allows you to specify a maximum length for the subpart of the value to be indexed onString
andBytes
types- Available on the
@id
,@@id
,@unique
,@@unique
and@@index
attributes - MySQL only
- Available on the
-
The
sort
argument 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
- Available on the
-
The
type
argument allows you to support index access methods other than PostgreSQL's defaultBTree
access method- Available on the
@@index
attribute - PostgreSQL only
- Supported index access methods:
Hash
,Gist
,Gin
,SpGist
andBrin
- Available on the
-
The
clustered
argument 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
- Available on the
See the linked sections for details of which version each feature was first introduced in.
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:
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.
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:
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:
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
The sort
argument is available for all databases supported by Prisma ORM. 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
model Unique {
unique Int @unique(sort: Desc)
}
The sort
argument can also be used on compound indexes:
model CompoundUnique {
unique_1 Int
unique_2 Int
@@unique([unique_1(sort: Desc), unique_2])
}
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:
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)
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.
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:
model Example {
id Int @id
value Int
@@index([value], type: Hash)
}
This translates to the following SQL commands:
CREATE TABLE "Example" (
id INT PRIMARY KEY,
value INT NOT NULL
);
CREATE INDEX "Example_value_idx" ON "Example" USING HASH (value);