API with NestJS #111. Constraints with PostgreSQL and Prisma

June 5, 2023

This entry is part 111 of 121 in the API with NestJS

One of the most important aspects of working with a database is ensuring the stored information is correct. One of the fundamental ways of doing that is by using the correct data types for the columns in our tables. Thanks to that, we can make sure that a particular column holds only numbers, for example. In this article, we learn to use constraints to have even more control over our data and reject it when it does not match our guidelines. Doing that on the database level can ensure the integrity of the data to a greater extent than doing that through our TypeScript code.

Primary key

A primary key is a unique identifier for rows in the table. Therefore, all values in the column marked as a primary key need to be unique. Also, they can’t be null.

CREATE TABLE posts (

id integer PRIMARY KEY

)

It is common to use the serial type when creating primary keys. Under the hood, PostgreSQL creates an integer column that auto increments every time we add a new row to the table.

CREATE TABLE posts (

id serial PRIMARY KEY

)

Right now it is recommended to use identity columns instead of the serial type with PostgreSQL. If you want to know more, check out this article.
Unfortunately, Prisma does not support that out of the box yet.

To achieve the above with Prisma, we need to create an integer property. By marking it with @id, we make it a primary key. To ensure PostgreSQL generates the value for the id automatically, we need to use @default(autoincrement()).

postSchema.prisma

model Post {

id Int @id @default(autoincrement())

// ...

}

Using multiple columns

While we usually use a single column as the primary key, it can consist of multiple columns.

CREATE TABLE users (

first_name text,

last_name text,

PRIMARY KEY (first_name, last_name)

)

In the above code, we create a primary key that consists of both the first_name and last_name. Since PostgreSQL enforces primary keys to be unique, we can’t have two users sharing the same combination of first and last names. Therefore, it would be better to use a separate id column.

Primary keys consisting of multiple columns are popular when working with many-to-many relationships. If you want to know more, check out API with NestJS #75. Many-to-many relationships using raw SQL queries

To create a composite primary key with Prisma, we need the @@id attribute.

model User {

firstName String

secondName String

@@id([firstName, secondName])

}

Unique

By using the unique constraint, we can force the values in a particular column to be unique across all of the rows in a specific table.

CREATE TABLE users (

id serial PRIMARY KEY,

email text UNIQUE

)

Thanks to the above, we require each user to have a unique email. To do that with Prisma, we need the @unique keyword.

userSchema.prisma

model User {

id Int @id @default(autoincrement())

email String @unique

}

We can also ensure that a group of columns have a unique value. To do that with SQL, we need a slightly different syntax.

CREATE TABLE users (

id serial PRIMARY KEY,

first_name text,

last_name text,

UNIQUE (first_name, last_name)

)

Through the above code, we expect the users to have a unique combination of the first and last names. To achieve that with Prisma, we need the @@unique keyword.

model User {

id Int @id @default(autoincrement())

firstName String

secondName String

@@unique([firstName, secondName])

}

Unique constraint violation

An essential part of defining constraints is handling the case when they are not complied with. Prisma has a set of codes that describe the error that occurred. Let’s start creating an enum that holds them.

prismaError.ts

export enum PrismaError {

RecordDoesNotExist = 'P2025',

}

We now need to use the try...catch statement whenever we perform an operation that might fail. We also should check if the error that occurred is an instance of the Prisma.PrismaClientKnownRequestError class.

authentication.service.ts

import { HttpException, HttpStatus, Injectable } from '@nestjs/common';

import { UsersService } from '../users/users.service';

import RegisterDto from './dto/register.dto';

import * as bcrypt from 'bcrypt';

import { PrismaError } from '../utils/prismaError';

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

@Injectable()

export class AuthenticationService {

constructor(private readonly usersService: UsersService) {}

public async register(registrationData: RegisterDto) {

const hashedPassword = await bcrypt.hash(registrationData.password, 10);

try {

const createdUser = await this.usersService.create({

...registrationData,

password: hashedPassword,

});

return {

...createdUser,

password: undefined,

};

} catch (error) {

if (

error instanceof Prisma.PrismaClientKnownRequestError &&

error?.code === PrismaError.UniqueConstraintFailed

) {

throw new HttpException(

'User with that email already exists',

HttpStatus.BAD_REQUEST,

);

}

throw new HttpException(

'Something went wrong',

HttpStatus.INTERNAL_SERVER_ERROR,

);

}

// ...

}

Not null

With the not-null constraint, we enforce a column to have a value other than null.

CREATE TABLE posts (

id serial PRIMARY KEY,

title text NOT NULL

)

The official PostgreSQL documentation states that in most database designs, most columns should be marked as non-nullable. Prisma embraces that approach by making all columns non-nullable by default.

If we want to make a particular column nullable with Prisma, we need to add the question mark.

postSchema.prisma.ts

model Post {

id Int @id @default(autoincrement())

title String

scheduledDate DateTime? @db.Timestamptz

}

If you want to know how to handle dates with Prisma, check out API with NestJS #108. Date and time with Prisma and PostgreSQL

A reliable approach to avoiding the null constraint violation is validating the data provided by the users through our API. A common way of doing that with NestJS is using the class-validator library.

createPost.dto.ts

import {

IsString,

IsNotEmpty,

IsNumber,

IsOptional,

IsISO8601,

} from 'class-validator';

export class CreatePostDto {

@IsString()

@IsNotEmpty()

title: string;

@IsString({ each: true })

@IsNotEmpty()

paragraphs: string[];

@IsOptional()

@IsNumber({}, { each: true })

categoryIds?: number[];

@IsISO8601({

strict: true,

})

@IsOptional()

scheduledDate?: string;

}

When using the above validation, it is also important to use the ValidationPipe provided by NestJS.

main.ts

import { NestFactory } from '@nestjs/core';

import { AppModule } from './app.module';

import { ValidationPipe } from '@nestjs/common';

import * as cookieParser from 'cookie-parser';

async function bootstrap() {

const app = await NestFactory.create(AppModule);

app.use(cookieParser());

app.useGlobalPipes(

new ValidationPipe({

transform: true,

}),

);

await app.listen(3000);

}

bootstrap();

Foreign key

By using the foreign key constraint, we ensure that the values from one column match values from another table. We use this when defining relationships.

CREATE TABLE users (

id serial PRIMARY KEY

);

CREATE TABLE posts (

id serial PRIMARY KEY,

author_id integer REFERENCES users(id)

);

By using the REFERENCES keyword, we define a foreign key. In the above example, each post needs to refer to an existing user.

Creating a relationship such as the one above using Prisma is straightforward.

userSchema.prisma

model User {

id Int @id @default(autoincrement())

email String @unique

posts Post[]

}

postSchema.prisma

model Post {

id Int @id @default(autoincrement())

title String

paragraphs String[]

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

}

However, relationships are a broad topic and deserve a separate article. To know more, check out API with NestJS #33. Managing PostgreSQL relationships with Prisma.

Check

The check constraint is the most constraint available in PostgreSQL. Using it, we can define the requirements for the value in a particular column.

CREATE TABLE products (

id serial PRIMARY KEY,

price numeric CHECK (price > 0)

)

The check constraint can also use multiple columns.

CREATE TABLE events (

id serial PRIMARY KEY,

start_date timestamptz,

end_date timestamptz,

CHECK (start_date < end_date)

)

Unfortunately, Prisma does not support check constraints currently. To deal with this problem, we can create a custom migration with raw SQL.

First, let’s add the price property to our Product schema.

product.prisma

model Product {

id Int @id @default(autoincrement())

name String

properties Json? @db.JsonB

price Int @default(0)

}

Now, let’s generate a migration.

1	npx prisma migrate dev --name product-price

Doing the above generated a new migration.

20230604193023_product_price/migration.sql

1 2	-- AlterTable ALTER TABLE "Product" ADD COLUMN "price" INTEGER NOT NULL DEFAULT 0;

Let’s modify the generated file to include the check constraint.

20230604193023_product_price/migration.sql

-- AlterTable

ALTER TABLE "Product" ADD COLUMN "price" INTEGER NOT NULL DEFAULT 0;

ALTER TABLE "Product" ADD CHECK(price > 0);

By doing the above, we add the check constraint even when Prisma does not support it yet.

Handling the check error violation

Since Prisma does not support the check constraint, it does not handle its violation very well, either. To catch this error, we need to use the Prisma.PrismaClientUnknownRequestError class.

products.service.ts

import { BadRequestException, Injectable } from '@nestjs/common';

import CreateProductDto from './dto/createProduct.dto';

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

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

@Injectable()

export default class ProductsService {

constructor(private readonly prismaService: PrismaService) {}

async createProduct(product: CreateProductDto) {

try {

return await this.prismaService.product.create({

data: product,

});

} catch (error) {

if (

error instanceof Prisma.PrismaClientUnknownRequestError &&

error.message.includes('check constraint')

) {

throw new BadRequestException();

}

throw error;

}

// ...

}

However, in our particular case, validating the price value through the class-validator library makes sense.

createProduct.dto.ts

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

import { IsString, IsNotEmpty, IsOptional, IsNumber, IsPositive } from 'class-validator';

import IsJsonObject from '../../utils/isJsonObject';

export class CreateProductDto {

@IsString()

@IsNotEmpty()

name: string;

@IsJsonObject()

@IsOptional()

properties?: Prisma.InputJsonObject;

@IsNumber()

@IsPositive()

price: number;

}

export default CreateProductDto;

Thanks to the above, trying to send a POST request would result in a “400 Bad Request” if the price is not a positive number.

Summary

In this article, we’ve gone through various constraints supported by PostgreSQL. We’ve learned how to use them both through raw SQL and Prisma. Learning SQL proves helpful, especially in the case of the check constraint that Prisma does not yet support. Fortunately, we managed to overcome this problem by writing a migration manually. Learning all of the above helped us have more control over the data saved in our database.

Series Navigation<< API with NestJS #110. Managing JSON data with PostgreSQL and PrismaAPI with NestJS #112. Serializing the response with Prisma >>