datasource

Defines a data source in the Prisma schema.

Fields

A datasource block accepts the following fields:

NameRequiredTypeDescription
providerYesArray of enums (postgresql, mysql, sqlite)Describes which data source connectors to use.
urlYesString (URL)Connection URL including authentication info. Most connectors use the syntax provided by the database, learn more.

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.
  • If you specify multiple providers, Prisma Client uses the provider that matches the connection string defined by the url field.

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 multiple providers

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", "mysql"] // Multiple providers
url = "postgresql://johndoe:mypassword@localhost:5432/mydb?schema=public"
}

In this example, Prisma Client will use the postgresql provider because this provider matches the format of the url field.

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:

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
binaryTargetsNoList of Enums (see below)Specify the OS on which the Prisma Client will run to ensure binary compatibility of the query engine. Default: native
previewFeaturesNoList of EnumsUse 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 OSBuild name
Mac OSdarwin
Windows
Build OSBuild name
Windowswindows
Linux (Alpine)
Build OSBuild name
Alpinelinux-musl
Linux (Debian)
Build OSBuild nameOpenSSL
Debian 8 (Jessie)debian-openssl-1.0.x1.0.x
Debian 9 (Stretch)debian-openssl-1.1.x1.1.x
Debian 10 (Jessie)debian-openssl-1.1.x1.1.x
Linux (Ubuntu)
Build OSBuild 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
Linux (CentOS)
Build OSBuild nameOpenSSL
CentOS 6rhel-openssl-1.0.x1.0.x
CentOS 7rhel-openssl-1.0.x1.0.x
Linux (Fedora)
Build OSBuild 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 OSBuild nameOpenSSL
Linux Mint 18debian-openssl-1.0.x1.0.x
Linux Mint 19debian-openssl-1.1.x1.1.x
Linux (Arch Linux)
Build OSBuild nameOpenSSL
Arch Linux 2019.09.01debian-openssl-1.1.x1.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 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

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.

Note: Prisma models also have model field types that define relations between models.

String

Variable length text.

Connectors

PostgreSQLMySQLSQLiteRaw JSON
textvarchar(191)TEXTstring

Generators

Prisma Client JS
string

Boolean

True or false value

Connectors

PostgreSQLMySQLSQLiteRaw JSON
booleanBOOLEANINTEGERboolean

Generators

Prisma Client JS
boolean

Int

Connectors

PostgreSQLMySQLSQLiteRaw JSON
integerINTINTEGERnumber

Generators

Prisma Client JS
number

Float

Floating point number

Connectors

PostgreSQLMySQLSQLiteRaw JSON
decimal(65,30)DECIMAL(65,30)REALnumber

Generators

Prisma Client JS
number

DateTime

Remarks

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

Connectors

PostgreSQLMySQLSQLiteRaw JSON
timestamp(3)DATETIME(3)NUMERICn/a

Generators

Prisma Client JS
Date

Json

A JSON object.

Connectors

PostgreSQLMySQLSQLiteRaw JSON
json / jsonbJSONn/aobject

Generators

Prisma Client JS
object

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(autincremenent())
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(autincremenent())
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 to have the ID initialized by your database. To do so, you can annotate it 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 @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 String without a default value
model User {
id String @id
name 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 @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

Defines a multi-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

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[])
Specify a multi-field ID on two String fields
model User {
firstName String
lastName String
email String @unique
isAdmin Boolean @default(false)
@@id([firstName, lastName])
}

When creating new User records, you now must provide a unique combination of values for firstName and lastName:

const user = await prisma.user.create({
data: {
firstName: 'Alice',
lastName: 'Smith',
},
})
Specify a multi-field ID on two String fields and one Boolean field
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:

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 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:

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.

    Note: You cannot manually add dbgenerated() to your schema.

  • 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

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)

Examples

Default value for an Int
model User {
email String @unique
profileViews Int @default(0)
}
Default value for a Float
model User {
email String @unique
number Float @default(1.1)
}
Default value for a String
model User {
email String @unique
name String @default("")
}
Default value for a Boolean
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. However, they must always include the time including the time offsets from UTC.

model User {
email String @unique
data DateTime @default("2020-03-19T14:21:00+0200")
}
Default value for an enum
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

  • Corresponding database type: UNIQUE
  • NULL values are considered to be distinct (multiple rows with NULL 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 @unique
name String
}
Specify a unique attribute on an optional String field
model User {
id Int @id @default(autoincrement())
email String? @unique
name String
}
Specify a unique attribute on relation scalar field authorId
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
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 be null:

    model User {
    firstname Int
    lastname Int
    id 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 | null
    John | Smith | null
  • A model can have any number of @@unique blocks

  • Adding a unique constraint automatically adds a corresponding unique index to the specified column(s).

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(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 String
lastName String
isAdmin Boolean @default(false)
@@unique([firstName, lastName])
}
Specify a multi-field unique attribute on two String fields and one Boolean field
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
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
model User {
id Int @default(autoincrement())
firstName String
lastName String
isAdmin Boolean @default(false)
@@unique(fields: [firstName, lastName, isAdmin], name: "admin_identifier")
}

See also: Custom name property on @@unique attributes in the Prisma Client

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

Note that while you can't configure these option in your Prisma schema, you can still configure them on the database-level directly.

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[], 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 String
content String?
@@index([title])
}
Define a multi-column index
model Post {
id Int @id @default(autoincrement())
title String
content String?
@@index([title, content])
}
Define an index with a name
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

  • Corresponding database types: FOREIGN KEY / REFERENCES

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](data-model#defining-fields of the current model["authorId"], ["authorFirstName, authorLastName"]
referencesFieldReference[]On annotated relation fieldsA list of [fields](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 different column name.

Arguments

NameTypeRequiredDescriptionExample
nameStringYesThe 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 User model to a table called users
model User {
id Int @id @default(autoincrement())
name String
@@map("users")
}
Map the firstName field to a column called first_name
model User {
id Int @id @default(autoincrement())
firstName String @map("first_name")
}
Map an enum named ADMIN to a database enu named admin
enum Role {
ADMIN @map("admin")
CUSTOMER
}

@@map

Maps a model name from the Prisma schema to a different table name, or an enum name to a different underlying enum in the database.

Arguments

NameTypeRequiredDescriptionExample
nameStringYesThe database column 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 table called users and firstName field to a column called first_name
model User {
id Int @id @default(autoincrement())
firstName String @map("first_name")
@@map("users")
}
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. Prisma Client automatically sets the value of fields with this attribute.

Remarks

Arguments

N/A

Signature

@updatedAt

Examples

model Post {
id String @id
updatedAt DateTime @updatedAt
}

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:

    PostgreSQLMySQLSQLite
    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

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

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:

    PostgreSQLMySQLSQLite
    CURRENT_TIMESTAMP and aliases like now()CURRENT_TIMESTAMP and aliases like now()CURRENT_TIMESTAMP and aliases like date('now')

Examples

dbgenerated()

Represents default values that can't be expressed in the Prisma schema (such as random()). Only available after introspection.

Remarks

  • Compatible with any type

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

See Database features Misc.

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

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

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