One-to-many relationships in Prisma

What is a database relationship?

A database relationship is a connection between two tables.

In a relational database, such as SQLite, tables can be connected to each other using foreign keys. These foreign keys are used to establish a link between the data in the two tables.

We are going to set up a database relationship using Prisma. We will create a new table called Category and link it to the Game table.

Each game must belong to a category, and each category can have many games. From the three types of relationships described above, which one do you think this is? 🤔

Setting up a database relationship

Create the `Category` table

We will start by creating a new table called Category in the schema.prisma file. This table will have the following attributes:

an id field that will be the primary key
a title field
an optional description field
createdAt and updatedAt fields to keep track of when the category was created and last updated.
an association with the Game table

Add this model to the schema.prisma file directly beneath the Game model:

model Game {
  id          String   @id @default(cuid())
  title       String
  description String
  price       Float
  rating      Float
  releaseDate DateTime

  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  categoryId  String?
  category    Category? @relation(fields: [categoryId], references: [id])
}

model Category {
  id          String @id @default(cuid())
  title       String
  description String?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  games       Game[]
}

model User {
5 collapsed lines
  id    String @id @default(cuid())
  email String @unique

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

To create the relationship between the Game and Category tables, we have added a new field called categoryId to the Game model, together with a category reference.

model Game {
6 collapsed lines
  id          String   @id @default(cuid())
  title       String
  description String
  price       Float
  rating      Float
  releaseDate DateTime

  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  categoryId  String?
  category    Category? @relation(fields: [categoryId], references: [id])
}

We set an optional foreign key on the Game table using the @relation attribute because we say a game “belongs to” a category:
- The @relation attribute specifies the fields that are used to establish the relationship between the two tables.
- The @relation attribute takes two arguments: fields and references.
- The fields argument specifies the field on the current table that is used to establish the relationship.
- The references argument specifies the field on the related table that the relationship is based on.

We’ve also added a games field to the Category model to establish a one-to-many relationship between the Category and Game tables.

1
model Category {
2
  id          String @id @default(cuid())
3
  title       String
4
  description String?
5

6
  createdAt   DateTime @default(now())
7
  updatedAt   DateTime @updatedAt
8
  games       Game[]
9
}

Apply the changes to the database

We have made changes to the schema.prisma file, but these changes have not yet been applied to the database.

If you remember from our previous lesson, we can apply these changes to the database by generating a migration file. This migration will contain the SQL commands needed to update the database schema to match the new schema.prisma file.

Can you can work out how to generate a migration file to apply the new changes to your database? 🤔

Don’t execute the command yet, but work with a pair programmer to write down the command you would need to run in NotePad.

TIPS

Remember that you will need to name the migration file with something suitable - add-category-model would work.
Refresh your memory of how to generate a migration file by reviewing the previous lesson.

Running the command below in your terminal window will generate the file you need:

npx prisma migrate dev --name add-category-model

Checking the database

Finally, let’s fire up Prisma Studio to check that the changes have been applied to the database.

npx prisma studio

If all is good, you should see the new (empty!) Category table in your browser:

Prisma Studio with Category table

Next, we will learn how to use seed files to populate our database with information.

🚀🚀🚀