Prismaのリレーションシップ、ついに解説（MySQLとの比較）

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 "this userId points to a User , and I want to call it .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: "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: 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 : " [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: 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 . 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. 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 Both FKs in a join model JoinModel[] on each side Explicit gives you extra fields 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: 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 e