Understanding Relationships in MySQL and Prisma
Introduction
In relational databases like MySQL, defining relationships between tables is essential for structuring data efficiently. There are three main types of relationships:One-to-One (1:1),One-to-Many (1:M), andMany-to-Many (M:N). Each type requires a specific table structure, foreign key constraints, and query approach.
In this article, we will explore:
- How to define relationships in MySQL with SQL table schemas.
- How to query related data using SQL.
- How to model the same relationships inPrisma ORM.
- How to retrieve related data using Prisma queries.
By the end, you’ll have a solid understanding of how to manage relational data efficiently withMySQL and Prisma. 🚀
1. Types of Relations in MySQL
In MySQL, relationships between tables are typically categorized into three main types:
- One-to-One (1:1)
- One-to-Many (1:M)
- Many-to-Many (M:N)
For each relation, I will explain:
- Table Schema in MySQL
- Querying the Relationship in SQL
- How to Define It in Prisma Schema
- Querying the Relationship in Prisma
- Example Output
2. One-to-One (1:1) Relationship
Example Scenario
AUser can haveone Profile, and aProfile belongs to only oneUser.
MySQL Table Schema
CREATETABLEusers(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(255)NOTNULL);CREATETABLEprofiles(idINTAUTO_INCREMENTPRIMARYKEY,bioTEXT,user_idINTUNIQUE,-- Ensures one-to-one relationshipFOREIGNKEY(user_id)REFERENCESusers(id)ONDELETECASCADE);Querying in MySQL (Fetching User with Profile)
SELECTusers.id,users.name,profiles.bioFROMusersLEFTJOINprofilesONusers.id=profiles.user_id;Example Output
| id | name | bio |
|---|---|---|
| 1 | John | Loves coding |
| 2 | Alice | Enjoys music |
Defining One-to-One in Prisma Schema
model User { id Int @id @default(autoincrement()) name String profile Profile?}model Profile { id Int @id @default(autoincrement()) bio String? user User @relation(fields: [userId], references: [id]) userId Int @unique}Querying in Prisma
constuserWithProfile=awaitprisma.user.findMany({include:{profile:true}});console.log(userWithProfile);Example Output in Prisma
[{"id":1,"name":"John","profile":{"bio":"Loves coding"}},{"id":2,"name":"Alice","profile":{"bio":"Enjoys music"}}]3. One-to-Many (1:M) Relationship
Example Scenario
AUser can havemany Posts, but eachPost belongs to only oneUser.
MySQL Table Schema
CREATETABLEusers(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(255)NOTNULL);CREATETABLEposts(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(255),contentTEXT,user_idINT,FOREIGNKEY(user_id)REFERENCESusers(id)ONDELETECASCADE);Querying in MySQL (Fetching User with Posts)
SELECTusers.id,users.name,posts.titleFROMusersLEFTJOINpostsONusers.id=posts.user_id;Example Output
| id | name | title |
|---|---|---|
| 1 | John | MySQL Guide |
| 1 | John | Prisma Intro |
| 2 | Alice | Music Life |
Defining One-to-Many in Prisma Schema
model User { id Int @id @default(autoincrement()) name String posts Post[]}model Post { id Int @id @default(autoincrement()) title String content String? user User @relation(fields: [userId], references: [id]) userId Int}Querying in Prisma
constusersWithPosts=awaitprisma.user.findMany({include:{posts:true}});console.log(usersWithPosts);Example Output in Prisma
[{"id":1,"name":"John","posts":[{"title":"MySQL Guide","content":"MySQL is great!"},{"title":"Prisma Intro","content":"Prisma is awesome!"}]},{"id":2,"name":"Alice","posts":[{"title":"Music Life","content":"I love music"}]}]4. Many-to-Many (M:N) Relationship
Example Scenario
AStudent can enroll inmany Courses, and aCourse can havemany Students.
MySQL Table Schema
CREATETABLEstudents(idINTAUTO_INCREMENTPRIMARYKEY,nameVARCHAR(255)NOTNULL);CREATETABLEcourses(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(255)NOTNULL);CREATETABLEenrollments(student_idINT,course_idINT,PRIMARYKEY(student_id,course_id),FOREIGNKEY(student_id)REFERENCESstudents(id)ONDELETECASCADE,FOREIGNKEY(course_id)REFERENCEScourses(id)ONDELETECASCADE);Querying in MySQL (Fetching Students with Courses)
SELECTstudents.name,courses.titleFROMenrollmentsJOINstudentsONenrollments.student_id=students.idJOINcoursesONenrollments.course_id=courses.id;Example Output
| name | title |
|---|---|
| John | Math 101 |
| John | Physics 201 |
| Alice | Math 101 |
Defining Many-to-Many in Prisma Schema
model Student { id Int @id @default(autoincrement()) name String courses Course[] @relation("Enrollments")}model Course { id Int @id @default(autoincrement()) title String students Student[] @relation("Enrollments")}model Enrollment { student Student @relation(fields: [studentId], references: [id]) studentId Int course Course @relation(fields: [courseId], references: [id]) courseId Int @@id([studentId, courseId])}Querying in Prisma
conststudentsWithCourses=awaitprisma.student.findMany({include:{courses:true}});console.log(studentsWithCourses);Example Output in Prisma
[{"name":"John","courses":[{"title":"Math 101"},{"title":"Physics 201"}]},{"name":"Alice","courses":[{"title":"Math 101"}]}]5. Summary Table
| Relationship | MySQL Schema | SQL Query | Prisma Schema | Prisma Query |
|---|---|---|---|---|
| One-to-One | user_id UNIQUE in second table | JOIN ON user_id | User has Profile? | include: { profile: true } |
| One-to-Many | Foreign key in child table | JOIN ON user_id | User has Post[] | include: { posts: true } |
| Many-to-Many | Junction table with two FKs | JOIN through junction | Student[] - Course[] | include: { courses: true } |
Conclusion
- MySQL usesforeign keys andjunction tables to define relationships.
- Prisma uses adeclarative schema to simplify relationship management.
- Querying relationships in Prisma is easy using
include. - Output in Prisma is structured as JSON, making it easy to work with.
Top comments(0)
Subscribe
For further actions, you may consider blocking this person and/orreporting abuse
