Como criar diferentes bancos de dados Postgres utilizando o Prisma 7

create-database-with-diffent-schema-with-prisma7.ts

// Adiciona no seu `src/env`

export const envSchema = z.object({
  DATABASE_URL: z.url(),
  DATABASE_SCHEMA: z.string(),
  PORT: z.coerce.number().optional().default(3333),
  JWT_PRIVATE_KEY: z.string(),
  JWT_PUBLIC_KEY: z.string(),
});


 // No `.env` passe seu schema principal (Ex: public) 
// DATABASE_SCHEMA="public"


// No `test/setup-e2e.ts`

import 'dotenv/config';

import { execSync } from 'node:child_process';
import { randomUUID } from 'node:crypto';

import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from 'generated/prisma/client';

let prisma: PrismaClient;
const schemaId = randomUUID();

function generateUniqueDatabaseURL(schemaId: string) {
  if (!process.env.DATABASE_URL) {
    throw new Error('DATABASE_URL is not set');
  }
  const url = new URL(process.env.DATABASE_URL);
  url.searchParams.set('schema', schemaId);
  return url.toString();
}

beforeAll(async () => {
  const baseDatabaseUrl = process.env.DATABASE_URL;
  if (!baseDatabaseUrl) throw new Error('DATABASE_URL is not set');

  // Cria o schema isolado antes de rodar as migrations
  const adminAdapter = new PrismaPg({ connectionString: baseDatabaseUrl });
  const adminPrisma = new PrismaClient({ adapter: adminAdapter });
  await adminPrisma.$connect();
  await adminPrisma.$executeRawUnsafe(
    `CREATE SCHEMA IF NOT EXISTS "${schemaId}"`,
  );
  await adminPrisma.$disconnect();

  const databaseURL = generateUniqueDatabaseURL(schemaId);
  process.env.DATABASE_URL = databaseURL;
  process.env.DATABASE_SCHEMA = schemaId;

  const adapter = new PrismaPg(
    { connectionString: databaseURL },
    { schema: schemaId },
  );
  prisma = new PrismaClient({ adapter });

  execSync('npx prisma migrate deploy');
});

afterAll(async () => {
  if (prisma) {
    await prisma.$executeRawUnsafe(
      `DROP SCHEMA IF EXISTS "${schemaId}" CASCADE`,
    );
    console.log('Schema dropped');
    await prisma.$disconnect();
  }
});


// No `src/prisma/prisma.service.ts`

import {
  Inject,
  Injectable,
  OnModuleDestroy,
  OnModuleInit,
} from '@nestjs/common';
import { ConfigService } from '@nestjs/config';
import { PrismaPg } from '@prisma/adapter-pg';
import { PrismaClient } from 'generated/prisma/client';

import type { Env } from '../env';

@Injectable()
export class PrismaService
  extends PrismaClient
  implements OnModuleInit, OnModuleDestroy
{
  constructor(
    @Inject(ConfigService)
    config: ConfigService<Env, true>,
  ) {
    const databaseUrl = config.get('DATABASE_URL', { infer: true });
    const databaseSchema = config.get('DATABASE_SCHEMA', { infer: true });

    super({
      adapter: new PrismaPg(
        { connectionString: databaseUrl },
        { schema: databaseSchema },
      ),
      log: ['warn', 'error'],
    });
  }
  async onModuleInit() {
    // Conecta com o banco quando o módulo iniciar
    return this.$connect();
  }

  async onModuleDestroy() {
    // Desconecta do banco quando o módulo for destruído
    return this.$disconnect();
  }
}


No `src/controllers/create-account.controller.e2e-spec.ts`

import { NestExpressApplication } from '@nestjs/platform-express';
import { Test } from '@nestjs/testing';
import request from 'supertest';

// import { AppModule } from '@/app.module';
import { PrismaService } from '@/prisma/prisma.service';

describe('Create account (E2E)', () => {
  let app: NestExpressApplication;
  let prisma: PrismaService;

  beforeAll(async () => {
    // Import dinâmico para que o Prisma use DATABASE_URL e DATABASE_SCHEMA
    // já definidos pelo setup-e2e.ts (schema isolado por UUID)

    const { AppModule } = await import('@/app.module');
    const module = await Test.createTestingModule({
      imports: [AppModule],
    }).compile();

    app = module.createNestApplication<NestExpressApplication>();
    prisma = module.get(PrismaService);
    await app.init();
  });

  test('[POST] /accounts', async () => {
    const response = await request(app.getHttpServer()).post('/accounts').send({
      name: 'John Doe',
      email: '[email protected]',
      password: '123456',
    });

    expect(response.statusCode).toBe(201);

    const userOnDatabase = await prisma.user.findUnique({
      where: {
        email: '[email protected]',
      },
    });
    expect(userOnDatabase).toBeTruthy();
  });
});