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): Promise { 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. A lot of not-null constraint violations can be avoided by validating the data sent by the users of our API. If you want to know more, check out API with NestJS #4. Error handling and data validation 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): Promise { 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): Promise { 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. If you want to know more about many-to-many relationships with Kysely, check out API with NestJS #122. Many-to-many relationships with Kysely and PostgreSQL 20230827204025_add_categories_table.ts import { Kysely } from 'kysely'; export async function up(database: Kysely): Promise { 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): Promise { 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