Constraints with PostgreSQL and Prisma

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.

1CREATE TABLE posts (
2  id integer PRIMARY KEY
3)

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.

1CREATE TABLE posts (
2  id serial PRIMARY KEY
3)

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()).

1model Post {
2  id Int @id @default(autoincrement())
3  // ...
4}

Using multiple columns

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

1CREATE TABLE users (
2  first_name text,
3  last_name text,
4  PRIMARY KEY (first_name, last_name)
5)

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.

1model User {
2  firstName  String
3  secondName String
4 
5  @@id([firstName, secondName])
6}

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.

1CREATE TABLE users (
2  id serial PRIMARY KEY,
3  email text UNIQUE
4)

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

1model User {
2  id        Int      @id @default(autoincrement())
3  email     String   @unique
4}

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

1CREATE TABLE users (
2  id serial PRIMARY KEY,
3  first_name text,
4  last_name text,
5  UNIQUE (first_name, last_name)
6)

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.

1model User {
2  id         Int    @id @default(autoincrement())
3  firstName  String
4  secondName String
5 
6  @@unique([firstName, secondName])
7}

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.

1export enum PrismaError {
2  RecordDoesNotExist = 'P2025',
3}

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.

1import { HttpException, HttpStatus, Injectable } from '@nestjs/common';
2import { UsersService } from '../users/users.service';
3import RegisterDto from './dto/register.dto';
4import * as bcrypt from 'bcrypt';
5import { PrismaError } from '../utils/prismaError';
6import { Prisma } from '@prisma/client';
7 
8@Injectable()
9export class AuthenticationService {
10  constructor(private readonly usersService: UsersService) {}
11 
12  public async register(registrationData: RegisterDto) {
13    const hashedPassword = await bcrypt.hash(registrationData.password, 10);
14    try {
15      const createdUser = await this.usersService.create({
16        ...registrationData,
17        password: hashedPassword,
18      });
19      return {
20        ...createdUser,
21        password: undefined,
22      };
23    } catch (error) {
24      if (
25        error instanceof Prisma.PrismaClientKnownRequestError &&
26        error?.code === PrismaError.UniqueConstraintFailed
27      ) {
28        throw new HttpException(
29          'User with that email already exists',
30          HttpStatus.BAD_REQUEST,
31        );
32      }
33      throw new HttpException(
34        'Something went wrong',
35        HttpStatus.INTERNAL_SERVER_ERROR,
36      );
37    }
38  }
39 
40  // ...
41}

Not null

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

1CREATE TABLE posts (
2  id serial PRIMARY KEY,
3  title text NOT NULL
4)

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.

1model Post {
2  id         Int          @id @default(autoincrement())
3  title      String
4  scheduledDate DateTime? @db.Timestamptz
5}

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.

1import {
2  IsString,
3  IsNotEmpty,
4  IsNumber,
5  IsOptional,
6  IsISO8601,
7} from 'class-validator';
8 
9export class CreatePostDto {
10  @IsString()
11  @IsNotEmpty()
12  title: string;
13 
14  @IsString({ each: true })
15  @IsNotEmpty()
16  paragraphs: string[];
17 
18  @IsOptional()
19  @IsNumber({}, { each: true })
20  categoryIds?: number[];
21 
22  @IsISO8601({
23    strict: true,
24  })
25  @IsOptional()
26  scheduledDate?: string;
27}

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

1import { NestFactory } from '@nestjs/core';
2import { AppModule } from './app.module';
3import { ValidationPipe } from '@nestjs/common';
4import * as cookieParser from 'cookie-parser';
5 
6async function bootstrap() {
7  const app = await NestFactory.create(AppModule);
8 
9  app.use(cookieParser());
10 
11  app.useGlobalPipes(
12    new ValidationPipe({
13      transform: true,
14    }),
15  );
16 
17  await app.listen(3000);
18}
19bootstrap();

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.

1CREATE TABLE users (
2  id serial PRIMARY KEY
3);
4 
5CREATE TABLE posts (
6  id serial PRIMARY KEY,
7  author_id integer REFERENCES users(id)
8);

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.

1model User {
2  id        Int      @id @default(autoincrement())
3  email     String   @unique
4  posts     Post[]
5}

1model Post {
2  id         Int      @id @default(autoincrement())
3  title      String
4  paragraphs String[]
5  author     User     @relation(fields: [authorId], references: [id])
6}

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.

1CREATE TABLE products (
2  id serial PRIMARY KEY,
3  price numeric CHECK (price > 0)
4)

The check constraint can also use multiple columns.

1CREATE TABLE events (
2  id serial PRIMARY KEY,
3  start_date timestamptz,
4  end_date timestamptz,
5  CHECK (start_date < end_date)
6)

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.

1model Product {
2  id         Int    @id @default(autoincrement())
3  name       String
4  properties Json?  @db.JsonB
5  price      Int    @default(0)
6}

Now, let’s generate a migration.

1npx prisma migrate dev --name product-price

Doing the above generated a new migration.

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

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

1-- AlterTable
2ALTER TABLE "Product" ADD COLUMN "price" INTEGER NOT NULL DEFAULT 0;
3 
4ALTER 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.

1import { BadRequestException, Injectable } from '@nestjs/common';
2import CreateProductDto from './dto/createProduct.dto';
3import { PrismaService } from '../prisma/prisma.service';
4import { Prisma } from '@prisma/client';
5 
6@Injectable()
7export default class ProductsService {
8  constructor(private readonly prismaService: PrismaService) {}
9 
10  async createProduct(product: CreateProductDto) {
11    try {
12      return await this.prismaService.product.create({
13        data: product,
14      });
15    } catch (error) {
16      if (
17        error instanceof Prisma.PrismaClientUnknownRequestError &&
18        error.message.includes('check constraint')
19      ) {
20        throw new BadRequestException();
21      }
22      throw error;
23    }
24  }
25 
26  // ...
27}

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

1import { Prisma } from '@prisma/client';
2import { IsString, IsNotEmpty, IsOptional, IsNumber, IsPositive } from 'class-validator';
3import IsJsonObject from '../../utils/isJsonObject';
4 
5export class CreateProductDto {
6  @IsString()
7  @IsNotEmpty()
8  name: string;
9 
10  @IsJsonObject()
11  @IsOptional()
12  properties?: Prisma.InputJsonObject;
13 
14  @IsNumber()
15  @IsPositive()
16  price: number;
17}
18 
19export 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.