Prisma schema reference
datasource
Defines a data source in the Prisma schema.
Fields
A datasource
block accepts the following fields:
Name | Required | Type | Description |
---|---|---|---|
provider | Yes | String (postgresql , mysql , sqlite , sqlserver ) | Describes which data source connectors to use. |
url | Yes | String (URL) | Connection URL including authentication info. Most connectors use the syntax provided by the database, learn more. |
shadowDatabaseUrl | No | String (URL) | Connection URL to the shadow database used by Prisma Migrate. Allows you to use a cloud-hosted database as the shadow database. |
The following providers are available:
sqlite
postgresql
mysql
sqlserver
(Preview)
Remarks
- You can only have one
datasource
block in a schema. datasource db
is convention - however, you can give your data source any name - for example,datasource mysql
ordatasource data
.
Examples
Specify a PostgreSQL data source
In this example, the target database is available with the following credentials:
- User:
johndoe
- Password:
mypassword
- Host:
localhost
- Post:
5432
- Database name:
mydb
- Schema name:
public
datasource db {provider = "postgresql"url = "postgresql://johndoe:mypassword@localhost:5432/mydb?schema=public"}
Learn more about PostgreSQL connection strings here.
Specify a MySQL data source
In this example, the target database is available with the following credentials:
- User:
johndoe
- Password:
mypassword
- Host:
localhost
- Post:
3306
- Database name:
mydb
datasource db {provider = "mysql"url = "mysql://johndoe:mypassword@localhost:3306/mydb"}
Learn more about MySQL connection strings here.
Specify a SQLite data source
In this example, the target database is located in a file called dev.db
:
datasource db {provider = "sqlite"url = "file:./dev.db"}
Learn more about SQLite connection strings here.
Specify a PostgreSQL data source via an environment variable
In this example, the target database is available with the following credentials:
- User:
johndoe
- Password:
mypassword
- Host:
localhost
- Post:
5432
- Database name:
mydb
- Schema name:
public
datasource db {provider = "postgresql"url = env("DATABASE_URL")}
When running a Prisma CLI command that needs the database connection URL (e.g. prisma generate
), you need to make sure that the DATABASE_URL
environment variable is set.
One way to do so is by creating a .env
file with the following contents. Note that the file must be in the same directory as your schema.prisma
file to automatically picked up the Prisma CLI.
DATABASE_URL=postgresql://johndoe:mypassword@localhost:5432/mydb?schema=public
generator
Defines a generator in the Prisma schema.
Fields
A generator
block accepts the following fields:
Name | Required | Type | Description |
---|---|---|---|
provider | Yes | String (file path) or Enum (prisma-client-js ) | Describes which generator to use. This can point to a file that implements a generator or specify a built-in generator directly. |
output | No | String (file path) | Determines the location for the generated client, learn more. Default: node_modules/@prisma/client |
binaryTargets | No | List of Enums (see below) | Specify the OS on which the Prisma Client will run to ensure binary compatibility of the query engine. Default: native |
previewFeatures | No | List of Enums | Use intellisense to see list of currently available Preview features (Ctrl+Space in Visual Studio Code) |
binaryTargets options
The following tables list all supported operating systems with the name of the binary file to specify in binaryTargets
.
Mac OS
Build OS | Prisma engine build name |
---|---|
Mac OS | darwin |
Windows
Build OS | Prisma engine build name |
---|---|
Windows | windows |
Linux (Alpine)
Build OS | Prisma engine build name |
---|---|
Alpine | linux-musl |
Linux (Debian)
Build OS | Prisma engine build name | OpenSSL |
---|---|---|
Debian 8 (Jessie) | debian-openssl-1.0.x | 1.0.x |
Debian 9 (Stretch) | debian-openssl-1.1.x | 1.1.x |
Debian 10 (Buster) | debian-openssl-1.1.x | 1.1.x |
Debian 11 (Bullseye) | debian-openssl-1.1.x | 1.1.x |
Linux (Ubuntu)
Build OS | Prisma engine build name | OpenSSL |
---|---|---|
Ubuntu 14.04 (trusty) | debian-openssl-1.0.x | 1.0.x |
Ubuntu 16.04 (xenial) | debian-openssl-1.0.x | 1.0.x |
Ubuntu 18.04 (bionic) | debian-openssl-1.1.x | 1.1.x |
Ubuntu 19.04 (disco) | debian-openssl-1.1.x | 1.1.x |
Ubuntu 20.04 (focal) | debian-openssl-1.1.x | 1.1.x |
Linux (CentOS)
Build OS | Prisma engine build name | OpenSSL |
---|---|---|
CentOS 7 | rhel-openssl-1.0.x | 1.0.x |
CentOS | rhel-openssl-1.0.x | 1.0.x |
Linux (Fedora)
Build OS | Prisma engine build name | OpenSSL |
---|---|---|
Fedora 28 | rhel-openssl-1.1.x | 1.1.x |
Fedora 29 | rhel-openssl-1.1.x | 1.1.x |
Fedora 30 | rhel-openssl-1.1.x | 1.1.x |
Linux (Linux Mint)
Build OS | Prisma engine build name | OpenSSL |
---|---|---|
Linux Mint 18 | debian-openssl-1.0.x | 1.0.x |
Linux Mint 19 | debian-openssl-1.1.x | 1.1.x |
Linux (Arch Linux)
Build OS | Prisma engine build name | OpenSSL |
---|---|---|
Arch Linux 2019.09.01 | debian-openssl-1.1.x | 1.1.x |
Linux ARM64
Build OS | Prisma engine build name | OpenSSL |
---|---|---|
Linux ARM64-based distro | linux-arm-openssl-1.0.x | 1.0.x |
Linux ARM64-based distro | linux-arm-openssl-1.1.x | 1.1.x |
Examples
Specify the prisma-client-js
generator with the default output
and binaryTargets
generator client {provider = "prisma-client-js"}
Note that the above generator
definition is equivalent to the following because it uses the default values for output
and binaryTargets
:
generator client {provider = "prisma-client-js"output = "node_modules/@prisma/client"binaryTargets = ["native"]}
Specify a custom output
location for Prisma Client
generator client {provider = "prisma-client-js"output = "../src/generated/client"}
Specify custom binaryTargets
to ensure compatibility with the OS
This example shows how to configure Prisma Client to run on Ubuntu 19.04 (disco)
based on the table above.
generator client {provider = "prisma-client-js"binaryTargets = ["debian-openssl-1.1.x"]}
Specify a provider
pointing to some custom generator implementation
This example shows how to use a custom generator that's located in a directory called my-generator
.
generator client {provider = "./my-generator"}
model
Defines a Prisma model .
Remarks
- Every record of a model must be uniquely identifiable. You must define at least one of the following attributes per model:
Naming conventions
- Model names must adhere to the following regular expression:
[A-Za-z][A-Za-z0-9_]*
- Model names must start with a letter and are typically spelled in PascalCase
- Model names should use the singular form (for example,
User
instead ofuser
,users
orUsers
) - Prisma has a number of reserved words that are being used by Prisma internally and therefore cannot be used as a model name. You can find the reserved words here and here.
Note: You can use the
@@map
attribute to map a model (for example,User
) to a table with a different name that does not match model naming conventions (for example,users
).
Order of fields
- In version 2.3.0 and later, introspection lists model fields same order as the corresponding columns in the database. Relation fields are listed after scalar fields.
Examples
A model named User
with two scalar fields
model User {email String @unique // `email` can not be optional because it's the only unique field on the modelname String?}
model fields
Fields are properties of models.
Remarks
Naming conventions
- Must start with a letter
- Typically spelled in camelCase
- Must adhere to the following regular expression:
[A-Za-z][A-Za-z0-9_]*
Note: You can use the
@map
attribute to map a field name (for example,MyField
) to a column with a different name that does not match field naming conventions (for example,myField
).
model field scalar types
The data source connector determines what native database type each of Prisma scalar type maps to. Similarly, the generator determines what type in the target programming language each of these types map to.
Prisma models also have model field types that define relations between models.
String
Variable length text.
Default type mappings
Connector | Default mapping |
---|---|
PostgreSQL | text |
Microsoft SQL | nvarchar(1000) |
MySQL | varchar(191) |
SQLite | TEXT |
PostgreSQL
Native database type | Native database type attribute | Notes |
---|---|---|
text | @db.Text | |
char | @db.Char(x) | |
varchar | @db.VarChar(n) | |
bit | @db.Bit(x) | |
varbit | @db.VarBit | |
uuid | @db.Uuid | |
xml | @db.Xml | |
inet | @db.Inet | |
citext | @db.Citext | Only available if Citext extension is enabled. |
MySQL
Native database type | Native database type attribute | Notes |
---|---|---|
VARCHAR | @db.VarChar(X) | |
TEXT | @db.Text | |
CHAR | @db.Char(X) | |
TINYTEXT | @db.TinyText | |
MEDIUMTEXT | @db.MediumText | |
LONGTEXT | @db.LongText |
You can use Prisma Migrate to map @db.Bit(1)
to String
:
model Model {/* ... */myField String @db.Bit(1)}
SQL Server (Preview)
Native database type | Native database type attribute | Notes |
---|---|---|
char | @db.Char(X) | |
nchar | @db.NChar(X) | |
varchar | @db.VarChar(X) | |
nvarchar | @db.NVarChar(X) | |
text | @db.Text | |
ntext | @db.NText | |
xml | @db.Xml | |
uniqueidentifier | @db.UniqueIdentifier |
SQLite
TEXT
Clients
Prisma Client JS |
---|
string |
Boolean
True or false value.
Default type mappings
Connector | Default mapping |
---|---|
PostgreSQL | boolean |
Microsoft SQL | tinyint |
MySQL | TINYINT(1) |
SQLite | INTEGER |
PostgreSQL
Native database types | Native database type attribute | Notes |
---|---|---|
boolean | @db.Boolean |
MySQL
Native database types | Native database type attribute | Notes |
---|---|---|
TINYINT(1) | @db.TinyInt(1) | TINYINT maps to Int if the max length is greater than 1 (for example, TINYINT(2) ) or the default value is anything other than 1 , 0 , or NULL |
BIT(1) | @db.Bit |
SQL Server (Preview)
Native database types | Native database type attribute | Notes |
---|---|---|
tinyint | @db.TinyInt |
SQLite
INTEGER
Clients
Prisma Client JS |
---|
boolean |
Int
Default type mappings
Connector | Default mapping |
---|---|
PostgreSQL | integer |
Microsoft SQL | int |
MySQL | INT |
SQLite | INTEGER |
PostgreSQL
Native database types | Native database type attribute | Notes |
---|---|---|
integer | int , int4 | @db.Integer | |
smallint | int2 | @db.SmallInt | |
smallserial | serial2 | @db.SmallInt @default(autoincrement()) | |
serial | serial4 | @db.Int @default(autoincrement()) | |
oid | @db.Oid |
MySQL
Native database types | Native database type attribute | Notes |
---|---|---|
INT | @db.Int | |
INT UNSIGNED | @db.UnsignedInt | |
SMALLINT | @db.SmallInt | |
SMALLINT UNSIGNED | @db.UnsignedSmallInt | |
MEDIUMINT | @db.MediumInt | |
MEDIUMINT UNSIGNED | @db.UnsignedMediumInt | |
TINYINT | @db.TinyInt | TINYINT maps to Int if the max length is greater than 1 (for example, TINYINT(2) ) or the default value is anything other than 1 , 0 , or NULL . TINYINT(1) maps to Boolean . |
TINYINT UNSIGNED | @db.UnsignedTinyInt | TINYINT(1) UNSIGNED maps to Int , not Boolean |
YEAR | @db.Year |
SQL Server (Preview)
Native database types | Native database type attribute | Notes |
---|---|---|
int | @db.Int | |
smallint | @db.SmallInt | |
tinyint | @db.TinyInt |
SQLite
INTEGER
Clients
Prisma Client JS |
---|
number |
BigInt
BigInt
is available in version 2.17.0 and later.
Default type mappings
Connector | Default mapping |
---|---|
PostgreSQL | integer |
Microsoft SQL | int |
MySQL | INT |
SQLite | INTEGER |
PostgreSQL
Native database types | Native database type attribute | Notes |
---|---|---|
bigint | int8 | @db.BigInt | |
bigserial | serial8 | @db.BigInt @default(autoincrement()) |
MySQL
Native database types | Native database type attribute | Notes |
---|---|---|
BIGINT | @db.BigInt | |
SERIAL | @db.UnsignedBigInt @default(autoincrement()) |
SQL Server (Preview)
Native database types | Native database type attribute | Notes |
---|---|---|
bigint | @db.BigInt |
SQLite
INTEGER
Clients
Client | Type | Description |
---|---|---|
Prisma Client JS | BigInt | See examples of working with BigInt |
Float
Floating point number.
Float
maps toDouble
in 2.17.0 and later - see release notes and Video: Changes to the default mapping of Float in Prisma 2.17.0 for more information about this change.
Default type mappings
Connector | Default mapping |
---|---|
PostgreSQL | double precision |
Microsoft SQL | float(53) |
MySQL | DOUBLE |
SQLite | REAL |
PostgreSQL
Native database types | Native database type attribute | Notes |
---|---|---|
double precision | @db.DoublePrecision | |
real | @db.Real |
MySQL
Native database types | Native database type attribute | Notes |
---|---|---|
FLOAT | @db.Float | |
DOUBLE | @db.Double |
SQL Server (Preview)
Native database types | Native database type attribute |
---|---|
float | @db.Float |
money | @db.Money |
smallmoney | @db.SmallMoney |
real | @db.Real |
SQLite connector
REAL
Clients
Prisma Client JS |
---|
number |
Decimal
Default type mappings
Connector | Default mapping |
---|---|
PostgreSQL | decimal(65,30) |
Microsoft SQL | decimal(32,16) |
MySQL | DECIMAL(65,30) |
SQLite | DECIMAL |
PostgreSQL
Native database types | Native database type attribute | Notes |
---|---|---|
decimal | numeric | @db.Decimal(X, Y) | |
money | @db.Money |
MySQL
Native database types | Native database type attribute | Notes |
---|---|---|
DECIMAL | NUMERIC | @db.Decimal(X, Y) |
SQL Server (Preview)
Native database types | Native database type attribute | Notes |
---|---|---|
decimal | numeric | @db.Decimal(X, Y) |
SQLite
DECIMAL
(changed from REAL
in 2.17.0)
Clients
Client | Type | Description |
---|---|---|
Prisma Client JS | Decimal | See examples of working with Decimal |
DateTime
Remarks
- Prisma Client returns all
DateTime
as ISO 8601-formatted strings.
Default type mappings
Connector | Default mapping |
---|---|
PostgreSQL | timestamp(3) |
Microsoft SQL | datetime2 |
MySQL | DATETIME(3) |
SQLite | NUMERIC |
PostgreSQL
Native database types | Native database type attribute | Notes |
---|---|---|
timestamp | @db.Timestamp(x) | |
timestamptz | @db.Timestamptz(x) | |
date | @db.Date | |
time | @db.Time(x) | |
timetz | @db.Timetz(x) |
MySQL
Native database types | Native database type attribute | Notes |
---|---|---|
DATETIME(x) | @db.DateTime(x) | |
DATE(x) | @db.Date(x) | |
TIME(x) | @db.Time(x) | |
TIMESTAMP(x) | @db.Timestamp(x) |
You can also use MySQL's YEAR
type with Int
:
yearField Int @db.Year
SQL Server (Preview)
Native database types | Native database type attribute | Notes |
---|---|---|
date | @db.Date | |
time | @db.Time | |
datetime | @db.DateTime | |
datetime2 | @db.DateTime2 | |
smalldatetime | @db.SmallDateTime | |
datetimeoffset | @db.DateTimeOffset |
SQLite
NUMERIC
or STRING
. If the underlying data type is STRING
, you must use one of the following formats:
Clients
Prisma Client JS |
---|
Date |
Json
A JSON object.
Default type mappings
Connector | Default mapping |
---|---|
PostgreSQL | jsonb |
Microsoft SQL | nvarchar(1000) |
MySQL | JSON |
SQLite | Not supported |
PostgreSQL
Native database types | Native database type attribute | Notes |
---|---|---|
json | @db.Json | |
jsonb | @db.JsonB |
MySQL
Native database types | Native database type attribute | Notes |
---|---|---|
JSON | @db.Json |
SQL Server (Preview)
SQL Server does not have a specific data type for JSON - however, there are a number of built-in functions for reading and modifying JSON.
Native database types | Native database type attribute |
---|---|
JSON | @db.NVarChar(1000) |
SQLite
Not supported
Clients
Prisma Client JS |
---|
object |
Bytes
Bytes
is available in version 2.17.0 and later.
Default type mappings
Connector | Default mapping |
---|---|
PostgreSQL | bytea |
Microsoft SQL | varbinary |
MySQL | LONGBLOB |
SQLite | Not supported |
PostgreSQL
Native database types | Native database type attribute |
---|---|
bytea | @db.ByteA |
MySQL
Native database types | Native database type attribute | Notes |
---|---|---|
LONGBLOB | @db.LongBlob | |
BINARY | @db.Binary | |
VARBINARY | @db.VarBinary | |
TINYBLOB | @db.TinyBlob | |
BLOB | @db.Blob | |
MEDIUMBLOB | @db.MediumBlob | |
BIT | @db.Bit |
SQL Server (Preview)
Native database types | Native database type attribute | Notes |
---|---|---|
binary | @db.Binary | |
varbinary | @db.VarBinary | |
image | @db.Image | |
bit | @db.Bit |
SQLite
Not supported
Clients
Client | Type | Description |
---|---|---|
Prisma Client JS | Buffer | See examples of working with Buffer |
Unsupported
The Unsupported
type was introduced in 2.17.0 and allows you to represent data types in the Prisma schema that are not supported by the Prisma Client. Unsupported
fields can be introspected with prisma introspect
or created with Prisma Migrate or db push
.
Remarks
Fields with
Unsupported
types are not available in the generated client.If a model contains a mandatory
Unsupported
type,prisma.model.create(..)
andprisma.model.update(...)
are not available in the client.Prisma will always warn that your schema contains unsupported types when you
introspect
:*** WARNING ***These fields are not supported by the Prisma Client, because Prisma currently does not support their types.* Model "Post", field: "circle", original data type: "circle"
Examples
model Star {id Int @id @default(autoincrement())position Unsupported("circle")?example1 Unsupported("circle")circle Unsupported("circle")? @default(dbgenerated("'<(10,4),11>'::circle"))
model field type modifiers
[] modifier
Makes a field a list.
Remarks
- Cannot be optional (for example
Post[]?
). - Scalar lists (arrays) are only supported in the data model if your database natively supports them. Currently, scalar lists are therefore only supported when using PostgreSQL (since MySQL and SQLite don't natively support scalar lists).
Examples
model User {id Int @id @default(autoincrement())posts Post[]}
? modifier
Makes a field optional.
Remarks
- Cannot be used with a list field (for example,
Posts[]
)
Examples
Optional name
field
model User {id Int @id @default(autoincrement())name String?}
Attributes
Attributes modify the behavior of a field or block (e.g. models). There are two ways to add attributes to your data model:
- Field attributes are prefixed with
@
- Block attributes are prefixed with
@@
Some attributes take arguments. Arguments in attributes are always named, but in most cases the argument name can be omitted.
Note: The leading underscore in a signature means the argument name can be omitted.
@id
Defines a single-field ID on the model.
Remarks
Corresponding database type:
PRIMARY KEY
Can be annotated with a
@default()
value that uses functions to auto-generate an ID:Cannot be optional
Can be defined on any scalar field (
String
,Int
,enum
)Cannot be defined on a relation field
Arguments
N/A
Signature
@id
Examples
In most cases, you want your database to create the ID. To do this, annotate the ID field with the @default
attribute and initialize the field with a function.
Generate cuid()
values as IDs
model User {id String @id @default(cuid())name String}
Generate uuid()
values as IDs
model User {id String @id @default(uuid())name String}
Generate auto-incrementing integers as IDs
model User {id Int @id @default(autoincrement())name String}
Single-field IDs without default values
In the following example, id
does not have a default value:
model User {id String @idname String}
Note that in the above case, you must provide your own ID values when creating new records for the User
model using Prisma Client, e.g.:
const newUser = await prisma.user.create({data: {id: 1,name: 'Alice',},})
Specify an ID on String
without a default value
model User {id String @idname String}
Specify an ID on relation scalar field without a default value
In the following example, authorId
is a both a relation scalar and the ID of Profile
:
model Profile {authorId Int @idauthor User @relation(fields: [authorId], references: [id])bio String}model User {id Int @idemail String @uniquename String?profile Profile?}
In this scenario, you cannot create a Profile
only - you must use Prisma Client's nested writes create a User
or connect the profile to an existing user.
The following example creates a user and a profile:
const userWithProfile = await prisma.user.create({data: {id: 3,email: 'bob@prisma.io',name: 'Bob Prismo',profile: {create: {bio:"Hello, I'm Bob Prismo and I love apples, blue nail varnish, and the sound of buzzing mosquitoes.",},},},})
The following example connects a new profile to a user:
const profileWithUser = await prisma.profile.create({data: {bio: "Hello, I'm Bob and I like nothing at all. Just nothing.",author: {connect: {id: 22,},},},})
@@id
Defines a multi-field ID (composite ID) on the model.
Remarks
- Corresponding database type:
PRIMARY KEY
- Can be annotated with a
@default()
value that uses functions to auto-generate an ID - Cannot be optional
- Can be defined on any scalar field (
String
,Int
,enum
) - Cannot be defined on a relation field
- The name of the composite ID field in Prisma Client has the following pattern:
field1_field2_field3
Arguments
Name | Required | Type | Description |
---|---|---|---|
fields | Yes | FieldReference[] | A list of field names - for example, ["firstname", "lastname"] |
The name of the fields
argument on the @@id
attribute can be omitted:
@@id(fields: [title, author])@@id([title, author])
Signature
@@id(_ fields: FieldReference[])
Specify a multi-field ID on two String
fields
model User {firstName StringlastName Stringemail String @uniqueisAdmin Boolean @default(false)@@id([firstName, lastName])}
When you create a user, you must provide a unique combination of firstName
and lastName
:
const user = await prisma.user.create({data: {firstName: 'Alice',lastName: 'Smith',},})
To get a retrieve a user, use the generated composite ID field (firstname_lastname
):
const user = await prisma.user.findUnique({where: {firstname_lastname: {firstName: 'Alice',lastName: 'Smith',}},})
Specify a multi-field ID on two String
fields and one Boolean
field
model User {firstName StringlastName Stringemail String @uniqueisAdmin Boolean @default(false)@@id([firstName, lastName, isAdmin])}
When creating new User
records, you now must provide a unique combination of values for firstName
, lastName
and isAdmin
:
const user = await prisma.user.create({data: {firstName: 'Alice',lastName: 'Smith',isAdmin: true,},})
Specify a multi-field ID that includes a relation field
model Post {title Stringpublished Boolean @default(false)author User @relation(fields: [authorId], references: [id])authorId Int@@id([authorId, title])}model User {id Int @default(autoincrement())email String @uniquename String?posts Post[]}
When creating new Post
records, you now must provide a unique combination of values for authorId
(foreign key) and title
:
const post = await prisma.post.create({data: {title: 'Hello World',author: {connect: {email: 'alice@prisma.io',},},},})
@default
Defines a default value for a field .
Remarks
Corresponding database type:
DEFAULT
Default values can be a static value (
4
,"hello"
) or one of the following functions:Default values that cannot yet be represented in the Prisma schema are represented by the
dbgenerated()
function when you use introspection.Default values are currently not allowed on relation fields in the Prisma schema. Note however that you can still define default values manually in the underlying database using plain SQL. These will not be represented in the Prisma schema though, this is a temporary limitation which you can track on GitHub.
Arguments
Name | Required | Type | Description |
---|---|---|---|
expression | Yes | An expression (e.g. 5 , true , now() ) |
The name of the value
argument on the @default
attribute can be omitted:
id Int @id @default(value: autoincrement())id Int @id @default(autoincrement())
Signature
@default(_ value: Expression)
Examples
Default value for an Int
model User {email String @uniqueprofileViews Int @default(0)}
Default value for a Float
model User {email String @uniquenumber Float @default(1.1)}
Default value for Decimal
model User {email String @uniquenumber Decimal @default(22.99)}
Default value for BigInt
model User {email String @uniquenumber BigInt @default(34534535435353)}
Default value for a String
model User {email String @uniquename String @default("")}
Default value for a Boolean
model User {email String @uniqueisAdmin Boolean @default(false)}
Default value for a DateTime
Note that static default values for DateTime
are based on the ISO 8601 standard. However, they must always include the time including the time offsets from UTC.
model User {email String @uniquedata DateTime @default("2020-03-19T14:21:00+0200")}
Default value for a Bytes
model User {email String @uniquesecret Bytes @default("SGVsbG8gd29ybGQ=")}
Default value for an enum
enum Role {USERADMIN}
model User {id Int @id @default(autoincrement())email String @uniquename String?role Role @default(USER)posts Post[]profile Profile?}
@unique
Defines a unique constraint for this field.
Remarks
- Corresponding database type:
UNIQUE
NULL
values are considered to be distinct (multiple rows withNULL
values in the same column are allowed)- Can be defined on any scalar field
- Cannot be defined on a relation field
- A field annotated with
@unique
can be optional or required - A field annotated with
@unique
must be required if it represents the only unique constraint on a model without an@id
/@@id
- A model can have any number of unique constraints
- Adding a unique constraint automatically adds a corresponding unique index to the specified column(s).
Arguments
N/A
Signature
@unique
Examples
Specify a unique attribute on a required String
field
model User {email String @uniquename String}
Specify a unique attribute on an optional String
field
model User {id Int @id @default(autoincrement())email String? @uniquename String}
Specify a unique attribute on relation scalar field authorId
model Post {author User @relation(fields: [authorId], references: [id])authorId Int @uniquetitle Stringpublished Boolean @default(false)}model User {id Int @id @default(autoincrement())email String? @uniquename StringPost Post[]}
Specify a unique attribute with cuid()
values as default values
model User {id String @unique @default(cuid())name String}
@@unique
Defines a compound unique constraint for the specified fields.
Remarks
Corresponding database type:
UNIQUE
A
@@unique
block is required if it represents the only unique constraint on a model without an@id
/@@id
All fields that make up the unique constraint must be mandatory fields. The following model is not valid because
id
could benull
:model User {firstname Intlastname Intid Int?@@unique([firstname, lastname, id])}The reason for this behavior is that SQL considers
null
values to be distinct, which means that two rows that look identical are considered unique:firstname | lastname | id-----------+----------+------John | Smith | nullJohn | Smith | nullA model can have any number of
@@unique
blocksAdding a unique constraint automatically adds a corresponding unique index to the specified column(s).
Arguments
Name | Required | Type | Description |
---|---|---|---|
fields | Yes | FieldReference[] | A list of field names - for example, ["firstname", "lastname"] . Fields must be mandatory - see remarks. |
name | No | String | The name of the unique combination of fields - defaults to fieldName1_fieldName2_fieldName3 |
The name of the fields
argument on the @@unique
attribute can be omitted:
@@unique(fields: [title, author])@@unique([title, author])@@unique(field: [title, author], name: "titleAuthor")
Signature
@@unique(_ fields: FieldReference[], name: String?)
Examples
Specify a multi-field unique attribute on two String
fields
model User {id Int @default(autoincrement())firstName StringlastName StringisAdmin Boolean @default(false)@@unique([firstName, lastName])}
To get a retrieve a user, use the use the generated field name (firstname_lastname
):
const user = await prisma.user.findUnique({where: {firstName_lastName: {firstName: 'Alice',lastName: 'Smith',isAdmmin: true,}},})
Specify a multi-field unique attribute on two String
fields and one Boolean
field
model User {id Int @default(autoincrement())firstName StringlastName StringisAdmin Boolean @default(false)@@unique([firstName, lastName, isAdmin])}
Specify a multi-field unique attribute that includes a relation field
model Post {id Int @default(autoincrement())author User @relation(fields: [authorId], references: [id])authorId Inttitle Stringpublished Boolean @default(false)@@unique([authorId, title])}model User {id Int @id @default(autoincrement())email String @uniqueposts Post[]}
Specify a custom name
for a multi-field unique attribute
model User {id Int @default(autoincrement())firstName StringlastName StringisAdmin Boolean @default(false)@@unique(fields: [firstName, lastName, isAdmin], name: "admin_identifier")}
To get a retrieve a user, use the custom field name (admin_identifier
):
const user = await prisma.user.findUnique({where: {admin_identifier: {firstName: 'Alice',lastName: 'Smith',isAdmmin: true,}},})
@@index
Defines an index in the database.
Remarks
- Corresponding database type:
INDEX
Limitations
It is currently not possible to provide more configuration options to the index:
- PostgreSQL
- Define index fields as expressions (e.g.
CREATE INDEX title ON public."Post"((lower(title)) text_ops);
) - Specify index methods with
USING
; PostgreSQL supports these index methods: B-tree, hash, GiST, and GIN; Prisma uses B-Tree by default - Define partial indexes with
WHERE
- Create indexes concurrently with
CONCURRENTLY
- Define index fields as expressions (e.g.
- MySQL
- Specify index methods with
USING
; MySQL supports these index methods: B-tree, hash; Prisma uses B-Tree by default
- Specify index methods with
Note that while you can't configure these option in your Prisma schema, you can still configure them on the database-level directly.
Arguments
Name | Required | Type | Description |
---|---|---|---|
fields | Yes | FieldReference[] | A list of field names - for example, ["firstname", "lastname"] |
name | No | name | The name of the index in the underlying database (Prisma generates an index name that respects identifier length limits if you do not specify a name. Prisma uses the following naming convention: tablename.field1_field2_field3_unique ) |
The name of the fields
argument on the @@index
attribute can be omitted:
@@index(fields: [title, author])@@index([title, author])
Signature
@@index(_ fields: FieldReference[], name: String?)
Examples
Assume you want to add an index for the title
field of the Post
model
Define a single-column index
model Post {id Int @id @default(autoincrement())title Stringcontent String?@@index([title])}
Define a multi-column index
model Post {id Int @id @default(autoincrement())title Stringcontent String?@@index([title, content])}
Define an index with a name
model Post {id Int @id @default(autoincrement())title Stringcontent String?@@index(fields: [title, content], name: "main_index")}
@relation
Defines meta information about the relation. Learn more.
Remarks
- Corresponding database types:
FOREIGN KEY
/REFERENCES
Arguments
Name | Type | Required | Description | Example |
---|---|---|---|---|
name | String | Sometimes (e.g. to disambiguate a relation) | Defines the name of the relationship. In an m-n-relation, it also determines the name of the underlying relation table. | "CategoryOnPost" , "MyRelation" |
fields | FieldReference[] | On annotated relation fields | A list of [fields](../../../concepts/components/prisma-schema/data-model#defining-fields of the current model | ["authorId"] , ["authorFirstName, authorLastName"] |
references | FieldReference[] | On annotated relation fields | A list of [fields](../../../concepts/components/prisma-schema/data-model#defining-fields of the model on the other side of the relation | ["id"] , ["firstName, lastName"] |
The name of the name
argument on the @relation
attribute can be omitted (references
is required):
@relation(name: "UserOnPost", references: [id])@relation("UserOnPost", references: [id])// or@relation(name: "UserOnPost")@relation("UserOnPost")
Signature
@relation(_ name: String?, fields: FieldReference[]?, references: FieldReference[]?)
@map
Maps a field name or enum value from the Prisma schema to a column with a different name in the database. If you do not use @map
, the field name matches the column name exactly.
See Using custom model and field names to see how
@map
and@@map
changes the generated Prisma Client.
Remarks
@map
does not rename the column in the database@map
does change the field names in the generated client
Arguments
Name | Type | Required | Description | Example |
---|---|---|---|---|
name | String | Yes | The database column name. | "comments" , "someTableName" |
The name of the name
argument on the @map
attribute can be omitted:
@map(name: "is_admin")@map("users")
Signature
@map(_ name: String)
Examples
Map the firstName
field to a column called first_name
model User {id Int @id @default(autoincrement())firstName String @map("first_name")}
The generated client:
await prisma.user.create({data: {firstName: 'Yewande', // first_name --> firstName},})
Map an enum named ADMIN
to a database enum named admin
enum Role {ADMIN @map("admin")CUSTOMER}
@@map
Maps the Prisma schema model name to a table with a different name, or an enum name to a different underlying enum in the database. If you do not use @@map
, the model name matches the table name exactly.
See Using custom model and field names to see how
@map
and@@map
changes the generated Prisma Client.
Remarks
@@map
does not rename the table in the database@@map
does change the model names in the generated client
Arguments
Name | Type | Required | Description | Example |
---|---|---|---|---|
name | String | Yes | The database table name. | "comments" , "someTableName" |
The name of the name
argument on the @@map
attribute can be omitted
@@map(name: "users")@@map("users")
Signature
@@map(_ name: String)
Examples
Map the User
model to a database table called users
model User {id Int @id @default(autoincrement())name String@@map("users")}
The generated client:
await prisma.user.create({// users --> userdata: {name: 'Yewande',},})
Map the Role
enum to a native enum in the database called _Role
its values to lowercase values in the database
enum Role {ADMIN @map("admin")CUSTOMER @map("customer")@@map("_Role")}
@updatedAt
Automatically stores the time when a record was last updated. If you don't supply a time yourself, the Prisma Client will automatically set the value for fields with this attribute.
Remarks
- Compatible with
DateTime
fields
Arguments
N/A
Signature
@updatedAt
Examples
schema.prisma
1model Post {2 id String @id3 updatedAt DateTime @updatedAt4}
@ignore
In 2.17.0 and later, Prisma adds @ignore
to fields that refer to invalid models. In the following example, tags
is ignored because it refers to an invalid model.
/// The underlying table does not contain a valid unique identifier and can therefore currently not be handled by the Prisma Client.model Tag {id String? @db.VarChar@@ignore}model Product {id Int @default(autoincrement())tags Tag[] @ignore}
@@ignore
In 2.17.0 and later, Prisma adds @@ignore
to an invalid model instead of commenting it out. In the following example, Tag
is ignored because it does not have a valid unique identifier.
/// The underlying table does not contain a valid unique identifier and can therefore currently not be handled by the Prisma Client.model Tag {id String? @db.VarChar@@ignore}
Attribute functions
autoincrement()
Create a sequence of integers in the underlying database and assign the incremented values to the ID values of the created records based on the sequence.
Remarks
Compatible with
Int
Implemented on the database-level, meaning that it manifests in the database schema and can be recognized through introspection. Database implementations:
PostgreSQL MySQL SQLite SERIAL
typeAUTO_INCREMENT
attributeAUTOINCREMENT
keyword
Examples
Generate auto-incrementing integers as IDs
model User {id Int @id @default(autoincrement())name String}
cuid()
Generate a globally unique identifier based on the cuid
spec.
Remarks
- Compatible with
String
- Implemented by Prisma and therefore not "visible" in the underlying database schema. You can still use
cuid()
when using introspection by manually changing your Prisma schema and generating Prisma Client, in that case the values will be generated by Prisma's query engine.
Examples
Generate cuid()
values as IDs
model User {id String @id @default(cuid())name String}
uuid()
Generate a globally unique identifier based on the UUID spec.
Remarks
- Compatible with
String
- Implemented by Prisma and therefore not "visible" in the underlying database schema. You can still use
uuid()
when using introspection by manually changing your Prisma schema and generating Prisma Client, in that case the values will be generated by Prisma's query engine.
Examples
Generate uuid()
values as IDs
model User {id String @id @default(uuid())name String}
now()
Set a timestamp of the time when a record is created.
Remarks
Compatible with
DateTime
Implemented on the database-level, meaning that it manifests in the database schema and can be recognized through introspection. Database implementations:
PostgreSQL MySQL SQLite CURRENT_TIMESTAMP
and aliases likenow()
CURRENT_TIMESTAMP
and aliases likenow()
CURRENT_TIMESTAMP
and aliases likedate('now')
dbgenerated()
Represents default values that cannot be expressed in the Prisma schema (such as random()
).
Remarks
Examples
Set default value for Unsupported
type
circle Unsupported("circle")? @default(dbgenerated("'<(10,4),11>'::circle"))
Override default value behavior for supported types
You can also use dbgenerated()
to set the default value for supported types. For example, you can generate UUIDs at database level rather than rely on Prisma's uuid()
:
model User {id String @id @db.Uuid @default(dbgenerated("gen_random_uuid()"))id String @id @db.Uuid @default(uuid())test String?}
Attribute argument types
FieldReference[]
An array of field names: [id]
, [firstName, lastName]
String
A variable length text in double quotes: ""
, "Hello World"
, "Alice"
Expression
An expression that can be evaluated by Prisma: 42.0
, ""
, Bob
, now()
, cuid()
enum
Defines an enum (only available if enums are supported natively by your database)
Remarks
Database support
Naming conventions
- Enum names must start with a letter (they are typically spelled in PascalCase)
- Enums must use the singular form (e.g.
Role
instead ofrole
,roles
orRoles
). - Must adhere to the following regular expression:
[A-Za-z][A-Za-z0-9_]*
Examples
Specify an enum
with two possible values
enum Role {USERADMIN}model User {id Int @id @default(autoincrement())role Role}
Specify an enum
with two possible values and set a default value
enum Role {USERADMIN}model User {id Int @id @default(autoincrement())role Role @default(USER)}