Handle a Many-to-Many relationship with Prisma and Node.js

Photo by Steve Johnson / Unsplash

A many-to-many relationship occurs when entity A can have n relations to entity B, and inversely, entity B can have m relations to entity A. n and m are numbers greater or equal to 1.

Handling this relationship in a web application is not straightforward, but fortunately, with an ORM like Prisma, we will learn how to implement it in a Node.js application.

The use case

We have a video-on-demand (VOD) platform where users can browse movie catalogs and watch a movie. In the end, they can give a rate over 10.

In addition to the rate, the user can add a review. We must also store the information on which user rated the movie.

A user can rate many movies, and a movie can be rated by many users. This is a Many-To-Many relationship. We must create an intermediary table to store each user and movie combination.

As the table's primary key, we will use the userId and MovieId because they are only a single occurrence of a user rating a specific movie.

Below is a simple database structure of the movie platform

Prerequisites

You must install the following tools on your computer to follow this tutorial.

• Node.js 18+ - Download's link
• NPM or Yarn - (I will use Yarn)
• Docker to run a container of the database.

Run the command below to start the Docker container from the MySQL image:

docker run -d -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=moviedb --name mysqldb -p 3307:3306 mysql:8.0

Set up the project

In this tutorial, we built a starter project for Node.js with TypeScript and will use it to initialize the project by running the command below:

git clone https://github.com/tericcabrel/node-ts-starter.git node-prisma-many-to-many

cd node-prisma-many-to-many

yarn install

yarn start

Set up Prisma

Inside the project directory, install the Prisma CLI.

yarn add -D prisma

Initialize Prisma in the project:

yarn prisma init

This command will create a Prisma schema and a .env file to set the environment variables to inject into the application.

Replace the content of the file prisma/schema.prisma with the code below to set the provider to MySQL:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

Open the .env file and update the database URL to the connection string of the MySQL database instance running in Docker.

The connection string is: mysql://root:secret@localhost:3307/moviedb

Design the Prisma schema

Update the file prisma/schema.prisma with the code below:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model Movie {
  id            String   @id @default(cuid())
  name          String   @unique
  description   String?
  releaseDate   DateTime @updatedAt @map("release_date")
  ratings       MovieRating[]

  @@map("movies")
}

model User {
  id          String   @id @default(cuid())
  email       String   @db.VarChar(100) @unique
  name        String   @db.VarChar(100)
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")

  ratings     MovieRating[]

  @@map("users")
}

model MovieRating {
  movie     Movie    @relation(fields: [movieId], references: [id])
  movieId   String   @map("movie_id")
  user      User     @relation(fields: [userId], references: [id])
  userId    String   @map("user_id")
  rate      Int
  review    String?
  addedAt   DateTime @default(now()) @map("added_at")

  @@id([movieId, userId])
  @@map("movies_rating")
}

The model MovieRating represents the intermediary table; inside, we define relationships with Movie and User model.

To set two columns as the primary key of a table in Prisma, use @@id().

Create a migration and execute it against the database with the command below:

yarn prisma migrate dev --name init-db-table

Seed the database

We will load the database with some movies and users to focus on how to work with Many-to-Many relationships. Prisma offers an easy way to load the database with some predefined data.

Create a file called seed.ts in the folder prisma and add the code below:

import { Prisma, PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({ errorFormat: 'pretty' });

const loadMovies = async () => {
  const moviesInput: Prisma.MovieCreateManyInput[] = [
    { name: 'Avatar', releaseDate: new Date(2009, 11, 16) },
    {
      description: "A soldier try to revenge his family's death",
      name: 'Gladiator',
      releaseDate: new Date(2000, 5, 20),
    },
    {
      description: 'Our here united to save the world',
      name: 'Avengers',
      releaseDate: new Date(2012, 3, 25),
    },
  ];

  await prisma.movie.createMany({
    data: moviesInput,
  });
};

const loadUsers = async () => {
  const usersInput: Prisma.UserCreateManyInput[] = [
    { name: 'Bob', email: 'bob@email.com' },
    { name: 'Alice', email: 'alice@email.com' },
  ];

  await prisma.user.createMany({
    data: usersInput,
  });
};

const main = async () => {
  await loadUsers();

  await loadMovies();
};

main().then();

We load three movies and two users, which is enough for this post. Run the command below to load the data:

yarn ts-node prisma/seed.ts

Insert data

Bob just watched the movie Gladiator and wanted to rate the movie. Create a file src/insert-data.ts and add the code below:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({ errorFormat: 'pretty' });

const main = async () => {
  const movieGladiator = await prisma.movie.findUniqueOrThrow({
    where: {
      name: 'Gladiator',
    },
  });
  const userBob = await prisma.user.findUniqueOrThrow({
    where: {
      email: 'bob@email.com',
    },
  });

  const gladiatorRatingByBob = await prisma.movieRating.create({
    data: {
      movieId: movieGladiator.id,
      userId: userBob.id,
      rate: 9,
      review: 'I was expecting a better ending.',
      addedAt: new Date(2022, 6, 10),
    },
  });

  console.log(gladiatorRatingByBob);
};

main().then();

Run the command yarn src/insert-data.ts

If you try to rerun this code, Prisma will throw an error because a key with the combination of the movieId and the userId already exists.

Retrieve data

For a Many-to-Many relationship, retrieving data is easy with Prisma. The code below shows some requests you could want to make:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({ errorFormat: 'pretty' });

const main = async () => {
  // Retrieve all ratings
  await prisma.movieRating.findMany();

  // Find rating with a rate greater or equal to 5
  await prisma.movieRating.findMany({
    where: {
      rate: {
        gte: 5,
      },
    },
  });

  // Calculate overall average movie rating
  await prisma.movieRating.aggregate({
    _avg: {
      rate: true,
    },
  });

  // Get the average rate of the movie Gladiator
  const movieGladiator = await prisma.movie.findUniqueOrThrow({ where: { name: 'Gladiator' } });

  await prisma.movieRating.aggregate({
    _avg: {
      rate: true,
    },
    where: {
      movieId: movieGladiator.id,
    },
  });
};

main().then();

You can find this code in the file src/retrieve-data.ts.

Delete data

The code below deletes some ratings from the database:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({ errorFormat: 'pretty' });

const main = async () => {
  const movieAvatar = await prisma.movie.findUniqueOrThrow({ where: { name: 'Avatar' } });
  const userBob = await prisma.user.findUniqueOrThrow({ where: { email: 'bob@email.com' } });

  // Delete 'Avatar' rating made by Bob
  await prisma.movieRating.delete({
    where: {
      movieId_userId: {
        movieId: movieAvatar.id,
        userId: userBob.id,
      },
    },
  });

  // Delete all the ratings for the movie 'Avatar'
  await prisma.movieRating.deleteMany({
    where: {
      movieId: movieAvatar.id,
    },
  });
};

main().then();

You can find this code in the file src/retrieve-data.ts.

Wrap up

We saw how to manage a Many-to-Many relationship with Prisma, making it easy for developers to define entities and perform database queries using TypeScript code.

Another interesting way Prisma offers to handle relationships between entities is by allowing implicit relationships. Check out this link to learn more about it.

To go further with building robust applications with Prisma, learn how to write database transactions.

You can find the code source on the GitHub repository.

Follow me on Twitter or subscribe to my newsletter to avoid missing the upcoming posts and the tips and tricks I occasionally share.