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), and Many-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 in Prisma 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 with MySQL 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
A User can have one Profile, and a Profile belongs to only one User.
MySQL Table Schema
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL ); CREATE TABLE profiles ( id INT AUTO_INCREMENT PRIMARY KEY, bio TEXT, user_id INT UNIQUE, -- Ensures one-to-one relationship FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); Querying in MySQL (Fetching User with Profile)
SELECT users.id, users.name, profiles.bio FROM users LEFT JOIN profiles ON users.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
const userWithProfile = await prisma.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
A User can have many Posts, but each Post belongs to only one User.
MySQL Table Schema
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL ); CREATE TABLE posts ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), content TEXT, user_id INT, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); Querying in MySQL (Fetching User with Posts)
SELECT users.id, users.name, posts.title FROM users LEFT JOIN posts ON users.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
const usersWithPosts = await prisma.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
A Student can enroll in many Courses, and a Course can have many Students.
MySQL Table Schema
CREATE TABLE students ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL ); CREATE TABLE courses ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL ); CREATE TABLE enrollments ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE, FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE ); Querying in MySQL (Fetching Students with Courses)
SELECT students.name, courses.title FROM enrollments JOIN students ON enrollments.student_id = students.id JOIN courses ON enrollments.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
const studentsWithCourses = await prisma.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 uses foreign keys and junction tables to define relationships.
- Prisma uses a declarative 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)