Connecting multiple databases using Prisma in NestJS

#node #typescript #prisma #tutorial

Prisma ORM is a Node.js and TypeScript ORM with an intuitive data model, automated migrations, type-safety, and auto-completion. I’m really fond of this ORM, though it still has its drawbacks.

Recently, I was challenged to set up database connections for PostgreSQL and MongoDB using Prisma ORM in a NestJS app. Though the official docs provide example for this case, I still feel like sharing my solution with you, cause I think there some improvments that can be done)

Folders structure
Initializing Prisma Clients in Nest
Helper scripts for migrations

Folders structure

Honestly, I don't see much value in creating separate folders for each database by uniting Prisma and renaming them for a specific database, as shown in the official example. Grouping specific database folders inside the original prisma folder seems more reasonable to me. So my preferred structure looks this way:

   📂 prisma
     |
     | --- 📂 mongodb 
     |        | --- schema.prisma
     |
     | --- 📂 postgres
     |        | --- schema.prisma
     |        | --- 📂 migrations

The custom output path is an important part of this configuration, as Prisma generates client types for certain databases, and these generated clients are needed for establishing connections.

According to the docs, generated types can be stored in ./src. I haven't tried this approach in plain Node with Express, but I recommend avoiding it in NestJS, because for some reason, it makes the generated files unloadable by Node. That is why for myself I store generated staff in node_modules or in the root of repo.

generator client {
  provider = "prisma-client-js"
  output   = "../../node_modules/@prisma/mongodb-client"
}

datasource db {
  provider = "mongodb"
  url      = env("MONGO_DATABASE_URL")
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearchPostgres"]
  output          = "../../node_modules/@prisma/postgres-client"
}

datasource db {
  provider = "postgresql"
  url      = env("POSTGRES_DATABASE_URL")
}

Initializing Prisma Clients in Nest

There’s not much to say in this section, except that PrismaClient should be imported from the generated clients located in node_modules. So, I’ll just leave this code example:

import { PrismaClient } from '@prisma/mongodb-client';
import { Injectable, OnModuleInit } from '@nestjs/common';

@Injectable()
export class MongodbDatabaseService extends PrismaClient implements OnModuleInit {
  async onModuleInit() {
    await this.$connect();
  }
}

import { PrismaClient } from '@prisma/postgres-client';
import { Injectable, OnModuleInit } from '@nestjs/common';

@Injectable()
export class PostgresDatabaseService extends PrismaClient implements OnModuleInit {
  async onModuleInit() {
    await this.$connect();
  }
}

Helper scripts for migrations

I'm not a fan of this example for helper scripts. Though it's a rare occasion, just imagine how it would look if you had four database connections... My point—and solution—is that all these manipulations are better handled in a separate script file:

import 'dotenv/config';
import { readdirSync } from 'fs';
import { execSync } from 'child_process';

const PRISMA_PATH = './prisma';
const COMMANDS_EXTENSIONS = [{ folderName: 'mongodb', commands: ['migrate', 'deploy'] }];

const generateSchemaParam = (folderName: string) => `--schema ./${PRISMA_PATH}/${folderName}/schema.prisma`;

const commands = {
  generate: (folderName) => `npx prisma generate ${generateSchemaParam(folderName)}`,
  migrate: (folderName) => `npx prisma migrate dev ${generateSchemaParam(folderName)}`,
  deploy: (folderName) => `npx prisma migrate deploy ${generateSchemaParam(folderName)}`,
};

const [, , actions] = process.argv;

if (!commands[actions]) {
  console.error('Invalid action. Available actions: generate, migrate, deploy, studio');
  process.exit(1);
}

readdirSync('./prisma').forEach((folderName, index) => {
  const isExeption = COMMANDS_EXTENSIONS.some(
    (exeption) => exeption.folderName === folderName && exeption.commands.includes(actions),
  );

  if (isExeption) return;

  const cmd = commands[actions](folderName, index);
  execSync(cmd, { stdio: 'inherit' });
});

It's also important to mention that different databases have different features—for MongoDB, for example, this means there are no migrate support.

All these commands can be added to package.json and easily executed via ts-node.

  "scripts": {
    "postinstall": "ts-node ./scripts/prisma.ts generate",
    "databases:generate": "ts-node ./scripts/prisma.ts generate",
    "databases:migrate": "ts-node ./scripts/prisma.ts migrate",
    "databases:deploy": "ts-node ./scripts/prisma.ts deploy"
  },