Relations and joins

Read related records in the same query by adding .include(...). The related records come back nested on the parent record, and their types match your models.

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

const posts = await db.orm.public.Post
  .where({ published: true })
  .include("author")
  .all();
// posts[0].author is the full User record

If this is your first Prisma ORM 8 query, start with the two files it needs. Your models live in src/prisma/contract.prisma. It is the same file Prisma ORM 7 called schema.prisma; the docs call it your contract. Both commands below write src/prisma/db.ts, which creates the client and exports it as db. It reads DATABASE_URL from .env.

npm create prisma@latest -- my-app   # a new project
npx prisma orm init                  # a project you already have

That is the file the examples above import as ./prisma/db, from a file directly inside src/. Here is what the rest of those lines are made of:

• On PostgreSQL the path is db.orm.<schema>.<ModelName>. db.orm is the model API; db.sql and db.raw.sql are for SQL. The PostgreSQL schema (not the contract file) is public unless you wrap the model in a namespace block. Write namespace billing { model Invoice { ... } } in your contract and the path becomes db.orm.billing.Invoice.
• On MongoDB there is no schema segment. The path is db.orm.<collectionName>. The collection name is the @@map(...) on the model, or the model name with a lowercase first letter. The models behind the MongoDB query above are further down this page.
• You await the whole chain, and the last call says what you want back. .all() returns every matching record as an array. .first() returns one record, or null when nothing matches.
• .where(...) takes two forms. Pass an object, like .where({ published: true }), to match fields to exact values. Pass a callback for a comparison, like .where((p) => p.title.like("Hello%")). Chaining .where(...) twice requires both to match.

The name you pass to .include(...) is the relation field name you declared in your contract. It is not a table name. .include(...) fetches the relation in the same query as the parent: one SQL statement, which reads the relation with a correlated subquery. .include("author") replaces Prisma ORM 7's include: { author: true }; see Coming from Prisma ORM 7 for the rest of the query API side by side. Use .include(...) when the caller needs the related data in the same response. Skip it when the foreign key already on the record is enough. This page walks through the three kinds of relationship, from the data model to the query and the result. If you already know how relational data is modeled, jump to filtering by relation data or the limitations.

One-to-one

One record is linked to at most one other record. For example, every profile belongs to exactly one user.

The model that holds the foreign key declares the relation. The @unique on the foreign key is what makes it one-to-one:

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

Write @default(cuid(2)) and you never pass id yourself: cuid(2) generates the id (the 2 is the CUID version). To read a profile with its user, query from the profile and include the relation:

const profileWithUser = await db.orm.public.Profile
  .where({ userId: "cuid20000000000000000001" })
  .include("user")
  .first();
// { id, bio, userId, user: { id, email } }

.first() returns null when the profile doesn't exist, so a user without a profile is a null check, not an error. You can also declare the matching field on the other side and start from users. An optional field typed as the other model is enough. It needs no @relation of its own:

model User {
  id      String   @id @default(cuid(2))
  email   String   @unique
  profile Profile?
}

This works as long as the foreign key on the other side is unique. So Profile.userId must carry @unique. Without it, npx prisma contract emit fails with the error code PSL_NON_UNIQUE_BACKRELATION.

Then run npx prisma contract emit, the command that regenerates the client types from your contract. Run it after every change you make to the contract. Then apply the change to the database with npx prisma db update (the replacement for prisma db push) during development, or with a migration: run npx prisma migration plan then npx prisma db migrate; see Generating a migration. With the field in place, include("profile") returns one record or null:

const usersWithProfiles = await db.orm.public.User
  .select("id", "email")
  .include("profile")
  .all();
// Array<{ id, email, profile: { id, bio, userId } | null }>

.select(...) narrows the parent's own fields only. An included relation still comes back with all of its fields, which is why profile above has bio and userId. To narrow the included relation's fields, use the callback form shown in the next section.

One-to-many

One parent record is linked to any number of child records: one user has many posts. This is the relationship you'll query most.

The child stores the parent's id, and the parent declares a list field:

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

model Post {
  id        String   @id @default(cuid(2))
  title     String
  published Boolean  @default(false)
  createdAt DateTime @default(now())
  authorId  String
  author    User     @relation(fields: [authorId], references: [id])
}

Query it in either direction. From the parent, the children arrive as an array. From the child, the parent arrives as one object:

// Each user with their posts
const usersWithPosts = await db.orm.public.User.include("posts").all();
// Array<{ id, email, posts: Post[] }>

// Each post with its author
const postsWithAuthors = await db.orm.public.Post.include("author").all();
// Array<{ id, title, published, createdAt, authorId, author: User }>

To choose what each relation returns, pass a callback as the second argument. Inside it, chain .where, .select, .orderBy, and .limit exactly like a top-level query. .desc() sorts newest first, and .asc() sorts the other way. This is how you fetch "each user with their five newest posts" in one query:

const usersWithRecentPosts = await db.orm.public.User
  .select("id", "email")
  .include("posts", (post) =>
    post
      .select("id", "title", "createdAt")
      .orderBy((post) => post.createdAt.desc())
      .limit(5),
  )
  .limit(10)
  .all();
// Array<{ id, email, posts: Array<{ id, title, createdAt }> }>

The callback also takes .include(...), so you can go two levels deep in one query. If Post also declares a comments Comment[] list field:

const usersWithPostsAndComments = await db.orm.public.User
  .include("posts", (post) => post.include("comments"))
  .all();
// Array<{ id, email, posts: Array<{ id, title, published, createdAt, authorId, comments: Comment[] }> }>

To write a parent and its children in one call, pass a callback for the list field and Prisma ORM fills in the foreign key on each child: db.orm.public.User.create({ email: "jane@prisma.io", posts: (p) => p.create([{ title: "Hello", published: false }]) }) writes the user and the post together. The common mistake here is the N+1 loop: fetching users, then querying posts inside a for loop over them. That runs one query per user. One .include("posts") on the user query returns the same data in a single query.

Many-to-many

Records on both sides connect to many on the other: a post has many tags, and a tag appears on many posts. Neither table can hold the other's foreign key, so a separate table holds one link per pair.

Write three models. On each side, declare a list field typed as the other model. Then write a third model for the join table, and give it a primary key made of exactly two foreign keys, one to each side:

model Post {
  id        String  @id @default(cuid(2))
  title     String
  published Boolean @default(false)
  tags      Tag[]
}

model Tag {
  id    String @id @default(cuid(2))
  name  String @unique
  posts Post[]
}

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

  @@id([postId, tagId])
}

Prisma ORM finds the model for the join table by that shape: the one model whose primary key is exactly a foreign key to Post and a foreign key to Tag. There is no other way to name it. If two models have that shape, npx prisma contract emit fails with PSL_AMBIGUOUS_BACKRELATION, and you pick one by adding the same @relation("...") name to the list field and to the foreign key pointing back at it. You do not add a PostTag[] field to Post or Tag. .include(...) then reads a post's tags in one query, and returns the tags themselves, not the link records. The callback that filters and narrows a relation works here like anywhere else:

const postsWithTags = await db.orm.public.Post
  .where({ published: true })
  .include("tags", (tag) => tag.select("id", "name").orderBy((tag) => tag.name.asc()))
  .all();

[{ title: 'Hello Prisma 8', tags: [{ id: 't2…', name: 'databases' }] }]

Writes reach the join table the same way. Pass a callback for the relation and use create, connect, or disconnect:

// Create a post and two new tags in one call
const post = await db.orm.public.Post.create({
  title: "Hello Prisma 8",
  tags: (t) => t.create([{ name: "typescript" }, { name: "databases" }]),
});

// Link an existing tag
const updated = await db.orm.public.Post.where({ id: post.id }).update({
  tags: (t) => t.connect([{ name: "typescript" }]),
});

// Unlink it again
await db.orm.public.Post.where({ id: post.id }).update({
  tags: (t) => t.disconnect([{ name: "typescript" }]),
});

connect and disconnect identify the tag by any primary key or @unique field, so { name: "typescript" } works here because Tag.name is @unique. When that key is made of several columns, pass every one of them in the object. update(...) returns the updated post, or null when no post matched the filter. create always inserts a new tag record, so creating a tag whose name already exists fails on that same unique constraint. Use connect for tags that already exist.

There is no connectOrCreate in Prisma ORM 8. Upsert the tag first, which creates it or leaves an existing one alone, then connect it:

const tag = await db.orm.public.Tag.upsert({
  create: { name: "typescript" },
  update: {},
  conflictOn: { name: "typescript" },
});
await db.orm.public.Post.where({ id: post.id }).update({
  tags: (t) => t.connect([{ id: tag.id }]),
});

The upsert is safe when two requests create the same tag at once. The connect is not: if both requests also link that tag to the same post, the second fails with an error whose code is ORM.RELATION_LINK_DUPLICATE. Catch that code and treat it as done.

See what is not available yet for the rest of the gaps, and Upsert a record for the top-level .upsert(...) call. These nested writes are for relational databases. On MongoDB, write the parent document and the child document in two calls, and set the reference field yourself:

const user = await db.orm.users.create({ email: "jane@prisma.io" });
const post = await db.orm.posts.create({ title: "Hello", published: false, authorId: user._id });

If you need the link records themselves, for example because the join table has columns of its own, query its model directly:

const links = await db.orm.public.PostTag.include("tag").all();
// Array<{ postId, tagId, tag: { id, name } }>

A join table may carry columns beyond the two foreign keys. Those extra columns must not be part of the primary key. Nested create and connect are rejected when one of them is required, because neither can supply a value for it, so insert the link records yourself:

const tag = await db.orm.public.Tag.first({ name: "typescript" });
await db.orm.public.PostTag.create({ postId: post.id, tagId: tag!.id, addedBy: "me" });

Filter parent records by relation data

On PostgreSQL, .where(...) can reach into a relation. .some(...) matches parents with at least one matching child. .none(...) matches parents with none. .every(...) matches parents whose children all match. A parent with no children also matches.

// Users who have at least one published post
const activeAuthors = await db.orm.public.User
  .where((u) => u.posts.some((p) => p.published.eq(true)))
  .all();

// Posts that carry a specific tag
const taggedPosts = await db.orm.public.Post
  .where((p) => p.tags.some((t) => t.id.eq("cuid20000000000000000007")))
  .all();

Chaining .where(...) combines conditions with AND. For OR, call the or helper inside a single callback. It comes from @prisma/orm-postgres/orm-client, already installed with @prisma/orm-postgres:

import { or } from "@prisma/orm-postgres/orm-client";

const posts = await db.orm.public.Post
  .where((p) => or(p.published.eq(true), p.title.eq("Hello Prisma 8")))
  .all();

See filter operators for the comparisons you can pass to .where(...). On MongoDB, relation filters are not available. Query the child collection directly instead, and include the parent:

// MongoDB: the published posts, each with its author
const publishedPosts = await db.orm.posts
  .where({ published: true })
  .include("author")
  .all();
// Every author that appears here has at least one published post

To group or reshape the result, use the pipeline builder, which has $lookup and $match.

PostgreSQL and MongoDB differences

MongoDB calls a related document that lives in its own collection, linked by id, a reference relation. Declare it as you would on PostgreSQL, and the three kinds of relationship above all apply:

type Address {
  street String
  city   String
}

model User {
  id      ObjectId @id @map("_id") // the property is _id, because of @map("_id")
  email   String
  address Address?
  posts   Post[]

  @@map("users")
}

model Post {
  id        ObjectId @id @map("_id")
  title     String
  published Boolean
  authorId  ObjectId
  author    User     @relation(fields: [authorId], references: [id])

  @@map("posts")
}

Post.author is a reference: the user is a separate document in the users collection, and .include("author") reads it. User.address is an embedded document, and you can tell by the declaration: Address is written with type, not model, so its fields are stored inside the user document itself. Embedded documents come back with every read, so you never include them. Calling .include(...) on an embedded relation throws an error with code ORM.INCLUDE_UNSUPPORTED. Use .include(...) only for documents stored in their own collection. MongoDB data modeling covers which to choose.

Current limitations

• A many-to-many needs the model for the join table. List fields on both sides with no such model are rejected. Write the model as shown above, with its primary key made of the two foreign keys.
• On PostgreSQL, .include(...) takes an optional callback to filter and narrow the relation. On MongoDB it takes the relation name only. Reshape joined documents with the pipeline builder instead.
• Relation filters (.some(...), .none(...), .every(...)) and nested writes on relations are for relational databases. They are not available on MongoDB.

Prompt your coding agent

Projects created with npm create prisma@latest -- my-app include the Prisma ORM skills, instruction files for your coding agent. The prisma-8 skill covers both relation queries and the relation fields in your contract. Prompts that map to each section:

• "Using the prisma-8 skill, add a one-to-one Profile model with a unique foreign key to User."
• "Using the prisma-8 skill, fetch each user with their five newest posts in one query."
• "Model a many-to-many between Post and Tag with a model for the join table, and write the nested include that reads a post's tag names."
• "Find users that have at least one published post, using .some(...) in the where clause instead of a loop."

Next

• Use advanced queries for the SQL query builder, explicit joins, and $lookup pipelines.
• Read data to filter, sort, paginate, and select fields from your models.
• Run writes that span several models atomically with a transaction.