How to connect Multiple Databases using Prisma in Nest JS

What is?Prisma?

Prisma is an open-source ORM for Node.js and TypeScript. It is used as an alternative to writing plain SQL, or using another database access tool such as SQL query builders (like knex.js) or ORMs (like TypeORM and Sequelize). Prisma currently supports PostgreSQL, MySQL, SQL Server, SQLite, MongoDB and CockroachDB (Preview).

Prisma can be used in plain JavaScript, supports TypeScript, and provides a level of type protection better than other ORMs in native TypeScript.You can find an in-depth comparison of the type-safety guarantees of Prisma and TypeORM here.

If you have two databases and need to connect both databases with Prisma, here’s how to do it.

Why do we 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 to use SQL database and NoSQL database simultaneously and you need to use both databases, you can set up Prisma to do so.

Lets start the journey???

Start a new NestJS?project

nest new prisma-multidatabase
cd prisma-multidatabase
        

Install and Initialise Prisma

yarn add prisma --dev
npx prisma init
        

This npx prisma init command creates a new prisma directory with the following contents:

• schema.prisma: Specifies your database connection and contains the database schema
• .env: A dotenv file, typically used to store your database credentials in a group of environment variables

Here I am connecting PostgreSQL DB and MongoDB for this tutorial

Create PostgreSQL DB in?Heroku

Now I need PostgreSQL DB for connect with my project. Here I’m using Heroku for get PostgreSQL instance. If you already have PostgreSQL db, please ignore these steps.

If you want to learn how to set up a PostgreSQL database free, you can follow this guide on How to set up a free PostgreSQL database on Heroku.

Connect PostgreSQL DB using?Prisma

Your database connection is configured in the datasource block in your schema.prisma file. By default it’s set to postgresql.

schema.prisma

generator client {
  provider = "prisma-client-js"
}

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

.env

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA"
        

Replace the placeholders spelled in all uppercase letters with your database credentials. Note that if you’re unsure what to provide for the SCHEMA placeholder, it's most likely the default value public:

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public"
        

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.

Therefore you can create another PostgreSQL DB in Heroku, and attach that DB as shadow database

schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL_PG")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL_PG")
}
        

.env

DATABASE_URL_PG="postgresql://iddcskzfiilqeu:bfb5be2104094a53d4b074067631abas7b78378ce4ced3a36014545b54ce157@ec2-44-205-64-253.compute-1.amazonaws.com:5432/d9nngmms9btujs?schema=public"

SHADOW_DATABASE_URL_PG="postgresql://mhwcwdcjodnvag:c5623dbee0b1edd2cff015a51bea5sd3f81b14e222493818142b699cbe50b8da@ec2-34-234-240-121.compute-1.amazonaws.com:5432/d3et35fov926n2?schema=public"
        

Create database table with Prisma?Migrate

Prisma Migrate generates SQL migration files for your declarative data model definition in the Prisma schema. These migration files are fully customizable so that you can configure any additional features of the underlying database or include additional commands, e.g. for seeding.

Add the following User models to your schema.prisma file:

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

“migrate”: “npx prisma migrate dev”
        

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

Note: The node_modules/.prisma/client/schema.prisma file should be the same as prisma/schema.prisma

This prisma migrate dev command generates SQL files and directly runs them against the database. In this case, the following migration files was created in the existing prisma directory:

$ tree prisma
prisma
├── dev.db
├── migrations
│   └── 20201207100915_init
│       └── migration.sql
└── schema.prisma
        

Install and generate Prisma?Client

Prisma Client is a type-safe database client that’s generated from your Prisma model definition. Because of this approach, Prisma Client can expose CRUD operations that are tailored specifically to your models.

To install Prisma Client in your project, run the following command in your terminal:

yarn add @prisma/client
        

Note that during installation, Prisma automatically invokes the prisma generate command for you. In the future, you need to run this command after every change to your Prisma models to update your generated Prisma Client.

Note: The prisma generate command reads your Prisma schema and updates the generated Prisma Client library inside node_modules/@prisma/client.

Use Prisma Client in your NestJS?services

You’re now able to send database queries with Prisma Client. If you want to learn more about building queries with Prisma Client, check out the API documentation.

When setting up your NestJS application, you’ll want to abstract away the Prisma Client API for database queries within a service. To get started, you can create a new PrismaService that takes care of instantiating PrismaClient and connecting to your database.

Inside the src directory, create a new file called prisma.service.ts and add the following code to it:

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();
    });
  }
}
        

Next, you can write services that you can use to make database calls for the User model from your Prisma schema.

Hooray!! Now we connected our first DB with the project. Let’s go and connect other DB also.

Create MongoDB in Mongo?Atlas

Now I need MongoDB for connect with my project. Here I’m using Mongo Atlas for get MongoDB instance. If you already have MongoDB db, please ignore these steps.

If you want to learn how to set up a MongoDB database free, you can follow this guide on How to set up a free MongoDB database on Mongo Atlas.

Connect MongoDB database using?Prisma

Here we are using a trick to connect other database with the NestJS project. Firstly we are creating a new folder for create a schema.prisma file inside a different folder.

For this tutorial I’m making a folder named prisma-mongo and store the schema.prisma file inside that folder.

For this Prisma Client, we need to change the output directory. Let’s place it inside node-modules,

../node_modules/@prisma-mongo/prisma/client

schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["mongodb", "filterJson"]
  output   = "../node_modules/@prisma-mongo/prisma/client"
}

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

.env

DATABASE_URL_PG="postgresql://iddcskzfiilqeu:bfb5be2104094a53d4b0740676731ab7b78378ce4cdced3a36014545b54ce157
@ec2-44-205-64-253.compute-1.amazonaws.com:5432/d9nngmms9btujs?schema=public"

SHADOW_DATABASE_URL_PG="postgresql://mhwcwdcjodnvag:c5623dbee0b1edd2cff0015a51bea5981e2b14e222493818142b699cbe50b8da
@ec2-34-234-240-121.compute-1.amazonaws.com:5432/d3et35fov926n2?schema=public"

DATABASE_URL_MONGO="mongodb+srv://iamtest:[email protected]/myFirstDatabase"
        

Create database table using Prisma Db?Push

During development, you will need to update your Prisma schema file (for example, to add new fields), then update the data in your your development environment’s database, and eventually push both the updated schema and the new data to the production database using prisma db push command.

Add the following Location model to your schema.prisma file:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["mongodb", "filterJson"]
  output   = "../node_modules/@prisma-mongo/prisma/client"
}

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

model Location{
  id String @id @default(auto()) @map("_id") @db.ObjectId
  userId String @unique
  location Json  
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}
        

To keep things simple we will only create a Location model with userId and location.

Now let’s add a script to run the migration in our package.json

"mongo:generate": "npx prisma generate --schema prisma-mongo/schema.prisma",
"mongo:dbpush": "npx prisma db push --schema prisma-mongo/schema.prisma"
        

And run the command yarn mongo:generate This will generate a Prisma Client inside node_modules/@prisma-mongo/prisma/client.

Note: The node_modules/@prisma-mongo/prisma/client/schema.prisma file should be the same as prisma-mongo/schema.prisma file

Create new Prisma service for Mongo?Database

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 @prisma-mongo/prisma/client instead of the default @prisma/client

// eslint-disable-next-line import/no-extraneous-dependencies
import { PrismaClient } from '@prisma-mongo/prisma/client';
import { INestApplication, Injectable, OnModuleInit } from '@nestjs/common';

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

  async enableShutdownHooks(app: INestApplication) {
    this.$on('beforeExit', async () => {
      await app.close();
    });
  }
}
        

Hooray?!!! Now you have it. You now have connections to both PostgreSQL and MongoDB databases with Prisma.

Note:-

You can use multiple databases with Prisma by creating different schema files and generating separate Prisma Clients for each database.

References:

github link → https://github.com/dulanwirajith/prisma-multidatabase
prisma with mongo → https://www.prisma.io/docs/concepts/database-connectors/mongodb
prisma → https://www.prisma.io/
        

For more content follow me?on:

• Twitter : @codewithdulan
• Instagram : @dulanwirajith
• Medium : dulanwirajith.medium.com
• GitHub : @dulanwirajith
• LinkedIn : dulanwirajith