Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 36 ยท Relations

One-to-Many Relations in Prisma

Model one-to-many relations with @relation, foreign keys and relation fields, learn how the two sides fit together, and query related data with include and some/every/none.

Real data is connected: a user has posts, a post has comments, a customer has orders. In a relational database those connections are foreign keys: a column in one table holding the id of a row in another. Prisma describes them with relations in the schema, so that you can query across tables with objects instead of hand-written joins.

The most common shape is one-to-many: one author, many posts; each post has exactly one author.

The schema

PRISMA
model User {
  id    Int    @id @default(autoincrement())
  email String @unique
  name  String
  posts Post[]                       // relation field: no column in the database
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User   @relation(fields: [authorId], references: [id])  // relation field
  authorId Int                                                      // the real foreign key column
}

Three kinds of field cooperate here:

Field Kind In the database
Post.authorId scalar (the foreign key) an INTEGER column
Post.author relation field, annotated with @relation(fields, references) no column
User.posts relation field (the "back" side, a list) no column

@relation(fields: [authorId], references: [id]) says: "the authorId column of Post points at the id column of User". The side with the foreign key is the owning side; the other side is the back-relation, and Prisma requires both to exist so queries work in both directions. prisma format will even add a missing back-relation for you.

One user, many posts

User id, email, name
Post title, authorId
authorId Foreign key to User.id
posts[] All posts of a user

Only authorId is a real column.

1 / 2

What the database gets

The migration for this schema creates two tables and a foreign key constraint. Here it is in real SQL on Node's SQLite, with the constraint enforced. SQLite needs PRAGMA foreign_keys = ON; Prisma turns it on for you.

Node.js The foreign key behind @relation

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Because the foreign key is a normal scalar, you can create a post by its author's id:

TypeScript
await prisma.post.create({
  data: { title: "Hello Prisma", authorId: 1 },
});

Or use the relation field and let Prisma handle the key. The two styles are called unchecked (scalar foreign keys) and checked (relation fields); you cannot mix them in one data object:

TypeScript
await prisma.post.create({
  data: {
    title: "Hello Prisma",
    author: { connect: { id: 1 } },          // link to an existing user
  },
});

await prisma.post.create({
  data: {
    title: "Brand new author",
    author: { create: { email: "[email protected]", name: "Cy" } },  // create the user too
  },
});

Nested writes get a full lesson later in this chapter.

Relations are not loaded by default. A plain findUnique returns only the user's own fields. Ask for the related rows with include:

TypeScript
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: true },
});
// { id: 1, email: '...', name: 'Ada', posts: [ { id: 1, title: 'Engines', authorId: 1 }, ... ] }

const postWithAuthor = await prisma.post.findFirst({ include: { author: true } });
// postWithAuthor.author is a User

Use select instead when you want to pick fields on both sides (next lessons).

Filtering by relations

For a to-many relation you filter with some, every and none:

TypeScript
// users with at least one published post
await prisma.user.findMany({ where: { posts: { some: { published: true } } } });

// users whose posts are ALL published (and users with no posts also match!)
await prisma.user.findMany({ where: { posts: { every: { published: true } } } });

// users with no posts at all
await prisma.user.findMany({ where: { posts: { none: {} } } });

For a to-one relation use is and isNot (or write the nested filter directly):

TypeScript
await prisma.post.findMany({ where: { author: { email: { endsWith: "@example.com" } } } });
await prisma.post.findMany({ where: { author: { is: { name: "Ada" } } } });

Try it: relation filters

This stand-in evaluates some, every and none on nested arrays so you can see the edge case: every is true for a user with no posts.

Node.js Stand-in: some, every, none

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with node:readline or process.stdin), it gets the next line from this box.

Design guidance

  1. Always add a back-relation so you can navigate from both sides.
  2. Index the foreign key (@@index([authorId])) on databases that do not do it for you (PostgreSQL).
  3. Choose the foreign key's optionality deliberately: authorId Int means a post must have an author; authorId Int? plus author User? allows authorless posts.
  4. Name relation fields after the role, not the model: author, reviewer, createdBy.
  5. When two relations connect the same pair of models, give each a name (@relation("Wrote")), as the self-relation lesson shows.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In the User/Post example, which field is the actual foreign key column?

    authorId is a scalar column storing the user's id. author and posts are relation fields without columns.
  2. 2. What does a plain findUnique on a User return for the posts relation?

    Prisma loads only scalar fields by default. Add include: { posts: true } to fetch related rows.
  3. 3. Which filter finds users with at least one published post?

    some matches when at least one related row satisfies the condition.
  4. 4. Why does where: { posts: { every: { published: true } } } also return users who have no posts?

    With no posts there is no counterexample, so every is true. Combine with some: {} when you need at least one post.

Summary

  • A one-to-many relation = a foreign key scalar on the "many" side plus a @relation(fields, references) field, with a list back-relation on the "one" side.
  • Only the scalar foreign key is a column; relation fields are virtual.
  • Load related rows with include or select; filter with some, every, none (to-many) and is, isNot (to-one).
  • Index foreign keys and name relation fields by role.

Next: One-to-One and Many-to-Many Relations