This content originally appeared on DEV Community and was authored by Harsh Mishra

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.

This content originally appeared on DEV Community and was authored by Harsh Mishra

Print Share Comment Cite Upload Translate Updates

APA

Harsh Mishra | Sciencx (2025-02-16T21:16:43+00:00) Understanding Relationships in MySQL and Prisma. Retrieved from https://www.scien.cx/2025/02/16/understanding-relationships-in-mysql-and-prisma/

MLA

" » Understanding Relationships in MySQL and Prisma." Harsh Mishra | Sciencx - Sunday February 16, 2025, https://www.scien.cx/2025/02/16/understanding-relationships-in-mysql-and-prisma/

HARVARD

Harsh Mishra | Sciencx Sunday February 16, 2025 » Understanding Relationships in MySQL and Prisma., viewed ,<https://www.scien.cx/2025/02/16/understanding-relationships-in-mysql-and-prisma/>

VANCOUVER

Harsh Mishra | Sciencx - » Understanding Relationships in MySQL and Prisma. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2025/02/16/understanding-relationships-in-mysql-and-prisma/

CHICAGO

" » Understanding Relationships in MySQL and Prisma." Harsh Mishra | Sciencx - Accessed . https://www.scien.cx/2025/02/16/understanding-relationships-in-mysql-and-prisma/

IEEE

" » Understanding Relationships in MySQL and Prisma." Harsh Mishra | Sciencx [Online]. Available: https://www.scien.cx/2025/02/16/understanding-relationships-in-mysql-and-prisma/. [Accessed: ]

rf:citation

» Understanding Relationships in MySQL and Prisma | Harsh Mishra | Sciencx | https://www.scien.cx/2025/02/16/understanding-relationships-in-mysql-and-prisma/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.

Understanding Relationships in MySQL and Prisma

Introduction

1. Types of Relations in MySQL

2. One-to-One (1:1) Relationship

Example Scenario

MySQL Table Schema

Querying in MySQL (Fetching User with Profile)

Example Output

Defining One-to-One in Prisma Schema

Querying in Prisma

Example Output in Prisma

3. One-to-Many (1:M) Relationship

Example Scenario

MySQL Table Schema

Querying in MySQL (Fetching User with Posts)

Example Output

Defining One-to-Many in Prisma Schema

Querying in Prisma

Example Output in Prisma

4. Many-to-Many (M:N) Relationship

Example Scenario

MySQL Table Schema

Querying in MySQL (Fetching Students with Courses)

Example Output

Defining Many-to-Many in Prisma Schema

Querying in Prisma

Example Output in Prisma

5. Summary Table

Conclusion

Related Posts