- The Strategic Engineer (Mr Stratgineer)
- Posts
- How to connect Multiple Databases using Prisma in Nest JS
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).
While Prisma can be used with plain JavaScript, it embraces TypeScript and provides a level to type-safety that goes beyond the guarantees other ORMs in the TypeScript ecosystem. 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-multidatabasecd prisma-multidatabase
Install and Initialise Prisma
yarn add prisma --devnpx 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.prismafile 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 prismaprisma├── 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 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-schemagenerator 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 pushcommand.
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-schemagenerator 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.prismafile should be the same as prisma-mongo/schema.prisma
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-dependenciesimport { 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-multidatabaseprisma with mongo → https://www.prisma.io/docs/concepts/database-connectors/mongodbprisma → https://www.prisma.io/
For more content follow me on:
