DEV Community

Callgent
Callgent

Posted on • Originally published at docs.botlet.io on

Nestjs + Prisma, transaction propagation & test rollback & multi-tenancy

#technologystack #prisma #nestjs #tenancy

Image description

Prisma interactive transaction

Prisma is great. Yet it's support for interactive transaction is currently limited (Up to v5+),

// interactive transaction
prisma.$transaction([array_of_operations,]);

prisma.$transaction(async (tx) => {
    // dependent operations
});
Enter fullscreen mode Exit fullscreen mode

Container managed transactions

As we integrate Prisma to NestJs, we want cross Services transaction propagation, container managed tx is even better. After some investigation, we come to @nestjs-cls/transactional,

@Injectable()
export class MyService {
  constructor(
    private readonly txHost: TransactionHost<TransactionalAdapterPrisma>,
    private readonly anotherService: AnotherService,
  ) {}

@Transactional()
  async myMethod(){
    const prisma = this.txHost.tx as PrismaClient;

    // db operations1
    await this.anotherService.anotherMethod(); // db operations2
    // db operations3
  }
}
Enter fullscreen mode Exit fullscreen mode

Just decorate your Service method with @Transactional(), it will automatically propagate the transaction across service calls. Under the hood, it uses cls (continuation-local storage) to manage the transaction context.

Rollback is triggered by just throwing an exception.

Setup the module 

    ClsModule.forRoot({
      plugins: [
        new ClsPluginTransactional({
          imports: [PrismaModule], // comment this line, if you have existing PrismaModule
          middleware: { mount: true }, // mount plugin as nestjs middleware
          adapter: new TransactionalAdapterPrisma({ // Prisma adaptor
            // the original PrismaService is wrapped into the adaptor
            prismaInjectionToken: PrismaService,
          }),
        }),
      ],
    }),
Enter fullscreen mode Exit fullscreen mode

More complicated use cases, please read it's documentation.

Transactions in e2e test cases

After each test case, we need to rollback the transaction, preventing side-effects. Thankfully again, it comes to @chax-at/transactional-prisma-testing,

// the PrismaService proxy
let prismaService;

// This function must be called before every test
async function beforeEachFn(): Promise<void> {
  if(prismaTestingHelper == null) {
    prismaTestingHelper = new PrismaTestingHelper(app.get(PrismaService));
    prismaService = prismaTestingHelper.getProxyClient();
  }
  await prismaTestingHelper.startNewTransaction();
}

// This function must be called after every test
function afterEachFn(): void {
  prismaTestingHelper?.rollbackCurrentTransaction();
}
Enter fullscreen mode Exit fullscreen mode

Integrated with @nestjs-cls/transactional

We need this orderly wrapping,

@nestjs-cls/transactional
  └─@chax-at/transactional-prisma-testing
      └─original-PrismaService
Enter fullscreen mode Exit fullscreen mode

So we override PrismaService with testing proxy in beforeAllFn,

import { Test } from '@nestjs/testing';

let prismaTestingHelper, prismaServiceForTest;

const beforeAllFn = () => {
  const moduleFixtureForTest = await Test.createTestingModule({ imports: [AppModule] })
      .overrideProvider(PrismaService)
      .useFactory({ factory: (originalPrisma: PrismaService) => {
          if (prismaTestingHelper == null) {
            prismaTestingHelper = new PrismaTestingHelper(originalPrisma);
            prismaServiceForTest = prismaTestingHelper.getProxyClient();
          }
          return prismaServiceForTest;
        },
        inject: [PrismaService],
      })
      .compile();

  const app: NestFastifyApplication =
    await moduleFixtureForTest.createNestApplication(new FastifyAdapter(), {
      abortOnError: false,
      bufferLogs: false,
    });

  // ... ...
}
Enter fullscreen mode Exit fullscreen mode

Don't forget to call these method around each test,

describe('AppController (e2e)', () => {
  beforeAll(beforeAllFn);
  afterAll(afterAllFn);
  beforeEach(beforeEachFn);
  afterEach(afterEachFn);

  it('/api (GET)', () => {
    // db operations are rollbacked after each test automatically.
  });
Enter fullscreen mode Exit fullscreen mode

🎉 🎉 🎉

Transparent multi-tenancy based on postgreSQL

We have a multi-tenant application, Botlet.IO, tenants data are isolated based on the tenant_id column in db tables.

By design, we want tenant isolation transparently implemented, so we don't need to add tenant_id to every query.

  // by default, we want the below query to be tenant isolated,
  // only users in context tenant are returned.
  prisma.$queryRaw`SELECT * FROM users`
Enter fullscreen mode Exit fullscreen mode

We utilize the postgreSQL row level security(RLS), following this article to achieve transparent tenant isolation.

:::note
RLS is only applicable in postgreSQL
:::

How to implement

  1. in schema.prisma file, set db column tenant_id default value to context variable tenancy.tenantId,
   tenant_id Int  @default(dbgenerated("(current_setting('tenancy.tenantId'))::int"))
Enter fullscreen mode Exit fullscreen mode

  1. enable postgres row level security(RLS), so that we can filter data by tenant_id automatically:
    write SQL in prisma/migrations/01_row_level_security/migration.sql,
    please refer this real example

  2. on each request, preset tenant_id into cls context, refer to auth-logined.listener.ts,

   cls.set('TENANT_ID', curentUser.tenantId);
Enter fullscreen mode Exit fullscreen mode
  1. extend PrismaClient to set tenant_id before any query, refer to prisma-tenancy.provider.ts,
   SELECT set_config('tenancy.tenantId', cls.get('TENANT_ID') ...
Enter fullscreen mode Exit fullscreen mode
  1. if you want to bypass rls, for example, by admin, or looking up the logon user to determine their tenant ID:
   CREATE POLICY bypass_rls_policy ON "User" USING (current_setting('tenancy.bypass_rls', TRUE)::text = 'on');
Enter fullscreen mode Exit fullscreen mode

then when you want to bypass rls, you must set tenancy.bypass_rls to on before running the query, refer to prisma-tenancy.service.ts,

   await prisma.$executeRaw`SELECT set_config('tenancy.bypass_rls', 'on', TRUE)`;
Enter fullscreen mode Exit fullscreen mode

how it works

  1. tenantId is set into cls context on each request from current user.
  2. PrismaClient is extended on $allOperations to set tenantId into db variable tenancy.tenantId before any query.
  3. postgreSQL RLS is enabled, so that all queries will be filtered by tenancy.tenantId automatically.
  4. on db insert operation, tenancy.tenantId is set into tenant_id column as default value.

Integrate them all

As we extended PrismaClient to enable transparent multi-tenancy, now the wrapping order is changed to,

@nestjs-cls/transactional
  └─@chax-at/transactional-prisma-testing
      └─**tenancy-extended-prisma**
        └─original-PrismaService
Enter fullscreen mode Exit fullscreen mode

  1. extend PrismaClient as name TENANTED_PRISMA_SERVICE

    @Global()
@Module({
  imports: [PrismaModule], // original PrismaModule
  providers: [ {
    provide: TENANTED_PRISMA_SERVICE,
    // ...
  } ],
  exports: [TENANTED_PRISMA_SERVICE], // extended PrismaClient
})
export class PrismaTenancyOnPgModule {}

  2. replace PrismaService with TENANTED_PRISMA_SERVICE in prismaInjectionToken,

  3. replace PrismaService with TENANTED_PRISMA_SERVICE in beforeAllFn,

🎉 🎉 🎉

References

  1. @nestjs-cls/transactional
  2. @chax-at/transactional-prisma-testing
  3. prisma-client-extensions: RLS
  4. Callgent - multi-tenant application

Top comments (0)

Read next

dbhatasana profile image

The Role of Change Management in Digital Adoption: A Pathway to Successful SQL Implementation

Divyesh Bhatasana -

alexandrecalaca profile image

How to Set Up SSH Keys for GitHub on PopOs or Ubuntu: A Beginner's Guide

Alexandre Calaça -

virajlakshitha profile image

Orchestrating Complex Workflows with AWS Step Functions and NestJS: A Deep Dive

Viraj Lakshitha Bandara -

developpeurtaf profile image

Utilisation de Postman pour les Tests d'API : Un Guide Complet

Fabrice -