Defining constraints with raw SQL

December 5, 2022

Having significant control over the data we store in our database is crucial. One of the ways to do that is to choose suitable column types. We can also use constraints to go further and reject the data that does not match our guidelines. By doing that, we can have an additional layer of security that ensures the integrity of our data.

Not null constraint#

With the not-null constraint, we can enforce a column to have a value other than null. For example, let’s look at the table we’ve implemented in one of the previous parts of this series.

export async function up(knex: Knex): Promise<void> {
  return knex.raw(`
    CREATE TABLE posts (
      id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      title text NOT NULL,
      post_content text NOT NULL
    )
  `);
}

Error handling for non-null constraints#

When using the NOT NULL constraint, PostgreSQL throws an error when trying to save a null value for the constrained column. When using try...catch with TypeScript, the type of the error is unknown.

posts.repository.ts#

import { Injectable } from "@nestjs/common"
import DatabaseService from "../database/database.service"
import PostModel from "./post.model"
import PostDto from "./post.dto"
@Injectable()
class PostsRepository {
  constructor(private readonly databaseService: DatabaseService) {}
  async create(postData: PostDto, authorId: number) {
    try {
      const databaseResponse = await this.databaseService.runQuery(
        `
      INSERT INTO posts (
        title,
        post_content,
        author_id
      ) VALUES (
        $1,
        $2,
        $3
      ) RETURNING *
    `,
        [postData.title, postData.content, authorId],
      )
      return new PostModel(databaseResponse.rows[0])
    } catch (error) {
      // the error is unknown
    }
  } // ...
}
export default PostsRepository

Because of the above, we need a way to narrow the type down. The best way to do that is to implement a type guard.

databaseError.ts#

import PostgresErrorCode from "../database/postgresErrorCode.enum"
import isRecord from "../utils/isRecord"
 
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)
}
export default DatabaseError

Above we also use the isRecord type guard function that helps us determine if the value is a valid object and not an array. If you want to check it out, see the file in the repository.

PostgreSQL uses a set of error codes to let us know what constraint was violated. Let’s put the not-null violation error code in an enum.

postgresErrorCode.enum.ts#

enum PostgresErrorCode {
  NotNullViolation = "23502",
}
export default PostgresErrorCode

Thanks to all of the above, we can use the isDatabaseError function to determine if a particular value matches the DatabaseError database.

posts.repository.ts#

import { BadRequestException, Injectable } from "@nestjs/common"
import DatabaseService from "../database/database.service"
import PostModel from "./post.model"
import PostDto from "./post.dto"
import { isDatabaseError } from "../types/databaseError"
import PostgresErrorCode from "../database/postgresErrorCode.enum"
@Injectable()
class PostsRepository {
  constructor(private readonly databaseService: DatabaseService) {}
  async create(postData: PostDto, authorId: number) {
    try {
      const databaseResponse = await this.databaseService.runQuery(
        `
          INSERT INTO posts (
            title,
            post_content,
            author_id
          ) VALUES (
            $1,
            $2,
            $3
          ) RETURNING *
        `,
        [postData.title, postData.content, authorId],
      )
      return new PostModel(databaseResponse.rows[0])
    } catch (error) {
      if (!isDatabaseError(error) || !["title", "post_content"].includes(error.column)) {
        throw error
      }
      if (error.code === PostgresErrorCode.NotNullViolation) {
        throw new BadRequestException(`A null value can't be set for the ${error.column} column`)
      }
      throw error
    }
  } // ...
}
export default PostsRepository

Unique constraint#

The unique constraint ensures that all values in a particular column are unique across the table. A good example is the users table we created in one of the previous parts of this series.

export async function up(knex: Knex): Promise<void> {
  return knex.raw(`
    CREATE TABLE users (
      id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      email text NOT NULL UNIQUE,
      name text NOT NULL,
      password text NOT NULL
    )
  `);
}

Thanks to using the UNIQUE constraint above, PostgreSQL throws an error if we try to create two users with the same email.

Error handling for unique constraints#

To handle the unique constraint, we should add it to our PostgresErrorCode enum.

postgresErrorCode.enum.ts#

enum PostgresErrorCode {
  UniqueViolation = "23505",
  NotNullViolation = "23502",
}
export default PostgresErrorCode

We can now use it in our repository.

users.repository.ts#

import { Injectable } from "@nestjs/common"
import DatabaseService from "../database/database.service"
import UserModel from "./user.model"
import { CreateUserDto } from "./dto/createUser.dto"
import PostgresErrorCode from "../database/postgresErrorCode.enum"
import UserAlreadyExistsException from "./exceptions/userAlreadyExists.exception"
import { isDatabaseError } from "../types/databaseError"
@Injectable()
class UsersRepository {
  constructor(private readonly databaseService: DatabaseService) {}
  async create(userData: CreateUserDto) {
    if (userData.address) {
      return this.createUserWithAddress(userData)
    }
    try {
      const databaseResponse = await this.databaseService.runQuery(
        `
      INSERT INTO users (
        email,
        name,
        password
      ) VALUES (
        $1,
        $2,
        $3
      ) RETURNING *
    `,
        [userData.email, userData.name, userData.password],
      )
      return new UserModel(databaseResponse.rows[0])
    } catch (error) {
      if (isDatabaseError(error) && error.code === PostgresErrorCode.UniqueViolation) {
        throw new UserAlreadyExistsException(userData.email)
      }
      throw error
    }
  } // ...
}
export default UsersRepository

Above, we throw a custom error using the UserAlreadyExistsException extending the BadRequestException.

userAlreadyExists.exception.ts#

import { BadRequestException } from "@nestjs/common"
 
class UserAlreadyExistsException extends BadRequestException {
  constructor(email: string) {
    super(`User with ${email} email already exists`)
  }
}
export default UserAlreadyExistsException

Using the unique constraint with a group of columns#

Using different syntax allows us to expect a group of columns to have a unique value.

CREATE TABLE users (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  first_name text,
  last_name text,
  UNIQUE (first_name, last_name)
)

Above, we expect users to have a unique combination of their first and last names. However, they can still share the same first name if their last name differs.

Primary key constraint#

The primary key is a very common constraint. When using it, we indicate that a particular column serves as a unique identifier for the rows in the table.

export async function up(knex: Knex): Promise<void> {
  return knex.raw(`
    CREATE TABLE categories (
      id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      name text NOT NULL
    );
    // ...
  `);
}

When we use the PRIMARY KEY constraint, we indicate that all values in a particular column should be unique and not equal to null. To ensure that, PostgreSQL creates a unique index to keep track of all of the values.

Error handling for primary keys#

In the case of the primary keys, we need to worry about the unique violation and not-null violation. Fortunately, we already have them as part of our PostgresErrorCode enum.

categories.repository.ts#

import { BadRequestException, Injectable } from "@nestjs/common"
import DatabaseService from "../database/database.service"
import CategoryModel from "./category.model"
import CategoryDto from "./category.dto"
import { isDatabaseError } from "../types/databaseError"
import PostgresErrorCode from "../database/postgresErrorCode.enum"
@Injectable()
class CategoriesRepository {
  constructor(private readonly databaseService: DatabaseService) {}
  async create(categoryData: CategoryDto) {
    try {
      const databaseResponse = await this.databaseService.runQuery(
        `
      INSERT INTO categories (
        name
      ) VALUES (
        $1
      ) RETURNING *
    `,
        [categoryData.name],
      )
      return new CategoryModel(databaseResponse.rows[0])
    } 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
    }
  } // ...
}
export default CategoriesRepository

In a lot of the cases there isn’t a high chance of violating the primary key constraint because we usually let PostgreSQL to generate it for us.

Using a group of columns as a primary key#

We can’t have a table that has more than one primary key. However, we can have a primary key that consists of multiple columns.

CREATE TABLE users (
  first_name text,
  last_name text,
  PRIMARY KEY (first_name, last_name)
)

Due to how the above constraint is designed, we can’t have two users with the same combination of first and last names.

Foreign key constraint#

We use the foreign key constraint when defining relations. To do that, we need to use the REFERENCES keyword.

export async function up(knex: Knex): Promise<void> {
  return knex.raw(`
    CREATE TABLE categories (
      id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      name text NOT NULL
    );
    CREATE TABLE categories_posts (
      category_id int REFERENCES categories(id),
      post_id int REFERENCES posts(id),
      PRIMARY KEY (category_id, post_id)
    );
  `);
}

Because of the foreign keys in the categories_posts table, each post needs to refer to a valid category.

Error handling for foreign key constraints#

A good example of a foreign key constraint violation is referring to an entity that does not exist. To be able to handle it, let’s add the appropriate code to our enum.

postgresErrorCode.enum.ts#

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

Thanks to the above, we can now handle the constraint violation appropriately.

posts.repository.ts#

import { BadRequestException, Injectable } from "@nestjs/common"
import { PoolClient } from "pg"
import PostgresErrorCode from "../database/postgresErrorCode.enum"
import { isDatabaseError } from "../types/databaseError"
@Injectable()
class PostsRepository {
  private async addCategoriesToPost(
    client: PoolClient,
    postId: number,
    categoryIdsToAdd: number[],
  ) {
    if (!categoryIdsToAdd.length) {
      return
    }
    try {
      await client.query(
        `
      INSERT INTO categories_posts (
        post_id, category_id
      )
        SELECT $1 AS post_id, unnest($2::int[]) AS category_id
    `,
        [postId, categoryIdsToAdd],
      )
    } catch (error) {
      if (isDatabaseError(error) && error.code === PostgresErrorCode.ForeignKeyViolation) {
        throw new BadRequestException("Category not found")
      }
      throw error
    }
  } // ...
}
export default PostsRepository

Check constraint#

The check constraint is more generic than the previous examples. We can use it to specify the requirements for a value in a particular column. Let’s inspect the migration we wrote in one of the previous parts of this series.

export async function up(knex: Knex): Promise<void> {
  return knex.raw(`
    CREATE TABLE comments (
      id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
      content text NOT NULL,
      post_id int REFERENCES posts(id) NOT NULL,
      author_id int REFERENCES posts(id) NOT NULL,
      deletion_date timestamptz
    );
  `);
}

We can modify the above table and add a constraint to an existing column by creating a new migration.

npx knex migrate:make add_comment_length_constraint

20221201022319_add_comment_length_constraint.ts#

import { Knex } from "knex"
 
export async function up(knex: Knex): Promise<void> {
  return knex.raw(`
    ALTER TABLE comments
    ADD CONSTRAINT comment_length_constraint CHECK (
      length(content) > 0
    )
  `)
}
export async function down(knex: Knex): Promise<void> {
  return knex.raw(`
    ALTER TABLE comments DROP CONSTRAINT comment_length_constraint;
  `)
}

With the above constraint, we specify that the content column can’t hold an empty string.

Handling the check constraint violation#

To handle the violation of the above constraint, we need to add the appropriate code to our enum.

postgresErrorCode.enum.ts#

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

We can now use the code to check if the constraint was violated.

comments.repository.ts#

import { BadRequestException, Injectable } from "@nestjs/common"
import DatabaseService from "../database/database.service"
import CommentModel from "./comment.model"
import CommentDto from "./comment.dto"
import { isDatabaseError } from "../types/databaseError"
import PostgresErrorCode from "../database/postgresErrorCode.enum"
@Injectable()
class CommentsRepository {
  constructor(private readonly databaseService: DatabaseService) {}
  async create(commentData: CommentDto, authorId: number) {
    try {
      const databaseResponse = await this.databaseService.runQuery(
        `
      INSERT INTO comments (
        content,
        post_id,
        author_id
      ) VALUES (
        $1,
        $2,
        $3
      ) RETURNING *
    `,
        [commentData.content, commentData.postId, authorId],
      )
      return new CommentModel(databaseResponse.rows[0])
    } catch (error) {
      if (isDatabaseError(error) && error.code === PostgresErrorCode.CheckViolation) {
        throw new BadRequestException("The length of the content needs to be greater than 0")
      }
      throw error
    }
  } // ...
}
export default CommentsRepository

Summary#

In this article, we’ve gone through constraints in PostgreSQL. We’ve learned how to apply them to our tables and how they can help manage our database. When doing that, we’ve also implemented error handling so that we can react accordingly when a particular constraint is violated.