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]

I never received this error, so I don't have the necessary knowledge to help you! I'm sorry!

I'm marking the @ludralph, because I see that he helps people in the Prisma community a lot and I believe he has the necessary knowledge to answer you

[simibruda]

It will be great if @ludralph could help me with this issue

[ludralph]

Hi @simibruda, Can you please open a new discussion and fill out the template with the necessary information to help debug this issue?

[simibruda]

sure #24870

[IArnaut2]

Have you heard of DRY principle?

Programmers should NOT repeat themselves by making schemas and repeating models. They should be able to create just ONE schema for multiple databases.

[anuoua]

I think Prisma should consider splitting schema datasource and generator. It must be more flexible, can do a lot of things like multiple connections.