SQL constraints with the Drizzle ORM

June 10, 2024

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 { isRecord } from "../utilities/is-record"
import { PostgresErrorCode } from "./postgres-error-code.enum"
 
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)
}

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 { pgTable, serial, text } 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 { pgTable, serial } 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 { integer, pgTable, serial, text } 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-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#

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