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.
1export async function up(knex: Knex): Promise<void> {
2 return knex.raw(`
3 CREATE TABLE posts (
4 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
5 title text NOT NULL,
6 post_content text NOT NULL
7 )
8 `);
9}Above, we’re using an identity column. If you want to know more, check out Serial type versus identity columns in PostgreSQL and TypeORM
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.
If you want to know more about the unknown type, check out Understanding any and unknown in TypeScript. Difference between never and void
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import PostModel from './post.model';
4import PostDto from './post.dto';
5
6@Injectable()
7class PostsRepository {
8 constructor(private readonly databaseService: DatabaseService) {}
9
10 async create(postData: PostDto, authorId: number) {
11 try {
12 const databaseResponse = await this.databaseService.runQuery(
13 `
14 INSERT INTO posts (
15 title,
16 post_content,
17 author_id
18 ) VALUES (
19 $1,
20 $2,
21 $3
22 ) RETURNING *
23 `,
24 [postData.title, postData.content, authorId],
25 );
26 return new PostModel(databaseResponse.rows[0]);
27 } catch (error) {
28 // the error is unknown
29 }
30 }
31
32 // ...
33}
34
35export 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.
If you want to read more about type guards, check out Structural type system and polymorphism in TypeScript. Type guards with predicates
1import PostgresErrorCode from '../database/postgresErrorCode.enum';
2import isRecord from '../utils/isRecord';
3
4interface 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}
18
19export 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.
1enum PostgresErrorCode {
2 NotNullViolation = '23502',
3}
4
5export default PostgresErrorCode;Thanks to all of the above, we can use the isDatabaseError function to determine if a particular value matches the DatabaseError database.
1import {
2 BadRequestException,
3 Injectable,
4} from '@nestjs/common';
5import DatabaseService from '../database/database.service';
6import PostModel from './post.model';
7import PostDto from './post.dto';
8import { isDatabaseError } from '../types/databaseError';
9import PostgresErrorCode from '../database/postgresErrorCode.enum';
10
11@Injectable()
12class PostsRepository {
13 constructor(private readonly databaseService: DatabaseService) {}
14
15 async create(postData: PostDto, authorId: number) {
16 try {
17 const databaseResponse = await this.databaseService.runQuery(
18 `
19 INSERT INTO posts (
20 title,
21 post_content,
22 author_id
23 ) VALUES (
24 $1,
25 $2,
26 $3
27 ) RETURNING *
28 `,
29 [postData.title, postData.content, authorId],
30 );
31 return new PostModel(databaseResponse.rows[0]);
32 } catch (error) {
33 if (
34 !isDatabaseError(error) ||
35 !['title', 'post_content'].includes(error.column)
36 ) {
37 throw error;
38 }
39 if (error.code === PostgresErrorCode.NotNullViolation) {
40 throw new BadRequestException(
41 `A null value can't be set for the ${error.column} column`,
42 );
43 }
44 throw error;
45 }
46 }
47
48 // ...
49}
50
51export 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.
1export async function up(knex: Knex): Promise<void> {
2 return knex.raw(`
3 CREATE TABLE users (
4 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
5 email text NOT NULL UNIQUE,
6 name text NOT NULL,
7 password text NOT NULL
8 )
9 `);
10}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.
1enum PostgresErrorCode {
2 UniqueViolation = '23505',
3 NotNullViolation = '23502',
4}
5
6export default PostgresErrorCode;We can now use it in our repository.
1import { Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import UserModel from './user.model';
4import { CreateUserDto } from './dto/createUser.dto';
5import PostgresErrorCode from '../database/postgresErrorCode.enum';
6import UserAlreadyExistsException from './exceptions/userAlreadyExists.exception';
7import { isDatabaseError } from '../types/databaseError';
8
9@Injectable()
10class UsersRepository {
11 constructor(private readonly databaseService: DatabaseService) {}
12
13 async create(userData: CreateUserDto) {
14 if (userData.address) {
15 return this.createUserWithAddress(userData);
16 }
17 try {
18 const databaseResponse = await this.databaseService.runQuery(
19 `
20 INSERT INTO users (
21 email,
22 name,
23 password
24 ) VALUES (
25 $1,
26 $2,
27 $3
28 ) RETURNING *
29 `,
30 [userData.email, userData.name, userData.password],
31 );
32 return new UserModel(databaseResponse.rows[0]);
33 } catch (error) {
34 if (
35 isDatabaseError(error) &&
36 error.code === PostgresErrorCode.UniqueViolation
37 ) {
38 throw new UserAlreadyExistsException(userData.email);
39 }
40 throw error;
41 }
42 }
43
44 // ...
45}
46
47export default UsersRepository;Above, we throw a custom error using the UserAlreadyExistsException extending the BadRequestException.
1import { BadRequestException } from '@nestjs/common';
2
3class UserAlreadyExistsException extends BadRequestException {
4 constructor(email: string) {
5 super(`User with ${email} email already exists`);
6 }
7}
8
9export 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.
1CREATE TABLE users (
2 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 first_name text,
4 last_name text,
5 UNIQUE (first_name, last_name)
6)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.
1export async function up(knex: Knex): Promise<void> {
2 return knex.raw(`
3 CREATE TABLE categories (
4 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
5 name text NOT NULL
6 );
7 // ...
8 `);
9}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.
If you want to know more about indexes, read API with NestJS #82. Introduction to indexes with raw SQL queries
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.
1import { BadRequestException, Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import CategoryModel from './category.model';
4import CategoryDto from './category.dto';
5import { isDatabaseError } from '../types/databaseError';
6import PostgresErrorCode from '../database/postgresErrorCode.enum';
7
8@Injectable()
9class CategoriesRepository {
10 constructor(private readonly databaseService: DatabaseService) {}
11
12 async create(categoryData: CategoryDto) {
13 try {
14 const databaseResponse = await this.databaseService.runQuery(
15 `
16 INSERT INTO categories (
17 name
18 ) VALUES (
19 $1
20 ) RETURNING *
21 `,
22 [categoryData.name],
23 );
24 return new CategoryModel(databaseResponse.rows[0]);
25 } catch (error) {
26 if (!isDatabaseError(error) || error.column !== 'id') {
27 throw error;
28 }
29 if (
30 error.code === PostgresErrorCode.UniqueViolation ||
31 error.code === PostgresErrorCode.NotNullViolation
32 ) {
33 throw new BadRequestException(
34 'The value for the id column violates the primary key constraint',
35 );
36 }
37 throw error;
38 }
39 }
40
41 // ...
42}
43
44export 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.
1CREATE TABLE users (
2 first_name text,
3 last_name text,
4 PRIMARY KEY (first_name, last_name)
5)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.
1export async function up(knex: Knex): Promise<void> {
2 return knex.raw(`
3 CREATE TABLE categories (
4 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
5 name text NOT NULL
6 );
7 CREATE TABLE categories_posts (
8 category_id int REFERENCES categories(id),
9 post_id int REFERENCES posts(id),
10 PRIMARY KEY (category_id, post_id)
11 );
12 `);
13}Above, we define a many-to-many relation. If you want to know more, read API with NestJS #75. Many-to-many relationships using raw SQL queries
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.
1enum PostgresErrorCode {
2 UniqueViolation = '23505',
3 NotNullViolation = '23502',
4 ForeignKeyViolation = '23503',
5}
6
7export default PostgresErrorCode;Thanks to the above, we can now handle the constraint violation appropriately.
1import { BadRequestException, Injectable } from '@nestjs/common';
2import { PoolClient } from 'pg';
3import PostgresErrorCode from '../database/postgresErrorCode.enum';
4import { isDatabaseError } from '../types/databaseError';
5
6@Injectable()
7class PostsRepository {
8 private async addCategoriesToPost(
9 client: PoolClient,
10 postId: number,
11 categoryIdsToAdd: number[],
12 ) {
13 if (!categoryIdsToAdd.length) {
14 return;
15 }
16 try {
17 await client.query(
18 `
19 INSERT INTO categories_posts (
20 post_id, category_id
21 )
22 SELECT $1 AS post_id, unnest($2::int[]) AS category_id
23 `,
24 [postId, categoryIdsToAdd],
25 );
26 } catch (error) {
27 if (
28 isDatabaseError(error) &&
29 error.code === PostgresErrorCode.ForeignKeyViolation
30 ) {
31 throw new BadRequestException('Category not found');
32 }
33 throw error;
34 }
35 }
36
37 // ...
38}
39
40export 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.
1export async function up(knex: Knex): Promise<void> {
2 return knex.raw(`
3 CREATE TABLE comments (
4 id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
5 content text NOT NULL,
6 post_id int REFERENCES posts(id) NOT NULL,
7 author_id int REFERENCES posts(id) NOT NULL,
8 deletion_date timestamptz
9 );
10 `);
11}We can modify the above table and add a constraint to an existing column by creating a new migration.
1npx knex migrate:make add_comment_length_constraint1import { Knex } from 'knex';
2
3export async function up(knex: Knex): Promise<void> {
4 return knex.raw(`
5 ALTER TABLE comments
6 ADD CONSTRAINT comment_length_constraint CHECK (
7 length(content) > 0
8 )
9 `);
10}
11
12export async function down(knex: Knex): Promise<void> {
13 return knex.raw(`
14 ALTER TABLE comments DROP CONSTRAINT comment_length_constraint;
15 `);
16}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.
1enum PostgresErrorCode {
2 UniqueViolation = '23505',
3 NotNullViolation = '23502',
4 ForeignKeyViolation = '23503',
5 CheckViolation = '23514'
6}
7
8export default PostgresErrorCode;We can now use the code to check if the constraint was violated.
1import { BadRequestException, Injectable } from '@nestjs/common';
2import DatabaseService from '../database/database.service';
3import CommentModel from './comment.model';
4import CommentDto from './comment.dto';
5import { isDatabaseError } from '../types/databaseError';
6import PostgresErrorCode from '../database/postgresErrorCode.enum';
7
8@Injectable()
9class CommentsRepository {
10 constructor(private readonly databaseService: DatabaseService) {}
11
12 async create(commentData: CommentDto, authorId: number) {
13 try {
14 const databaseResponse = await this.databaseService.runQuery(
15 `
16 INSERT INTO comments (
17 content,
18 post_id,
19 author_id
20 ) VALUES (
21 $1,
22 $2,
23 $3
24 ) RETURNING *
25 `,
26 [commentData.content, commentData.postId, authorId],
27 );
28 return new CommentModel(databaseResponse.rows[0]);
29 } catch (error) {
30 if (
31 isDatabaseError(error) &&
32 error.code === PostgresErrorCode.CheckViolation
33 ) {
34 throw new BadRequestException(
35 'The length of the content needs to be greater than 0',
36 );
37 }
38 throw error;
39 }
40 }
41
42 // ...
43}
44
45export 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.