Mohamed Idris

Posted on May 8

Prisma relationships, finally explained (with MySQL side by side)

#prisma #beginners #database #sql

If Prisma relationships feel like a maze, this post is for you. We are going to build the data model for a small job posting app and walk through every kind of relationship, side by side with MySQL and a quick ER diagram for each one.

You already know MySQL and ER diagrams. The goal here is not to teach you what a foreign key is. The goal is to make Prisma's syntax click so you stop guessing where to put what.

The one idea that fixes everything

Most people get stuck because Prisma asks you to declare a relationship on both models. That looks redundant, like you are saying the same thing twice. You are not.

Here is the rule that unlocks the whole thing:

The foreign key column lives on exactly one side.
Both models name each other so Prisma can see the link in both directions.

In MySQL you only write the foreign key once, on the table that holds it. Prisma still does that, but it also asks the other table to name the relationship from its point of view, just for the JavaScript side. That second declaration does not create any extra column. It is purely so you can write user.jobPostings later in your code.

Keep that in your head as we go.

The app we are building

A simple job posting platform. Three things to track:

A User (someone who uses the app)
A Profile (extra info about each user, like bio and avatar)
A JobPosting (a job a user has posted on the platform)
A SavedJob (a job a user has bookmarked)

That gives us all four common shapes of relationship:

Shape	In our app
One to one	User has one Profile
One to many	User has many JobPostings
Many to one	JobPosting belongs to one User (same thing)
Many to many	Users save many JobPostings, jobs are saved by many Users

Let us build them one at a time.

1. One to many (and many to one)

This is the most common shape. A user posts many jobs. Each job belongs to one user.

ER picture

┌────────┐ 1        N ┌─────────────┐
│  User  │───────────>│ JobPosting  │
└────────┘            └─────────────┘

The arrow goes from User (the "one" side) to JobPosting (the "many" side).

MySQL version

CREATE TABLE User (
  id    INT PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE JobPosting (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  title   VARCHAR(255) NOT NULL,
  salary  INT,
  userId  INT NOT NULL,
  FOREIGN KEY (userId) REFERENCES User(id)
);

The foreign key sits on JobPosting. That is the "many" side. There is no column on User that points to jobs. Users do not need to know who their jobs are. The jobs know who their user is.

Prisma version

model User {
  id           Int          @id @default(autoincrement())
  name         String
  email        String       @unique
  jobPostings  JobPosting[]
}

model JobPosting {
  id      Int    @id @default(autoincrement())
  title   String
  salary  Int?
  user    User   @relation(fields: [userId], references: [id])
  userId  Int
}

Notice three things:

The real column is userId on JobPosting. That is the foreign key, and it is exactly the same column you wrote in MySQL.
user User @relation(...) does not create a column. It is a "relation field" that tells Prisma "my userId column points at the id column on User, and I want to call this connection .user in code".
jobPostings JobPosting[] on User does not create a column either. It is the back reference. It exists so you can write user.jobPostings to fetch them.

So one foreign key in the database, two relation fields in the schema. One per model.

What `@relation(fields: [userId], references: [id])` actually says

Read it as a sentence:

"Use my userId column (fields) to point at the id column (references) on User."

Two arguments, two sides:

Argument	Whose column?	In our example
`fields`	This model (JobPosting)	`userId`
`references`	The other model (User)	`id`

It mirrors the MySQL line you already know:

FOREIGN KEY (userId) REFERENCES User(id)
--             ^^^^^             ^^^^^^^^^^
--             fields            references

The arrays exist because you can have composite foreign keys (multiple columns on each side), but in 99% of cases you will see single element arrays like in our example.

You only put @relation(fields, references) on one side, the side that holds the foreign key. The other side just gets a bare JobPosting[] (or JobPosting for one to one) with no @relation attribute, because there is nothing to declare there.

Querying it

// All jobs posted by user 1
const jobs = await prisma.jobPosting.findMany({
  where: { userId: 1 },
});

// A user with their jobs included
const user = await prisma.user.findUnique({
  where: { id: 1 },
  include: { jobPostings: true },
});

// Create a job for an existing user
await prisma.jobPosting.create({
  data: {
    title: "Junior Developer",
    salary: 40000,
    user: { connect: { id: 1 } },
  },
});

connect is how you say "use an existing user, do not create a new one". It is one of the most useful pieces of the Prisma syntax once you spot it.

2. One to one

A user has one extended profile. The profile belongs to exactly one user.

ER picture

┌────────┐ 1        1 ┌─────────┐
│  User  │────────────│ Profile │
└────────┘            └─────────┘

A 1 to 1 relationship gets a plain line, no arrow. Both ends are "1", so there is no "many side" to point at. The cardinality labels carry all the meaning.

MySQL version

CREATE TABLE Profile (
  id      INT PRIMARY KEY AUTO_INCREMENT,
  bio     TEXT,
  avatar  VARCHAR(255),
  userId  INT NOT NULL UNIQUE,
  FOREIGN KEY (userId) REFERENCES User(id)
);

Same as one to many, with one extra trick: UNIQUE on userId. That constraint is what turns "many to one" into "one to one". Without it, multiple profiles could point at the same user.

Prisma version

model User {
  id      Int      @id @default(autoincrement())
  name    String
  email   String   @unique
  profile Profile?
}

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

Two changes from one to many:

profile Profile? instead of Profile[]. Singular, not a list. The ? means "optional", which fits real life: users may or may not have a profile yet.
@unique on userId. Same job as MySQL. It enforces "at most one profile per user". Without @unique, Prisma would treat this like one to many.

The shape on the back reference (Profile? vs Profile[]) is what tells Prisma whether you want one to one or one to many. The @unique at the database level is what enforces it.

Querying it

const userWithProfile = await prisma.user.findUnique({
  where: { id: 1 },
  include: { profile: true },
});

// Create a user and their profile in one go
await prisma.user.create({
  data: {
    name: "Bob",
    email: "bob@example.com",
    profile: {
      create: { bio: "I write code", avatar: "bob.png" },
    },
  },
});

That nested create is one of Prisma's nicest features. Two tables, one call, one transaction.

3. Many to many

Users can save jobs they like. Each user saves many jobs. Each job can be saved by many users.

In MySQL you handle this with a join table. In Prisma you have two choices: implicit (Prisma builds the join table for you) or explicit (you build it yourself). We will look at the explicit one because it matches MySQL exactly and gives you room to add extra fields later, which you almost always end up wanting.

ER picture

┌────────┐ 1   N ┌──────────┐ N   1 ┌─────────────┐
│  User  │──────>│ SavedJob │<──────│ JobPosting  │
└────────┘       └──────────┘       └─────────────┘

A many to many is really just two one to many relationships meeting in the middle.

MySQL version

CREATE TABLE SavedJob (
  userId        INT NOT NULL,
  jobPostingId  INT NOT NULL,
  savedAt       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (userId, jobPostingId),
  FOREIGN KEY (userId) REFERENCES User(id),
  FOREIGN KEY (jobPostingId) REFERENCES JobPosting(id)
);

The SavedJob table holds two foreign keys and uses both together as the primary key. That guarantees a user cannot save the same job twice.

Prisma version

model User {
  id           Int          @id @default(autoincrement())
  name         String
  email        String       @unique
  jobPostings  JobPosting[]
  savedJobs    SavedJob[]
}

model JobPosting {
  id            Int         @id @default(autoincrement())
  title         String
  salary        Int?
  user          User        @relation(fields: [userId], references: [id])
  userId        Int
  savedBy       SavedJob[]
}

model SavedJob {
  user          User        @relation(fields: [userId], references: [id])
  userId        Int
  jobPosting    JobPosting  @relation(fields: [jobPostingId], references: [id])
  jobPostingId  Int
  savedAt       DateTime    @default(now())

  @@id([userId, jobPostingId])
}

A few things to notice:

SavedJob is just a regular model. It has its own fields, including the two foreign keys. It is the join table from MySQL, written as a Prisma model.
@@id([userId, jobPostingId]) is the composite primary key. Same effect as PRIMARY KEY (userId, jobPostingId) in MySQL.
Both User and JobPosting get a SavedJob[] field, because both can be on the "one" side of a one-to-many that points at SavedJob.

Heads up: lists are never optional in Prisma. You write SavedJob[], never SavedJob[]?. An empty array already means "none", so there is no difference between "missing" and "empty". The ? modifier only applies to scalars (String?) and singular relations (Profile?).

This explicit version is more typing than the implicit one, but it gives you the savedAt timestamp for free, and it maps one to one to what your MySQL brain already expects.

Querying it

// User 1 saves job 7
await prisma.savedJob.create({
  data: {
    userId: 1,
    jobPostingId: 7,
  },
});

// All jobs user 1 has saved, with the job details
const saved = await prisma.savedJob.findMany({
  where: { userId: 1 },
  include: { jobPosting: true },
});

// Unsave: delete the join row
await prisma.savedJob.delete({
  where: {
    userId_jobPostingId: { userId: 1, jobPostingId: 7 },
  },
});

That userId_jobPostingId syntax is how Prisma exposes composite primary keys to your code. The two field names get joined with an underscore.

Wait, why not just `where: { userId: 1, jobPostingId: 7 }`?

Good question. That looks like it should work, but it does not, at least not with delete. Here is why.

prisma.savedJob.delete() only accepts a unique identifier in where. With a composite primary key, the unique identifier is the pair together, not either field alone. userId: 1 by itself is not unique (user 1 might have saved 50 jobs). Same for jobPostingId: 7. So Prisma asks you to package the two values into a single object, which is what the userId_jobPostingId wrapper does.

The flat shape does work, but only with the "many" methods that accept any filter:

// works: deleteMany takes any filter
await prisma.savedJob.deleteMany({
  where: { userId: 1, jobPostingId: 7 },
});

// does NOT work: delete needs a unique key
await prisma.savedJob.delete({
  where: { userId: 1, jobPostingId: 7 },
});

So the rule we saw earlier still holds: singular methods need a unique key, plural methods accept any condition. Composite primary keys just add the wrapper twist when you use the singular methods.

Method	Composite key syntax
`delete`	wrapped: `{ userId_jobPostingId: { userId, jobPostingId } }`
`update`	wrapped (same)
`findUnique`	wrapped (same)
`deleteMany`	flat: `{ userId, jobPostingId }`
`updateMany`	flat
`findMany`	flat
`findFirst`	flat

What if I do not need fields on the join?

Sometimes the join table really is just a connection. No savedAt, no notes, no extra columns. Just "this user is linked to that job" or "this job has these skills". In that case, the explicit SavedJob model is overkill, and Prisma offers a shorter form called implicit many-to-many.

You write list fields on both models pointing at each other, with no @relation and no join model:

model JobPosting {
  id      Int     @id @default(autoincrement())
  title   String
  skills  Skill[]
}

model Skill {
  id    Int          @id @default(autoincrement())
  name  String       @unique
  jobs  JobPosting[]
}

That is it. Prisma creates a hidden join table behind the scenes (named something like _JobPostingToSkill) and manages it for you. You never write the join model yourself.

How implicit feels different in code

You connect and disconnect through the parent models, not through a separate join table:

// Add skills to a job
await prisma.jobPosting.update({
  where: { id: 1 },
  data: {
    skills: { connect: [{ id: 5 }, { id: 7 }] },
  },
});

// Get a job with its skills
const job = await prisma.jobPosting.findUnique({
  where: { id: 1 },
  include: { skills: true },
});

// Remove a skill from a job
await prisma.jobPosting.update({
  where: { id: 1 },
  data: {
    skills: { disconnect: { id: 7 } },
  },
});

There is no prisma.jobPostingSkill to query directly, because there is no model. That is the trade off.

When to pick which

Situation	Use
Just a connection, no extra fields needed	Implicit
Need a timestamp, status, role, or other field on the join	Explicit model
Need to query the join itself (e.g. "all saves from last week")	Explicit model
You might add fields later	Explicit model

A useful rule of thumb: start implicit, upgrade to explicit the moment you reach for an extra field. The schema migration is small, and you have not paid for a join model you never used.

For our app:

User saves JobPosting -> explicit (we want savedAt).
JobPosting has many Skills -> implicit (no extra fields needed).

Both shapes are valid many-to-many. They are just different points on the "how much do I need to know about the join" spectrum.

When the default relation name is not enough

Most of the time Prisma figures out which back reference goes with which relation on its own. There is one case where it cannot, and you have to help: when two models have more than one relationship between them.

Imagine our job platform now has admin moderation. A JobPosting is created by a poster, and approved by an admin. Both of those are users.

A naive attempt looks like this:

model JobPosting {
  id            Int   @id @default(autoincrement())
  title         String
  poster        User  @relation(fields: [posterId], references: [id])
  posterId      Int
  approvedBy    User? @relation(fields: [approvedById], references: [id])
  approvedById  Int?
}

model User {
  id            Int            @id @default(autoincrement())
  postedJobs    JobPosting[]
  approvedJobs  JobPosting[]
}

Run prisma validate on that and you get an error. Prisma sees two User fields on JobPosting and two JobPosting[] fields on User, but it has no idea which pairs up with which. Should postedJobs follow poster or approvedBy? It cannot guess.

The fix is to name the relations so Prisma can match them up:

model JobPosting {
  id            Int    @id @default(autoincrement())
  title         String
  poster        User   @relation("PostedBy",   fields: [posterId],     references: [id])
  posterId      Int
  approvedBy    User?  @relation("ApprovedBy", fields: [approvedById], references: [id])
  approvedById  Int?
}

model User {
  id            Int            @id @default(autoincrement())
  postedJobs    JobPosting[]   @relation("PostedBy")
  approvedJobs  JobPosting[]   @relation("ApprovedBy")
}

The first argument to @relation is now a string label. Both ends of the same relationship use the same label. That is how Prisma knows postedJobs belongs to poster, and approvedJobs belongs to approvedBy.

The names themselves are arbitrary. Pick whatever reads well. "PostedBy", "poster", "AuthoredBy", all fine. They never appear in your TypeScript code. They only exist for Prisma to wire up the schema.

The Laravel parallel

If you have written Eloquent, this will feel familiar. In Laravel, when a model has multiple relations to the same other model, you have to specify the foreign key explicitly so Eloquent does not guess:

class JobPosting extends Model
{
    public function poster()
    {
        return $this->belongsTo(User::class, 'poster_id');
    }

    public function approvedBy()
    {
        return $this->belongsTo(User::class, 'approved_by_id');
    }
}

Same problem (multiple relations between the same two models), different fix. Laravel asks for the column name. Prisma asks for a relation name. Both are doing the same job: telling the framework "do not guess, use this".

When you do not need a relation name

If there is only one relationship between two models, leave the name out. The default is fine:

poster   User  @relation(fields: [posterId], references: [id])

Adding "PostedBy" here does nothing useful. The name is only needed to disambiguate, and adding it to single relations just adds noise.

A self-relation example (extra credit)

The same problem comes up when a model points at itself. For example, "users follow other users":

model User {
  id         Int     @id @default(autoincrement())
  name       String
  followers  User[]  @relation("UserFollows")
  following  User[]  @relation("UserFollows")
}

User has two list fields that both point at User. Without the "UserFollows" label, Prisma cannot tell which side is which. The named relation pairs them up.

(For self-relations you usually also need an explicit join model, but the naming idea is the same.)

The cheat sheet

Here is the whole picture in one table. Save this and refer back to it.

Shape	"Many" side has FK?	Back reference type	Notes
One to many	Yes (the many side)	`Model[]`	Most common shape
One to one	Yes, with `@unique`	`Model?`	The `@unique` is the magic
Many to many (implicit)	Hidden, Prisma manages it	`Model[]` on each side	Use when the join has no extra fields
Many to many (explicit)	Both FKs in a join model	`JoinModel[]` on each side	Use when the join has fields like `savedAt`, etc.

And the schema rules:

@relation(fields, references) lives on the side with the foreign key.
The other side gets a bare relation field with no @relation attribute.
[Model] means "many of these". Model? means "optional one of these". Model (no symbol) means "exactly one".
Lists are never optional. Always Model[], never Model[]?.
When two models have more than one relationship between them, give each one a name: @relation("PostedBy", ...). The same name appears on both ends.

What about deletes? `onDelete`

Real apps need to decide what happens to job postings when a user is deleted. MySQL has ON DELETE CASCADE and friends. Prisma has the same idea, written like this:

user User @relation(fields: [userId], references: [id], onDelete: Cascade)

The options are:

Option	What it does
`Cascade`	Delete the children when the parent is deleted
`SetNull`	Set the foreign key to null on the children
`Restrict`	Refuse to delete if children exist (default)
`NoAction`	Like Restrict, with subtle DB level differences
`SetDefault`	Set FK to its default value on the children

For our app: if you want deleting a user to also delete all their job postings, you would write onDelete: Cascade on the JobPosting.user relation. That mirrors MySQL exactly.

Mental model in two sentences

The foreign key lives on one side, the side with the @relation(fields, references) attribute. The other side just names the relationship so you can navigate to it in code.

Many to many is just two one to manys meeting at a join model, exactly like a join table in MySQL.

If those two sentences feel right, you have the whole picture. Everything else, the connect, the include, the composite keys, is just syntax sugar on top.

A tiny exercise

Open your own schema and try to answer these three questions for each relation you have:

Which side has the actual foreign key column?
What is the back reference type on the other side (Model, Model?, or Model[])?
Should this delete cascade, or do I want it to fail loudly?

If you can answer all three for every relation, your model is in good shape. The job posting app we just built is a good template to come back to whenever you start a new project and need a reminder of how to wire things up.

Top comments (1)

Mohamed Idris • May 8

The implicit vs explicit many-to-many call is one of the things juniors second guess the most, so here is the analogy that finally made it click for me.

Handshake vs contract.

A handshake is just two people agreeing on something. The connection exists, but nothing is written down. You know they shook hands, but you cannot ask "when did the handshake happen?", because nobody recorded it.

A contract is the same agreement, but written. Same two parties, same link, but now there is a signed document with a date, terms, maybe witnesses. The connection itself is worth keeping notes on.

Two examples from the job platform in the post:

1. Skills on a job posting -> handshake (implicit)

A JobPosting requires "React", "TypeScript", "SQL". A Skill like "React" sits on hundreds of jobs. It is purely "this job and this skill are linked". No date, no priority, no notes. Nothing to remember about the connection itself.

model JobPosting {
  skills Skill[]
}
model Skill {
  jobs JobPosting[]
}

Prisma manages a hidden join table. You never see it. You never query it directly.

2. Saved jobs -> contract (explicit)

When a user bookmarks a job, you almost always want to know when they saved it (to sort by recent), maybe whether they have applied yet, maybe a personal note. The connection itself carries information now.

model SavedJob {
  userId        Int
  jobPostingId  Int
  savedAt       DateTime @default(now())
  note          String?
  @@id([userId, jobPostingId])
}

Now the join is a real model. You can query it, filter on its fields, attach more data to it later.

The shortcut: the moment you reach for a date, status, or tag on the join, you are looking at a contract, not a handshake. Start implicit. Upgrade only when an extra field shows up. The migration is small, and you have not paid for a model you never used.

The one idea that fixes everything

The app we are building

1. One to many (and many to one)

ER picture

MySQL version

Prisma version

What @relation(fields: [userId], references: [id]) actually says

Querying it

2. One to one

ER picture

MySQL version

Prisma version

Querying it

3. Many to many

ER picture

MySQL version

Prisma version

Querying it

Wait, why not just where: { userId: 1, jobPostingId: 7 }?

What if I do not need fields on the join?

How implicit feels different in code

When to pick which

When the default relation name is not enough

The Laravel parallel

When you do not need a relation name

A self-relation example (extra credit)

The cheat sheet

What about deletes? onDelete

Mental model in two sentences

A tiny exercise

What `@relation(fields: [userId], references: [id])` actually says

Wait, why not just `where: { userId: 1, jobPostingId: 7 }`?

What about deletes? `onDelete`