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:[email protected]/testing?retryWrites=true&w=majority"
}

Learn more about MongoDB 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 .

Fields

A generator block accepts the following fields:

generator client {
  provider = "prisma-client-js"
}

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

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.

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

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: '[email protected]',
    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])

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 you create a user, you must provide a unique combination of firstName and lastName :

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

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

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

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: '[email protected]',
      },
    },
  },
})

Relational databases

• Corresponding database type: DEFAULT

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

  • autoincrement()
  • sequence() (CockroachDB only)
  • 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 not allowed on relation fields in the Prisma schema. Note however that you can still define default values on the fields backing a relation (the ones listed in the fields argument in the @relation attribute). A default value on the field backing a relation will mean that relation is populated automatically for you.

• Default values can be used with scalar lists in databases that natively support them.

MongoDB

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

  • auto() (can only be used with @db.ObjectId to generate an ObjectId in MongoDB)
  • cuid()
  • uuid()
  • now()

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

• Default values can be used with scalar lists in databases that natively support them.

Default values for scalar lists

model User {
  id             Int      @id @default(




    
autoincrement())
  posts          Post[]
  favoriteColors String[] @default(["red", "yellow", "purple"])
  roles          Role[]   @default([USER, DEVELOPER])
}

enum Role {
  USER
  DEVELOPER
  ADMIN
}

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

Arguments

• ¹ Can be required by some of the index and field types.

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

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

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

The length and sort 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 : Before version 4.0.0, or before version 3.5.0 with the extendedIndexes Preview feature enabled, the signature was:

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

Note : Before version 3.0.0, the signature was:

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

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

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

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

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

Relational databases

• Corresponding database type: INDEX
• There are some additional index configuration options that cannot be provided via the Prisma schema yet. These include:
  • PostgreSQL and CockroachDB:
    • Define index fields as expressions (e.g. CREATE INDEX title ON public."Post"((lower(title)) text_ops); )
    • Define partial indexes with WHERE
    • Create indexes concurrently with CONCURRENTLY

Arguments

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

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

The length and sort arguments are added to the relevant field names:

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

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

Define an index on a composite type field

type Address {
  street String
  number Int
}

model User {
  id      Int     @id
  email   String
  address Address

  @@index([address.number])
}

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

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

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

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

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

@ignore

schema.prisma
1/// The underlying table does not contain a valid unique identifier and can therefore currently not be handled by Prisma Client.
2model Post {
3  id       Int  @default(autoincrement()) // no unique identifier
4  author   User @relation(fields: [authorId], references: [id])
5  authorId Int
6
7  @@ignore
8}

schema.prisma
1/// The underlying table does not contain a valid unique identifier and can therefore currently not be handled by Prisma Client.
2model Post {
3  id       Int  @default(autoincrement()) // no unique identifier
4  author   User @relation(fields: [authorId], references: [id])
5  authorId Int
6
7  @@ignore
8}
9
10model User {
11  id    Int     @id @default(autoincrement()




    
)
12  name  String?
13  posts Post[]  @ignore
14}

@@schema

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["auth"]
}

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

  @@schema("auth")
}

Remarks

• Compatible with Int on most databases ( BigInt on CockroachDB)

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

  Database	Implementation
  PostgreSQL	SERIAL type
  MySQL	AUTO_INCREMENT attribute
  SQLite	AUTOINCREMENT keyword
  CockroachDB	SERIAL type

Optional arguments

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 .
• Since the length of cuid() output is undefined per the cuid creator, a safe field size is 30 characters, in order to allow for enough characters for very large values. If you set the field size as less than 30, and then a larger value is generated by cuid() , you might see Prisma errors such as Error: The provided value for the column is too long for the column's type.

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

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