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

> For the complete Prisma documentation index, see [llms.txt](https://www.prisma.io/docs/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](https://www.prisma.io/docs/orm/data-modeling).

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.

## How relations are declared [#how-relations-are-declared]

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:

```prisma
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.

## One-to-many [#one-to-many]

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.

```prisma
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:

```typescript
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](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins#one-to-many) covers querying in both directions.

## One-to-one [#one-to-one]

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.

```prisma
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](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins#one-to-one), as in `db.orm.public.User.include("profile").all()`.

### Which side owns the foreign key [#which-side-owns-the-foreign-key]

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.

## Many-to-many [#many-to-many]

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.

```prisma
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:

```typescript
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](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins#many-to-many) shows how to read across the join table in one query.

## Polymorphic relations [#polymorphic-relations]

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:

```prisma
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:

```typescript
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:

```typescript
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.

## Prompt your coding agent [#prompt-your-coding-agent]

Projects created with `npm create prisma@latest` include the [Prisma ORM skills](https://www.prisma.io/docs/ai/tools/skills#available-skills-for-prisma-8) 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."

## Next steps [#next-steps]

* [Query relations](https://www.prisma.io/docs/orm/fundamentals/relations-and-joins) 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](https://www.prisma.io/docs/orm/migrations/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](https://www.prisma.io/docs/orm/data-modeling/mongodb).

## Related pages

- [`MongoDB data modeling`](https://www.prisma.io/docs/orm/data-modeling/mongodb): Model documents, embedded documents, and references for MongoDB, and decide when to embed and when to reference.