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"
}
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.
The ESM-first client generator. It generates plain TypeScript code into a directory you specify, so the generated code lives alongside your application code. Learn more about the new prisma-client generator.
A generator block accepts the following fields:
Name
.
Required
.
Type
.
Description
.
provider
.
Yes
.
prisma-client
.
Describes which generator to use. This can point to a file that implements a generator or specify a built-in generator directly.
Determines whether the generated code supports ESM (uses import) or CommonJS (uses require(...)) modules. We always recommend esm unless you have a good reason to use cjs. Default: Inferred from environment.
.
generatedFileExtension
.
No
.
Enum (ts or mts or cts)
.
File extension for generated TypeScript files. Default: ts
.
importFileExtension
.
No
.
Enum (ts,mts,cts,js,mjs,cjs, empty (for bare imports))
.
File extension used in import statements Default: Inferred from environment.
.
compilerBuild
.
No
.
String (fast, small)
.
Defines what build of the query compiler to use for the generated client. fast, the default, gives you fast query compilation, but with an increase in size. small gives you the smallest size, but with a slightly slower execution.
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):
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 ORM has a number of reserved words that are being used by Prisma ORM internally and therefore cannot be used as a model name. You can find the reserved words in the Prisma Client generator source and in the Prisma engines source.
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).
The data source connector determines what native database type each of Prisma ORM 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.
TINYINT maps to Int if the max length is greater than 1 (for example, TINYINT(2)) or the default value is anything other than 1, 0, or NULL. TINYINT(1) maps to Boolean.
† 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.
† 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.
† 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.
PostgreSQL's money type is not yet supported by 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.
There currently is a bug that doesn't allow you to pass in DateTime values as strings and produces a runtime error when you do. DateTime values need to be passed as Date objects (i.e. new Date('2024-12-04') instead of '2024-12-04').
The Unsupported type was introduced in 2.17.0 and allows you to represent data types in the Prisma schema that are not supported by Prisma Client. Fields of type Unsupported can be created during Introspection with prisma db pull or written by hand, and created in the database with Prisma Migrate or db push.
Fields with Unsupported types are not available in the generated client.
If a model contains a requiredUnsupported type, prisma.model.create(..), prisma.model.update(...) and prisma.model.upsert(...) are not available in Prisma Client.
When you introspect a database that contains unsupported types, Prisma ORM will provide the following warning:
*** WARNING ***
These fields are not supported by Prisma Client, because Prisma does not currently support their types.
* Model "Post", field: "circle", original data type: "circle"
model Star {
id Int @id @default(autoincrement())
position Unsupported("circle")?
example1 Unsupported("circle")
circle Unsupported("circle")? @default(dbgenerated("'<(10,4),11>'::circle"))
}
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 or CockroachDB (since MySQL and SQLite don't natively support scalar lists).
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
}
prisma
model User {
id String @id @default(cuid()) @map("_id")
name String
}
[!WARNING]
You cannot use cuid() to generate a default value if your id field is of type ObjectId. Use the following syntax to generate a valid ObjectId:
prisma
id String @id @default(auto()) @db.ObjectId @map("_id")
model User {
id String @id @default(uuid())
name String
}
prisma
model User {
id String @id @default(uuid()) @map("_id")
name String
}
[!WARNING]
You cannot use uuid() to generate a default value if your id field is of type ObjectId. Use the following syntax to generate a valid ObjectId:
prisma
id String @id @default(auto()) @db.ObjectId @map("_id")
model User {
id String @id @default(ulid())
name String
}
prisma
model User {
id String @id @default(ulid()) @map("_id")
name String
}
[!WARNING]
You cannot use ulid() to generate a default value if your id field is of type ObjectId. Use the following syntax to generate a valid ObjectId:
prisma
id String @id @default(auto()) @db.ObjectId @map("_id")
Single-field IDs without default values
In the following example, id does not have 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?
}
prisma
model Profile {
authorId String @id @map("_id") @db.ObjectId
author User @relation(fields: [authorId], references: [id])
bio String
}
model User {
id String @id @map("_id") @db.ObjectId
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 Useror connect the profile to an existing user.
The following example creates a user and a profile:
TypeScript
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:
TypeScript
const profileWithUser = await prisma.profile.create({
data: {
bio: "Hello, I'm Bob and I like nothing at all. Just nothing.",
author: {
connect: {
id: 22,
},
},
},
});
In this scenario, you cannot create a Profile only - you must use Prisma Client's nested writes create a Useror 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,
},
},
},
});
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:
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.
JSON data. Note that JSON needs to be enclosed with double-quotes inside the @default attribute, e.g.: @default("[]"). If you want to provide a JSON object, you need to enclose it with double-quotes and then escape any internal double quotes using a backslash, e.g.: @default("{ \"hello\": \"world\" }").
model User {
id Int @id @default(autoincrement())
email String @unique
name String?
role Role @default(USER)
posts Post[]
profile Profile?
}
title="MongoDB"
enum Role {
USER
ADMIN
}
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
email String @unique
name String?
role Role @default(USER)
posts Post[]
profile Profile?
}
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
}
prisma
enum Role {
USER
ADMIN
}
prisma
model User { id String @id @default(auto()) @map("_id") @db.ObjectId email String @unique name String? role Role @default(USER) posts Post[] profile Profile?}
Allows you to specify a maximum length for the subpart of the value to be indexed. MySQL only.
.
sort
.
No
.
String
.
Allows you to specify in what order the entries of the constraint are stored in the database. The available options are Asc and Desc.
.
clustered
.
No
.
Boolean
.
Defines whether the constraint is clustered or non-clustered. Defaults to false. SQL Server only.
.
where
.
No
.
function or object
.
Defines a partial index that only includes rows matching the specified condition. Accepts raw("SQL expression") or an object literal like { field: value }. PostgreSQL, SQLite, SQL Server, and CockroachDB. Requires the partialIndexes Preview feature.
.
¹ Can be required by some of the index and field types.
Note: The where argument accepts either raw("SQL expression") for raw SQL predicates or an object literal like { field: value } for type-safe conditions. See Configuring partial indexes for details.
Note: Before the partialIndexes Preview feature, the signature was:
model User { id String @id @default(auto()) @map("_id") @db.ObjectId posts Post[] favoriteColors String[] @default(["red", "yellow", "purple"]) roles Role[] @default([USER, DEVELOPER])}enum Role { USER DEVELOPER ADMIN}
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[]
}
prisma
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
email String? @unique
name String
}
Specify a unique attribute on relation scalar field authorId
model User {
token String @unique @default(cuid())
name String
}
prisma
model Post {
author User @relation(fields: [authorId], references: [id])
authorId String @unique @db.ObjectId
title String
published Boolean @default(false)
}
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
email String? @unique
name String
Post Post[]
}
Specify a unique attribute with cuid() values as default values
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 list of field names - for example, ["firstname", "lastname"]. Fields must be mandatory - see remarks.
.
name
.
No
.
String
.
The name of the unique combination of fields - defaults to fieldName1_fieldName2_fieldName3
.
map
.
No
.
String
.
.
length
.
No
.
number
.
Allows you to specify a maximum length for the subpart of the value to be indexed. MySQL only.
.
sort
.
No
.
String
.
Allows you to specify in what order the entries of the constraint are stored in the database. The available options are Asc and Desc.
.
clustered
.
No
.
Boolean
.
Defines whether the constraint is clustered or non-clustered. Defaults to false. SQL Server only.
.
where
.
No
.
function or object
.
Defines a partial index that only includes rows matching the specified condition. Accepts raw("SQL expression") or an object literal like { field: value }. PostgreSQL, SQLite, SQL Server, and CockroachDB. Requires the partialIndexes Preview feature.
.
The name of the fields argument on the @@unique attribute can be omitted:
Note: The where argument accepts either raw("SQL expression") for raw SQL predicates or an object literal like { field: value } for type-safe conditions. See Configuring partial indexes for details.
Note: Before the partialIndexes Preview feature (and before version 4.0.0 / 3.5.0 with the extendedIndexes Preview feature), the signature was:
model User {
id Int @default(autoincrement())
firstName String
lastName String
isAdmin Boolean @default(false)
@@unique([firstName, lastName, isAdmin])
}
prisma
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
firstName String
lastName String
isAdmin Boolean @default(false)
@@unique([firstName, lastName])
}
To retrieve a user, use the generated field name (firstname_lastname):
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[]
}
prisma
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
firstName String
lastName String
isAdmin Boolean @default(false)
@@unique([firstName, lastName, isAdmin])
}
Specify a multi-field unique attribute that includes a relation field
model User {
id Int @default(autoincrement())
firstName String
lastName String
isAdmin Boolean @default(false)
@@unique(fields: [firstName, lastName, isAdmin], name: "admin_identifier")
}
prisma
model Post {
id String @id @default(auto()) @map("_id") @db.ObjectId
author User @relation(fields: [authorId], references: [id])
authorId String @db.ObjectId
title String
published Boolean @default(false)
@@unique([authorId, title])
}
model User {
id String @id @default(auto()) @map("_id") @db.ObjectId
email String @unique
posts Post[]
}
Specify a custom name for a multi-field unique attribute
To retrieve a user, use the custom field name (admin_identifier):
A list of field names - for example, ["firstname", "lastname"]
.
name
.
No
.
String
.
The name that Prisma Client will expose for the argument covering all fields, e.g. fullName in fullName: { firstName: "First", lastName: "Last"}
.
map
.
No
.
map
.
The 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)
.
length
.
No
.
number
.
Allows you to specify a maximum length for the subpart of the value to be indexed. MySQL only.
.
sort
.
No
.
String
.
Allows you to specify in what order the entries of the index or constraint are stored in the database. The available options are asc and desc.
.
clustered
.
No
.
Boolean
.
Defines whether the index is clustered or non-clustered. Defaults to false. SQL Server only.
.
type
.
No
.
identifier
.
Allows you to specify an index access method. Defaults to BTree. PostgreSQL and CockroachDB only.
.
ops
.
No
.
identifier or a function
.
Allows you to define the index operators for certain index types. PostgreSQL only.
.
where
.
No
.
function or object
.
Defines a partial index that only includes rows matching the specified condition. Accepts raw("SQL expression") or an object literal like { field: value }. PostgreSQL, SQLite, SQL Server, and CockroachDB. Requires the partialIndexes Preview feature.
.
The name of the fields argument on the @@index attribute can be omitted:
Note: The where argument accepts either raw("SQL expression") for raw SQL predicates or an object literal like { field: value } for type-safe conditions. See Configuring partial indexes for details.
Note: With the partialIndexes Preview feature, the where argument is available. Before this Preview feature, the signature was:
If your model's primary key is of type ObjectId in the underlying database, both the primary key and the foreign key must have the @db.ObjectId attribute
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.
Maps the Prisma schema model name to a table (relational databases) or collection (MongoDB) with a different name, or an enum name to a different underlying enum in the database. If you do not use @@map, the model name matches the table (relational databases) or collection (MongoDB) name exactly.
Automatically stores the time when a record was last updated. If you do not supply a time yourself, Prisma Client will automatically set the value for fields with this attribute.
Add @ignore to a field that you want to exclude from Prisma Client (for example, a field that you do not want Prisma Client users to update). Ignored fields are excluded from the generated Prisma Client. The model's create method is disabled when doing this for required fields with no @default (because the database cannot create an entry without that data).
Add @@ignore to a model that you want to exclude from Prisma Client (for example, a model that you do not want Prisma users to update). Ignored models are excluded from the generated Prisma Client.
In the following example, the Post model is invalid because it does not have a unique identifier. Use @@ignore to exclude it from the generated Prisma Client API:
title="schema.prisma"
/// The underlying table does not contain a valid unique identifier and can therefore currently not be handled by Prisma Client.
model Post {
id Int @default(autoincrement()) // no unique identifier
author User @relation(fields: [authorId], references: [id])
authorId Int
@@ignore
}
In the following example, the Post model is invalid because it does not have a unique identifier, and the posts relation field on User is invalid because it refers to the invalid Post model. Use @@ignore on the Post model and @ignore on the posts relation field in User to exclude both the model and the relation field from the generated Prisma Client API:
title="schema.prisma"
/// The underlying table does not contain a valid unique identifier and can therefore currently not be handled by Prisma Client.
model Post {
id Int @default(autoincrement()) // no unique identifier
author User @relation(fields: [authorId], references: [id])
authorId Int
@@ignore
}
model User {
id Int @id @default(autoincrement())
name String?
posts Post[] @ignore
}
Add @@schema to a model to specify which schema in your database should contain the table associated with that model. Learn more about adding multiple schema's here.
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.
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:
@default(sequence(virtual)) Virtual sequences are sequences that do not generate monotonically increasing values and instead produce values like those generated by the built-in function unique_rowid().
.
cache
.
@default(sequence(cache: 20)) The number of sequence values to cache in memory for reuse in the session. A cache size of 1 means that there is no cache, and cache sizes of less than 1 are not valid.
.
increment
.
@default(sequence(increment: 4)) The new value by which the sequence is incremented. A negative number creates a descending sequence. A positive number creates an ascending sequence.
.
minValue
.
@default(sequence(minValue: 10)) The new minimum value of the sequence.
.
maxValue
.
@default(sequence(maxValue: 3030303)) The new maximum value of the sequence.
.
start
.
@default(sequence(start: 2)) The value the sequence starts at, if it's restarted or if the sequence hits the maxValue.
Implemented by Prisma ORM 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 ORM.
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 Client errors such as Error: The provided value for the column is too long for the column's type.
For MongoDB: cuid() does not generate a valid ObjectId. You can use @db.ObjectId syntax if you want to use ObjectId in the underlying database. However, you can still use cuid() if your _id field is not of type ObjectId.
Implemented by Prisma ORM 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 ORM.
For MongoDB: uuid() does not generate a valid ObjectId. You can use @db.ObjectId syntax if you want to use ObjectId in the underlying database. However, you can still use uuid() if your _id field is not of type ObjectId.
Generated values based on the Nano ID spec. nanoid() accepts an integer value between 2 and 255 that specifies the length of the generate ID value, e.g. nanoid(16) will generated ID with 16 characters. If you don't provide a value to the nanoid() function, the default value is 21.
For MongoDB: nanoid() does not generate a valid ObjectId. You can use @db.ObjectId syntax if you want to use ObjectId in the underlying database. However, you can still use nanoid() if your _id field is not of type ObjectId.
Implemented on the database-level, meaning that it manifests in the database schema and can be recognized through introspection. Database implementations:
String values in dbgenerated(...) might not match what the DB returns as the default value, because values such as strings may be explicitly cast (e.g. 'hello'::STRING). When a mismatch is present, Prisma Migrate indicates a migration is still needed. You can use prisma db pull to infer the correct value to resolve the discrepancy. (Related issue)
You can also use dbgenerated(...) to set the default value for supported types. For example, in PostgreSQL you can generate UUIDs at the database level rather than rely on Prisma ORM's uuid():
model User {
id String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
id String @id @default(uuid()) @db.Uuid
test String?
}