Add Account Service and new DB Table

We will create a new account table where the ID of the connected external accounts will be stored. So we will generate a migration for this.

In addition to the account table, indexes are created to perform more optimized searches in the database.

import { MigrationInterface, QueryRunner } from 'typeorm';

export class createTableAccounts1659967614370 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `CREATE TABLE "public"."account" ("id" uuid NOT NULL DEFAULT uuid_generate_v4(), "external_id" character varying NULL, "baas_id" character varying NULL, "integration_id" uuid NOT NULL, "user_id" uuid NOT NULL, "created_at" TIMESTAMP NOT NULL DEFAULT now(), "updated_at" TIMESTAMP NOT NULL DEFAULT now(), "deleted_at" TIMESTAMP , CONSTRAINT "PK_9162znh8ld8tylna9z6odivqt8u" PRIMARY KEY ("id"))`,
    );
    await queryRunner.query(
      `ALTER TABLE "public"."account" ADD CONSTRAINT "FK_5i6mtodoa8fcqzujy1qg53nvxlh" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE CASCADE`,
    );
    await queryRunner.query(
      `ALTER TABLE "public"."account" ADD CONSTRAINT "FK_ugmbyl1vy7py0xhyx6qdx46w8a3" FOREIGN KEY ("integration_id") REFERENCES "public"."integration"("id") ON DELETE CASCADE`,
    );

    await queryRunner.query(
      `CREATE INDEX "IDX_twrqghqf5afgc0u5ejz3pb1oco" ON "public"."account" ("user_id")`,
    );

    await queryRunner.query(
      `CREATE INDEX "IDX_oyv3ypxovks0kioee3tk4nyvzq" ON "public"."account" ("integration_id")`,
    );

    await queryRunner.query(
      `CREATE INDEX "IDX_ueqv91y92rs0stsvoyegrhue14" ON "public"."account" ("external_id")`,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `ALTER TABLE "public"."account" DROP CONSTRAINT "FK_5i6mtodoa8fcqzujy1qg53nvxlh"`,
    );
    await queryRunner.query(
      `ALTER TABLE "public"."account" DROP CONSTRAINT "FK_ugmbyl1vy7py0xhyx6qdx46w8a3"`,
    );
    await queryRunner.query(
      `DROP INDEX IF EXISTS "public"."IDX_twrqghqf5afgc0u5ejz3pb1oco"`,
    );
    await queryRunner.query(
      `DROP INDEX IF EXISTS "public"."IDX_oyv3ypxovks0kioee3tk4nyvzq"`,
    );
    await queryRunner.query(
      `DROP INDEX IF EXISTS "public"."IDX_ueqv91y92rs0stsvoyegrhue14"`,
    );

    await queryRunner.query('DROP TABLE "public"."account"');
  }
}

Inside the feature folder, we will create a folder named account and with the following files:

Account folder.

account.entity.ts

In this file we will add the following configuration where we declare the properties and relationships that the accounts table will have.

import { Entity, Column, ManyToOne } from 'typeorm';
import { BaseModel } from 'src/common/database/base-model';
import { Integration } from 'src/features/integration/integration.entity';
import { User } from 'src/features/user/user.entity';
import { encryptionTransformer } from 'src/common/transformers/typeorm-encrypted.transformer';
@Entity()
export class Account extends BaseModel {
  @Column({
    nullable: true,
    transformer: encryptionTransformer,
  })
  public externalId!: string;

 @Column({
    name: 'service_external_id',
    nullable: true,
    transformer: encryptionTransformer,
  })
  public serviceExternalId!: string;

  @Column({ name: 'user_id' })
  public userId!: string;

  @Column({ name: 'integration_id' })
  public integrationId!: string;

  @ManyToOne(() => Integration, (integration) => integration.accounts, {
    nullable: true,
  })
  public integration?: Integration;

  @ManyToOne(() => User, (user) => user.accounts, { nullable: true })
  public user?: User;
}

account.service.ts

This file will have all the methods needed to enter, get, and delete records from the accounts table.

• addAccount: this method is used to add a new record to the account table.

• getAccount: this method is used to get an account by its external ID and the integration it belongs to.

• getAccountsByIntegrationId: this method is used to get all the accounts based on the ID of an integration.

• getAccountByExternalId: this method is used to get an account using your external ID.

• removeAccountById: this method is used to delete a record from the accounts table based on the database ID.

• removeAllAccountsByIngratationId: this method is used to remove all records from the account table based on the ID of an integration.

• removeAccountByExternalId: this method is used to delete an account based on its external ID.

• exitsAccountByUser: this method will be used to check if the user already has an account based on an integration and external ID of an account. Returns a boolean value.

• verifyAccountByUser: this method is used to verify a user's account. If the account does not exist, an exception occurs with a custom message that is passed as a parameter.

import { Injectable, UnprocessableEntityException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { IsNull, Repository } from 'typeorm';
import { Account } from './account.entity';

@Injectable()
export class AccountService {
  public constructor(
    @InjectRepository(Account)
    private readonly accountRepository: Repository<Account>,
  ) {}

  public async addAccount(
    accountId: string,
    userId: string,
    integrationId: string,
    serviceExternalId?: string,
  ): Promise<void> {
    await this.accountRepository.save({
      externalId: accountId,
      userId: userId,
      integrationId: integrationId,
      serviceExternalId: serviceExternalId,
    });
  }

  public async getAccount(
    accountId: string,
    integrationId: string,
  ): Promise<Account> {
    return await this.accountRepository.findOne({
      where: {
        externalId: accountId,
        integrationId: integrationId,
        deletedAt: IsNull(),
      },
    });
  }

  public async getAccountsByIntegrationId(
    userId: string,
    integrationId: string,
  ): Promise<Account[]> {
    return await this.accountRepository.find({
      where: {
        userId: userId,
        integrationId: integrationId,
      },
    });
  }

  public async getAccountByExternalId(externalId: string): Promise<Account> {
    return await this.accountRepository.findOne({
      withDeleted: true,
      where: {
        externalId: externalId,
      },
    });
  }

  public async removeAccountById(id: string): Promise<void> {
    await this.accountRepository.softDelete(id);
  }

  public async removeAllAccountsByIngratationId(
    integrationId: string,
  ): Promise<void> {
    await this.accountRepository.softDelete({ integrationId });
  }

  public async removeAccountByExternalId(externalId: string): Promise<void> {
    await this.accountRepository.softDelete({ externalId });
  }

  public async exitsAccountByUser(
    userId: string,
    externalId: string,
  ): Promise<boolean> {
    const account = await this.accountRepository.findOne({
      where: { userId, externalId, deletedAt: IsNull() },
    });
    return account != undefined;
  }

  public async verifyAccountByUser(
    userId: string,
    externalId: string,
    errorMessage: string,
  ): Promise<void> {
    const account = await this.accountRepository.findOne({
      where: { userId, externalId, deletedAt: IsNull() },
    });
    if (!account) {
      throw new UnprocessableEntityException(errorMessage);
    }
  }
}

accounts.module.ts

This file will make the connection between the service and the account entity. We will also import the integration module that will be used in the account service.

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { IntegrationsModule } from '../integration/integrations.module';
import { Account } from './account.entity';
import { AccountService } from './account.service';

@Module({
  imports: [TypeOrmModule.forFeature([Account]), IntegrationsModule],
  providers: [AccountService],
  controllers: [],
  exports: [AccountService],
})
export class AccountsModule {}