Handle Database data integrity with the Prisma ORM
Cloud-hosted databases are gaining in popularity among developers for their scalability, no infrastructure management, pricing model, developer experience, etc... Some of these cloud-hosted databases made a choice not to support some important database features in favor of better performance, such as PlanetScale, a Serverless database for MySQL.
An important feature is the integrity constraints check that prevents storing inconsistent data in the database. If you are not familiar with database and SQL concepts, check out this link to this book to help you better understand.
For developers wanting this feature, the ORM Prisma built an interesting feature called relation mode that emulates the integrity constraints check at the application level.
This post will show how to enable and use it in a Node.js application.
Use case
We want to build a task manager where users can sign up and manage their tasks. A user can create many tasks, and a task always has an owner, the user who added the task.
The database schema can look like the picture below:
In this post, we will use the Prisma relation mode feature to:
- Prevent creating a task with a user that doesn't exist.
- Prevent deleting a user having tasks associated with him.
- Perform cascading deletion of tasks when deleting a user.
Prerequisites
You need these tools installed on your computer to follow this tutorial.
- Node.js 16+ - Download link
- NPM or Yarn - I will use Yarn
- A PlanetScale account with the database configured. I wrote this blog post to guide you.
- Docker - Download link
The PlanetScale free plan offers two branches; one is used for the main branch, and the second for the development branch. Since we will use Prisma ORM, it requires a shadow database when working with a cloud-hosted database.
This shadow database cannot be created on PlanetScale free plan, so we will use Docker to create one and use it. If you have a paid plan on PlanetScale, you can create a third database and avoid using Docker.
Run the command below to create the shadow database:
docker run -d --rm -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=task-shadow --name task-shadow-db -p 3309:3306 mysql:8.0.23
The connection string to the shadow database is mysql://root:secret@localhost:3309/task-shadow
To learn more about why Prisma requires a shadow database, check out this blog post I wrote.
Eric Cabrel TIOGO
Set up the project
We will use the Node.js starter project we built in this post; run the commands below to initialize the project and Prisma in the project:
git clone https://github.com/tericcabrel/node-ts-starter.git node-prisma-data-integrity
cd node-prisma-data-integrity
yarn install
yarn add -D prisma
yarn prisma init
Set up the database credentials
A .env file has been generated in the project root directory, and we must set two properties in the file DATABASE_URL and SHADOW_DATABASE_URL`.
The DATABASE_URL is the connection string to the development database branch.
The PlanetScale CLI provides a command to create a tunnel to a remote database. Run the command below:
pscale connect <database_name> <branch_name> --port 3308
My .env file looks like this:
DATABASE_URL="mysql://root@localhost:3308/task-manager-db"
SHADOW_DATABASE_URL="mysql://root:secret@localhost:3309/task-shadow"
Initialize the database schema
Replace the content of the file prisma/schema.prisma with the code below:
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
enum TaskStatus {
PENDING
IN_PROGRESS
DONE
}
model User {
id Int @id @default(autoincrement())
firstName String @map("first_name")
lastName String @map("last_name")
email String
password String
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
tasks Task[]
@@map("users")
}
model Task {
id Int @id @default(autoincrement())
name String
description String @db.Text
status TaskStatus @default(PENDING)
dueDate DateTime @map("due_date")
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
owner User @relation(fields: [ownerId], references: [id], onDelete: Restrict)
ownerId Int @map("owner_id")
@@map("tasks")
}
Let's run the command below to generate SQL for table creation from the Prisma schema:
yarn prisma migrate dev --name init-db-table
We get the following output:
We can clearly see that the error indicates that foreign key constraints are not allowed, and it is because of the line below in the Prisma schema:
owner User @relation(fields: [ownerId], references: [id], onDelete: Restrict)
Which generates the following SQL:
ALTER TABLE `tasks` ADD CONSTRAINT `tasks_owner_id_fkey` FOREIGN KEY (`owner_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
This constraint prevents the deletion of the user who owns a task because a task must always have an owner.
This is a constraint you must want to keep, but the reason Prisma asks you to delete it is related to PlanetScale, which is a cloud-hosted database. Check out the link below to learn more about the non-support of foreign keys constraint.
Enable the relation mode on Prisma
To fix the issue, The Prisma engine has a built-in feature to handle integrity constraints at the application level. This means the database schema will not have the foreign keys constraint, and Prisma will manage it.
- If you want to create a task with a user ID that doesn't exists in the users' table, Prisma will check it and throw an error.
- If you delete a user having tasks, Prisma will check it before performing the action.
The referential actions supported for MySQL are Cascade, Restrict, NoAction, and SetNull. The referential action SetDefault is not supported.
To enable it, update the prisma/prisma.schema file to add the code below:
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
relationMode = "prisma"
}
Re-run the command to generate the database schema from the Prisma schema.
If you check out the migration.sql file, you can see no SQL query related to defining integrity constraints.
Seed the database with some users
Create file named prisma/seed.ts and add the code below:
import { PrismaClient } from '@prisma/client';
(async () => {
const { randomBytes } = await import('crypto');
const prismaClient = new PrismaClient();
try {
await prismaClient.$connect();
await prismaClient.user.createMany({
data: [
{
firstName: 'John',
lastName: 'Doe',
email: 'john.doe@email.com',
password: randomBytes(10).toString('hex'),
},
{
firstName: 'Jane',
lastName: 'Doe',
email: 'jane.doe@email.com',
password: randomBytes(10).toString('hex'),
},
],
skipDuplicates: true,
});
} catch (e) {
console.error(e);
} finally {
await prismaClient.$disconnect();
}
})();
Add the following code to the project package.json
"prisma": {
"seed": "ts-node ./prisma/seed.ts"
}
Run the command below to execute the seed file using Prisma.
yarn prisma db seed
Now we have two users in the database with IDs 1 and 2.
Prisma relation mode in action
To showcase the Prisma relation mode, we will try to execute some actions and see the output.
Add a task with a user that doesn't exist
Create a file named create-task.ts and add the code below:
import { PrismaClient, TaskStatus } from '@prisma/client';
(async () => {
const prismaClient = new PrismaClient({
log: ['query'],
});
try {
await prismaClient.$connect();
await prismaClient.task.create({
data: {
name: 'Task One',
description: 'Description of task one',
dueDate: new Date(2023, 4, 31),
status: TaskStatus.PENDING,
owner: {
connect: {
id: 100,
},
},
},
});
} catch (e) {
console.error(e);
} finally {
await prismaClient.$disconnect();
}
})();
We try to create a task with the user ID 100 that doesn't exists. We also enabled the SQL query log to see what happens under the hood.
Run the command yarn ts-node ./src/create-task.ts
From the SQL query log, we can see that Prisma tried first to find the user having ID 100. Since it didn't find it, it threw an error that prevented inserting inconsistent data in the database.
Replacing with a valid owner ID creates the task successfully.
owner: {
connect: {
id: 2,
},
},
Important notes:
Creating a user using by assigning the ownerId instead of owner will not perform the integrity check and thus insert invalid data in the database.
await prismaClient.task.create({
data: {
name: 'Task One',
description: 'Description of task one',
dueDate: new Date(2023, 4, 31),
status: TaskStatus.PENDING,
ownerId: 100,
},
});
Always keep this in mind when the relation mode is set to Prisma.
Delete a user having a task
Create a file named delete-task.ts and add the code below:
import { PrismaClient } from '@prisma/client';
(async () => {
const prismaClient = new PrismaClient({
log: ['query'],
});
try {
await prismaClient.$connect();
await prismaClient.user.delete({
where: {
id: 2,
},
});
} catch (e) {
console.error(e);
} finally {
await prismaClient.$disconnect();
}
})();
Run the command yarn ts-node ./src/create-task.ts
The Prisma client tried first to find all the tasks associated with the user we wanted to delete. This query returned a non-empty result meaning we cannot delete the user, which is the message thrown by the Prisma client.
What about cascading deletion?
It is great to prevent deleting a user having a task. Still, if we want to delete all the tasks associated with the user, we can update the relationship in the Prisma schema to allow cascading deletion.
owner User @relation(fields: [ownerId], references: [id], onDelete: Cascade)
Since there are no database changes, run the command yarn prisma generate so that the Prisma client gets the update of the relation mode.
Re-run the command to delete the user again.
We can see the Prisma engine deleted the task associated first and then deleted the user. From the application side, the experience is the same as working with a database supporting foreign key constraints, but Prisma handles the dirty task for us under the hood.
The caveat about the Prisma relation mode
As you can guess, handling integrity constraints at the application level by the Prisma client will impact application performance because the client makes additional database queries, as we saw in the previous examples.
Always let the database handle data integrity whenever possible to not impact application performance. The default relation mode is set to foreignKeys so you don't even have to define it in the Prisma schema.
Wrap up
The Prisma relation mode is very helpful when working with databases that don't support integrity constraints, such as MongoDB, Serverless MySQL databases, cloud-hosted databases, etc...
When enabled, the Prisma client will emulate the foreign keys constraint check and referential actions such as cascading deletion. Pay attention to how you define the query using the Prisma client to avoid storing inconsistent data.
Check out the Prisma documentation about the Prisma relation mode.
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.