SQL constraints with the Drizzle ORM

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.npx drizzle-kit generate --custom --name add-articles-title-checkNow, 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 ALTER TABLE "articles" ADD CHECK(length(title) > 0);We can now apply it using the migrate command.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.driz