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

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:

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:

1. The real column is userId on JobPosting. That is the foreign key, and it is exactly the same column you wrote in MySQL.
2. user User @relation(...) does not create a column. It is a "relation field" that tells Prisma "this userId points to a User, and I want to call it .user in code".
3. 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:

"The user field on this model is connected via my userId column, which references the id column on User."

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 │
└────────┘            └─────────┘

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:

1. 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.
2. @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: "[email protected]",
    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:

1. 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.
2. @@id([userId, jobPostingId]) is the composite primary key. Same effect as PRIMARY KEY (userId, jobPostingId) in MySQL.
3. 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.

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.

The cheat sheet

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

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".

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:

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:

1. Which side has the actual foreign key column?
2. What is the back reference type on the other side (Model, Model?, or Model[])?
3. 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.