Mysql – Nest.js Sequelize transaction commitment issue

I am facing quite interesting problem that I cannot solve. Here is the issue. I have my Nest.js project and I am using Sequelize in order to communicate with the database. At some point I decided that I would like to implement functionality that would handle trasnactions globally. So, here is how it work:

1. Controller receives request.
2. Transaction starts.
3. Function in the service proceeds the request.
4. Transactions is commited.
5. User gets the response.

Here is how I implemented this. I wrote this interceptor that is applied globally:

import {
  CallHandler,
  ExecutionContext,
  HttpException,
  HttpStatus,
  Injectable,
  NestInterceptor
} from '@nestjs/common';
import { Sequelize } from 'sequelize-typescript';
import { catchError, Observable, tap } from 'rxjs';
import { Transaction } from 'sequelize';

@Injectable()
export class TransactionInterceptor implements NestInterceptor {
  constructor(private readonly sequelizeInstance: Sequelize) {}

  async intercept(
    context: ExecutionContext,
    next: CallHandler
  ): Promise<Observable<any>> {
    const httpContext = context.switchToHttp();
    const req = httpContext.getRequest();

    const transaction: Transaction = await this.sequelizeInstance.transaction();
    req.transaction = transaction;

    return next.handle().pipe(
      tap(async () => {
        await transaction.commit();
      }),
      catchError(async (err: HttpException) => {
        await transaction.rollback();

        const errorMessage = err.message || 'internal-server-error';
        const errorStatus = HttpStatus.INTERNAL_SERVER_ERROR;

        throw new HttpException(errorMessage, errorStatus);
      })
    );
  }
}

Then, I wrote this decorator, in order to get access to this transaction:

import { createParamDecorator, ExecutionContext } from '@nestjs/common';

export const TransactionParam = createParamDecorator(
  (data: string, ctx: ExecutionContext) => {
    const request = ctx.switchToHttp().getRequest();
    return request.transaction;
  }
);

So, this way, I can access the transaction from the controller and pass it to the service:

@Controller('company')
export class CompanyController {
  constructor(private readonly companyService: CompanyService) {}

  @UsePipes(ValidationPipe)
  @Post('create-company')
  createCompany(
    @Body() payload: CreateCompanyDto,
    @TransactionParam() trx: Transaction
  ) {
    return this.companyService.createCompany({ payload, trx });
  }
}

This is where the problem starts. First of all, let me introduce the database I have from this createCompany function:

Then, when I trigger this function, I get the error back this this payload: insert or update on table "company_users" violates foreign key constraint "company_users_user_id_fkey".

Generally speaking, if you take a look at the createCompany function you will see that the sequence looks next:

1. Create User
2. Create Company
3. Create Comapny User

The thing is that everything crashes after step 2. Basically, the user is created, the company (which needs user_id) is created, but the company user (which needs both company_id and user_id) causes the error.

I checked database logs and here is what I found:

api        | Executing (9277a385-4170-4f91-9664-3ee9d3501fb3): INSERT INTO "users_settings" ("id","email_changed","user_id","created_at","updated_at") VALUES ($1,$2,$3,$4,$5) RETURNING "id","phone","phone_code","code_sent_at","two_fa_token","email_changed","password_changed","recovery_keys_fingerprint","user_id","created_at","updated_at";
api        | Executing (9277a385-4170-4f91-9664-3ee9d3501fb3): INSERT INTO "companies" ("id","company_name","company_location","company_website","company_owner_id","is_confirmed","created_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8) RETURNING "id","company_name","company_location","company_website","company_owner_id","is_confirmed","created_at","updated_at";
api        | Executing (default): INSERT INTO "company_users" ("id","user_id","company_id","invitation_confirmed","invitation_sent_at","created_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING "id","user_id","company_id","invitation_confirmed","invitation_sent_at","created_at","updated_at";
db         | 2023-08-21 11:39:50.434 UTC [99] ERROR:  insert or update on table "company_users" violates foreign key constraint "company_users_user_id_fkey"
db         | 2023-08-21 11:39:50.434 UTC [99] DETAIL:  Key (user_id)=(ad713365-a690-49c1-891c-158f39514ecd) is not present in table "users".

As you can see, everything happens as I was describing. Created record in the users_settings basically means that the user was sucessfully created, then, using user_id, company is created, but when it comes to creating the Company User, here is where it crashes. Below I am going to present the piece of createCompany function where it happens:

if (ownerExistingAccount) {
  to = ownerExistingAccount.email;
  userId = ownerExistingAccount.id;
} else {
  const createdOwnerAccount = await this.usersService.createUser({
    payload: { email: companyOwnerEmail },
    role: Roles.PRIMARY_ADMIN,
    trx
  });
  to = companyOwnerEmail;
  userId = createdOwnerAccount.id;
}

const companyCreationPayload = {
  companyName,
  companyLocation,
  companyWebsite,
  companyOwnerId: userId,
  trx
};

const { id: companyId } = await this.createCompanyAccount({
  ...companyCreationPayload
});

// After this line, everything crashes

await this.companyUsersService.createCompanyUser({
  userId,
  companyId
});

To be honest, I am not quite sure why it happens, but my guess is that it is because wrong database foreign keys. I think so because, as you can see from ERD, in order to create comapny, I need to have a user. When user is created, the company is created successfully also, but it doesn’t work for comapny user.

I had a guess that it could be something with transactions, as you can see from the log error message, it says that such user doesn’t exist in the database, which is wrong, because otherwise, company wouldn’t be created.

So, what is the issue and how can I fix it?

Thanks everyone in advance for help!