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

MongoDB (Preview)

String

SQL Server (Preview)

SQLite

TEXT

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB (Preview)

Bool

SQL Server (Preview)

SQLite

INTEGER

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB (Preview)

Int

SQL Server (Preview)

SQLite

INTEGER

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB (Preview)

Long

SQL Server (Preview)

SQLite

INTEGER

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB (Preview)

Double

SQL Server (Preview)

SQLite connector

REAL

Clients

Default type mappings

PostgreSQL

MySQL

MongoDB (Preview)

Decimal

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

MongoDB (Preview)

Timestamp

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

MongoDB (Preview)

A valid BSON object (Relaxed mode)

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

MongoDB (Preview)

BinData

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

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    Int     @id @default(autoincrement())
  name  String
}

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

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

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

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

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',
      isAdmin: 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

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

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

Examples

See: The @relation attribute.

Remarks

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

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/collection 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)