Handling SQL constraints with Kysely

September 11, 2023

When using SQL databases, we can set constraints to ensure our data remains accurate and reliable during insertions, updates, or deletions. In this article, we’ll explore different SQL constraints and demonstrate how to apply them using Kysely and NestJS to maintain data integrity.

Check out this repository for the full code from this article.

Not-null constraint#

In SQL databases, null represents the absence of value. Using the not-null constraint, we can ensure the column does not accept the null value. Let’s take a look at a migration we created with Kysely in one of the previous articles:

20230806213313_add_articles_table.ts#

import { Kysely } from "kysely"
 
export async function up(database: Kysely<unknown>): Promise<void> {
  await database.schema
    .createTable("articles")
    .addColumn("title", "text", (column) => column.notNull())
    .addColumn("article_content", "text", (column) => column.notNull()) // ...
    .execute()
}

When we call the addColumn method, we define a new column by providing its name and type. We also can supply a third argument that allows us to describe the column further. By calling the column.notNull() function, we add the not-null constraint to our column.

Not-null constraint violation error handling#

Thanks to the not-null constraint, the database throws an error when trying to save a null value. To indicate that the not-null constraint violation causes the error, PostgreSQL uses the 23502 code. To avoid having to remember it later, let’s create an enum to store error codes.

postgresErrorCode.enum.ts#

export enum PostgresErrorCode {
  NotNullViolation = '23502',
}

In TypeScript, all caught errors have the unknown type by default. Let’s create an interface dedicated to database errors.

databaseError.ts#

import { PostgresErrorCode } from "../database/postgresErrorCode.enum"
import { isRecord } from "../utils/isRecord"
 
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 is a function that we wrote earlier. It checks if a particular value is of the Record<string, unknown> type.

Thanks to the isDatabaseError type guard, we can check if a particular value matches the DatabaseError interface. Let’s use it in our repository.

articles.repository.ts#

import { Database } from "../database/database"
import { Article } from "./article.model"
import { BadRequestException, Injectable } from "@nestjs/common"
import { ArticleDto } from "./dto/article.dto"
import { PostgresErrorCode } from "../database/postgresErrorCode.enum"
import { isDatabaseError } from "../types/databaseError"
@Injectable()
export class ArticlesRepository {
  constructor(private readonly database: Database) {}
  async create(data: ArticleDto, authorId: number) {
    try {
      const databaseResponse = await this.database
        .insertInto("articles")
        .values({
          title: data.title,
          article_content: data.content,
          author_id: authorId,
        })
        .returningAll()
        .executeTakeFirstOrThrow()
      return new Article(databaseResponse)
    } catch (error) {
      if (isDatabaseError(error) && error.code === PostgresErrorCode.NotNullViolation) {
        throw new BadRequestException(`A null value can't be set for the ${error.column} column`)
      }
      throw error
    }
  } // ...
}

If the caught error matches the DatabaseError interface and contains the expected code, we throw the BadRequestException to inform NestJS that we want to respond with a 400 Bad Request status code.

If the error is not something we recognize, we want to rethrow it. Thanks to that, we are not hiding or suppressing errors. Instead, we move them up to the higher-level error handlers.

Unique constraint#

With the unique constraint, we can ensure that all values in a particular column are unique across the entire table. A good example is the email column we added in one of the recent articles.

20230813165809_add_users_table.ts#

import { Kysely } from "kysely"
 
export async function up(database: Kysely<unknown>): Promise<void> {
  await database.schema
    .createTable("users")
    .addColumn("email", "text", (column) => {
      return column.notNull().unique()
    }) // ...
    .execute()
}

By calling the unique() function, we add the unique constraint to the email column. PostgreSQL will throw an error if we try to create two users with the same email.

Unique constraint violation error handling#

To handle the violation of the unique constraint, we need to start by adding the appropriate code to our enum.

postgresErrorCode.enum.ts#

export enum PostgresErrorCode {
  UniqueViolation = '23505',
  NotNullViolation = '23502',
}

We should now check for the above code in our repository.

users.repository.ts#

import { BadRequestException, Injectable } from "@nestjs/common"
import { User } from "./user.model"
import { CreateUserDto } from "./dto/createUser.dto"
import { Database } from "../database/database"
import { isDatabaseError } from "../types/databaseError"
import { PostgresErrorCode } from "../database/postgresErrorCode.enum"
@Injectable()
export class UsersRepository {
  constructor(private readonly database: Database) {}
  async create(userData: CreateUserDto) {
    try {
      const databaseResponse = await this.database
        .insertInto("users")
        .values({
          password: userData.password,
          email: userData.email,
          name: userData.name,
        })
        .returningAll()
        .executeTakeFirstOrThrow()
      return new User(databaseResponse)
    } catch (error) {
      if (isDatabaseError(error) && error.code === PostgresErrorCode.UniqueViolation) {
        throw new BadRequestException("User with this email already exists")
      }
      throw error
    }
  } // ...
}

Primary key constraint#

When adding the primary key constraint, we indicate that a particular column serves as a unique identifier for the rows in the table.

20230827204025_add_categories_table.ts#

import { Kysely } from "kysely"
 
export async function up(database: Kysely<unknown>): Promise<void> {
  await database.schema.createTable("categories").addColumn("id", "serial", (column) => {
    return column.primaryKey()
  }) // ...
}

When we call the primaryKey() function, we indicate that all values in the column should not equal null and be unique.

Primary key constraint violation error handling#

There isn’t a high chance of violating the primary key constraint because we usually let PostgreSQL generate the key for us. However, we could check for the unique violation and the not-null violation.

categories.repository.ts#

import { Database } from "../database/database"
import { BadRequestException, Injectable } from "@nestjs/common"
import { Category } from "./category.model"
import { CategoryDto } from "./dto/category.dto"
import { isDatabaseError } from "../types/databaseError"
import { PostgresErrorCode } from "../database/postgresErrorCode.enum"
@Injectable()
export class CategoriesRepository {
  constructor(private readonly database: Database) {}
  async create(data: CategoryDto) {
    try {
      const databaseResponse = await this.database
        .insertInto("categories")
        .values({
          name: data.name,
        })
        .returningAll()
        .executeTakeFirstOrThrow()
      return new Category(databaseResponse)
    } catch (error) {
      if (!isDatabaseError(error) || error.column !== "id") {
        throw error
      }
      if (
        error.code === PostgresErrorCode.UniqueViolation ||
        error.code === PostgresErrorCode.NotNullViolation
      ) {
        throw new BadRequestException(
          "The value for the id column violates the primary key constraint",
        )
      }
      throw error
    }
  } // ...
}

Using multiple columns as a primary key#

We can’t create a table that has more than one primary key. However, we can define a primary key that consists of multiple columns. This is very common when designing many-to-many relationships.

20230827204025_add_categories_table.ts#

import { Kysely } from "kysely"
 
export async function up(database: Kysely<unknown>): Promise<void> {
  await database.schema // ...
    .addPrimaryKeyConstraint("primary_key", ["category_id", "article_id"])
    .execute()
}

Foreign key constraint#

To define relationships, we need the foreign key constraint. It requires a value in one table’s column to match a value in another table’s column.

20230827204025_add_categories_table.ts#

import { Kysely } from "kysely"
 
export async function up(database: Kysely<unknown>): Promise<void> {
  await database.schema
    .createTable("categories")
    .addColumn("id", "serial", (column) => {
      return column.primaryKey()
    })
    .addColumn("name", "text", (column) => column.notNull())
    .execute()
  await database.schema
    .createTable("categories_articles")
    .addColumn("category_id", "integer", (column) => {
      return column.references("categories.id").notNull()
    })
    .addColumn("article_id", "integer", (column) => {
      return column.references("articles.id").notNull()
    })
    .addPrimaryKeyConstraint("primary_key", ["category_id", "article_id"])
    .execute()
}

When calling the column.references('categories.id') function above, we ensure that the category_id column matches the id from the categories table.

The column.references('articles.id') ensures that the article_id contains the matching id from the articles table.

Foreign key constraint violation error handling#

A very good example of violating the foreign key constraint is trying to refer to a record that does not exist. To handle it, let’s add the necessary code to our enum.

postgresErrorCode.enum.ts#

export enum PostgresErrorCode {
  UniqueViolation = '23505',
  ForeignKeyViolation = '23503',
  NotNullViolation = '23502',
}

We can now handle the violation appropriately in our repository.

categories.repository.ts#

import { Database, Tables } from "../database/database"
import { BadRequestException, Injectable } from "@nestjs/common"
import { Transaction } from "kysely"
import { PostgresErrorCode } from "../database/postgresErrorCode.enum"
import { isDatabaseError } from "../types/databaseError"
@Injectable()
export class ArticlesRepository {
  constructor(private readonly database: Database) {}
  private async addCategoriesToArticle(
    transaction: Transaction<Tables>,
    articleId: number,
    categoryIdsToAdd: number[],
  ) {
    if (!categoryIdsToAdd.length) {
      return
    }
    try {
      await transaction
        .insertInto("categories_articles")
        .values(
          categoryIdsToAdd.map((categoryId) => {
            return {
              article_id: articleId,
              category_id: categoryId,
            }
          }),
        )
        .execute()
    } catch (error) {
      if (isDatabaseError(error) && error.code === PostgresErrorCode.ForeignKeyViolation) {
        throw new BadRequestException("Category not found")
      }
      throw error
    }
  } // ...
}

Check constraint#

With the check constraint, we can specify the requirements for a value in a particular column in a more generic way. Let’s add a constraint that ensures the article does not contain empty content.

20230909022554_article_title_length_constraint.ts#

import { Kysely, sql } from "kysely"
 
export async function up(database: Kysely<unknown>): Promise<void> {
  await database.schema
    .alterTable("articles")
    .addCheckConstraint("article_title_length_constraint", sql`length(article_content) > 0`)
    .execute()
}
export async function down(database: Kysely<unknown>): Promise<void> {
  await database.schema
    .alterTable("articles")
    .dropConstraint("article_title_length_constraint")
    .execute()
}

Thanks to the above, the article_content column can’t hold empty strings.

Check constraint violation error handling#

To handle the check constraint violation, we need to start by adding the error code to our enum.

postgresErrorCode.enum.ts#

export enum PostgresErrorCode {
  UniqueViolation = '23505',
  ForeignKeyViolation = '23503',
  NotNullViolation = '23502',
  CheckViolation = '23514',
}

Let’s check if the constraint is violated in the existing try...catch block in the repository.

articles.repository.ts#

import { Database } from "../database/database"
import { Article } from "./article.model"
import { BadRequestException, Injectable } from "@nestjs/common"
import { ArticleDto } from "./dto/article.dto"
import { PostgresErrorCode } from "../database/postgresErrorCode.enum"
import { isDatabaseError } from "../types/databaseError"
@Injectable()
export class ArticlesRepository {
  constructor(private readonly database: Database) {}
  async create(data: ArticleDto, authorId: number) {
    try {
      const databaseResponse = await this.database
        .insertInto("articles")
        .values({
          title: data.title,
          article_content: data.content,
          author_id: authorId,
        })
        .returningAll()
        .executeTakeFirstOrThrow()
      return new Article(databaseResponse)
    } catch (error) {
      if (!isDatabaseError(error)) {
        throw error
      }
      if (error.code === PostgresErrorCode.CheckViolation) {
        throw new BadRequestException("The length of the content needs to be greater than 0")
      }
      if (error.code === PostgresErrorCode.NotNullViolation) {
        throw new BadRequestException(`A null value can't be set for the ${error.column} column`)
      }
      throw error
    }
  } // ...
}

Summary#

In this article, we’ve gone through constraints in PostgreSQL and implemented them through Kysely. When doing that, we also added error handling to handle any of our constraints being violated. Thanks to doing that, we ensured the integrity of our data on the database level.