Prisma schema reference

datasource

Defines a data source in the Prisma schema.

Fields

A datasource block accepts the following fields:

NameRequiredTypeDescription
providerYesString (postgresql, mysql, sqlite, sqlserver, mongodb)Describes which data source connectors to use.
urlYesString (URL)Connection URL including authentication info. Most connectors use the syntax provided by the database.
shadowDatabaseUrlNoString (URL)Connection URL to the shadow database used by Prisma Migrate. Allows you to use a cloud-hosted database as the shadow database.
referentialIntegrityNoString (foreignKeys, prisma)Allows setting the referential integrity.

The following providers are available:

  • sqlite
  • postgresql
  • mysql
  • sqlserver
  • mongodb (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 or datasource 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
  • Port: 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
  • Port: 3306
  • Database name: mydb
datasource db {
provider = "mysql"
url = "mysql://johndoe:mypassword@localhost:3306/mydb"
}

Learn more about MySQL connection strings here.

Specify a MongoDB data source

  • User: root
  • Password: password
  • Host: cluster1.test1.mongodb.net
  • Port: N/A
  • Database name: testing
datasource db {
provider = "mongodb"
url = "mongodb+srv://root:password@cluster1.test1.mongodb.net/testing?retryWrites=true&w=majority"
}

Learn more about MongoDB 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
  • Port: 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:

NameRequiredTypeDescription
providerYesString (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.
outputNoString (file path)Determines the location for the generated client, learn more. Default: node_modules/.prisma/client
previewFeaturesNoList of EnumsUse intellisense to see list of currently available Preview features (Ctrl+Space in Visual Studio Code) Default: none
engineTypeNoEnum (library or binary)Defines the query engine type to download and use. Default: library
binaryTargetsNoList of Enums (see below)Specify the OS on which the Prisma Client will run to ensure compatibility of the query engine. Default: native

binaryTargets options

The following tables list all supported operating systems with the name of platform to specify in binaryTargets.

Mac OS
Build OSPrisma engine build name
Mac OSdarwin
Windows
Build OSPrisma engine build name
Windowswindows
Linux (Alpine)
Build OSPrisma engine build name
Alpinelinux-musl
Linux (Debian)
Build OSPrisma engine build nameOpenSSL
Debian 8 (Jessie)debian-openssl-1.0.x1.0.x
Debian 9 (Stretch)debian-openssl-1.1.x1.1.x
Debian 10 (Buster)debian-openssl-1.1.x1.1.x
Debian 11 (Bullseye)debian-openssl-1.1.x1.1.x
Linux (Ubuntu)
Build OSPrisma engine build nameOpenSSL
Ubuntu 14.04 (trusty)debian-openssl-1.0.x1.0.x
Ubuntu 16.04 (xenial)debian-openssl-1.0.x1.0.x
Ubuntu 18.04 (bionic)debian-openssl-1.1.x1.1.x
Ubuntu 19.04 (disco)debian-openssl-1.1.x1.1.x
Ubuntu 20.04 (focal)debian-openssl-1.1.x1.1.x
Linux (CentOS)
Build OSPrisma engine build nameOpenSSL
CentOS 7rhel-openssl-1.0.x1.0.x
CentOSrhel-openssl-1.0.x1.0.x
Linux (Fedora)
Build OSPrisma engine build nameOpenSSL
Fedora 28rhel-openssl-1.1.x1.1.x
Fedora 29rhel-openssl-1.1.x1.1.x
Fedora 30rhel-openssl-1.1.x1.1.x
Linux (Linux Mint)
Build OSPrisma engine build nameOpenSSL
Linux Mint 18debian-openssl-1.0.x1.0.x
Linux Mint 19debian-openssl-1.1.x1.1.x
Linux (Arch Linux)
Build OSPrisma engine build nameOpenSSL
Arch Linux 2019.09.01debian-openssl-1.1.x1.1.x
Linux ARM64
Build OSPrisma engine build nameOpenSSL
Linux ARM64-based distrolinux-arm64-openssl-1.0.x1.0.x
Linux ARM64-based distrolinux-arm64-openssl-1.1.x1.1.x

Examples

Specify the prisma-client-js generator with the default output, previewFeatures, engineType 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, engineType and binaryTargets (and implicitly previewFeatures):

generator client {
provider = "prisma-client-js"
output = "node_modules/.prisma/client"
engineType = "library"
binaryTargets = ["native"]
}

Specify a custom output location for Prisma Client

This example shows how to define a custom output location of the generated asset to override the default one.

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 of user, users or Users)
  • 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

Relational databases
MongoDB
model User {
email String @unique // `email` can not be optional because it's the only unique field on the model
name 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

ConnectorDefault mapping
PostgreSQLtext
Microsoft SQLnvarchar(1000)
MySQLvarchar(191)
MongoDBString
SQLiteTEXT

PostgreSQL

Native database typeNative database type attributeNotes
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.CitextOnly available if Citext extension is enabled.

MySQL

Native database typeNative database type attributeNotes
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)
}

MongoDB (Preview)

String

Native database type attributeNotes
@db.String
@db.ObjectIdRequired if the underlying BSON type is OBJECT_ID (ID fields, relation scalars)

Microsoft SQL Server

Native database typeNative database type attributeNotes
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

ConnectorDefault mapping
PostgreSQLboolean
Microsoft SQLtinyint
MySQLTINYINT(1)
MongoDBBool
SQLiteINTEGER

PostgreSQL

Native database typesNative database type attributeNotes
boolean@db.Boolean

MySQL

Native database typesNative database type attributeNotes
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

MongoDB (Preview)

Bool

Microsoft SQL Server

Native database typesNative database type attributeNotes
bit@db.Bit

SQLite

INTEGER

Clients

Prisma Client JS
boolean

Int

Default type mappings

ConnectorDefault mapping
PostgreSQLinteger
Microsoft SQLint
MySQLINT
MongoDBInt
SQLiteINTEGER

PostgreSQL

Native database typesNative database type attributeNotes
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 typesNative database type attributeNotes
INT@db.Int
INT UNSIGNED@db.UnsignedInt
SMALLINT@db.SmallInt
SMALLINT UNSIGNED@db.UnsignedSmallInt
MEDIUMINT@db.MediumInt
MEDIUMINT UNSIGNED@db.UnsignedMediumInt
TINYINT@db.TinyIntTINYINT 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.UnsignedTinyIntTINYINT(1) UNSIGNED maps to Int, not Boolean
YEAR@db.Year

MongoDB (Preview)

Int

Native database type attributeNotes
@db.Int
@db.Long

Microsoft SQL Server

Native database typesNative database type attributeNotes
int@db.Int
smallint@db.SmallInt
tinyint@db.TinyInt
bit@db.Bit

SQLite

INTEGER

Clients

Prisma Client JS
number

BigInt

BigInt is available in version 2.17.0 and later.

Default type mappings

ConnectorDefault mapping
PostgreSQLinteger
Microsoft SQLint
MySQLINT
MongoDBLong
SQLiteINTEGER

PostgreSQL

Native database typesNative database type attributeNotes
bigint | int8@db.BigInt
bigserial | serial8@db.BigInt @default(autoincrement())

MySQL

Native database typesNative database type attributeNotes
BIGINT@db.BigInt
SERIAL@db.UnsignedBigInt @default(autoincrement())

MongoDB (Preview)

Long

Microsoft SQL Server

Native database typesNative database type attributeNotes
bigint@db.BigInt

SQLite

INTEGER

Clients

ClientTypeDescription
Prisma Client JSBigIntSee examples of working with BigInt

Float

Floating point number.

Float maps to Double 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

ConnectorDefault mapping
PostgreSQLdouble precision
Microsoft SQLfloat(53)
MySQLDOUBLE
MongoDBDouble
SQLiteREAL

PostgreSQL

Native database typesNative database type attributeNotes
double precision@db.DoublePrecision
real@db.Real

MySQL

Native database typesNative database type attributeNotes
FLOAT@db.Float
DOUBLE@db.Double

MongoDB (Preview)

Double

Microsoft SQL Server

Native database typesNative 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

ConnectorDefault mapping
PostgreSQLdecimal(65,30)
Microsoft SQLdecimal(32,16)
MySQLDECIMAL(65,30)
MongoDBDecimal
SQLiteDECIMAL

PostgreSQL

Native database typesNative database type attributeNotes
decimal | numeric@db.Decimal(p, s)
money@db.Money
  • p (precision), the maximum total number of decimal digits to be stored. s (scale), the number of decimal digits that are stored to the right of the decimal point.

MySQL

Native database typesNative database type attributeNotes
DECIMAL | NUMERIC@db.Decimal(p, s)
  • p (precision), the maximum total number of decimal digits to be stored. s (scale), the number of decimal digits that are stored to the right of the decimal point.

MongoDB (Preview)

Decimal

Microsoft SQL Server

Native database typesNative database type attributeNotes
decimal | numeric@db.Decimal(p, s)
  • p (precision), the maximum total number of decimal digits to be stored. s (scale), the number of decimal digits that are stored to the right of the decimal point.

SQLite

DECIMAL (changed from REAL in 2.17.0)

Clients

ClientTypeDescription
Prisma Client JSDecimalSee examples of working with Decimal

DateTime

Remarks

  • Prisma Client returns all DateTime as ISO 8601-formatted strings.

Default type mappings

ConnectorDefault mapping
PostgreSQLtimestamp(3)
Microsoft SQLdatetime2
MySQLDATETIME(3)
MongoDBTimestamp
SQLiteNUMERIC

PostgreSQL

Native database typesNative database type attributeNotes
timestamp@db.Timestamp(x)
timestamptz@db.Timestamptz(x)
date@db.Date
time@db.Time(x)
timetz@db.Timetz(x)

MySQL

Native database typesNative database type attributeNotes
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

MongoDB (Preview)

Timestamp

Microsoft SQL Server

Native database typesNative database type attributeNotes
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

ConnectorDefault mapping
PostgreSQLjsonb
Microsoft SQLnvarchar(1000)
MySQLJSON
MongoDBA valid BSON object (Relaxed mode)
SQLiteNot supported

PostgreSQL

Native database typesNative database type attributeNotes
json@db.Json
jsonb@db.JsonB

MySQL

Native database typesNative database type attributeNotes
JSON@db.Json

MongoDB (Preview)

A valid BSON object (Relaxed mode)

Microsoft SQL Server

Microsoft 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 typesNative database type attribute
JSON@db.NVarChar

SQLite

Not supported

Clients

Prisma Client JS
object

Bytes

Bytes is available in version 2.17.0 and later.

Default type mappings

ConnectorDefault mapping
PostgreSQLbytea
Microsoft SQLvarbinary
MySQLLONGBLOB
MongoDBBinData
SQLiteNot supported

PostgreSQL

Native database typesNative database type attribute
bytea@db.ByteA

MySQL

Native database typesNative database type attributeNotes
LONGBLOB@db.LongBlob
BINARY@db.Binary
VARBINARY@db.VarBinary
TINYBLOB@db.TinyBlob
BLOB@db.Blob
MEDIUMBLOB@db.MediumBlob
BIT@db.Bit

MongoDB (Preview)

BinData

Native database type attributeNotes
@db.ObjectIdRequired if the underlying BSON type is OBJECT_ID (ID fields, relation scalars)
@db.BinData

Microsoft SQL Server

Native database typesNative database type attributeNotes
binary@db.Binary
varbinary@db.VarBinary
image@db.Image

SQLite

Not supported

Clients

ClientTypeDescription
Prisma Client JSBufferSee examples of working with Buffer

Unsupported

Not supported by MongoDB
The MongoDB connector does not currently support the Unsupported type.

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 db pull 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(..) and prisma.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 does not currently support their types.
    * Model "Post", field: "circle", original data type: "circle"

Examples

Relational databases only
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[]?).
Relational databases
  • 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).
MongoDB
  • Scalar lists require @db.Array(String), where the parameter matches the scalar type (for example, String[])

Examples

Relational databases
MongoDB
model User {
id Int @id @default(autoincrement())
posts Post[]
favoriteColors String[]
}

? 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

General
  • Cannot be defined on a relation field
  • Cannot be optional
Relational databases
MongoDB
  • Corresponding database type: Any valid BSON type, except arrays

  • Every model must define an @id field

  • The underlying ID field name is always _id, and must be mapped with @map(_id)

  • Can be defined on any scalar field (String, Int, enum) unless you want to use ObjectId in your database

  • To use an ObjectId as your ID, you must:

    • Use the String or Bytes field type

    • Annotate your field with @db.ObjectId:

      id String @db.ObjectId @map("_id")
    • Optionally, annotate your field with a @default() value that uses the dbgenerated() function to auto-generate an ObjectId

      id String @db.ObjectId @map("_id") @default(dbgenerated())
  • cuid() and uuid() are supported but do not generate a valid ObjectId - use dbgenerated() instead for @id

  • autoincrement() is not supported

Arguments

N/A

Signature

@id(map: String?)

Note: Until version 3.0.0, the signiture was:

@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 auto-incrementing integers as IDs
Relational databases only
model User {
id Int @id @default(autoincrement())
name String
}
Generate ObjectId as IDs (MongoDB only)
MongoDB only
model User {
id String @id @default(dbgenerated()) @map("_id") @db.ObjectId
name String
}
Generate cuid() values as IDs
Relational databases only
MongoDB
model User {
id String @id @default(cuid())
name String
}
Generate uuid() values as IDs
Relational databases
MongoDB
model User {
id String @id @default(uuid())
name String
}
Single-field IDs without default values

In the following example, id does not have a default value:

Relational databases
MongoDB
model User {
id String @id
name 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 relation scalar field without a default value

In the following example, authorId is a both a relation scalar and the ID of Profile:

Relational databases
MongoDB
model Profile {
authorId Int @id
author User @relation(fields: [authorId], references: [id])
bio String
}
model User {
id Int @id
email String @unique
name 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

Not supported by MongoDB
The MongoDB connector does not support composite IDs.

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

NameRequiredTypeDescription
fieldsYesFieldReference[]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[], name: String?, map: String?)

Note: Until version 3.0.0, the signiture was:

@@id(_ fields: FieldReference[])
Specify a multi-field ID on two String fields
Relational databases only
model User {
firstName String
lastName String
email String @unique
isAdmin Boolean @default(false)
@@id([firstName, lastName])
}

When you create a user, you must provide a unique combination of firstName and lastName:

Relational databases only
const user = await prisma.user.create({
data: {
firstName: 'Alice',
lastName: 'Smith',
},
})

To retrieve a user, use the generated composite ID field (firstName_lastName):

Relational databases only
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
Relational databases only
model User {
firstName String
lastName String
email String @unique
isAdmin 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:

Relational databases only
const user = await prisma.user.create({
data: {
firstName: 'Alice',
lastName: 'Smith',
isAdmin: true,
},
})
Specify a multi-field ID that includes a relation field
Relational databases only
model Post {
title String
published Boolean @default(false)
author User @relation(fields: [authorId], references: [id])
authorId Int
@@id([authorId, title])
}
model User {
id Int @default(autoincrement())
email String @unique
name String?
posts Post[]
}

When creating new Post records, you now must provide a unique combination of values for authorId (foreign key) and title:

Relational databases only
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

Relational databases
  • 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.

MongoDB
  • Default values can be a static value (4, "hello") or one of the following functions:

  • Default values are currently not allowed on relation fields in the Prisma schema.

Arguments

NameRequiredTypeDescription
expressionYesAn 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, map: String?)

Note: Until version 3.0.0, the signiture was:

@default(_ value: Expression)

Examples

Default value for an Int
Relational databases
MongoDB
model User {
email String @unique
profileViews Int @default(0)
}
Default value for a Float
Relational databases
MongoDB
model User {
email String @unique
number Float @default(1.1)
}
Default value for Decimal
Relational databases
MongoDB
model User {
email String @unique
number Decimal @default(22.99)
}
Default value for BigInt
Relational databases
MongoDB
model User {
email String @unique
number BigInt @default(34534535435353)
}
Default value for a String
Relational databases
MongoDB
model User {
email String @unique
name String @default("")
}
Default value for a Boolean
Relational databases
MongoDB
model User {
email String @unique
isAdmin Boolean @default(false)
}
Default value for a DateTime

Note that static default values for DateTime are based on the ISO 8601 standard.

Relational databases
MongoDB
model User {
email String @unique
data DateTime @default("2020-03-19T14:21:00+02:00")
}
Default value for a Bytes
Relational databases
MongoDB
model User {
email String @unique
secret Bytes @default("SGVsbG8gd29ybGQ=")
}
Default value for an enum
Relational databases
MongoDB
enum Role {
USER
ADMIN
}
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
role Role @default(USER)
posts Post[]
profile Profile?
}

@unique

Defines a unique constraint for this field.

Remarks

General
  • 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
  • Can be defined on any scalar field
  • Cannot be defined on a relation field
Relational databases
  • Corresponding database type: UNIQUE
  • NULL values are considered to be distinct (multiple rows with NULL values in the same column are allowed)
  • Adding a unique constraint automatically adds a corresponding unique index to the specified column(s).
MongoDB

Arguments

N/A

Signature

@unique(map: String?)

Note: Until version 3.0.0, the signiture was:

@unique

Examples

Specify a unique attribute on a required String field
Relational databases
MongoDB
model User {
email String @unique
name String
}
Specify a unique attribute on an optional String field
Relational databases
MongoDB
model User {
id Int @id @default(autoincrement())
email String? @unique
name String
}
Specify a unique attribute on relation scalar field authorId
Relational databases
MongoDB
model Post {
author User @relation(fields: [authorId], references: [id])
authorId Int @unique
title String
published Boolean @default(false)
}
model User {
id Int @id @default(autoincrement())
email String? @unique
name String
Post Post[]
}
Specify a unique attribute with cuid() values as default values
Relational databases
MongoDB
model User {
token String @unique @default(cuid())
name String
}

@@unique

Defines a compound unique constraint for the specified fields.

Remarks

General
  • All fields that make up the unique constraint must be mandatory fields. The following model is not valid because id could be null:

    model User {
    firstname Int
    lastname Int
    id Int?
    @@unique([firstname, lastname, id])
    }

    The reason for this behavior is that all connectors consider null values to be distinct, which means that two rows that look identical are considered unique:

    firstname | lastname | id
    -----------+----------+------
    John | Smith | null
    John | Smith | null
  • A model can have any number of @@unique blocks

Relational databases
  • Corresponding database type: UNIQUE
  • A @@unique block is required if it represents the only unique constraint on a model without an @id / @@id
  • Adding a unique constraint automatically adds a corresponding unique index to the specified column(s)
MongoDB
  • Enforced by a compound index in MongoDB - you must create this index yourself
  • A @@unique block cannot be used as the only unique identifier for a model - MongoDB requires an @id field

Arguments

NameRequiredTypeDescription
fieldsYesFieldReference[]A list of field names - for example, ["firstname", "lastname"]. Fields must be mandatory - see remarks.
nameNoStringThe 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(fields: [title, author], name: "titleAuthor")

Signature

@@unique(_ fields: FieldReference[], name: String?, map: String?)

Note: Until version 3.0.0, the signiture was:

@@unique(_ fields: FieldReference[], name: String?)

Examples

Specify a multi-field unique attribute on two String fields
Relational databases
MongoDB
model User {
id Int @default(autoincrement())
firstName String
lastName String
isAdmin Boolean @default(false)
@@unique([firstName, lastName])
}

To retrieve a user, use the generated field name (firstname_lastname):

const user = await prisma.user.findUnique({
where: {
firstName_lastName: {
firstName: 'Alice',
lastName: 'Smith',
isAdmin: true,
},
},
})
Specify a multi-field unique attribute on two String fields and one Boolean field
Relational databases
MongoDB
model User {
id Int @default(autoincrement())
firstName String
lastName String
isAdmin Boolean @default(false)
@@unique([firstName, lastName, isAdmin])
}
Specify a multi-field unique attribute that includes a relation field
Relational databases
MongoDB
model Post {
id Int @default(autoincrement())
author User @relation(fields: [authorId], references: [id])
authorId Int
title String
published Boolean @default(false)
@@unique([authorId, title])
}
model User {
id Int @id @default(autoincrement())
email String @unique
posts Post[]
}
Specify a custom name for a multi-field unique attribute
Relational databases
MongoDB
model User {
id Int @default(autoincrement())
firstName String
lastName String
isAdmin Boolean @default(false)
@@unique(fields: [firstName, lastName, isAdmin], name: "admin_identifier")
}

To retrieve a user, use the custom field name (admin_identifier):

const user = await prisma.user.findUnique({
where: {
admin_identifier: {
firstName: 'Alice',
lastName: 'Smith',
isAdmin: true,
},
},
})

@@index

Defines an index in the database.

Remarks

Relational databases
  • Corresponding database type: INDEX
  • 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
    • MySQL
      • Specify index methods with USING; MySQL supports these index methods: B-tree, hash; Prisma uses B-Tree by default

While you cannot configure these option in your Prisma schema, you can still configure them on the database-level directly.

MongoDB

Not currently used by MongoDB
Introspection and Prisma Migrate do not currently support MongoDB, which means Prisma Migrate will never use the @@index attribute to create an index in the database, and Introspection will never add an @@index attribute to the Prisma schema

Arguments

NameRequiredTypeDescription
fieldsYesFieldReference[]A list of field names - for example, ["firstname", "lastname"]
nameNonameThe 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[], map: String?)

Note: Until version 3.0.0, the signiture was:

@@index(_ fields: FieldReference[], name: String?)

The old name argument will still be accepted to avoid a breaking change.

Examples

Assume you want to add an index for the title field of the Post model

Define a single-column index
Relational databases only
model Post {
id Int @id @default(autoincrement())
title String
content String?
@@index([title])
}
Define a multi-column index
Relational databases only
model Post {
id Int @id @default(autoincrement())
title String
content String?
@@index([title, content])
}
Define an index with a name
Relational databases only
model Post {
id Int @id @default(autoincrement())
title String
content String?
@@index(fields: [title, content], name: "main_index")
}

@relation

Defines meta information about the relation. Learn more.

Remarks

Relational databases
  • Corresponding database types: FOREIGN KEY / REFERENCES
MongoDB
  • If your model's primary key is of type ObjectId in the underlying database, both the primary key and the foreign key must be have the @db.ObjectId attribute

Arguments

NameTypeRequiredDescriptionExample
nameStringSometimes (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"
fieldsFieldReference[]On annotated relation fieldsA list of fields of the current model["authorId"], ["authorFirstName, authorLastName"]
referencesFieldReference[]On annotated relation fieldsA list of 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[]?, onDelete: ReferentialAction?, onUpdate: ReferentialAction?, map: String?)

With SQLite, the signature changes to:

@relation(_ name: String?, fields: FieldReference[]?, references: FieldReference[]?, onDelete: ReferentialAction?, onUpdate: ReferentialAction?)

Note: Until version 3.0.0, the signature was:

@relation(_ name: String?, fields: FieldReference[]?, references: FieldReference[]?)

Examples

See: The @relation attribute.

@map

Maps a field name or enum value from the Prisma schema to a column or document field with a different name in the database. If you do not use @map, the Prisma field name matches the column name or document field name exactly.

See Using custom model and field names to see how @map and @@map changes the generated Prisma Client.

Remarks

General
MongoDB

Your @id field must include @map("_id"). For example:

model User {
id String @default(dbgenerated()) @map("_id") @db.ObjectId
}

Arguments

NameTypeRequiredDescriptionExample
nameStringYesThe database column (relational databases) or document field (MongoDB) name."comments", "someFieldName"

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
Relational databases
MongoDB
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 (relational databases) or collection (MongoDB) 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 (relational databases) or collection (MongoDB) name exactly.

See Using custom model and field names to see how @map and @@map changes the generated Prisma Client.

Remarks

Arguments

NameTypeRequiredDescriptionExample
nameStringYesThe database table (relational databases) or collection (MongoDB) name."comments", "someTableOrCollectionName"

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/collection named users
Relational databases
MongoDB
model User {
id Int @id @default(autoincrement())
name String
@@map("users")
}

The generated client:

await prisma.user.create({
// users --> user
data: {
name: 'Yewande',
},
})
Map the Role enum to a native enum in the database named _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 do not supply a time yourself, the Prisma Client will automatically set the value for fields with this attribute.

Remarks

  • Compatible with DateTime fields
  • Implemented at Prisma level

Arguments

N/A

Signature

@updatedAt

Examples

Relational databases
MongoDB
model Post {
id String @id
updatedAt DateTime @updatedAt
}

@ignore

In 2.17.0 and later, Prisma adds @ignore to fields that refer to invalid models when you introspect.

Remarks

  • Ignored fields are excluded from the generated Prisma Client
  • You can manually add @ignore to a field that you want to exclude from the Prisma Client (for example, a field that you do not want Prisma users to update)

Examples

In the following example, tags is ignored because it refers to an invalid model (no @id on `Product):

/// 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.

Remarks

  • Ignored models are excluded from the generated Prisma Client
  • You can manually add @@ignore to a model that you want to exclude from the Prisma Client (for example, a model that you do not want Prisma users to update)

Examples

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()

Not supported by MongoDB
The MongoDB connector does not support the autoincrement() function.

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:

    PostgreSQLMySQLSQLite
    SERIAL typeAUTO_INCREMENT attributeAUTOINCREMENT keyword

Examples

Generate auto-incrementing integers as IDs
Relational databases only
model User {
id Int @id @default(autoincrement())
name String
}

cuid()

Generate a globally unique identifier based on the cuid spec.

Remarks

MongoDB
  • cuid() does not generate a valid ObjectId - use the @db.ObjectId syntax if you want to use ObjectId in the underlying database. However, you can still use cuid() if your _id field is not of type ObjectId.

Examples

Generate cuid() values as IDs
Relational databases
MongoDB
model User {
id String @id @default(cuid())
name String
}

uuid()

Generate a globally unique identifier based on the UUID spec.

Remarks

Note (Relational databases): If you do not want to use Prisma's uuid() function, you can use the native database function with dbgenerated.

MongoDB
  • uuid() does not generate a valid ObjectId - use the @db.ObjectId syntax if you want to use ObjectId in the underlying database. However, you can still use uuid() if your _id field is not of type ObjectId.

Examples

Generate uuid() values as IDs
Relational databases
MongoDB
model User {
id String @id @default(uuid())
name String
}

now()

Set a timestamp of the time when a record is created.

Remarks

General
Relational databases
  • Implemented on the database-level, meaning that it manifests in the database schema and can be recognized through introspection. Database implementations:

    PostgreSQLMySQLSQLite
    CURRENT_TIMESTAMP and aliases like now()CURRENT_TIMESTAMP and aliases like now()CURRENT_TIMESTAMP and aliases like date('now')
MongoDB
  • Implemented at Prisma level

dbgenerated()

Represents default values that cannot be expressed in the Prisma schema (such as random()).

Remarks

Relational databases
MongoDB
  • Can only be used to generate ObjectId for @id fields:

    id String @map("_id") @db.ObjectId @default(dbgenerated())
  • Must be empty: dbgenerated()

Examples

Set default value for Unsupported type
Relational databases only
circle Unsupported("circle")? @default(dbgenerated("'<(10,4),11>'::circle"))
Override default value behavior for supported types
Relational databases only

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 @default(dbgenerated("gen_random_uuid()")) @db.Uuid
id String @id @default(uuid()) @db.Uuid
test String?
}

Note: gen_random_uuid() is a PostgreSQL function. To use it, you must enable the pgcrypto extension.

Generate ObjectId (MongoDB only)
MongoDB only
model User {
id String @id @default(dbgenerated()) @map("_id") @db.ObjectId
name 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

Not supported by SQLite and Microsoft SQL Server
The SQLite connector and the The Microsoft SQL Server connector do not support the enum type.

Defines an enum .

Remarks

  • Enums are natively supported by PostgreSQL and MySQL
  • Enums are implemented and enforced at Prisma level in MongoDB

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 of role, roles or Roles).
  • Must adhere to the following regular expression: [A-Za-z][A-Za-z0-9_]*

Examples

Specify an enum with two possible values

Relational databases
MongoDB
enum Role {
USER
ADMIN
}
model User {
id Int @id @default(autoincrement())
role Role
}

Specify an enum with two possible values and set a default value

Relational databases
MongoDB
enum Role {
USER
ADMIN
}
model User {
id Int @id @default(autoincrement())
role Role @default(USER)
}
Edit this page on GitHub