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 { return knex.raw(` CREATE TABLE posts ( id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, title text NOT NULL, post_content text NOT NULL ) `); } 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 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. If you want to read more about type guards, check out Structural type system and polymorphism in TypeScript. Type guards with predicates 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 { 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 { 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. 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. 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 co