Handle database validation (exists, unique constraint, etc) [closed]

Question

Let's assume you are writing a REST Api with a common structure like Controller > Service > Repository > Database. In the database there's a table with a column with a unique constraint:

   // Examples with typeorm/typescript 

   // user.entity.ts
   @Entity()
   export class User {
      @PrimaryGeneratedColumn()
      id: number;

      @Unique()
      @Column()
      email: string;
      
      // Other properties
   }

In the updateUser method you have to check if the user exists and if the user email is not used by another one.

Which one of the following approaches would it be better?

A) Interrogate the database to check if the user with the given id exists and do it again to make sure there's not another user with the same email:

   // users.service.ts
   @Entity()
   export class UsersService {
      async updateUser(user: UserDTO): Promise<UserDTO> {
         const userById = await this.repository.findOne(user.id);
         if (!userById) {
            throw new Error('User does not exist');
         }

         const userWithSameEmail = await this.repository.findOne({
            where: { id: Not(user.id), email: user.email }
         });
         if (userWithSameEmail) {
            throw new Error('Email already in use');
         }

         // Handle errors with some interceptor
         // Rest of the method
      }
   }

B) Database calls could lead to performance issues. The orm will anyway throw an error in case of invalid input:

   @Entity()
   export class UsersService {
      async updateUser(user: UserDTO): Promise<UserDTO> {
         try {
            const updatedUserEntity = await this.repository.update(user);

            // Rest of the method
         }
         catch (e) {
            // Handle error
         }
      }
   }

Quick bonus question

Should the service layer be aware about entities and handle operations like receive the updatedUserEntity from the repository, map it to the DTO and return it to the level above?

Answer 1

With approach A) you may run in a concurrency problem.
For example process A and process B want to write a new entry. Both load the data from the database and check if their new entry would be valid. Both decide "YES, valid".
Now process A writes the new entry, and then process B tries to write. But A already changed the database, therefor the validation in B was executed on obsolete data.

Be aware that node will execute things concurrently, because as soon as a "process" (task) has some waiting time, another process (task) will be executed until that one is finished or also waiting for something.

With version B) this problem gets (for most db´s) fixed. On the other side, now your business logic is scattered over multiple systems, which makes maintenance harder.

--> As always, there is no YES or NO but a "depends on your context" :-)

About the bonus question: If your application uses the same entities as the DB, then your whole application (from top to bottom) has a dependency to your database schema.
As a result, a change in your DB then has to be followed by a lot of changes in your application. If you do the mapping in the service layer (thats where i would expect the business logic), then all changes run at least until there.

Therefor i would "map" the data in the data access layer into the entities i use in my application. Yep, in many cases the entites will look similar, but it decouples my application from my database. And my business logic will not know anything about the format that is used when data gets stored.

By the way, some people use their entities over the whole application, some create new entities for each layer and the data get maped a lot. Both approaches have their reason, personaly i work with a entity set for the whole application.

Just a few thoughts. Pick those that make sense to you and skip the rest. :-)

warm regards