Multiple Prisma connections and schemas

[rdrigos]

Question

Good morning, I have a project that I am developing that needs to deal with multiple databases, initially two, both Sql Server:

Database 1: Server=database1;Database=tesla1;User Id=sa;Password=mysecret;TrustServerCertificate=true;
Database 2: Server=database2;Database=tesla2;User Id=sa;Password=mysecret;TrustServerCertificate=true;

and needed to create schemas for both to create their tables. How could I do that? So when I do:

npm prism migrate

Create the tables in their respective database.

Would a command like this work?
npx prisma migrate dev --schema=prisma/schema1.prisma --datasource=db1
npx prisma migrate dev --schema=prisma/schema2.prisma --datasource=db2

[ludralph]

Hi @rdrigos 👋

To work with multiple databases, you can create separate schema files for each database. In each schema file, you define the datasource with the respective database connection string. Here's an example of how you can set up your schema files:
For Database 1, create a schema file prisma/db-1/schema.prisma:

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL_DB_1")
}

generator client {
  provider        = "prisma-client-js"
  output          = "../../node_modules/@prisma-db-1/client"
}

For Database 2, create a schema file prisma/db-2/schema.prisma:

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL_DB_2")
}

generator client {
  provider        = "prisma-client-js"
  output          = "../../node_modules/@prisma-db-2/client"
}

In your .env file, you should define the DATABASE_URL_DB_1 and DATABASE_URL_DB_2 environment variables with your respective database connection strings.
To generate clients and run migrations, you need to specify the schema:

npx prisma generate --schema prisma/db-1/schema.prisma
npx prisma generate --schema prisma/db-2/schema.prisma

npx prisma migrate dev --schema prisma/db-1/schema.prisma --name initial
npx prisma migrate dev --schema prisma/db-2/schema.prisma --name initial

In your code, you can import the generated clients like this:

import { PrismaClient as PrismaClient1} from '@prisma-db-1/client';
import { PrismaClient as PrismaClient2} from '@prisma-db-2/client';

This way, PrismaClient1 connects to Database 1 and PrismaClient2 connects to Database 2.

Currently, Prisma does not support running a single migrate command for multiple schemas. You need to run the migrate command separately for each schema.

[rdrigos]

Ok, see if I understood correctly, I will need to create Prisma Schemas for each database:

root
   |---- prisma
               |---- rbsdb
               |         |---- schema.prisma (Configured with the respective output and URL)
               |---- x3v12
               |          |---- schema.prisma (Configured with the respective output and URL)

And when generating migrations, I use the commands:
npx prisma migrate dev --schema prisma/rbsdb/schema.prisma --name initial
npx prisma migrate dev --schema prisma/x3v12/schema.prisma --name initial

and use it like this:
import { PrismaClient as PrismaClient1} from '@prisma-db-1/client';
import { PrismaClient as PrismaClient2} from '@prisma-db-2/client';

or create a file src/libs/prisma.ts:

import { PrismaClient as PrismaClient1} from '@prisma-db-1/client';
import { PrismaClient as PrismaClient2} from '@prisma-db-2/client';

const Client1 = new PrismaClient1();
const Client2 = new PrismaClient2();

export {
     Client1,
     Client2
}

[ludralph]

Yes, that's correct.

[rdrigos]

Okay, I'll test this later, and I'll report back with the results.
Thanks