API with NestJS #135. Referential actions and foreign keys in PostgreSQL with Prisma

November 27, 2023

This entry is part 135 of 180 in the API with NestJS

A foreign key is a column that connects two tables. A constraint keeps this connection in check, and PostgreSQL ensures the foreign keys point to the correct row. Therefore, we need to think about what happens when we delete a record that’s connected this way. In this article, we learn more about foreign keys and handle them with referential actions using Prisma.

So far, in this series, we’ve created the following schema:

schema.prisma

model Category {

id Int @id @default(autoincrement())

name String

articles Article[]

}

model Article {

id Int @id @default(autoincrement())

title String

content String?

upvotes Int @default(0)

author User @relation(fields: [authorId], references: [id], onDelete: Restrict)

authorId Int

categories Category[]

}

model Address {

id Int @id @default(autoincrement())

street String

city String

country String

user User?

}

model User {

id Int @id @default(autoincrement())

email String @unique

name String

password String

articles Article[]

address Address? @relation(fields: [addressId], references: [id])

addressId Int? @unique

}

Mandatory relationships and their default behavior

In our schema, we can see a relationship between the User and Article models. Every article needs to have an author, which creates a one-to-many relationship.

If you want to know more about using relationships with Prisma, check out API with NestJS #33. Managing PostgreSQL relationships with Prisma

Let’s inspect the migration Prisma generated for us.

migration.sql

-- AlterTable

ALTER TABLE "Article" ADD COLUMN "authorId" INTEGER NOT NULL;

-- AddForeignKey

ALTER TABLE "Article" ADD CONSTRAINT "Article_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

Restrict

Above, we see ON DELETE RESTRICT. This means that PostgreSQL prevents us from deleting users if they have any articles. Fortunately, we can handle this error with the try...catch block.

users.service.ts

import {

ConflictException,

Injectable,

NotFoundException,

} from '@nestjs/common';

import { PrismaError } from '../database/prisma-error.enum';

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

import { PrismaService } from '../database/prisma.service';

@Injectable()

export class UsersService {

constructor(private readonly prismaService: PrismaService) {}

async delete(userId: number) {

try {

return await this.prismaService.user.delete({

where: {

id: userId,

});

} catch (error) {

if (!(error instanceof Prisma.PrismaClientKnownRequestError)) {

throw error;

}

if (error.code === PrismaError.RecordDoesNotExist) {

throw new NotFoundException();

}

const affectedField = error.meta?.field_name;

if (

error.code === PrismaError.ForeignKeyConstraintViolated &&

typeof affectedField === 'string' &&

affectedField.toLowerCase().includes('article')

) {

throw new ConflictException(

"Can't remove the user that is an author of some articles",

);

}

throw error;

}

// ...

}

If Prisma fails to delete the User because they have some articles, it throws an error. We can then catch it and respond with the 409 Conflict response code. To achieve the above, we need to adjust our PrismaError enum.

prisma-enum.ts

export enum PrismaError {

RecordDoesNotExist = 'P2025',

UniqueConstraintViolated = 'P2002',

ForeignKeyConstraintViolated = 'P2003',

}

Choosing ON UPDATE RESTRICT would cause PostgreSQL to prevent us from changing the ID of a user with some articles.

Cascade

In our migration, we can see ON UPDATE CASCADE. In PostgreSQL, “cascade” means that the database will try to adjust to changes we make automatically.

Therefore, FOREIGN KEY ("authorId") REFERENCES "User"("id") ON UPDATE CASCADE means that if we change the id of an existing user, the corresponding authorId is updated automatically.

On the other hand, ON DELETE CASCADE would mean that deleting a particular user would cause PostgreSQL to delete all related articles.

Optional relationships and their default behavior

In our Prisma schema, we have a relationship between the User and Address models. What’s crucial is that the address is optional.

migration.sql

-- AlterTable

ALTER TABLE "User" ADD COLUMN "addressId" INTEGER;

-- CreateTable

CREATE TABLE "Address" (

"id" SERIAL NOT NULL,

"street" TEXT NOT NULL,

"city" TEXT NOT NULL,

"country" TEXT NOT NULL,

CONSTRAINT "Address_pkey" PRIMARY KEY ("id")

);

-- CreateIndex

CREATE UNIQUE INDEX "User_addressId_key" ON "User"("addressId");

-- AddForeignKey

ALTER TABLE "User" ADD CONSTRAINT "User_addressId_fkey" FOREIGN KEY ("addressId") REFERENCES "Address"("id") ON DELETE SET NULL ON UPDATE CASCADE;

Similar to mandatory relationships, we can see ON UPDATE CASCADE. It means that if we change the ID of a particular existing address, the addressId is adjusted automatically.

SetNull

We can see that the migration Prisma generated contains FOREIGN KEY ("addressId") REFERENCES "Address"("id") ON DELETE SET NULL. It means that if we delete an address that belongs to a user, PostgreSQL automatically sets addressId to null.

It’s important to acknowledge that this behavior only makes sense with optional relationships. Using it with required relationships would lead to errors since the foreign key would not be nullable.

Changing the referential action

Prisma allows us to change the default referential actions through additional @relation() attribute arguments. For example, let’s adjust the relationship between the User and the Article models.

schema.prisma

model Article {

id Int @id @default(autoincrement())

title String

content String?

upvotes Int @default(0)

author User @relation(fields: [authorId], references: [id], onDelete: Cascade)

authorId Int

}

Above, we specified that we want to change the delete behavior to cascade. Let’s generate the migration and see the SQL code.

schema.prisma

-- DropForeignKey

ALTER TABLE "Article" DROP CONSTRAINT "Article_authorId_fkey";

-- AddForeignKey

ALTER TABLE "Article" ADD CONSTRAINT "Article_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

Writing onDelete: Cascade caused Prisma to recreate the foreign key constraint, this time with ON DELETE CASCADE. This means that if we delete a user, PostgreSQL also deletes their articles automatically.

SetDefault

When we choose the SetDefault referential action, we tell Prisma to set the foreign key to its default value when the entity is updated, or deleted.

schema.prisma

model Book {

id Int @id @default(autoincrement())

title String

authorName String? @default("anonymous")

author Writer? @relation(fields: [authorName], references: [name], onDelete: SetDefault, onUpdate: SetDefault)

}

model Writer {

name String @id

books Book[]

}

NoAction

The NoAction referential action is similar to Restrict.

schema.prisma

model Article {

id Int @id @default(autoincrement())

title String

content String?

upvotes Int @default(0)

author User @relation(fields: [authorId], references: [id], onDelete: Restrict)

authorId Int

}

There is a subtle difference between them, however. With NoAction, when we try to delete or update a row in the parent table, PostgreSQL checks at the end of the transaction if any foreign key references in the child table would be violated. If there are, the transaction is rolled back. However, it allows you to temporarily have a state within a transaction where the foreign key relationship is violated, as long as it’s resolved by the end of the transaction. When we use Restrict, on the other hand, PostgreSQL checks for foreign key violations immediately and doesn’t allow the transaction to proceed if violations exist, not even temporarily within a transaction.

Many-to-many relationships

In our schema, we have a many-to-many relationship between the Article and Category models. An article can belong to many categories, and a category can contain multiple articles.

schema.prisma

model Category {

id Int @id @default(autoincrement())

name String

articles Article[]

}

model Article {

id Int @id @default(autoincrement())

title String

content String?

upvotes Int @default(0)

author User @relation(fields: [authorId], references: [id], onDelete: Restrict)

authorId Int

categories Category[]

}

Let’s take a look at the migration that Prisma generates for the above schema.

migration.sql

-- CreateTable

CREATE TABLE "Category" (

"id" SERIAL NOT NULL,

"name" TEXT NOT NULL,

CONSTRAINT "Category_pkey" PRIMARY KEY ("id")

);

-- CreateTable

CREATE TABLE "_ArticleToCategory" (

"A" INTEGER NOT NULL,

"B" INTEGER NOT NULL

);

-- ...

-- AddForeignKey

ALTER TABLE "_ArticleToCategory" ADD CONSTRAINT "_ArticleToCategory_A_fkey" FOREIGN KEY ("A") REFERENCES "Article"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey

ALTER TABLE "_ArticleToCategory" ADD CONSTRAINT "_ArticleToCategory_B_fkey" FOREIGN KEY ("B") REFERENCES "Category"("id") ON DELETE CASCADE ON UPDATE CASCADE;

The crucial part of the above migration is that it creates the _ArticleToCategory table where the foreign key “A” references the article, and the foreign key “B” references the category.

If you want to know more about the many-to-many relationships, read API with NestJS #33. Managing PostgreSQL relationships with Prisma

We can also see that Prisma chose ON DELETE CASCADE ON UPDATE CASCADE for the foreign keys. The catch is that we can’t adjust them when using the above approach. If we want to have more control over the _ArticleToCategory table, we need to create it explicitly.

schema.prisma

model ArticleCategory {

article Article @relation(fields: [articleId], references: [id])

articleId Int

category Category @relation(fields: [categoryId], references: [id])

categoryId Int

@@id([articleId, categoryId])

}

model Category {

id Int @id @default(autoincrement())

name String

articlesCategory ArticleCategory[]

}

model Article {

id Int @id @default(autoincrement())

title String

text String?

author User @relation(fields: [authorId], references: [id])

authorId Int

articlesCategories ArticleCategory[]

}

Since we are using the @relation() attribute explicitly in the above schema, we are free to adjust the referential actions.

Summary

In this article, we explored the use of foreign keys in PostgreSQL and Prisma. Through the referential actions, we learned to control PostgreSQL’s behavior to implement cascading deletes or prevent deletions if dependencies exist. This knowledge can come in handy for managing database integrity and handling relationships between tables more effectively.

Series Navigation<< API with NestJS #134. Aggregating statistics with PostgreSQL and PrismaAPI with NestJS #136. Raw SQL queries with Prisma and PostgreSQL range types >>