Relations and joins

Read related records in the same query by adding .include(...). The related records come back nested on the parent, typed to match.

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

The relation name in .include(...) is the field name from your contract, not a table name. Use .include(...) when the caller needs the related data in the same response; skip it when the foreign key on the record is enough.

This page walks through the three relationship shapes, 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. The classic example: every profile belongs to exactly one user.

The model that holds the foreign key declares the relation, and 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])
}

To read a profile with its user, query from the profile and include the relation:

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

.first() returns null when the profile doesn't exist, so a user without a profile is a null check, not an error.

One thing to know: the mirror field on the other model (profile Profile? on User) is not supported in the contract yet, so include the relation from the side that owns the foreign key. To start from users and attach profiles in one query, join the two tables with the SQL query builder:

const plan = db.sql.public.user
  .as("u")
  .innerJoin(db.sql.public.profile.as("pr"), (f, fns) => fns.eq(f.pr.userId, f.u.id))
  .select((f) => ({ email: f.u.email, bio: f.pr.bio }))
  .build();

const usersWithProfiles = await db.runtime().execute(plan);

[ { email: 'alice@prisma.io', bio: 'Writes about typed databases.' } ]

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
  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, authorId, author: User }>

To shape what each relation returns, pass a callback as the second argument. Inside it, chain .where, .select, .orderBy, and .take exactly like a top-level query. 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((p) => p.createdAt.desc())
      .take(5),
  )
  .take(10)
  .all();
// Array<{ id, email, posts: Array<{ id, title, createdAt }> }>

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 junction model holds one link per pair.

Model the junction explicitly. Each PostTag record links one post to one tag:

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

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

Traverse both hops in one query by nesting an include inside the relation callback:

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

[
  {
    title: 'Hello Prisma Next',
    // ...
    tags: [
      { id: 'k2…', postId: 'i3…', tagId: 't1…', tag: { id: 't1…', name: 'typescript' } },
      { id: 'k3…', postId: 'i3…', tagId: 't2…', tag: { id: 't2…', name: 'databases' } }
    ]
  },
  { title: 'Typed queries', tags: [ /* one link record */ ] }
]

The result keeps the junction records in the shape, with each tag nested inside its link record. Read the tag names as post.tags.map((pt) => pt.tag.name).

Connecting a post to a tag is a plain create on the junction model:

await db.orm.public.PostTag.create({ postId: post.id, tagId: tag.id });

For a flat result without the junction records (one row per post-tag pair), join through the junction table with the SQL builder.

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, and .every(...) requires all children to match.

// 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((pt) => pt.tagId.eq(tag.id)))
  .all();

On MongoDB, query the child collection directly, or express the shape as a pipeline with $lookup and $match.

PostgreSQL and MongoDB differences

On PostgreSQL, Prisma Next fetches included relations with joins. On MongoDB, it uses $lookup for reference-style relations; embedded documents are already part of the parent and need no include.

The relationship shapes above apply to reference-style relations on both databases. On MongoDB, one-to-one and one-to-many data is often embedded in the parent document instead of referenced; embedded data comes back with every read automatically.

Current limitations

• Relations declared with an implicit many-to-many (a through junction the contract manages for you) are not supported by .include(...) yet. Model the junction explicitly, as shown above, and it works today.
• A one-to-one relation can only declare its relation field on the side that holds the foreign key. The mirror field on the other model is not supported yet.
• The include refinement callback is tested on PostgreSQL. On MongoDB, start with the plain .include("author") form and use the pipeline builder to reshape joined documents.

Prompt your coding agent

Projects scaffolded with create-prisma@next install Prisma Next skills for your coding agent; the prisma-next-queries skill covers relation queries, and prisma-next-contract covers the relation fields in your schema. Prompts that map to each section:

• "Using the prisma-next-contract skill, add a one-to-one Profile model with a unique foreign key to User."
• "Using the prisma-next-queries skill, fetch each user with their five newest posts in one query."
• "Model a many-to-many between Post and Tag with an explicit junction model, and write the nested include that reads a post's tag names."
• "Find users that have at least one published post, using a relation predicate instead of a loop."

Next

• Use advanced queries for explicit joins, flat junction traversals, 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.