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

binaryTargets options

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

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

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

SQL Server (Preview)

SQLite

TEXT

Clients

Default type mappings

PostgreSQL

MySQL

SQL Server (Preview)

SQLite

INTEGER

Clients

Default type mappings

PostgreSQL

MySQL

SQL Server (Preview)

SQLite

INTEGER

Clients

Default type mappings

PostgreSQL

MySQL

SQL Server (Preview)

SQLite

INTEGER

Clients

Default type mappings

PostgreSQL

MySQL

SQL Server (Preview)

SQLite connector

REAL

Clients

Default type mappings

PostgreSQL

MySQL

SQL Server (Preview)

SQLite

DECIMAL (changed from REAL in 2.17.0)

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

SQL Server (Preview)

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)

Clients

Default type mappings

PostgreSQL

MySQL

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.

SQLite

Not supported

Clients

Default type mappings

PostgreSQL

MySQL

SQL Server (Preview)

SQLite

Not supported

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

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

Remarks

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

Examples

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

Remarks

• Corresponding database type: PRIMARY KEY

• Can be annotated with a @default() value that uses functions to auto-generate an ID:

  • autoincrement()
  • cuid()
  • uuid()

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

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

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

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

model User {
  id    String  @id
  name  String
}

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

model User {
  id    String  @id
  name  String
}

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

Signature

@@id(_ fields: FieldReference[])

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

• Corresponding database type: DEFAULT

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

  • autoincrement()
  • dbgenerated()
  • cuid()
  • uuid()
  • now()

• 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

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

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+0200")
}

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

• 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

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 {
  id    String  @unique @default(cuid())
  name  String
}

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

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

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',
      isAdmmin: true,
    }
  },
})

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

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

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

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

  @@unique(fields: [firstName, lastName, isAdmin], name: "admin_identifier")
}

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

Remarks

• Corresponding database type: INDEX

Arguments

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

model Post {
  id       Int     @id @default(autoincrement())
  title    String
  content  String?

  @@index([title])
}

model Post {
  id       Int     @id @default(autoincrement())
  title    String
  content  String?

  @@index([title, content])
}

model Post {
  id       Int     @id @default(autoincrement())
  title    String
  content  String?

  @@index(fields: [title, content], name: "main_index")
}

Remarks

• Corresponding database types: FOREIGN KEY / REFERENCES

Arguments

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[]?)

Remarks

• @map does not rename the column in the database
• @map does change the field names in the generated client

Arguments

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

@map(name: "is_admin")
@map("users")

Signature

@map(_ name: String)

Examples

model User {
  id        Int    @id @default(autoincrement())
  firstName String @map("first_name")
}

await prisma.user.create({
  data: {
    firstName: 'Yewande', // first_name --> firstName
  },
})

enum Role {
  ADMIN     @map("admin")
  CUSTOMER
}

Remarks

• @@map does not rename the table in the database
• @@map does change the model names in the generated client

Arguments

The name of the name argument on the @@map attribute can be omitted

@@map(name: "users")
@@map("users")

Signature

@@map(_ name: String)

Examples

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

await prisma.user.create({
  // users --> user
  data: {
    name: 'Yewande',
  },
})

enum Role {
  ADMIN     @map("admin")
  CUSTOMER  @map("customer")

  @@map("_Role")
}

Remarks

• Compatible with DateTime fields

Arguments

N/A

Signature

@updatedAt

Examples

schema.prisma
1model Post {
2  id        String   @id
3  updatedAt DateTime @updatedAt
4}

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 type	AUTO_INCREMENT attribute	AUTOINCREMENT keyword

Examples

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

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

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

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

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

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 like now()	CURRENT_TIMESTAMP and aliases like now()	CURRENT_TIMESTAMP and aliases like date('now')

Remarks

• Compatible with any type

• If a value is present, it cannot be empty (for example, dbgenerated("")) - 2.21.0 and later

• Accepts a String value in 2.17.0 and later, which allows you to:

  • Set default values for Unsupported types
  • Override default value behavior for supported types

Examples

circle     Unsupported("circle")?   @default(dbgenerated("'<(10,4),11>'::circle"))

model User {
  id   String  @id @db.Uuid @default(dbgenerated("gen_random_uuid()"))
  id   String  @id @db.Uuid @default(uuid())
  test String?
}

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_]*

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