Relational data modeling (/docs/orm/data-modeling/relational-databases)

For the complete Prisma documentation index, see llms.txt. A markdown version of any docs page is available by appending .md to its URL.

Model one-to-one, one-to-many, many-to-many, and polymorphic relations for PostgreSQL.

Location: ORM > Data modeling > Relational data modeling

In a relational database, each model becomes a table and each scalar field becomes a column. Models connect through foreign keys: a column in one table that holds the primary key of a row in another table. If you are new to models and keys, start with the data modeling overview.

You write your models in your contract, the contract.prisma file that replaced schema.prisma. After every change to it, run npx prisma contract emit, which checks the contract and regenerates your TypeScript types without touching the database. The query examples below use db, the client you create once in src/prisma/db.ts, the file npx prisma orm init writes. On it, db.orm holds your models by model name, and public is the PostgreSQL schema your tables are in.

The model whose table gets the foreign key column declares two fields: a scalar field for the foreign key column, and a relation field typed as the other model. @relation(fields:, references:) ties them together:

model Post {
  id       Int  @id @default(autoincrement())
  authorId Int
  author   User @relation(fields: [authorId], references: [id])
}

The side that declares the fields argument owns the foreign key, and deciding which side that should be is the main choice in each relation kind below.

To let a post have no author, make the foreign key field and the relation field both optional: authorId Int? and author User?. The two fields have to agree, so if one is optional and the other is required, npx prisma contract emit reports an error.

When a User row is deleted or its id changes, referential actions decide what happens to the Post rows that point at it. Add onDelete and onUpdate to @relation, as in @relation(fields: [userId], references: [id], onDelete: Cascade), and they become the foreign key's ON DELETE and ON UPDATE clauses. If you write neither, the foreign key gets neither clause and the database decides what happens. The five actions are:

• Cascade deletes or updates the matching rows.
• Restrict blocks the change.
• SetNull clears the foreign key, so the foreign key field has to be optional.
• SetDefault writes the foreign key's default value, which has to point at a row that exists.
• NoAction is the same as writing nothing.

Two relations between the same pair of models need a name on both sides, such as @relation("Authored", fields: [authorId], references: [id]) on one side and @relation("Authored") on the other. Without the name, npx prisma contract emit reports an error.

A one-to-many relation (1:n) connects one record to many: one user writes many posts, and each post belongs to one user. This is the most common relation kind.

model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  posts Post[]
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  authorId Int
  author   User   @relation(fields: [authorId], references: [id])
}

The foreign key is on the "many" side, Post.authorId, because each post points at exactly one user, while a user points at an open-ended list of posts. Use a one-to-many whenever a record belongs to one parent and a parent has many children: comments on a post, line items on an order, employees in a department. A model can also point at itself: a category with a parent category is an ordinary one-to-many whose relation field is typed as the same model, and a single self-relation like this needs no relation name.

posts Post[] on User is the mirror field, the field on the other side of the relation, which stores nothing in the database. You can leave it out, and the relation still works: you then read it from the foreign key side with db.orm.public.Post.where({ authorId: user.id }).all(), where .all() is the call that runs the query and returns every matching row.

To connect a post to a user, set the foreign key when you create the post. Here user is a user record you read earlier:

import { db } from "./prisma/db";

const post = await db.orm.public.Post.create({
  title: "Hello",
  authorId: user.id,
});

Reading back the other way, db.orm.public.Post.include("author").all() returns each post with its user nested on it. Relations and joins covers querying in both directions.

A one-to-one relation (1:1) connects at most one record on each side: a user has at most one profile, and a profile belongs to exactly one user.

Model it like a one-to-many, then add @unique to the foreign key. The unique constraint is what turns "many" into "at most one": no two profiles can reference the same user.

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

model Profile {
  id     Int    @id @default(autoincrement())
  bio    String
  userId Int    @unique
  user   User   @relation(fields: [userId], references: [id])
}

Declare the relation on the side that holds the foreign key, Profile here. You can also add the mirror field profile Profile? to User, with a type and nothing else, because it needs no @relation of its own. It is a single Profile? rather than a list because userId is unique, and it has to be optional because nothing in the database guarantees a matching profile row. With it in place you can read the relation from either side, as in db.orm.public.User.include("profile").all().

Put the foreign key on the dependent side, meaning the record that cannot exist on its own. A profile needs a user, but a user does not need a profile, so Profile holds userId.

The other signals all point at the same side. The dependent record is:

• The one you create second. Insert the User, then its Profile.
• The one you would delete when the parent goes away.
• The one whose absence is normal. A user without a profile is fine, and a profile without a user is broken data.

A many-to-many relation (m:n) connects many records on each side: a post has many tags, and a tag applies to many posts.

One foreign key column cannot hold many values, so you add a third model, the model for the join table, which holds one record per connected pair.

model Post {
  id    Int       @id @default(autoincrement())
  title String
  tags  Tag[]
}

model Tag {
  id    Int       @id @default(autoincrement())
  label String    @unique
  posts Post[]
}

model PostTag {
  postId Int
  tagId  Int
  post   Post @relation(fields: [postId], references: [id])
  tag    Tag  @relation(fields: [tagId], references: [id])

  @@id([postId, tagId])
}

The composite primary key on PostTag, @@id([postId, tagId]), allows one record per pair. Note that Post.tags is typed Tag[], not PostTag[], and the same goes for Tag.posts: you name the model at the other end, not the join table. If a second join table connects the same two models, the relation name goes on both sides again, which here means the list field Post.tags and the field that points back at Post, PostTag.post.

Connect a post to a tag with a nested write on the mirror field:

await db.orm.public.Post.where({ id: post.id }).update({
  tags: (t) => t.connect([{ id: tag.id }]),
});

t is the argument of the nested write callback: t.create([...]) makes new related records and links them, t.connect([...]) links existing ones, and t.disconnect([...]) removes the link. The nested write goes through the mirror fields, Post.tags and Tag.posts, so if you leave them out you write the PostTag rows yourself with db.orm.public.PostTag.create({ postId: post.id, tagId: tag.id }). Because PostTag is an ordinary model, the join table can also hold data about the pairing itself: to record when a tag was added, add addedAt DateTime @default(now()) to PostTag.

An implicit many-to-many, with list fields on both sides and no join table as in Prisma ORM 7, is not supported, so declare PostTag yourself. If you skip it, npx prisma contract emit reports an error whose code is PSL_ORPHANED_BACKRELATION. Relations and joins shows how to read across the join table in one query.

Sometimes several kinds of record share a common core but each has its own extra fields: every Task has a title, but a Bug also has a severity and a Feature has a target release. Prisma ORM models this with a base model plus variant models. One field on the base model, the discriminator, records which variant each row is:

model Task {
  id    Int    @id @default(autoincrement())
  title String
  type  String

  @@discriminator(type)
}

model Bug {
  severity String

  @@base(Task, "bug")
}

model Feature {
  targetRelease String?

  @@base(Task, "feature")

  @@map("features")
}

@@discriminator(type) marks the field that records the variant, and @@base(Task, "bug") declares that Bug is the "bug" variant of Task. Both take a field or model name without quotes, while the variant value is a string. A variant has every field of its base plus the ones it declares, and it needs no @id of its own because it uses the base model's primary key.

Read and write variants through the base model, where .variant(...) limits the query to one variant. It takes the model name, "Bug", with a capital letter, and passing the discriminator value "bug" instead is a type error. On a create, the discriminator value is filled in for you:

await db.orm.public.Task.variant("Bug").create({ title: "Crash on save", severity: "high" });

A query on Task with no .variant(...) returns every variant. Each row carries the base fields plus only its own variant's fields, and the discriminator field holds that variant's value, such as "bug". Switch on task.type to reach a variant's own fields:

switch (task.type) {
  case "bug":
    return task.severity;
  case "feature":
    return task.targetRelease;
}

@@map decides how a variant is stored, which is a new job for it: in Prisma ORM 7, @@map only renamed a table. A variant with no @@map, like Bug, is stored in the base model's table, and its extra columns are added there as nullable columns, because a Feature row has no severity. A variant with its own @@map, like Feature, is split into its own table, named by the string you pass, and its columns there can be NOT NULL, though reading a full variant then costs a join.

Start with the shared table, and give a variant its own table only when its columns have to be NOT NULL in the database or when the variant has many columns of its own. The pattern as a whole fits when you query the variants together (one feed of tasks, one stream of events) and each variant has real structure of its own. If you never query them together, separate models are simpler, and if the variants differ by one nullable field, a single model is simpler still.

Projects created with npm create prisma@latest include the Prisma ORM skills for your coding agent. In an existing project, run npx prisma skills sync to add them. The prisma-8 skill covers relation modeling, so try prompts that map to each section:

• "Using the prisma-8 skill, add a one-to-many between User and Post with the foreign key on Post."
• "Make the Profile relation one-to-one by adding a unique constraint to its foreign key."
• "Model a many-to-many between Post and Tag with an explicit model for the join table and an addedAt timestamp."
• "Split Task into Bug and Feature variants with a discriminator field."

• Query relations with .include(...), filters on relation data, and reads across the join table.
• To change the database to match the contract, edit contract.prisma, run npx prisma contract emit, then run npx prisma migration plan to write the migration files and npx prisma db migrate to apply them. Generating a migration has the flags and explains how the next plan finds its starting point.
• To model for MongoDB, see the MongoDB data modeling guide.

• MongoDB data modeling: Model documents, embedded documents, and references for MongoDB, and decide when to embed and when to reference.