Skip to main content

Prisma

In MBC CQRS Serverless, we use prisma as an ORM. It helps developers be more productive when working with databases.

A common scenario when working with Prisma is needing to make changes to the database, such as creating tables, updating fields in tables, etc. Follow these steps:

  1. Update prisma/schema.prisma file.
  2. For local development, create and apply migrations with command npm run migrate:dev.

Setup​

PrismaService​

The mbc new CLI command generates a PrismaService in src/prisma/prisma.service.ts. It extends PrismaClient and integrates with NestJS's module lifecycle:

// src/prisma/prisma.service.ts (generated by CLI)
import { Injectable, Logger, OnModuleInit, Inject, Optional } from '@nestjs/common';
import { Prisma, PrismaClient } from '@prisma/client';

@Injectable()
export class PrismaService
  extends PrismaClient<Prisma.PrismaClientOptions, 'query' | 'error'>
  implements OnModuleInit
{
  private readonly logger = new Logger(PrismaService.name);

  async onModuleInit() {
    // Lazy connection by default — avoids holding connections between Lambda invocations
    // Set explicitConnect: true in prismaServiceOptions only if you need startup connection
  }
}

Register in AppModule​

Register PrismaModule as a global module in src/main.module.ts so PrismaService is available in all modules without re-importing:

import { Module } from '@nestjs/common';
import { PrismaModule } from './prisma';

@Module({
  imports: [
    PrismaModule.forRoot({
      isGlobal: true,            // Make PrismaService available everywhere without re-importing
      prismaServiceOptions: {
        explicitConnect: false,  // Recommended for Lambda: lazy connection per invocation
        prismaOptions: {
          log: process.env.NODE_ENV !== 'local' ? ['error'] : ['info', 'error', 'warn', 'query'],
        },
      },
    }),
    // ... other modules
  ],
})
export class MainModule {}
Lambda Connection Management

Set explicitConnect: false (the default) for Lambda functions. Prisma establishes the RDS connection lazily on the first query and does not hold it between invocations. Use RDS Proxy in production to pool connections across concurrent Lambdas and prevent connection exhaustion.

Migration Scripts​

CommandWhen to useWhat it does
npm run migrate:devLocal development onlyCreates a new Prisma migration file and applies it to the local RDS database. Use this when you change schema.prisma.
npm run migrateLocal setup and CIApplies existing Prisma migrations to RDS (without creating new ones), then runs DynamoDB table migration. Use this after cloning or pulling.
npm run migrate:ddbDynamoDB onlyCreates or updates DynamoDB tables defined in prisma/dynamodbs/*.json without touching the RDS schema.
warning

For local development, please make sure to set the correct DATABASE_URL environment variable.

# Example
DATABASE_URL="mysql://root:RootCqrs@localhost:3306/cqrs"

You could view prisma-client documentation for more information

Design table convention​

When creating an RDS table that maps to a DynamoDB table, ensure you add the necessary fields and indexes to the RDS table accordingly. The cpk/csk fields store the original command table keys (used to link back to the DynamoDB command record). Include them when you need full traceability from RDS to DynamoDB; omit them if you only need the data table keys (pk/sk).

model YourEntity {
  id         String   @id
  cpk        String // Command PK
  csk        String // Command SK
  pk         String // Data PK
  sk         String // Data SK
  tenantCode String   @map("tenant_code") // Tenant code
  seq        Int      @default(0) // Sort order, uses sequence feature
  code       String // Record code
  name       String // Record name
  version    Int // Version
  isDeleted  Boolean  @default(false) @map("is_deleted") // Deleted flag
  createdBy  String   @default("") @map("created_by") // Created by
  createdIp  String   @default("") @map("created_ip") // Created IP, supports IPv6
  createdAt  DateTime @default(now()) @map("created_at") @db.Timestamp(0) // Created at
  updatedBy  String   @default("") @map("updated_by") // Updated by
  updatedIp  String   @default("") @map("updated_ip") // Updated IP, supports IPv6
  updatedAt  DateTime @updatedAt @map("updated_at") @db.Timestamp(0) // Updated at

  // domain-specific properties

  // relations

  // index
  @@unique([cpk, csk])
  @@unique([pk, sk])
  @@unique([tenantCode, code])
  @@index([tenantCode, name])
}