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:
1import { Kysely } from 'kysely';
2
3export async function up(database: Kysely<unknown>): Promise<void> {
4 await database.schema
5 .createTable('articles')
6 .addColumn('title', 'text', (column) => column.notNull())
7 .addColumn('article_content', 'text', (column) => column.notNull())
8 // ...
9 .execute();
10}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.
1export enum PostgresErrorCode {
2 NotNullViolation = '23502',
3}In TypeScript, all caught errors have the unknown type by default. Let’s create an interface dedicated to database errors.
1import { PostgresErrorCode } from '../database/postgresErrorCode.enum';
2import { isRecord } from '../utils/isRecord';
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}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.
1import { Database } from '../database/database';
2import { Article } from './article.model';
3import { BadRequestException, Injectable } from '@nestjs/common';
4import { ArticleDto } from './dto/article.dto';
5import { PostgresErrorCode } from '../database/postgresErrorCode.enum';
6import { isDatabaseError } from '../types/databaseError';
7
8@Injectable()
9export class ArticlesRepository {
10 constructor(private readonly database: Database) {}
11
12 async create(data: ArticleDto, authorId: number) {
13 try {
14 const databaseResponse = await this.database
15 .insertInto('articles')
16 .values({
17 title: data.title,
18 article_content: data.content,
19 author_id: authorId,
20 })
21 .returningAll()
22 .executeTakeFirstOrThrow();
23 return new Article(databaseResponse);
24 } catch (error) {
25 if (
26 isDatabaseError(error) &&
27 error.code === PostgresErrorCode.NotNullViolation
28 ) {
29 throw new BadRequestException(
30 `A null value can't be set for the ${error.column} column`,
31 );
32 }
33 throw error;
34 }
35 }
36
37 // ...
38}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.
1import { Kysely } from 'kysely';
2
3export async function up(database: Kysely<unknown>): Promise<void> {
4 await database.schema
5 .createTable('users')
6 .addColumn('email', 'text', (column) => {
7 return column.notNull().unique();
8 })
9 // ...
10 .execute();
11}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.
1export enum PostgresErrorCode {
2 UniqueViolation = '23505',
3 NotNullViolation = '23502',
4}We should now check for the above code in our repository.
1import { BadRequestException, Injectable } from '@nestjs/common';
2import { User } from './user.model';
3import { CreateUserDto } from './dto/createUser.dto';
4import { Database } from '../database/database';
5import { isDatabaseError } from '../types/databaseError';
6import { PostgresErrorCode } from '../database/postgresErrorCode.enum';
7
8@Injectable()
9export class UsersRepository {
10 constructor(private readonly database: Database) {}
11
12 async create(userData: CreateUserDto) {
13 try {
14 const databaseResponse = await this.database
15 .insertInto('users')
16 .values({
17 password: userData.password,
18 email: userData.email,
19 name: userData.name,
20 })
21 .returningAll()
22 .executeTakeFirstOrThrow();
23
24 return new User(databaseResponse);
25 } catch (error) {
26 if (
27 isDatabaseError(error) &&
28 error.code === PostgresErrorCode.UniqueViolation
29 ) {
30 throw new BadRequestException('User with this email already exists');
31 }
32 throw error;
33 }
34 }
35
36 // ...
37}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.
1import { Kysely } from 'kysely';
2
3export async function up(database: Kysely<unknown>): Promise<void> {
4 await database.schema
5 .createTable('categories')
6 .addColumn('id', 'serial', (column) => {
7 return column.primaryKey();
8 })
9 // ...
10}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.
1import { Database } from '../database/database';
2import { BadRequestException, Injectable } from '@nestjs/common';
3import { Category } from './category.model';
4import { CategoryDto } from './dto/category.dto';
5import { isDatabaseError } from '../types/databaseError';
6import { PostgresErrorCode } from '../database/postgresErrorCode.enum';
7
8@Injectable()
9export class CategoriesRepository {
10 constructor(private readonly database: Database) {}
11
12 async create(data: CategoryDto) {
13 try {
14 const databaseResponse = await this.database
15 .insertInto('categories')
16 .values({
17 name: data.name,
18 })
19 .returningAll()
20 .executeTakeFirstOrThrow();
21
22 return new Category(databaseResponse);
23 } catch (error) {
24 if (!isDatabaseError(error) || error.column !== 'id') {
25 throw error;
26 }
27 if (
28 error.code === PostgresErrorCode.UniqueViolation ||
29 error.code === PostgresErrorCode.NotNullViolation
30 ) {
31 throw new BadRequestException(
32 'The value for the id column violates the primary key constraint',
33 );
34 }
35 throw error;
36 }
37 }
38
39 // ...
40}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
1import { Kysely } from 'kysely';
2
3export async function up(database: Kysely<unknown>): Promise<void> {
4 await database.schema
5 // ...
6 .addPrimaryKeyConstraint('primary_key', ['category_id', 'article_id'])
7 .execute();
8}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.
1import { Kysely } from 'kysely';
2
3export async function up(database: Kysely<unknown>): Promise<void> {
4 await database.schema
5 .createTable('categories')
6 .addColumn('id', 'serial', (column) => {
7 return column.primaryKey();
8 })
9 .addColumn('name', 'text', (column) => column.notNull())
10 .execute();
11
12 await database.schema
13 .createTable('categories_articles')
14 .addColumn('category_id', 'integer', (column) => {
15 return column.references('categories.id').notNull();
16 })
17 .addColumn('article_id', 'integer', (column) => {
18 return column.references('articles.id').notNull();
19 })
20 .addPrimaryKeyConstraint('primary_key', ['category_id', 'article_id'])
21 .execute();
22}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.
1export enum PostgresErrorCode {
2 UniqueViolation = '23505',
3 ForeignKeyViolation = '23503',
4 NotNullViolation = '23502',
5}We can now handle the violation appropriately in our repository.
1import { Database, Tables } from '../database/database';
2import { BadRequestException, Injectable } from '@nestjs/common';
3import { Transaction } from 'kysely';
4import { PostgresErrorCode } from '../database/postgresErrorCode.enum';
5import { isDatabaseError } from '../types/databaseError';
6
7@Injectable()
8export class ArticlesRepository {
9 constructor(private readonly database: Database) {}
10
11 private async addCategoriesToArticle(
12 transaction: Transaction<Tables>,
13 articleId: number,
14 categoryIdsToAdd: number[],
15 ) {
16 if (!categoryIdsToAdd.length) {
17 return;
18 }
19 try {
20 await transaction
21 .insertInto('categories_articles')
22 .values(
23 categoryIdsToAdd.map((categoryId) => {
24 return {
25 article_id: articleId,
26 category_id: categoryId,
27 };
28 }),
29 )
30 .execute();
31 } catch (error) {
32 if (
33 isDatabaseError(error) &&
34 error.code === PostgresErrorCode.ForeignKeyViolation
35 ) {
36 throw new BadRequestException('Category not found');
37 }
38 throw error;
39 }
40 }
41
42 // ...
43}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.
1import { Kysely, sql } from 'kysely';
2
3export async function up(database: Kysely<unknown>): Promise<void> {
4 await database.schema
5 .alterTable('articles')
6 .addCheckConstraint(
7 'article_title_length_constraint',
8 sql`length(article_content) > 0`,
9 )
10 .execute();
11}
12
13export async function down(database: Kysely<unknown>): Promise<void> {
14 await database.schema
15 .alterTable('articles')
16 .dropConstraint('article_title_length_constraint')
17 .execute();
18}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.
1export enum PostgresErrorCode {
2 UniqueViolation = '23505',
3 ForeignKeyViolation = '23503',
4 NotNullViolation = '23502',
5 CheckViolation = '23514',
6}Let’s check if the constraint is violated in the existing try...catch block in the repository.
1import { Database } from '../database/database';
2import { Article } from './article.model';
3import {
4 BadRequestException,
5 Injectable,
6} from '@nestjs/common';
7import { ArticleDto } from './dto/article.dto';
8import { PostgresErrorCode } from '../database/postgresErrorCode.enum';
9import { isDatabaseError } from '../types/databaseError';
10
11@Injectable()
12export class ArticlesRepository {
13 constructor(private readonly database: Database) {}
14
15 async create(data: ArticleDto, authorId: number) {
16 try {
17 const databaseResponse = await this.database
18 .insertInto('articles')
19 .values({
20 title: data.title,
21 article_content: data.content,
22 author_id: authorId,
23 })
24 .returningAll()
25 .executeTakeFirstOrThrow();
26 return new Article(databaseResponse);
27 } catch (error) {
28 if (!isDatabaseError(error)) {
29 throw error;
30 }
31 if (error.code === PostgresErrorCode.CheckViolation) {
32 throw new BadRequestException(
33 'The length of the content needs to be greater than 0',
34 );
35 }
36 if (error.code === PostgresErrorCode.NotNullViolation) {
37 throw new BadRequestException(
38 `A null value can't be set for the ${error.column} column`,
39 );
40 }
41 throw error;
42 }
43 }
44
45 // ...
46}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.