API with NestJS #152. SQL constraints with the Drizzle ORM

June 10, 2024

This entry is part 152 of 168 in the API with NestJS

When working with SQL databases, we can configure constraints to ensure our data does not get corrupted. In this article, we explore and implement different SQL constraints using PostgreSQL, NestJS, and the Drizzle ORM.

Not-null constraint

By default, SQL columns can hold nulls, representing an absence of value. We need the not-null constraint using the notNull() function to prevent that.

database-schema.ts

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

content: text('content').notNull(),

authorId: integer('author_id')

.references(() => users.id)

.notNull(),

});

// ...

Not-null error handling

Thanks to adding the not-null constraint, the database now throws an error when we try to save a null value into the database. PostgreSQL uses the 23502 error code. To avoid having to remember it, let’s create an enum to store error codes.

postgres-error-code.enum.ts

export enum PostgresErrorCode {

NotNullViolation = '23502',

}

In TypeScript, all caught errors are initially of the unknown type. Let’s create an interface and a type guard to deal with that.

database-error.ts

import { PostgresErrorCode } from './postgres-error-code.enum';

import { isRecord } from '../utilities/is-record';

export interface DatabaseError {

code: PostgresErrorCode;

detail: string;

table: string;

column?: string;

}

export function isDatabaseError(value: unknown): value is DatabaseError {

if (!isRecord(value)) {

return false;

}

const { code, detail, table } = value;

return Boolean(code && detail && table);

}

If you want to know more about type guards, check out Structural type system and polymorphism in TypeScript. Type guards with predicates

The isRecord function checks if a particular value is of the Record<string, unknown> type.

Using the isDatabaseError function, we can check if a particular error matches the DatabaseError interface.

articles.service.ts

import {

BadRequestException,

Injectable,

} from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { CreateArticleDto } from './dto/create-article.dto';

import { isDatabaseError } from '../database/databse-error';

import { PostgresErrorCode } from '../database/postgres-error-code.enum';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

async create(article: CreateArticleDto, authorId: number) {

try {

const createdArticles = await this.drizzleService.db

.insert(databaseSchema.articles)

.values({

authorId,

title: article.title,

content: article.content,

})

.returning();

return createdArticles.pop();

} catch (error) {

if (

isDatabaseError(error) &&

error.code === PostgresErrorCode.NotNullViolation

) {

throw new BadRequestException(

`The value of ${error.column} can not be null`,

);

}

throw error;

}

// ...

}

If we don’t recognize the error, we want to re-throw it to make sure the exception is properly propagated and not ignored.

Unique constraint

Using the unique constraint, we can ensure that all values in a certain column are unique across the entire table. A great example is the email column in the users table. Thanks to the unique constraint, we ensure that no users share the same email.

database-schema.ts

import { serial, text, pgTable } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {

id: serial('id').primaryKey(),

email: text('email').unique().notNull(),

// ...

});

// ...

Using the unique() function, we tell Drizzle ORM to add the unique constraint. Because of that, PostgreSQL will throw an error when we try to create two users with the same email.

Unique constraint error handling

To handle the violation of the unique constraint, we should start by adding the 23505 error code to our enum.

postgres-error-code.enum.ts

export enum PostgresErrorCode {

UniqueViolation = '23505',

NotNullViolation = '23502',

}

We can now use it in our service to respond with a custom exception if someone tries to use an occupied email address.

users.service.ts

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

import { UserDto } from './user.dto';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { PostgresErrorCode } from '../database/postgres-error-code.enum';

import { UserAlreadyExistsException } from './user-already-exists.exception';

import { isDatabaseError } from '../database/databse-error';

@Injectable()

export class UsersService {

constructor(private readonly drizzleService: DrizzleService) {}

async create(user: UserDto) {

try {

const createdUsers = await this.drizzleService.db

.insert(databaseSchema.users)

.values(user)

.returning();

return createdUsers.pop();

} catch (error) {

if (

isDatabaseError(error) &&

error.code === PostgresErrorCode.UniqueViolation

) {

throw new UserAlreadyExistsException(user.email);

}

throw error;

}

// ...

}

Primary key constraint

When we add a primary key constraint, we choose a particular column to be a unique identifier for the rows in the table.

database-schema.ts

import { serial, pgTable } from 'drizzle-orm/pg-core';

export const addresses = pgTable('addresses', {

id: serial('id').primaryKey(),

// ...

});

// ...

Primary key constraint error handling

In most cases, we won’t have a chance to violate the primary key constraint because we usually let PostgreSQL generate the key for us. An example where that might not be the case is when dealing with many-to-many relationships.

Under the hood, the primary key constraint consists of the not-null and unique constraints. To implement error handling for the primary key constraint, you should look for the not-null and unique constraint violations.

Foreign key constraint

We use the foreign key constraint to define relationships. It ensures that a value in one table’s column matches a value in another table’s column.

database-schema.ts

import { serial, text, integer, pgTable } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {

id: serial('id').primaryKey(),

email: text('email').unique().notNull(),

// ...

});

export const articles = pgTable('articles', {

id: serial('id').primaryKey(),

title: text('title').notNull(),

content: text('content').notNull(),

authorId: integer('author_id')

.references(() => users.id)

.notNull(),

});

// ...

Foreign constraint error handling

A good example of when the above constraint might cause an error is when we try to delete a user who is the author of at least one article. To handle that, we first need to adjust our enum.

postgres-error-code.enum.ts

export enum PostgresErrorCode {

UniqueViolation = '23505',

NotNullViolation = '23502',

ForeignKeyViolation = '23503',

}

We can now use it to handle the foreign constraint violation.

users.service.ts

import {

BadRequestException,

Injectable,

NotFoundException,

} from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq } from 'drizzle-orm';

import { PostgresErrorCode } from '../database/postgres-error-code.enum';

import { isDatabaseError } from '../database/databse-error';

@Injectable()

export class UsersService {

constructor(private readonly drizzleService: DrizzleService) {}

// ...

async delete(userId: number) {

try {

const deletedUsers = await this.drizzleService.db

.delete(databaseSchema.users)

.where(eq(databaseSchema.users.id, userId))

.returning();

if (deletedUsers.length === 0) {

throw new NotFoundException();

}

} catch (error) {

if (

isDatabaseError(error) &&

error.code === PostgresErrorCode.ForeignKeyViolation

) {

throw new BadRequestException(

'Can not remove a user that is an author of an article',

);

}

throw error;

}

Check constraint

We can use the check constraint to specify a particular column’s requirements more generically. Unfortunately, Drizzle Kit does not support it out of the box yet. To add it, we need to create a custom migration.

1	npx drizzle-kit generate --custom --name add-articles-title-check

Now, we need to write a SQL migration that adds the constraint manually. Let’s ensure that the title of each article is not an empty string.

0003_add-articles-title-check.sql

1	ALTER TABLE "articles" ADD CHECK(length(title) > 0);

We can now apply it using the migrate command.

1	npx drizzle-kit migrate

Check constraint error handling

Let’s start by adding the check constraint violation to our enum.

postgres-error-code.enum.ts

export enum PostgresErrorCode {

UniqueViolation = '23505',

NotNullViolation = '23502',

ForeignKeyViolation = '23503',

CheckViolation = '23514',

}

We can now use the enum to handle the check constraint violation.

articles.service.ts

import {

BadRequestException,

Injectable,

} from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { CreateArticleDto } from './dto/create-article.dto';

import { isDatabaseError } from '../database/databse-error';

import { PostgresErrorCode } from '../database/postgres-error-code.enum';

@Injectable()

export class ArticlesService {

constructor(private readonly drizzleService: DrizzleService) {}

async create(article: CreateArticleDto, authorId: number) {

try {

const createdArticles = await this.drizzleService.db

.insert(databaseSchema.articles)

.values({

authorId,

title: article.title,

content: article.content,

})

.returning();

return createdArticles.pop();

} catch (error) {

if (!isDatabaseError(error)) {

throw error;

}

if (error.code === PostgresErrorCode.NotNullViolation) {

throw new BadRequestException(

`The value of ${error.column} can not be null`,

);

}

if (error.code === PostgresErrorCode.CheckViolation) {

throw new BadRequestException('The title can not be an empty string');

}

throw error;

}

// ...

}

Summary

In this article, we’ve gone through various constraints PostgreSQL offers and implemented them using the Drizzle ORM. We also learned how to implement constraint violation handling in a NestJS application that uses Drizzle. Thanks to that, we can now better care for the integrity of the data in our database.

Series Navigation<< API with NestJS #151. Implementing many-to-one relationships with Drizzle ORMAPI with NestJS #153. SQL transactions with the Drizzle ORM >>