Prisma schema reference

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

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 CockroachDB data source

In this example, the target database is available with the following credentials:

• User: johndoe
• Password: mypassword
• Host: localhost
• Port: 26257
• Database name: mydb
• Schema name: public

datasource db {
  provider = "cockroachdb"
  url      = "postgresql://johndoe:mypassword@localhost:26257/mydb?schema=public"
}

The format for connection strings is the same as for PostgreSQL. Learn more about PostgreSQL connection strings here.

binaryTargets options

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

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"
}

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.

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?
}

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

Default type mappings

PostgreSQL

MySQL

You can use Prisma Migrate to map @db.Bit(1) to String:

model Model {
  /* ... */
  myField String @db.Bit(1)
}

MongoDB

String

Microsoft SQL Server

SQLite

TEXT

CockroachDB

Note that the xml and citext types supported in PostgreSQL are not currently supported in CockroachDB.

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB

Bool

Microsoft SQL Server

SQLite

INTEGER

CockroachDB

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB

Int

Microsoft SQL Server

SQLite

INTEGER

CockroachDB

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB

Long

Microsoft SQL Server

SQLite

INTEGER

CockroachDB

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB

Double

Microsoft SQL Server

SQLite connector

REAL

CockroachDB

Clients

Default type mappings

PostgreSQL

• † 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

• † 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

Not supported.

Microsoft SQL Server

• † 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)

CockroachDB

• † 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.

Clients

Remarks

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

Default type mappings

PostgreSQL

MySQL

You can also use MySQL's YEAR type with Int:

yearField     Int    @db.Year

MongoDB

Timestamp

Microsoft SQL Server

SQLite

NUMERIC or STRING. If the underlying data type is STRING, you must use one of the following formats:

• RFC 3339 (1996-12-19T16:39:57-08:00)
• RFC 2822 (Tue, 1 Jul 2003 10:52:37 +0200)

CockroachDB

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB

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.

SQLite

Not supported

CockroachDB (Preview)

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB

BinData

Microsoft SQL Server

SQLite

BLOB

CockroachDB

Clients

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

model Star {
  id       Int                    @id @default(autoincrement())
  position Unsupported("circle")?
  example1 Unsupported("circle")
  circle   Unsupported("circle")? @default(dbgenerated("'<(10,4),11>'::circle"))
}

Remarks

• Cannot be optional (for example Post[]?).

Examples

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

Remarks

• Cannot be used with a list field (for example, Posts[])

Examples

model User {
  id   Int     @id @default(autoincrement())
  name String?
}

Remarks

Arguments

Note: In version 3.5.0 and later, new length and sort arguments are available as a Preview feature. See Index configuration for more details.

Signature

@id(map: String?)

Note: Until version 3.0.0, the signature was:

@id

Note: In version 3.5.0 and later, with the "extendedIndexes" Preview feature enabled, the signature is:

@id(map: String?, length: number?, sort: String?)

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.

model User {
  id   Int    @id @default(autoincrement())
  name String
}

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

model User {
  id   String @id @default(cuid())
  name String
}

model User {
  id   String @id @default(uuid())
  name String
}

model User {
  id   String @id
  name String
}

const newUser = await prisma.user.create({
  data: {
    id: 1,
    name: 'Alice',
  },
})

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?
}

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.",
      },
    },
  },
})

const profileWithUser = await prisma.profile.create({
  data: {
    bio: "Hello, I'm Bob and I like nothing at all. Just nothing.",
    author: {
      connect: {
        id: 22,
      },
    },
  },
})

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

The name of the fields argument on the @@id attribute can be omitted:

@@id(fields: [title, author])
@@id([title, author])

Note: In version 3.5.0 and later, new length and sort arguments are available as a Preview feature. See Index configuration for more details.

Signature

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

Note: Until version 3.0.0, the signature was:

@@id(_ fields: FieldReference[])

Examples

model User {
  firstName String
  lastName  String
  email     String  @unique
  isAdmin   Boolean @default(false)

  @@id([firstName, lastName])
}

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

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

model User {
  firstName String
  lastName  String
  email     String  @unique
  isAdmin   Boolean @default(false)

  @@id([firstName, lastName, isAdmin])
}

const user = await prisma.user.create({
  data: {
    firstName: 'Alice',
    lastName: 'Smith',
    isAdmin: true,
  },
})

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[]
}

const post = await prisma.post.create({
  data: {
    title: 'Hello World',
    author: {
      connect: {
        email: 'alice@prisma.io',
      },
    },
  },
})

Remarks

Arguments

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

@default(_ value: Expression)

Examples

model User {
  email        String @unique
  profileViews Int    @default(0)
}

model User {
  email  String @unique
  number Float  @default(1.1)
}

model User {
  email  String  @unique
  number Decimal @default(22.99)
}

model User {
  email  String @unique
  number BigInt @default(34534535435353)
}

model User {
  email String @unique
  name  String @default("")
}

model User {
  email   String  @unique
  isAdmin Boolean @default(false)
}

model User {
  email String   @unique
  data  DateTime @default("2020-03-19T14:21:00+02:00")
}

model User {
  email  String @unique
  secret Bytes  @default("SGVsbG8gd29ybGQ=")
}

enum Role {
  USER
  ADMIN
}

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  role    Role     @default(USER)
  posts   Post[]
  profile Profile?
}

Remarks

Arguments

Note: In version 3.5.0 and later, new length and sort arguments are available as a Preview feature. See Index configuration for more details.

Signature

@unique(map: String?)

Note: Until version 3.0.0, the signature was:

@unique

Note: In version 3.5.0 and later, the signature for the "extendedIndexes" Preview feature is:

@unique(map: String?, length: number?, sort: String?)

Examples

model User {
  email String @unique
  name  String
}

model User {
  id    Int     @id @default(autoincrement())
  email String? @unique
  name  String
}

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[]
}

model User {
  token String @unique @default(cuid())
  name  String
}

Remarks

Arguments

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

Note: In version 3.5.0 and later, new length and sort arguments are available as a Preview feature. See Index configuration for more details.

These arguments are added to the relevant field names:

@@unique(fields: [title(length:10), author])
@@unique([title(sort:"Desc"), author(sort:"Asc")])

Signature

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

Note: Until version 3.0.0, the signature was:

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

Note: In version 3.5.0 and later, the signature for the "extendedIndexes" Preview feature is:

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

Examples

model User {
  id        Int     @default(autoincrement())
  firstName String
  lastName  String
  isAdmin   Boolean @default(false)

  @@unique([firstName, lastName])
}

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

model User {
  id        Int     @default(autoincrement())
  firstName String
  lastName  String
  isAdmin   Boolean @default(false)

  @@unique([firstName, lastName, isAdmin])
}