How to connect to two databases using Prisma

Do you want to learn how to connect Prisma with two databases? In this step-by-step guide, you will learn exactly that! We will start with an example use case and then set it up.

• An example use case for two databases
• Connect two databases with Prisma step-by-step
• Conclusion

An example use case for two databases

An example use case for two different databases in the same application could be an industrial solution where you want to store different types of data in the most efficient way possible. Take a power plant, and your application monitors the energy flows inside this power plant. You will have multiple devices that produce time-series data, and these producers are located in different locations around the plant. For better localization and better information to the user, you also want to store structural data.
You could store the different types of data inside different databases, for example, the structural data in a PostgreSQL database and the time-series data inside CrateDB. The reason to do something like this is that the databases have different characteristics. PostgreSQL, for example, is ACID compliant, while CrateDB is easily horizontally scalable.

In the following section, we will use Prisma with two different PostgreSQL instances in one application.

Connect two databases with Prisma step-by-step

The first step is to set up the two databases. For this, we will use docker and the following docker-compose.yml, that we can start with docker compose up -d:

services:
  postgres:
    image: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres

  postgres2:
    image: postgres
    ports:
      - "5433:5432"
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: postgres

Before we can go into detail, we need to set up a project. For this example, I will create a REST API with one endpoint that requests data from both databases. To start, follow these steps:

1. Create directory: mkdir prisma-example && cd prisma-example
2. Init a new project: pnpm init
3. Install typescript: pnpm install typescript ts-node @types/node --save-dev
4. Install Prisma: pnpm install prisma --save-dev
5. Install Fastify: pnpm install fastify

With this, we have the basic setup done. Next, we will initialize Prisma for both databases:

1. Initialize Prisma for the first database: npx prisma init --datasource-provider postgresql
2. Adjust the connection string inside .env and create a second variable for the second database, name it PSQL2_URL

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/postgres?schema=public"

PSQL2_URL="postgresql://postgres:postgres@localhost:5433/postgres?schema=public"

1. Rename the generated schema.prisma file to schema-psql.prisma
2. Open it and change the generator to:

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

5. Duplicate the file and rename it to schema-crate.psql
6. Open it and change the generator to:

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

7. Also, change the data source URL to the PSQL2_URL environment variable
8. Now define your schema. I will just create an example (first for PostgreSQL and second for PostgreSQL 2):

model ModelPSQL1 {
  id         Int @id @default(autoincrement())
  model String
}
--
model ModelPSQL2 {
  id         Int @id @default(autoincrement())
  model String
}

Next, we bring these schemas to the database using migrate:

npx prisma migrate dev --name init --schema prisma/schema1.prisma; npx prisma migrate dev --name init --schema prisma/schema2.prisma

With this, we set up the basics. Sadly our clients will not be generated automatically. For this, we will create a new script inside the package.json and call it prisma. The script will execute the following command:

If this guide is helpful to you and you like what I do, please support me with a coffee!

...
"scripts": {
    "prisma": "prisma generate --schema prisma/schema1.prisma --watch & prisma generate --schema prisma/schema2.prisma --watch"
}
...

We immediately run it using pnpm run prisma.

In addition, we will also add the dev command to run the program. In case you want to know how to set up fastify with hot reload, check this post here.

...
"scripts": {
    "dev": "ts-node src/index.ts",
    ...
}
...

Lastly, we can create our example endpoint. The important bits here are the import of the Prisma Client in lines two and three. These are the locations we specified in the schema files. For this, we create a file src/index.ts

import fastify from 'fastify'
import { PrismaClient as PrismaClient1 } from '../prisma/generated/client1'
import { PrismaClient as PrismaClient2 } from '../prisma/generated/client2'


const server = fastify()

server.get('/example', async (request, reply) => {
    const res1 = await new PrismaClient1().modelPSQL1.findMany();
    const res2 = await new PrismaClient2().modelPSQL2.findMany();

    return {"PSQL1": res1, "PSQL2": res2}
})

server.listen({ port: 8080 }, (err, address) => {
  console.log(`Server listening at ${address}`)
})

Now we start it using pnpm run dev and then call http://localhost:8080/example to see the data of both databases returned.

Conclusion

With that, you learned how to connect Prisma with two different databases in the same application. I hope this guide was helpful to you. In case you have any questions, feel free to ask!

If you like this post, consider subscribing to my monthly newsletter!