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.
1export const articles = pgTable('articles', {
2 id: serial('id').primaryKey(),
3 title: text('title').notNull(),
4 content: text('content').notNull(),
5 authorId: integer('author_id')
6 .references(() => users.id)
7 .notNull(),
8});
9
10// ...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.
1export enum PostgresErrorCode {
2 NotNullViolation = '23502',
3}In TypeScript, all caught errors are initially of the unknown type. Let’s create an interface and a type guard to deal with that.
1import { PostgresErrorCode } from './postgres-error-code.enum';
2import { isRecord } from '../utilities/is-record';
3
4export interface DatabaseError {
5 code: PostgresErrorCode;
6 detail: string;
7 table: string;
8 column?: string;
9}
10
11export function isDatabaseError(value: unknown): value is DatabaseError {
12 if (!isRecord(value)) {
13 return false;
14 }
15 const { code, detail, table } = value;
16 return Boolean(code && detail && table);
17}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.
1import {
2 BadRequestException,
3 Injectable,
4} from '@nestjs/common';
5import { DrizzleService } from '../database/drizzle.service';
6import { databaseSchema } from '../database/database-schema';
7import { CreateArticleDto } from './dto/create-article.dto';
8import { isDatabaseError } from '../database/databse-error';
9import { PostgresErrorCode } from '../database/postgres-error-code.enum';
10
11@Injectable()
12export class ArticlesService {
13 constructor(private readonly drizzleService: DrizzleService) {}
14
15 async create(article: CreateArticleDto, authorId: number) {
16 try {
17 const createdArticles = await this.drizzleService.db
18 .insert(databaseSchema.articles)
19 .values({
20 authorId,
21 title: article.title,
22 content: article.content,
23 })
24 .returning();
25
26 return createdArticles.pop();
27 } catch (error) {
28 if (
29 isDatabaseError(error) &&
30 error.code === PostgresErrorCode.NotNullViolation
31 ) {
32 throw new BadRequestException(
33 `The value of ${error.column} can not be null`,
34 );
35 }
36 throw error;
37 }
38 }
39
40 // ...
41}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.
1import { serial, text, pgTable } from 'drizzle-orm/pg-core';
2
3export const users = pgTable('users', {
4 id: serial('id').primaryKey(),
5 email: text('email').unique().notNull(),
6 // ...
7});
8
9// ...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.
1export enum PostgresErrorCode {
2 UniqueViolation = '23505',
3 NotNullViolation = '23502',
4}We can now use it in our service to respond with a custom exception if someone tries to use an occupied email address.
1import { Injectable } from '@nestjs/common';
2import { UserDto } from './user.dto';
3import { DrizzleService } from '../database/drizzle.service';
4import { databaseSchema } from '../database/database-schema';
5import { PostgresErrorCode } from '../database/postgres-error-code.enum';
6import { UserAlreadyExistsException } from './user-already-exists.exception';
7import { isDatabaseError } from '../database/databse-error';
8
9@Injectable()
10export class UsersService {
11 constructor(private readonly drizzleService: DrizzleService) {}
12
13 async create(user: UserDto) {
14 try {
15 const createdUsers = await this.drizzleService.db
16 .insert(databaseSchema.users)
17 .values(user)
18 .returning();
19
20 return createdUsers.pop();
21 } catch (error) {
22 if (
23 isDatabaseError(error) &&
24 error.code === PostgresErrorCode.UniqueViolation
25 ) {
26 throw new UserAlreadyExistsException(user.email);
27 }
28 throw error;
29 }
30 }
31
32 // ...
33}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.
1import { serial, pgTable } from 'drizzle-orm/pg-core';
2
3export const addresses = pgTable('addresses', {
4 id: serial('id').primaryKey(),
5 // ...
6});
7
8// ...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.
1import { serial, text, integer, pgTable } from 'drizzle-orm/pg-core';
2
3export const users = pgTable('users', {
4 id: serial('id').primaryKey(),
5 email: text('email').unique().notNull(),
6 // ...
7});
8
9export const articles = pgTable('articles', {
10 id: serial('id').primaryKey(),
11 title: text('title').notNull(),
12 content: text('content').notNull(),
13 authorId: integer('author_id')
14 .references(() => users.id)
15 .notNull(),
16});
17
18// ...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.
1export enum PostgresErrorCode {
2 UniqueViolation = '23505',
3 NotNullViolation = '23502',
4 ForeignKeyViolation = '23503',
5}We can now use it to handle the foreign constraint violation.
1import {
2 BadRequestException,
3 Injectable,
4 NotFoundException,
5} from '@nestjs/common';
6import { DrizzleService } from '../database/drizzle.service';
7import { databaseSchema } from '../database/database-schema';
8import { eq } from 'drizzle-orm';
9import { PostgresErrorCode } from '../database/postgres-error-code.enum';
10import { isDatabaseError } from '../database/databse-error';
11
12@Injectable()
13export class UsersService {
14 constructor(private readonly drizzleService: DrizzleService) {}
15
16 // ...
17
18 async delete(userId: number) {
19 try {
20 const deletedUsers = await this.drizzleService.db
21 .delete(databaseSchema.users)
22 .where(eq(databaseSchema.users.id, userId))
23 .returning();
24 if (deletedUsers.length === 0) {
25 throw new NotFoundException();
26 }
27 } catch (error) {
28 if (
29 isDatabaseError(error) &&
30 error.code === PostgresErrorCode.ForeignKeyViolation
31 ) {
32 throw new BadRequestException(
33 'Can not remove a user that is an author of an article',
34 );
35 }
36 throw error;
37 }
38 }
39}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.
1npx 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.
1ALTER TABLE "articles" ADD CHECK(length(title) > 0);We can now apply it using the migrate command.
1npx drizzle-kit migrateCheck constraint error handling
Let’s start by adding the check constraint violation to our enum.
1export enum PostgresErrorCode {
2 UniqueViolation = '23505',
3 NotNullViolation = '23502',
4 ForeignKeyViolation = '23503',
5 CheckViolation = '23514',
6}We can now use the enum to handle the check constraint violation.
1import {
2 BadRequestException,
3 Injectable,
4} from '@nestjs/common';
5import { DrizzleService } from '../database/drizzle.service';
6import { databaseSchema } from '../database/database-schema';
7import { CreateArticleDto } from './dto/create-article.dto';
8import { isDatabaseError } from '../database/databse-error';
9import { PostgresErrorCode } from '../database/postgres-error-code.enum';
10
11@Injectable()
12export class ArticlesService {
13 constructor(private readonly drizzleService: DrizzleService) {}
14
15 async create(article: CreateArticleDto, authorId: number) {
16 try {
17 const createdArticles = await this.drizzleService.db
18 .insert(databaseSchema.articles)
19 .values({
20 authorId,
21 title: article.title,
22 content: article.content,
23 })
24 .returning();
25
26 return createdArticles.pop();
27 } catch (error) {
28 if (!isDatabaseError(error)) {
29 throw error;
30 }
31 if (error.code === PostgresErrorCode.NotNullViolation) {
32 throw new BadRequestException(
33 `The value of ${error.column} can not be null`,
34 );
35 }
36 if (error.code === PostgresErrorCode.CheckViolation) {
37 throw new BadRequestException('The title can not be an empty string');
38 }
39 throw error;
40 }
41 }
42
43 // ...
44}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.