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.

Comments

[Martin Häusler]

Doing data validation as a last line of defense on the database level is always a good idea. Just keep in mind that the emphasis is on LAST line of defense.

Depending on your setup (O/R mapper etc) the SQL commit may occur at a point in time where you can no longer produce meaningful error messages for your user. While I fully agree that SQL side data validation with constraints is great, it should not be the only place where you validate the data. For instance, it cannot - and should not - replace data validation right at the REST controller.

[Emtiaj Hasan]

you can no longer produce meaningful error messages for your user

Sorry, I couldn't understand the above statement. Can you please explain a bit more?

[Martin Häusler]

As I said before, it depends a lot on the O/R mapper and application framework you use. If your framework supports transactional methods (e.g. Spring Boot's @Transactional) , multiple method calls (within a single REST call) will share a common transaction. This transaction will not be committed until all of these methods have completed. For more complex applications, this "transactional boundary" can be quite big; you may have a lot of changes in the transaction. And then, way way later (way outside the scope of the method which changed the data) you get a data validation error when the transaction is finally committed. You can't catch that in a try-catch because the commit occurs outside of your method. Of course, you can wrap that into a JSON and return it to the client, but it will probably contain only some SQL State code and will be meaningless to an end user. I've spent a lot of time tracking down SQL constraint validation errors through my code so that I can create proper error messages for the user, at the right location in the code.

Even if it means duplicating the constraints: validate early, validate often. Both in SQL as well as in your application.

[Emtiaj Hasan]

Yeah, as you said, it depends!

Thanks, @martinhaeusler.

[Arpan Saha]

How to pinpoint which error it is? For example, what if only the email is a duplicate and there is no other problem? In that case we can't send both exceptions, right?

[Emtiaj Hasan]

That is a good question!

Well, we won't send both exceptions.

Let's figure out the below code.

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];
}

It is equivalent to the below code.

private handleRepositoryException(repositoryException: RepositoryException): void {
    if (repositoryException.constraint === 'UQ_User_email') {
        throw new ConflictException(`Email is associated with another user`);
    }
    if (repositoryException.constraint === 'FK_User_roleId_Role_id') {
        throw new UnprocessableEntityException(`Invalid role ID`);
    }
}

It indicates, we are throwing the expected exception based on the DB given error.

If you want to play, please change the automated tests (written using Jest), and check the behavior.

I hope I make it understand. Thank you!

[Arpan Saha]

I see, thanks.