Leverage the power of PostgreSQL for validating data

Having come from the MongoDB world, I had limited familiarity with SQL before I started using it professionally. As a student, I had some rudimentary ideas, but trust me, that was almost nothing to mention!

When I look back at the CRUD operation-related codes I used to write, my inner self laughs at me. I wish I could travel to the past and rewrite those codes.

I entered into the SQL world with PostgreSQL along with TypeORM. As an experienced MongoDB user, my created methods were similar to the NoSQL functionality.

Let’s dive into an example. Let’s give the full specification of what we need to implement.

• We have to create a user. The input payload contains email and role ID.
• We need to make sure the user’s email is unique. For an already allocated email, we need to throw the conflict exception.
• We need to ensure the requester receives an unprocessable entity exception when they try to create a user with an invalid role ID. For that, we need to assume the role creation functionality is already available in the system.

Pretty simple, ha?

By the way, what is ORM (and TypeORM)? The scope of this post will be increased if I introduce those. I recommend checking out What is an ORM by Ihechikara Vincent Abba and TypeORM’s official documentation.

TypeORM provides a simple approach to quickly create the data model, allowing us to create tables and database constraints in just a few minutes.

Long story short, I created a tiny project using the Nest JS framework, designed the data model following the TypeORM approach and built the Role and User tables. The roleId column in the User table is the foreign key of the Role table. In addition, I made the email unique.

Screenshot: ER diagram of the DB along with primary and foreign keys of tables

Everything is set up. Now it is all about creating the method to accept input payload and persist the user into the DB.

My newbie self used to write code like the one below, which used to assume whatever I needed to achieve, I needed to do it by myself.

async saveUserV1(userSaveRequest: UserSaveRequest): Promise<string> {
    await this.validateRole(userSaveRequest.roleId);
    await this.validateEmail(userSaveRequest.email);

    const insertResult = await this.userRepository.insert({
        email: userSaveRequest.email,
        role: {
            id: userSaveRequest.roleId,
        },
    });

    return insertResult.generatedMaps[0].id;
}

private async validateRole(roleId: string): Promise<void> {
   const isRoleExist = await this.roleRepository.exist({ where: { id: roleId } });
   if (!isRoleExist) {
       throw new UnprocessableEntityException
(`Invalid role ID`);
   }
}


private async validateEmail(email: string): Promise<void> {
   const isUserWithEmailExist = await this.userRepository.exist({ where: { email: email } });
   if (isUserWithEmailExist) {
       throw new ConflictException(`Email is associated with another user`);
   }
}

Let’s review the codes. Here is my observation.

In the SQL world, databases like PostgreSQL handles validations automatically. With the unique constraint UQ_User_email on the email field and the referential integrity enforced by the foreign key FK_User_roleId_Role_id, there's no need for additional queries to validate the data. These built-in features make the manual validation redundant and reduce the hit into the database.

Therefore as a pull request reviewer, my current self will mark the PR as the Request Change and suggest delegating the validation responsibility to the database.

Okay, we need to refactor the codes.

I will leverage the database's built-in error-handling capabilities. My goal is to handle the exception so that the requester will get the proper error message.

Here is my changed code.

async saveUserV2(userSaveRequest: UserSaveRequest): Promise<string> {
    try {
        const insertResult = await this.userRepository.insert({
            email: userSaveRequest.email,
            role: {
                id: userSaveRequest.roleId,
            },
        });
        return insertResult.generatedMaps[0].id;
    } catch (error) {
        this.handleRepositoryException(error);
        throw error;
    }
}

private handleRepositoryException(repositoryException: RepositoryException): void {
   const errorMap: Record<string, Error> = {
       UQ_User_email: new ConflictException(`Email is associated with another user`),
       FK_User_roleId_Role_id: new UnprocessableEntityException
(`Invalid role ID`),
   };
   throw errorMap[repositoryException.constraint];
}

Pretty shiny, isn’t it? Unfortunately, I kept following the first approach of manually validating everything for a long time with SQL.

By the way, all codes are available on a GitHub repository. You are welcome to clone it and run it locally.

In conclusion, this shift in mindset has allowed me to embrace the power of SQL and make the most of its capabilities, reducing execution time and simplifying code. If the database can handle tasks for us, why not rely on it entirely?

Thank you for reading it. I hope you enjoyed it. I would like to share my teeny-tiny TypeORM and PostgreSQL experience with everyone; this is my first attempt.

NB: I have taken the cover image from here.