Database features matrix
This page gives an overview of the features which are provided by the databases that Prisma ORM supports. Additionally, it explains how each of these features can be used in Prisma ORM with pointers to further documentation.
Note: If a feature is not supported natively by the database, it's also not available in Prisma ORM.
Relational database features
This section describes which database features exist on the relational databases that are currently supported by Prisma ORM. The Prisma schema column indicates how a certain feature can be represented in the Prisma schema and links to its documentation. Note that database features can be used in Prisma Client even though they might not yet be representable in the Prisma schema.
Constraints
| Constraint | PostgreSQL | Microsoft SQL Server | MySQL | SQLite | CockroachDB | Prisma schema | Prisma Client | Prisma Migrate |
|---|---|---|---|---|---|---|---|---|
PRIMARY KEY | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | @id and @@id | ✔️ | ✔️ |
FOREIGN KEY | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | Relation fields | ✔️ | ✔️ |
UNIQUE | ✔️ | ✔️† | ✔️ | ✔️ | ✔️ | @unique and @@unique | ✔️ | ✔️ |
CHECK | ✔️ | ✔️ | ✔️* | ✔️ | ✔️ | Not yet | ✔️ | Not yet |
NOT NULL | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ? | ✔️ | ✔️ |
DEFAULT | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | @default | ✔️ | ✔️ |
Referential Actions (Delete and Update behaviors for foreign key references)
| Deletion behavior | PostgreSQL | Microsoft SQL Server | MySQL | SQLite | CockroachDB | Prisma schema | Prisma Client | Prisma Migrate |
|---|---|---|---|---|---|---|---|---|
CASCADE | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️† | ✔️ | ✔️† |
RESTRICT | ✔️ | No | ✔️ | ✔️ | ✔️ | ✔️† | ✔️ | ✔️† |
NO ACTION | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️† | ✔️ | ✔️† |
SET DEFAULT | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️† | ✔️ | ✔️† |
SET NULL | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️† | ✔️ | ✔️† |
- † In 2.26.0 and later you can define referential actions on your relation fields. Referential actions determine what should happen to a record when a related record is deleted or updated.
Indexes
| Index | PostgreSQL | Microsoft SQL Server | MySQL | SQLite | CockroachDB | Prisma schema | Prisma Client | Prisma Migrate |
|---|---|---|---|---|---|---|---|---|
UNIQUE | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | @unique and @@unique | ✔️ | ✔️ |
USING | ✔️ | No | No | No | ✔️ | type† | ✔️ | ✔️† |
WHERE | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | Not yet | ✔️ | Not yet |
(expression) | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | Not yet | ✔️ | Not yet |
INCLUDE | ✔️ | ✔️ | No | No | ✔️ | Not yet | ✔️ | Not yet |
- † Available in preview in 3.6.0 and later and in general availability in 4.0.0 and later, with the PostgreSQL connector only.
Algorithm specified via USING:
| Index type (Algorithm) | PostgreSQL | Microsoft SQL Server | MySQL | SQLite | CockroachDB | Prisma schema | Prisma Client | Prisma Migrate |
|---|---|---|---|---|---|---|---|---|
| B-tree | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️† | ✔️ | Not yet |
| Hash | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️† | ✔️ | Not yet |
| GiST | ✔️ | ✔️ | No | No | ✔️ | ✔️† | ✔️* | Not yet |
| GIN | ✔️ | ✔️ | No | No | ✔️ | ✔️† | ✔️* | Not yet |
| BRIN | ✔️ | ✔️ | No | No | ✔️ | ✔️† | ✔️* | Not yet |
| SP-GiST | ✔️ | ✔️ | No | No | ✔️ | ✔️† | ✔️* | Not yet |
- * Only available if natively supported by database.
- † Available with the PostgreSQL connector only in Prisma ORM versions
4.0.0and later.
Misc
| Feature | PostgreSQL | Microsoft SQL Server | MySQL | SQLite | CockroachDB | Prisma schema | Prisma Client | Prisma Migrate |
|---|---|---|---|---|---|---|---|---|
| Autoincrementing IDs | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | autoincrement() | ✔️ | ✔️ |
| Arrays | ✔️ | No | No | No | ✔️ | [] | ✔️* | ✔️* |
| Enums | ✔️ | No | ✔️ | No | ✔️ | enum | ✔️* | ✔️* |
| Native database types | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | Not yet |
| SQL Views | ✔️ | ✔️ | ✔️ | ✔️ | ✔️ | Not yet | Not yet | Not yet |
| JSON support | ✔️ | ✔️† | ✔️ | No | ✔️‡ | ✔️* | ✔️* | ✔️* |
| Fuzzy/Phrase full text search | ✔️ | ✔️ | ✔️ | No | ✔️ | Not yet | Not yet | Not yet |
| Table inheritance | ✔️ | ✔️ | No | No | ✔️ | Not yet | ✔️* | Not yet |
| Authorization and user management | ✔️ | ✔️ | ✔️ | No | ✔️ | Not yet | Not yet | Not yet |
- * Only available if natively supported by database.
- † Only supports JSON through SQL functions, but doesn't have a JSON column type. Therefore client JSON operations are not supported.
- ‡ JSON arrays are not yet supported: see the CockroachDB connector page for details
NoSQL database features
This section describes which database features exist on the NoSQL databases that are currently supported by Prisma ORM.
MongoDB
The following table lists common MongoDB features and describes the level of support offered by Prisma ORM:
| Feature | Supported by Prisma ORM | Notes |
|---|---|---|
| Embedded documents | ✔️ | |
| Transactions | ✔️ | |
| Indexes | ✔️ with caveats | Indexes can only be introspected if the field they refer to includes at least some data. |
| Autoincrementing IDs | No | |
| Compound IDs | No | MongoDB does not support composite IDs (@@id) |
Generated ObjectId | ✔️ | See: Defining IDs for MongoDB |
| Arrays | ✔️ | |
| Enums | ✔️ | Implemented at Prisma ORM level |
| Native database types | ✔️ | See: Field mapping reference |
| JSON support | ✔️ | Advanced Json field filtering is not yet supported. |
| DBrefs | No | |
| Change streams | No | |
| Direct access to the aggregation pipeline | No |