How To Connect Multiple Database With Node JS and Prisma

Have you ever been in a situation where you have to access two databases on different platforms in an application?

Well, you found the right place to seek for help! When I researched this, I thought it would be very easy because I thought ORM currently supports the multiple database feature for my needs. But when I did research, I found that Prisma can only support multiple schema in its official documentation.

Given that idea, it couldn't fit in to the business case I was facing, not until I found this

This works like magic! I could access both PostgreSQL and MySQL databases in one application simultaneously.

Now, I'll show you how to do it. Follow me.

Step 1: Prerequisite#

What you need:

• Node JS
• Nest JS CLI
• PostgreSQL
• MySQL

Preparing Repository#

We will be doing this tutorial with a Nest JS template. Therefore, if you haven't installed the NestJS CLI yet, you need to install it globally first

npm install -g @nestjs/cli 

After that, we initialize the project with the command

 nest new multi-database-prisma 

When there is a choice, we choose npm then after it's done we move to the folder, then we move to the project and install Prisma with the command

cd multi-database-prisma && npm install prisma --save-dev

Then when it's done, we will initialize Prisma with the command

npx prisma init

The above command does 2 things:

• Create a prisma folder containing the schema.prisma file
• Create a .env file After all the steps above are done, you're finished preparing the project repository we will use, but you can also check the official documentation if you want to know more.

Preparing Database#

After we've finished setting up the repository, you need to prepare a simulation of the existing databases. In this case, the naming of the 2 databases and each of their tables will be like this:

First Database:

name: first_db
platform: PostgreSQL
table: person

Second Database:

name: second_db
platform: MySQL
table: pets

You can create tables with a structure like the above by running the following queries:

first_db(PostgreSQL)

CREATE DATABASE "first_db";
CREATE TABLE "public"."person" ("id" serial,"name" text NOT NULL, PRIMARY KEY ("id"));
INSERT INTO "public"."person" ("name") VALUES ('ken');

second_db(MySQL)

CREATE DATABASE `second_db`;
CREATE TABLE `second_db`.`pets` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(255),`type` varchar(255),`owner_id` int, PRIMARY KEY (id));
INSERT INTO `second_db`.`pets` (`name`, `type`, `owner_id`) VALUES ('mochi', 'cat', '1');

Step 2: defining the first database connection#

First, you need to fill in the DATABASE_URL variable in the .env file according to the credentials formed into a connection string like this. In the local env, the value of the env becomes like this

postgresql://postgres:root@localhost:5432/db_name?schema=public

because in this case, we will be using an existing database, we will run

npx prisma db pull

so we can generate schema based on the connected database.

After the above process is successful, we will generate the prisma client with the command.

npx prisma generate

according to the data model defined in the prisma/schema.prisma file.

Step 3: defining the second database connection#

To define the second part, we need to create a new folder at the root named prisma-second, inside it we will create a file schema.prisma like this:

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

datasource db {
  provider = "mysql"
  url      = env("SECOND_DATABASE_URL")
}

You might wonder why do we have to add the output to the client. It's because when there are 2 instances of Prisma in one application, we have to specify the output when generating the data model, as mentioned here to avoid overwriting the Prisma client from the first database connection.

Then, we need to add the SECOND_DATABASE_URL variable to the .env file, and you fill in the DB credentials according to the connection string format like this in my case, the value of the variable will be like this:

mysql://root:root@localhost:3306/second_db

After that, we will do the DB pull again but with the added schema argument to generate the second connection using this command:

npx prisma db pull --schema=./prisma-second/schema.prisma

After we finish generating the data model for the second existing database, we will then generate the Prisma client for the second DB using the command:

npx prisma generate --schema=./prisma-second/schema.prisma

And we must also add the schema argument to specify the schema we want to generate the Prisma client for. And if you look in the node_modules/@internal there will be a folder called Prisma Second, according to the path we defined in the schema.prisma file in the prisma-second folder.

Step 4: Fetch data from 2 data source#

You will be trying to retrieve data from 2 different data sources. First of all, we will create a folder named prisma under src containing 3 files like this

prisma.service.ts

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


@Injectable()
export class PrismaService
  extends PrismaClient<Prisma.PrismaClientOptions, 'error' | 'query'>
  implements OnModuleInit, OnModuleDestroy {

  async onModuleInit() {
    await this.$connect();

    this.$on('error', (_e) => {
      // Do something
    });

  }

  async onModuleDestroy() {
    await this.$disconnect();
  }
}

prisma-second.service.ts

import { Injectable, OnModuleDestroy, OnModuleInit } from '@nestjs/common';
import { Prisma, PrismaClient } from '@internal/prisma-second/client';

@Injectable()
export class PrismaSecondService
  extends PrismaClient<Prisma.PrismaClientOptions, 'error' | 'query'>
  implements OnModuleInit, OnModuleDestroy {


  async onModuleInit() {
    await this.$connect();

    this.$on('error', (_e) => {
      // Do something
    });

  }

  async onModuleDestroy() {
    await this.$disconnect();
  }
}

prisma.module.ts

import { Module } from '@nestjs/common';
import { PrismaSecondService } from './prisma-second.service';

import { PrismaService } from './prisma.service';

@Module({
  providers: [PrismaService, PrismaSecondService],
  exports: [PrismaService, PrismaSecondService],
})
export class PrismaModule {}

Your folder's structure should be like this when you've added the files.

After defining the Prisma module, we will create an API that will retrieve Person and Pets data from 2 different databases and return the result when we hit the API. To do that, change the files below like this: app.service.ts

import { Injectable } from '@nestjs/common';
import { PrismaService } from './prisma/prisma.service';
import { PrismaSecondService } from './prisma/prisma-second.service';
@Injectable()
export class AppService {
  constructor(private prisma: PrismaService, private prisma_second: PrismaSecondService) {}

  async getData(): Promise<any> {
    
    const persons = await this.prisma.person.findMany();

    const pets = await this.prisma_second.pets.findMany();

    let data = persons.map(person =>{
      let pet = pets.filter(x => x.owner_id === person.id)
      return {
        ...person,
        pets: pet
      }
    })
    
    return data
  }
}

app.controller.ts

import { Controller, Get } from '@nestjs/common';
import { AppService } from './app.service';

@Controller()
export class AppController {
  constructor(private readonly appService: AppService) {}

  @Get()
  getData() {
    return this.appService.getData();
  }
}

app.module.ts

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { PrismaModule } from './prisma/prisma.module';

@Module({
  imports: [PrismaModule],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Then run the command below to start:

npm run start 

If successful, next you need to open http://localhost:3000 then we will successfully be able to call the API with 2 different data sources

Conclusion#

You can create multiple database connections of different types in one project using Prisma.io as long as you pay attention to the following things:

• Creating separate schema.prisma files that are separate from one another
• Defining output for each Prisma client and ensuring that there are no paths that are the same
• Running the npx prisma db pull and npx prisma generate commands for the other Prisma instances with the --schema argument to specify which schema we will run
• Defining a different Prisma service for each connection.

Resource#

1. Full code repository
2. Multiple schema documentation
3. Multiple Database / Datasource issue

Enjoyed this post?#

Check out some of my other articles:

1. Debugging Dockerized Go Applications with VS Code
2. Race condition on state react.js
3. Understanding pointer in golang