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)