Data modeling

Data model definition
Example
Models
IDs
Fields
Enums
Attributes
Indexes
Functions
Scalar types
Relations
Reserved model names

Data model definition

The data model definition (short: data model or datamodel) is part of your schema file.

It describes the shape of the data per data source. For example, when connecting to a relational database as a data source, the data model definition is a declarative representation of the database schema (tables, columns, indexes, ...).

Example

Here is an example based on a local SQLite database located in the same directory of the schema file (called data.db):

// schema.prisma

datasource sqlite {
  url      = "file:data.db"
  provider = "sqlite"
}

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

model Profile {
  id   Int    @id @default(autoincrement())
  user User
  bio  String
}

model Post {
  id         Int        @id @default(autoincrement())
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
  author     User
  title      String
  published  Boolean    @default(false)
  categories Category[]
}

model Category {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]
}

enum Role {
  USER
  ADMIN
}

While this file mostly consists of the data model definition, it is a valid schema file because it also specifies a data source connector (for SQLite, in this case).

Models

Models represent the entities of your application domain. They are defined using model blocks in the data model. In the example data model above, User, Profile, Post and Category are models. Here's the User model again for reference:

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

On a technical level, a model maps to the underlying structures of the data source, e.g.:

In PostgreSQL, a model maps to a table
In MySQL, a model maps to a table
In SQLite, a model maps to a table

Note: In the future there might be connectors for non-relational databases and other data sources. For example, for MongoDB a model would map to a collection, for a REST API it would map to a resource.

Naming models

Models are typically spelled in PascalCase and use the singular form (e.g. User instead of Users).

Technically, a model can be named anything that adheres to this regular expression:

[A-Za-z_][A-Za-z0-9_]*

Model operations in the Prisma Client JS API (CRUD)

Every model in the data model definition will result in a number of CRUD operations in the generated Prisma Client JS API:

findMany
findOne
create
update
upsert
delete
updateMany
deleteMany

The operations are accessible via a generated property on the Prisma Client JS instance. By default the name of the property is the plural, lowercase form of the model name, e.g. users for a User model or posts for a Post model.

Here is an example illustrating the use of a users property from the Prisma Client JS API:

const newUser = await prisma.users.create({
  data: {
    name: 'Alice',
  },
})
const allUsers = await prisma.users.findMany()

Note that for Prisma Client JS the name of the users property is auto-generated using the pluralize package.

IDs

Every model in your Prisma schema needs to have a unique ID. In relational databases, this unique ID corresponds to a column with a primary key constraint. Note that composite primary keys are not yet supported (but will be soon).

To determine which field of a model is the ID field, you can annotate it with the @id attribute. Fields annotated with the @id attribute must be of type String or Int:

model User {
  id    String  @id
  name  String
}

or

model User {
  id    Int     @id
  name  String
}

Note that in the above cases, you must provide your own ID values when creating new records for the User model using Prisma Client JS, e.g.:

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

However, you can also specify default values for the IDs. The following default values are supported:

String:
- cuid: Prisma will generate a globally unqiue identifier based on the cuid spec and set it as the record's ID value
- uuid: Prisma will generate a globally unqiue identifier based on the UUID spec and set it as the record's ID value
Int:
- autoincrement: Prisma will 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. This corresponds to e.g. using SERIAL in PostgreSQL.

Here are examples for using default values for the model:

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

or

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

or

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

Fields

The properties of a model are called fields. A field consists of several parts:

Name
Type
Type modifier (optional)
Attributes (optional)

You can see examples of fields on the sample models above.

Naming fields

Field names are typically spelled in camelCase starting with a lowercase letter.

Technically, a field can be named anything that adheres to this regular expression:

[A-Za-z_][A-Za-z0-9_]*

Note: There's currently a bug that doesn't allow for field names prepended with an underscore. The current regular expression for valid field names therefore is: [A-Za-z][A-Za-z0-9_]*

Types

The type of a field determines its structure. A type falls in either of two categories:

Scalar type (includes enums)
Model

Type modifiers

The type of a field can be modified by appending either of two modifiers:

[]: Make a field a list
?: Make a field optional

In the main example above, the field name on the User model is optional and the posts field is a list.

Field attributes

Learn more about attributes below.

Enums

An enum describes a type that has a predefined set of values and is defined via an enum block:

enum Color {
  Red
  Teal
}

Attributes

Attributes modify the behavior of a field or block (e.g. models). There are two ways to add attributes to your data model:

Field attributes are prefixed with @.
Block attributes are prefixed with @@.

Depending on their signature, attributes may be called in the following cases:

Case 1. No arguments

Signature: @attribute
Description: Parenthesis must be omitted.
Examples:
- @id
- @unique
- @updatedAt

Case 2. One positional argument

Signature: @attribute(_ p0: T0, p1: T1, ...)
Description: There may be up to one positional argument that doesn't need to be named.
Examples:
- @field("my_column")
- @default(10)
- @createdAt(now())

For arrays with a single parameter, you may omit the surrounding brackets:

@attribute([email]) // is the same as
@attribute(email)

Case 3. Many named arguments

Signature: @attribute(_ p0: T0, p1: T1, ...)
Description: There may be any number of named arguments. If there is a positional argument, then it may appear anywhere in the function signature, but if it's present and required, the caller must place it before any named arguments. Named arguments may appear in any order.

You must not have multiple arguments with the same name:

// compiler error
@attribute(key: "a", key: "b")

For arrays with a single parameter, you may omit the surrounding brackets:

@attribute([item], key: [item]) // is the same as
@attribute(item, key: item)

Field attributes

Field attributes are marked by an @ prefix placed at the end of the field definition. A field can have any number of field arguments, potentially spanning multiple lines:

// A field with one attribute
model _ {
  myField String @attribute
}

// A field with two attributes
models _ {
  myField String @attribute @attribute2
}

// A type definition with three attributes
type MyType String @attribute("input")
         @attribute2("input", key: "value", key2: "value2")
         @attribute3

Block attributes

Block attributes are marked by an @@ prefix placed anywhere inside a block. You can have as many block attributes as you want and they may also span multiple lines:

model \_ { @@attribute0

---

@@attribute1("input") @attribute2("input", key: "value", key2: "value2")

---

@@attribute1 @@attribute2("input") }

Core attributes

Core attributes must be implemented by every data source connector (with a best-effort implementation), this means they will be available in any Prisma setup.

They may be used in model blocks as well as on type definitions.

Here is a list of all available core field attributes:

@id: Defines the primary key.
@unique: Defines a unique constraint.
@map(_ name: String): Defines the raw column name the field is mapped to.
@default(_ expr: Expr): Specifies a default value.
@relation(_ fields?: Field[], name?: String): Disambiguates relationships when needed. More details here.
@updatedAt: Updates the time to now() whenever a record is updated.

Here is a list of all available core block attributes:

@@map(_ name: String): Defines the raw table name the field is mapped to.
@@index(_ fields: Field[]): Defines an index on the specifief fields/columns.

Connector attributes

Connector attributes let you use the native features of your data source. With a PostgreSQL database, you can use it for example to X.

Here is where you can find the documentation of connector attributes per data source connector:

MySQL
PostgreSQL
SQLite
MongoDB

Indexes

You can define indexes on one or multiple fields of your models via the @@index([...]) attribute on a model.

Examples

Assume you want to add an index for the title field of the Post model from the example above. You can define the index like so:

model Post {
  id         Int        @id
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
  author     User
  title      String
  published  Boolean    @default(false)
  categories Category[]

  @@index([title])
}

This will translate into the following SQL statement for the index:

-- PostgreSQL
CREATE INDEX "Post.title" ON public."Post"(title text_ops);

-- MySQL
CREATE  INDEX `Post.title` ON `mydb`.`Post`(`title`)

To define an index on multiple fields (i.e. a multi-column index), you can add more fields to the array passed to the @@index attribute, e.g.:

model Post {
  id         Int        @id
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt
  author     User
  title      String
  published  Boolean    @default(false)
  categories Category[]

  @@index([title, content])
}

This will translate into the following SQL statement for the index:

-- PostgreSQL
CREATE INDEX "Post.title_content" ON public."Post"(title text_ops,content text_ops);

-- MySQL
CREATE  INDEX `Post.title_content` ON `mydb`.`Post`(`title`,`content`)

Limitations

It is currently not possible to provide more configuration options to the index:

PostgreSQL
- Define index fields as expressions (e.g. CREATE INDEX title ON public."Post"((lower(title)) text_ops);)
- Specify index methods with USING; PostgreSQL supports these index methods: B-tree, hash, GiST, and GIN; Prisma uses B-Tree by default
- Define partial indexes with WHERE
- Create indexes concurrently with CONCURRENTLY
MySQL
- Specify index methods with USING; MySQL supports these index methods: B-tree, hash; Prisma uses B-Tree by default

Functions

Prisma core provides a set of functions that must be implemented by every connector with a best-effort implementation. Functions only work inside field and block attributes that accept them:

uuid(): Generates a fresh UUID
cuid(): Generates a fresh cuid
now(): Current date and time

Note: It is currently not possible to annotate more than one field per model with @default(now()).

Default values using a dynamic generator can be specified as follows:

model User {
  id         String    @id @default(cuid())
  createdAt  DateTime  @default(now())
}

Functions will always be provided at the Prisma level by the query engine.

The data types that these functions return will be defined by the data source connectors. For example, now() in a PostgreSQL database will return a timestamp with time zone, while now() with a JSON connector would return an ISOString.

Scalar types

Prisma core provides the following scalar types:

Prisma Type	Description
`String`	Variable length text
`Boolean`	True or false value
`Int`	Integer value
`Float`	Floating point number
`DateTime`	Timestamp

The data source connector determines what native database type each of these types map to. Similarly, the generator determines what type in the target programming language each of these types map to.

Expand below to see the mappings per connector and generator.

Scalar mapping to connectors and generators

Connectors

Prisma Type	PostgreSQL	MySQL	SQLite	Mongo	Raw JSON
`String`	`text`	`TEXT`	`TEXT`	`string`	`string`
`Boolean`	`boolean`	`BOOLEAN`	N/A	`bool`	`boolean`
`Int`	`integer`	`INT`	`INTEGER`	`int32`	`number`
`Float`	`real`	`FLOAT`	`REAL`	`double`	`number`
`DateTime`	`timestamp`	`TIMESTAMP`	N/A	`date`	N/A

N/A: Means that there is no perfect equivalent, but we can probably get pretty close.

Generators

Prisma Type	JS / TS	Go
`String`	`string`	`string`
`Boolean`	`boolean`	`bool`
`Int`	`number`	`int`
`Float`	`number`	`float64`
`DateTime`	`Date`	`time.Time`

Relations

Learn more about relations here.

Reserved model names

When generating Prisma Client JS based on your data model definition, there are a number of reserved names that you can't use for your models. Here is a list of the reserved names:

String
Int
Float
Subscription
DateTime
WhereInput
IDFilter
StringFilter

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

data-modeling.md

data-modeling.md

Data modeling

Data model definition

Example

Models

Naming models

Model operations in the Prisma Client JS API (CRUD)

IDs

Fields

Naming fields

Types

Type modifiers

Field attributes

Enums

Attributes

Field attributes

Block attributes

Core attributes

Connector attributes

Indexes

Examples

Limitations

Functions

Scalar types

Relations

Reserved model names

Files

data-modeling.md

Latest commit

History

data-modeling.md

File metadata and controls

Data modeling

Data model definition

Example

Models

Naming models

Model operations in the Prisma Client JS API (CRUD)

IDs

Fields

Naming fields

Types

Type modifiers

Field attributes

Enums

Attributes

Field attributes

Block attributes

Core attributes

Connector attributes

Indexes

Examples

Limitations

Functions

Scalar types

Relations

Reserved model names