Nestjs configure two DBs in one project

I will explain how to configure more than one RDBMS DB in a Nestjs project.

As a software engineer, I encountered a situation to configure more than one Database in a single project. I used Prisma as an ORM in the project.

Install Prisma and Configuration:

$ npm install prisma --save-dev
$ npm install @prisma/client
$ npx prisma init

It creates a prisma folder in the root folder of the project. Inside the folder, it has a file schema.prisma file. We need to configure the schema inside the schema.prisma file.

We need to change that file name. schema.prisma to schema1.prisma . And create a file called schema2.prisma for our second database.

Configure schema1.prisma

// schema1.prisma

generator client {
  provider = "prisma-client-js"
  output = "./generated/client1"
}

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

model User {
  id                 Int       @unique @default(autoincrement())
  first_name         String    @db.VarChar(100)
  last_name          String    @db.VarChar(100)
  kc_user_id         String    @db.VarChar()
  email              String    @db.VarChar(255)
  create_user        User?     @relation(name: "create_user", fields: [created_by], references: [id])
  created_by         Int?      @db.Integer()
  update_user        User?     @relation(name: "update_user", fields: [updated_by], references: [id])
  updated_by         Int?      @db.Integer()
  confirmed_at       DateTime? @db.Timestamp()
  created_at         DateTime  @default(now())
  updated_at         DateTime  @updatedAt
  deleted_at         DateTime? @db.Timestamp()
  created_users      User[]    @relation("create_user")
  updated_users      User[]    @relation("update_user")

  @@map("users")
}

Here we need to configure output = “./generated/client1” the generator. By default, if we need to access the User table then we need to call it from ‘@prisma/client’. Since we configured output we need to call the User table from the generated folder.

import { User } from 'prisma/generated/client1';

Configure Schema2.prisma

// schema2.prisma

generator client {
    provider = "prisma-client-js"
    output   = "./generated/client2"
}

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

model User {
    name                       String   @unique() @db.VarChar()
    password_hash              String?  @db.VarChar()
    creation_ts                Int      @db.Integer()
    admin                      Int?     @default(0) @db.Integer()
    upgrade_ts                 Int?     @db.Integer()
    is_guest                   Int?     @default(0) @db.Integer()
    appservice_id              Int?     @db.Integer()
    consent_version            Int?     @db.Integer()
    consent_server_notice_sent String?  @db.VarChar()
    user_type                  String?  @db.VarChar()
    deactivated                Int?     @default(0) @db.Integer()
    shadow_banned              Boolean? @db.Boolean()
    consent_ts                 Int?     @db.Integer()
    approved                   String?  @db.VarChar()

    @@map("users")
}

Now configured schema2.prisma — Which is my second DB. It is readable only.

If we need to call the user table schema2.prismawe need to call the following.

import { User } from 'prisma/generated/client2';

Run the migration

$ npx prisma migrate dev --schema=./prisma/schema1.prisma

For running migrate dev, we need to give — schema=./prisma/schema1.prisma . It run the first database migration only.

Run the application

Now we can run the application usingnpm run start:dev. Now we will face the following error.

NestJS: Error: Cannot find module './generated/client1'

We face this error, because by default, in nestjs project the dist folder will have the prisma/seeders folder but it does not have a prisma/generated folder. In that case, we can manually copy-pas the prisma/generated folder to dist the folder.

For this, we can give a solution in two ways.

The first approach, we need to update the tsconfig.build.json file. Then we need to copy-paste prisma/generated to under the dist folder. Whenever we run the application it recreates entirely newdist so existing things will be removed.

{
  "extends": "./tsconfig.json",
  "exclude": ["node_modules", "test", "dist", "**/*spec.ts"],
  "assets": ["prisma/generated"],
  "watchAssets": true
}

We need to add this line “assets”: [“prisma/generated”], along with the existing configuration. Then thegenerated folder always persists under the dist folder.

Note: Whenever schema1.prisma or schema2.prisma updated, we need to copy the prisma/generated folder and paste it under the dist folder. Look at the below example.

In the second approach, we need to install cpx — it is the plugin for copying files.

Install cpx

$ npm install cpx

Then update the package.json file as follows.

  "scripts": {
    "build": "nest build",
    "copy:prisma": "cpx 'prisma/generated/**' 'dist/prisma/generated'",
    "start": "npm run copy:prisma && nest start",
    "start:dev": "npm run copy:prisma && nest start --watch"
  },

Now it will copy-paste the generated folder from prisma the folder to dist/prisma folder. It will resolve the issue. Now we can run the application without any issue.

Sample output:

Thank you for reading. Have a nice day!

Reference links:

Documentation | NestJS - A progressive Node.js framework

NestJS: `Error: Cannot find module './generated/client'` when importing Prisma Client when using…

cpx

Multiple Connections / Databases / Datasources · Issue #2443 · prisma/prisma