How to connect multiple databases with NestJs and Prisma

March 13, 20226 min read

nestjsprismadatabasebackend

How to connect multiple databases with NestJs and Prisma

How to connect multiple databases with prisma and nestjs using multiple schema by generating multiple prisma clients

Why do you need multiple database connections?

You don’t typically need to use multiple databases. If you’re not sure why you probably don’t need it. But you could be in a situation where you have an old database that’s maintained by a different API and you’re using a separate database for your new API and you need to use both databases, you can set up Prisma to do so.

TLDR: Talk is cheap, show me the code

https://github.com/sagarPakhrin/prisma-multidatabase-demo

Let’s get started

Create MySQL container

For the database, we will use MySQL image with docker-compose. Create a docker-compose.yml

yml
version: '3.8'
services:
  mysql:
    image: mysql:8
    ports:
      - '3306:3306'
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    volumes:
      - ./tmp:/var/lib/mysql

Add variables to .env

bash
MYSQL_ROOT_PASSWORD=prisma
MYSQL_DATABASE=prisma
MYSQL_USER=prisma
MYSQL_PASSWORD=prisma
DATABASE_URL=mysql://prisma:prisma@localhost:3306/prisma

Prisma needs to have a shadow database for it to be able to run migrations safely so let’s create a shadow database and a second database to demonstrate multiple database connections with Prisma.

Create a db/init.sql

sql
CREATE DATABASE IF NOT EXISTS `prisma-shadow`;
GRANT ALL ON `prisma-shadow`.* TO 'prisma'@'%';
CREATE DATABASE IF NOT EXISTS `prisma2`;
GRANT ALL ON `prisma2`.* TO 'prisma'@'%';

This will create two databases, and grant all privileges for our user prisma

Let’s update docker-compose.yml to set db/init.sql as entry point for our MySQL container. In your compose file, replace the previous volumes with

yaml
volumes:
      - ./tmp:/var/lib/mysql
      - ./db:/docker-entrypoint-initdb.d

Run the container with docker-compose up -d and start the development server with npm run start:dev

Set the first database connection

prisma
# prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}
datasource db {
  provider          = "mysql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}
model User {
  id   Int    @id @default(autoincrement())
  name String
}

To keep things simple we will only create a User model with id and name. Now let’s add a script to run the migration in our package.json

json
"migrate": "npx prisma migrate dev"

And run the command npm run migrate This will generate a Prisma client inside node_modules/.prisma/client , generate migration files and create appropriate tables in our prisma database

Install and generate Prisma Client

bash
npm install @prisma/client

Create prisma.service.ts in src folder

js
import { INestApplication, Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';
 
@Injectable()
export class PrismaService extends PrismaClient
  implements OnModuleInit {
 
  async onModuleInit() {
    await this.$connect();
  }
 
  async enableShutdownHooks(app: INestApplication) {
    this.$on('beforeExit', async () => {
      await app.close();
    });    
  }
}

Update the API to get the list of users

js
//app.service.ts
import { Injectable } from '@nestjs/common';
import { User } from '@prisma/client';
import { PrismaService } from './prisma.service';
 
@Injectable()
export class AppService {
  constructor(private readonly prisma: PrismaService) {}
 
  async getUsers(): Promise<User[]> {
    return this.prisma.user.findMany();
  }
}

js
//app.controller.ts
import { Controller, Get } from '@nestjs/common';
import { User } from '@prisma/client';
import { AppService } from './app.service';
 
@Controller()
export class AppController {
  constructor(private readonly appService: AppService) {}
 
  @Get('/users')
  getUsers(): Promise<User[]> {
    return this.appService.getUsers();
  }
}

js
//app.module.ts
import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { PrismaService } from './prisma.service';
 
@Module({
  imports: [],
  controllers: [AppController],
  providers: [AppService, PrismaService],
})
export class AppModule {}

You can create a route to add users and once you do that, you will get the list of the users.

Set the second database connection

Create a schema.prisma inside a different folder

For the second connection, the output directory for the client needs to point to a different directory. We will place it inside node_modules/@internal/prisma/client

Generate migrations

Let’s add a new command in package.json to generate migrations for the second schema file

json
"schema2:migrate": "npx prisma migrate dev --schema ./prisma2/schema.prisma"

Add BLOG_DATABASE_URL to .env file

bash
BLOG_DATABASE_URL=mysql://prisma:prisma@localhost:3306/prisma2

Now when you run

bash
npm run schema2:migrate

a new folder @internal/prisma/client will be generated.

If you have an old database with data in it, you don’t need to generate migrations files. Instead of running migrate dev, you can generate Prisma client by running

bash
npx prisma generate --schema ./prisma2/schema.prisma

Create Prisma service

We need to create a separate prisma.service that will connect with the second database. This service will be the same but the PrismaClient will be imported from the newly generated @internal/prisma/client instead of the default @prisma/client

js
// prisma2.service.ts
 
import { PrismaClient } from '@internal/prisma/client';
import { INestApplication, Injectable, OnModuleInit } from '@nestjs/common';
 
@Injectable()
export class PrismaService extends PrismaClient implements OnModuleInit {
  async onModuleInit() {
    await this.$connect();
  }
 
  async enableShutdownHooks(app: INestApplication) {
    this.$on('beforeExit', async () => {
      await app.close();
    });
  }
}

Now let’s update the API to list the blogs using the second database connection

js
// app.service.ts
import { Blog } from '@internal/prisma/client';
import { Injectable } from '@nestjs/common';
import { User } from '@prisma/client';
import { PrismaService } from './prisma.service';
import { PrismaService as PrismaService2 } from './prisma2.service';
 
@Injectable()
export class AppService {
  constructor(
    private readonly prisma: PrismaService,
    private readonly prisma2: PrismaService2,
  ) {}
 
  async getUsers(): Promise<User[]> {
    return this.prisma.user.findMany();
  }
 
  async getBlogs(): Promise<Blog[]> {
    return this.prisma2.blog.findMany();
  }
}

js
// app.controller.ts
import { Blog } from '@internal/prisma/client';
import { Controller, Get } from '@nestjs/common';
import { User } from '@prisma/client';
import { AppService } from './app.service';
 
@Controller()
export class AppController {
  constructor(private readonly appService: AppService) {}
 
  @Get('/users')
  getUsers(): Promise<User[]> {
    return this.appService.getUsers();
  }
 
  @Get('/blogs')
  getBlogs(): Promise<Blog[]> {
    return this.appService.getBlogs();
  }
}

js
// app.module.ts
import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { PrismaService } from './prisma.service';
import { PrismaService as PrismaService2 } from './prisma2.service';
 
@Module({
  imports: [],
  controllers: [AppController],
  providers: [AppService, PrismaService, PrismaService2],
})
export class AppModule {}

And there you have it. You now have connections to both databases with Prisma.

Tip:
If you’re running CI pipelines, you might come across an issue where @internal/prisma/client is not found. To resolve this, you can add a postinstall script in package.json which will run automatically after installing packages and generate the prisma clients for us.

json
    "postinstall": "npm run prisma:generate-clients",
    "prisma:generate-clients": "npx prisma generate --schema ./prisma2/schema.prisma",

Conclusion

You can use multiple databases with Prisma by creating different schema files and generating separate Prisma clients for each database. If there’s a better way to do this, please leave some comments.