Multiple Connections / Databases / Datasources

[FredericLatour]

Problem

An application may need to access different connections/databases.
One use case could be having the exact same set of tables into different databases (multi-tenant approach).
Another use case could be having to access punctually some specific data in a separate database/server.

In any cases "a single connection" for the entire application is a really strong limitation.
Any SQL driver or ORM is capable of connecting to multiple databases either by direct support (TypeOrm) or by creating 2 instances. Unless I missed something this is not possible with Prisma client.

Suggested solution

I believe that it can be easily achieved with the following approach that does not change much both on the schema and client side of things.

datasource proj01 {
  provider = "postgresql"
  url      = env("DATABASE_URL_PROJ01")
  models = [Post, User]
}

datasource proj02 {
  provider = "postgresql"
  url      = env("DATABASE_URL_PROJ02")
  models = [Post, User]
}

datasource common {
  provider = "postgresql"
  url      = env("DATABASE_URL_COMMON")
  models = [Config]
}


generator client {
  provider = "prisma-client-js"
}
model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields:  [authorId], references: [id])
  authorId  Int?
}
model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

model Config {
  id    Int     @id @default(autoincrement())
  field string
}

You would then need to make it possible to use one connection/db or another:

import { PrismaClient } from '@prisma/client'
const proj01= new PrismaClient('proj01')
const proj02= new PrismaClient('proj02')
const proj01Users = await proj01.user.findMany()
const proj02Users = await proj02.user.findMany()

Additional context

Please note that it has nothing to do with having a dynamic url for being able to point to a different database when in development and/or staging. This feature is also needed but it's a different matter.
Note also that this is not exclusively to solve "multi-tenant" scenarios. I may need to access a database on another server whatever the reason.

Update 01: different database per environment use case

Thinking a bit more about it, this feature also provides a solution to the problem of having a different database (sqlite vs other database in prod for instance) depending on the environment (prod, staging, dev).

Adding on my previous example, you could have :

datasource proj01 {
  provider = "postgresql"
  url      = env("DATABASE_URL_PROJ01")
  models = [Post, User]
}

datasource proj01-dev {
  provider = "sqlitel"
  url      = env("DATABASE_URL_PROJ01")
  models = [Post, User]
}

And then depending on the environment:

import { PrismaClient } from '@prisma/client'
const proj01 = ( process.env.NODE_ENV === 'prod')  ? 
                            new PrismaClient('proj01')  :
                            new PrismaClient('proj01-dev')

[WellFail]

I think it would be nice to put each database in a separate file and have one file to which they will be connected. It also seems to me that it would be more convenient to have one client for all databases, something like this: prisma.database_name.model.query ()

[FredericLatour]

@WellFail I'm not completely sure of what you mean by putting each database in a separate file.
Models are not specific to a database. You associate the model to a database/connection through the models property.In that respect the separation, as far as database/connection is concerned, does not really make sense.

Your prisma.database_name.model.query () does not sound like a sensible choice in my opinion.

• It's verbose. Why having the database name hanging around when using only one?
• it breaks compatibility with the current approach
• moreover if you like this kind of approach, nothing prevents you from having an additional layer that handles connections that way in your application.

[0x0000F1]

I'm not sure if this would work, but have considered creating a model and a client for each datasource and then creating the contexts for them?

[WellFail]

@FredericLatour I meant that the data set may differ depending on the database, and it is not very correct to store them all in one file, this complicates the understanding. I understand that in your case this is not necessary. But a similar approach will be more flexible.
As for my prisma.database_name.model.query () I agree that this is not the best solution, indeed it can be processed already at the level of my application.

[FredericLatour]

@WellFail Having entirely different models for different databases/connection is a specific use case. In a multi tenant application, models will be shared by databases/connection.
You may want some flexibility for defining a schema over multiple files (is that currently possible?) but a rules based on the databases/connection does not work.

[WellFail]

@FredericLatour Different databases for different environments currently work depending on environment variables and do it perfectly, what you offer is unnecessary. If we are talking about the possibility of several databases within the same application, then I understand what it is about, but the best approach would be to create several schema.prisma files and the client will be generated one for all connections (in this case, we forbid storing the same models in different databases), or in the generated client, it should be possible to select the necessary database when generating the request, but in any case, the generation of several clients of the prisma is unnecessary.

[FredericLatour]

@WellFail
How do you currently make it possible to use Sqlite in development while using say Postgres in production?
Regarding using multiple databases/connections, I don't get what you are proposing. Would you mind elaborating?
Keep in mind that in a multi-tenant scenario, you certainly don't want to repeat your model that are identical whatever the database/connection.
In my solution, you are not generating multiple clients: you are instantiating a client for the desired connection. I can't see what is wrong with this approach. This is the same approach you will be using with whatever Driver ORM in the Node ecosystem.

The approach I'm proposing has the advantage to be fully compatible with the existing approach. When defining a single datasource (like this is the case now) in your schema, then :

• the model property at the datasource level is not necessary
• the parameter representing the datasource when instantiating PrismaClient is not necessary.

Please, provide some example of what is wrong in your mind and how you see things should work.

[WellFail]

@FredericLatour Currently, I use environment variables to use different databases in different environments. During the deployment, migrations are performed and a client is generated, depending on the variables.

As for multiple databases within a single application.
For example:

schema-1.prisma

datasource proj01 {
  provider = "postgresql"
  url      = env("DATABASE_URL_PROJ01")
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
}



schema-2.prisma

datasource proj02 {
  provider = "postgresql"
  url      = env("DATABASE_URL_PROJ02")
}

model Log {
  id    Int     @id @default(autoincrement())
 description  String
}

In result we have one client like this

import { PrismaClient } from '@prisma/client'
const prisma= new PrismaClient()
const users = await prisma.user.findMany()
const logs = await prisma.log.findMany()

At the moment I am not considering a multi-tenant option, because how it is implemented now is more than enough.

In this case, work with the prisma remains the same as it is now, and different data sets relating to different databases are clearly delimited by files, which simplifies the work, but we have a limitation, we cannot have models with the same names in different databases. In your solution I need to manually handle many prisma clients in my app for using different databases.

In any case, in your approach it is worth spreading different data schemes into different files for better understanding and convenience. We do not consider multi-tenant, but I do not see other examples in which we need the same data set in different databases

[FredericLatour]

@WellFail
Considering that the provider property can't be set through an environment variable (correct me if I'm wrong), I can't see how you can use different type of database depending on the environment? (except by modifying the file with an external tool before generating the schema)

Honestly I can't understand what's the point of your approach:

• What if you have the same table name in multiple databases? Say both proj01 and proj02 have a Log table?
• It's not because you don't have multi-tenant projects that it doesn't exist!
• It doesn't provide a solution for having a different type of database depending on the environment either.

My approach has basically 2 added optional "keywords":

• an optional models property while defining the datasource schema.
• an optional datasource parameter when instantiating PrismaClient()

Having 1, 2 or 10 files for the schema definition is a completely different matter. It does not change anything to the proposed solution.

[WellFail]

@FredericLatour Oh, sorry, I’ve made a mistake, the database provider really can’t be a variable, but nothing prevents changing it by other means, unless of course you deploy app manually.

Regarding the problem, my solution has only the problem of the impossibility of creating models with the same names in different databases (it seems to me that having the same models in different databases is not a good idea).
But anyway, I can agree with your decision, but I believe that the definition of models and datasources should be in different files, which will remove the models property from datasourse definition. I still insist that what we are discussing now should not be a solution to the multi-tenant problem

[timsuchanek]

Thanks for raising the issue and it's great to see such a lively discussion!

What is already possible today

It seems that important information is missing here.
You can already today connect to multiple databases with the same datamodel like so:

datasource db {
  provider = "postgres"
  url = env("PG_URL")
}

import { PrismaClient } from '@prisma/client'

const client1 = new PrismaClient({ datasources: { db: { url: 'postgres://localhost/db1' }} })
const client2 = new PrismaClient({ datasources: { db: { url: 'postgres://localhost/db2' }} })

That means you can override the connection string in the PrismaClient constructor.

What is possible today, but not nice

Another use-case mentioned in this issue is having different datamodels active in one application.

In order to do that, you would need multiple schema.prisma files and point prisma generate to those files with the --schema flag. The generators then also need to generate into custom directories instead of node_modules/@prisma/client:

prisma/schema1.prisma

datasource db {
  provider = "postgres"
  url      = env("DB1_URL")
}

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

model Model1 {
  id         Int @id @default(autoincrement())
  model1Name String
}

prisma/schema2.prisma

datasource db {
  provider = "postgres"
  url      = env("DB2_URL")
}

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

model Model2 {
  id         Int @id @default(autoincrement())
  model2Name String
}

In the code

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

const client1 = new PrismaClient1()
const client2 = new PrismaClient2()

In the CLI

prisma generate --schema prisma/schema1.prisma
prisma generate --schema prisma/schema2.prisma

We're aware that this solution is not nice to use and are looking into solutions to improve this experience. But from what I could see in this issue, the mentioned use-cases are already possible today.

[FredericLatour]

@timsuchanek thanks for the update.

I had initially opened a discussion asking if it was possible to have multiple connections pointing to different db/schemas and was told that it was not currently supported by Prisma.
Moreover, I could read a couple of discussions/issues around the same matter and couldn't see the information you just provided.
And that's why, starting from the getting started example, I came up with a conceptual solution that seems to work without breaking compatibility.

So, yes, indeed, it seems that the mentioned use cases are already possible even though it's a bit hacky.
That said, as far as multi-tenant is concerned, being able to override the connection URL makes it possible for a very dynamic multi-tenant approach where you don't necessarily know the datasource at build time. It's therefore quite nice to have.
The real ugly part in my mind is when you need to access different datamodels in a different datasources.
And the proposed solution would solve this matter in a rather elegant fashion (IMO but I may be missing some constraints).

Now, if you want to stick with one file=one datasource (am I correct assuming that is the case?), another alternative would be to allow for being able to dispatch the schema over multiple files either at the CLI level or by having an include <file> feature in the prisma file.

[douglasrcjames]

Maybe I am missing something here, but is the below "workaround" the only way for me to have a development database and production database for my Next.js project? This is a standard practice for my Firebase projects so I can interact and test my database without touching the production records. I'd be shocked coming from Firebase if this is the best work around using Prisma.

Thanks for raising the issue and it's great to see such a lively discussion!

What is already possible today

It seems that important information is missing here. You can already today connect to multiple databases with the same datamodel like so:

datasource db {
  provider = "postgres"
  url = env("PG_URL")
}

import { PrismaClient } from '@prisma/client'

const client1 = new PrismaClient({ datasources: { db: { url: 'postgres://localhost/db1' }} })
const client2 = new PrismaClient({ datasources: { db: { url: 'postgres://localhost/db2' }} })

That means you can override the connection string in the PrismaClient constructor.

What is possible today, but not nice

Another use-case mentioned in this issue is having different datamodels active in one application.

In order to do that, you would need multiple schema.prisma files and point prisma generate to those files with the --schema flag. The generators then also need to generate into custom directories instead of node_modules/@prisma/client:

prisma/schema1.prisma

datasource db {
  provider = "postgres"
  url      = env("DB1_URL")
}

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

model Model1 {
  id         Int @id @default(autoincrement())
  model1Name String
}

prisma/schema2.prisma

datasource db {
  provider = "postgres"
  url      = env("DB2_URL")
}

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

model Model2 {
  id         Int @id @default(autoincrement())
  model2Name String
}

In the code

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

const client1 = new PrismaClient1()
const client2 = new PrismaClient2()

In the CLI

prisma generate --schema prisma/schema1.prisma
prisma generate --schema prisma/schema2.prisma

We're aware that this solution is not nice to use and are looking into solutions to improve this experience. But from what I could see in this issue, the mentioned use-cases are already possible today.

[JoeRoddy]

@douglasrcjames

Maybe I am missing something here, but is the below "workaround" the only way for me to have a development database and production database for my Next.js project? This is a standard practice for my Firebase projects so I can interact and test my database without touching the production records. I'd be shocked coming from Firebase if this is the best work around using Prisma.

Nope! Prisma definitely supports your use case without having to resort to this. This issue is for setting up multiple databases in the same process, not configuring which db to use in a given process.

For managing different environments, you can check out this:
https://www.prisma.io/docs/guides/development-environment/environment-variables#example-set-the-database_url-environment-variable-in-an-env-file

You can just configure your prod DATABASE_URL in env.production.local and your dev one in env.development.local, then you can generate whichever client you want to use:
dotenv -e .env.development.local -- npx prisma generate

[douglasrcjames]

Amazing, thank you @JoeRoddy, this is exactly what I was looking for!

[sbuss1980]

I hope I am correct in bringing this question up here - otherwise please let know and I put in a separate discussion...

We are currently evaluating prisma and are discussing possible architectures for a solution.

The current approach is based von Next.JS, Prisma and MSSQL Server and involves the need that users can connect to different databases by configuration at runtime. So a user might have access to multiple databases by permission provided from a different backend and can choose at runtime to which db she wants to connect to. For now I'd assume that schema is structure-wise identical for all. So we wouldn't need to generate and maintain multiple prisma schemas - at least not for this. Potential user base is some 100s up to 1,000 of users, for concurrent users I wouldn't assume more than 50 (rather less).

So we are wondering if there is a way to implement this using prisma.
From reading the docs and following discussions here it appears technically possible, but we are wondering if it is a reasonable approach non-functionally, performance-wise to
a) First Approach: alter the prisma client instance by request and user everytime it is requested (resp. when a different db is to be queried based on user's settings). This doesn't appear ideal performance-wise assuming that there is one shared prisma client instance for all users / sessions, which would have to be reinitialised every time. Or am I wrong about this?
b) Second Approach: maintain a map or comparable structure of prisma client instances in the nextjs app / node server instance in memory, one for each db / schema instance requested? That can obviously result in numerous prisma client instances based on the number of dbs/schemas requested. And would infer some complexity because we'd have to take care of purging the list when clients are not used for a certain amout of time. etc. The risks which come to mind are of course memory issues when there are too many prisma client instances etc., and also connection pooling issues on db side

Is there any experience towards any of the approaches here?

Obvious alternatives would be:

• merge all data into one db schema instance (somewhat difficult for some reasons lying in the nature of the app / use cases)
• Scale horizontally - one node instance per DB schema connection (appears very time consuming and expensive, no idea how to implement in a way that it creates an acceptable ux).

Any advice, recommendations are appreciated! Thank you!

[janpio]

@sbuss1980 You might want to create a new discussion for this, from your description it does not sound like you want to be connected to multiple different databases (with different schemas, or even different database system) at the same time. What you describe sounds more like a multi tenancy setup. And that can already work independent of "multi database" supprot in Prisma, which this issue is about.

[sbuss1980]

@sbuss1980 You might want to create a new discussion for this, from your description it does not sound like you want to be connected to multiple different databases (with different schemas, or even different database system) at the same time. What you describe sounds more like a multi tenancy setup. And that can already work independent of "multi database" supprot in Prisma, which this issue is about.

Thanks for the advice @janpio - moved / copied to #20920

[lightningspirit]

Just posted a possible schema solution for this feature
#1274 (comment)

[souzacavalheiro]

I would like to make a contribution on the subject.

For the use case where 'user1 connects to db1, and user2 to db2' (same tables and schemas). LucidORM (by AdonisJS) does this simply, and can serve as inspiration for PrismJS:

// List of available connections
  connections: {
    db1: {
      client: 'pg',
      connection: {
        host: Env.get('PG1_HOST'),
        port: Env.get('PG1_PORT'),
        user: Env.get('PG1_USER'),
        password: Env.get('PG1_PASSWORD', ''),
        database: Env.get('PG1_DB_NAME'),
      },
	  db2: {
      client: 'pg',
      connection: {
        host: Env.get('PG2_HOST'),
        port: Env.get('PG2_PORT'),
        user: Env.get('PG2_USER'),
        password: Env.get('PG2_PASSWORD', ''),
        database: Env.get('PG2_DB_NAME'),
      },
  }
  ...

//Query
import Database from '@ioc:Adonis/Lucid/Database'

Database
  .connection('db1') // only change
  .from('posts')
  .select('*')

I don't know technical details about how LUCID changes or manages these connections. (I'm new to programming)

translated by Google

[brandongallagher1999]

This is the kind of functionality that we need. Thanks for the recommendation @souzacavalheiro

[sebolio]

It's been over 3 years, has Prisma solved this already?

[Lukas-Sturm]

Is this related to the talk about Prisma at Prisma Day 2019: https://youtu.be/RAoXdyI_PH4?si=Zbi_zKwLskyX4tKk&t=1410
In the talk, a Model is shown using multiple Datasources (Fauna and MySql) in a single Query. Kind of implementing a Polyglot Persistence Model.
I know it is very old, but I can't find any mention of this in the documentation. Also, the Video description/comments did not say if this was a canceled feature.
Will something like that ever be part of Prisma ?

[ftognetto]

Thanks for raising the issue and it's great to see such a lively discussion!

What is already possible today

It seems that important information is missing here. You can already today connect to multiple databases with the same datamodel like so:

datasource db {
  provider = "postgres"
  url = env("PG_URL")
}

import { PrismaClient } from '@prisma/client'

const client1 = new PrismaClient({ datasources: { db: { url: 'postgres://localhost/db1' }} })
const client2 = new PrismaClient({ datasources: { db: { url: 'postgres://localhost/db2' }} })

That means you can override the connection string in the PrismaClient constructor.

What is possible today, but not nice

Another use-case mentioned in this issue is having different datamodels active in one application.

In order to do that, you would need multiple schema.prisma files and point prisma generate to those files with the --schema flag. The generators then also need to generate into custom directories instead of node_modules/@prisma/client:

prisma/schema1.prisma

datasource db {
  provider = "postgres"
  url      = env("DB1_URL")
}

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

model Model1 {
  id         Int @id @default(autoincrement())
  model1Name String
}

prisma/schema2.prisma

datasource db {
  provider = "postgres"
  url      = env("DB2_URL")
}

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

model Model2 {
  id         Int @id @default(autoincrement())
  model2Name String
}

In the code

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

const client1 = new PrismaClient1()
const client2 = new PrismaClient2()

In the CLI

prisma generate --schema prisma/schema1.prisma
prisma generate --schema prisma/schema2.prisma

We're aware that this solution is not nice to use and are looking into solutions to improve this experience. But from what I could see in this issue, the mentioned use-cases are already possible today.

Hello all I also have the same problem, I have an app in which every customer has it's own db with the same schema.
I managed to solve it via the answer and generated some migration scripts, but I have another problem.
Since the deployment is performed on Cloud run, where each instance has a limit of 100 connections to the database, considering that each PrismaClient reserves a pool of default connections https://www.prisma.io/docs/orm/prisma-client/setup -and-configuration/databases-connections/connection-pool#default-connection-pool-size each instance obviously takes a short time to exhaust the number of connections.
I limited the pool by adding ?connection_limit=3 to the connection string resulting in a temporary solution.

Do you know how I can solve my problem? The database is a mysql managed on Google Cloud SQL. Maybe prism data proxy could solve it?

Thank you!

[wmtrinu]

We would also need this, please, if possible

We currently have two services with NestJS

• auth
• core

both services share the same database but the auth needs another account to bypassrls. (we also use introspection)

[kollein]

I also tried hard to find a workaround to work correctly with several databases (since there's no MySQL support yet). So, I made some last week and it seems to work well. The solution is similar to the one mentioned above by @Peter-Hong, but in my case, there's needed to keep the migrations separate. So, I just used that next way.
Inside the prisma (in the project root) there are two directories:

prisma/
  db-1/
    migrations/
    schema.prisma

and

prisma/
  db-2/
    migrations/
    schema.prisma

prisma/db-1/schema.prisma

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

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

prisma/db-2/schema.prisma

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

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

Of course, to generate clients, run a migration, etc., there's a 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-2/schema.prisma --name initial
...

And importing in code this way:

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

export const db1 = new PrismaClient1()
export const db2 = new PrismaClient2()

I want to note that there were some problems with imports (mentioned by @fagkoz) when trying to make output directories as follows: node_modules/@prisma/client/db-1 node_modules/@prisma/client/db-2
So, it works properly when specifying different client outputs, like this: node_modules/@prisma-db-1/client node_modules/@prisma-db-2/client
Maybe it would help someone.

Do you know which code to use when not using migrations, but just using push? Normally I would call npx prisma db push, can this be used with this multi-database approach?

Thanks! @notflip i prefer this approach because of clean structure which separate each database migration folders into different places. It works like a charm.

[JCtapuk]

Thanks for raising the issue and it's great to see such a lively discussion!

What is already possible today

It seems that important information is missing here. You can already today connect to multiple databases with the same datamodel like so:

datasource db {
  provider = "postgres"
  url = env("PG_URL")
}

import { PrismaClient } from '@prisma/client'

const client1 = new PrismaClient({ datasources: { db: { url: 'postgres://localhost/db1' }} })
const client2 = new PrismaClient({ datasources: { db: { url: 'postgres://localhost/db2' }} })

That means you can override the connection string in the PrismaClient constructor.

What is possible today, but not nice

Another use-case mentioned in this issue is having different datamodels active in one application.

In order to do that, you would need multiple schema.prisma files and point prisma generate to those files with the --schema flag. The generators then also need to generate into custom directories instead of node_modules/@prisma/client:

prisma/schema1.prisma

datasource db {
  provider = "postgres"
  url      = env("DB1_URL")
}

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

model Model1 {
  id         Int @id @default(autoincrement())
  model1Name String
}

prisma/schema2.prisma

datasource db {
  provider = "postgres"
  url      = env("DB2_URL")
}

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

model Model2 {
  id         Int @id @default(autoincrement())
  model2Name String
}

In the code

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

const client1 = new PrismaClient1()
const client2 = new PrismaClient2()

In the CLI

prisma generate --schema prisma/schema1.prisma
prisma generate --schema prisma/schema2.prisma

We're aware that this solution is not nice to use and are looking into solutions to improve this experience. But from what I could see in this issue, the mentioned use-cases are already possible today.

I also wondered how to make dev and production in 1 scheme. So that I can test, for example, dev sqlite and production mysql. Since the provider environment cannot be loaded to create 2 env

[vatoer]

when I implement multiple database connection, I encounter error during build on Windows only

Error: EPERM: operation not permitted, scandir 'C:\Users\admin\Application Data'

as I mentioned here
#23223

[marcelldac]

Hello! This error returns to me when i try create more than one datasource

How to reproduce:

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL_EXTERNAL")
  schemas  = ["internal", "external"]
}
datasource db2 {
  provider = "postgresql"
  url      = env("DATABASE_URL_EXTERNAL")
  schemas  = ["internal", "external"]
}

Returs this error:

Error validating datasource db2: You defined more than one datasource. This is not allowed yet because support for multiple databases has not been implemented yet.Prisma

[AmrViernes]

Hello! This error returns to me when i try create more than one datasource

How to reproduce:

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["multiSchema"]
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL_EXTERNAL")
  schemas  = ["internal", "external"]
}
datasource db2 {
  provider = "postgresql"
  url      = env("DATABASE_URL_EXTERNAL")
  schemas  = ["internal", "external"]
}

Returs this error:

Error validating datasource db2: You defined more than one datasource. This is not allowed yet because support for multiple databases has not been implemented yet.Prisma

Read the comments above

[marcelldac]

I've read now. I did not saw these comments yesterday, sorry sir.

[AlecBlance]

I also tried hard to find a workaround to work correctly with several databases (since there's no MySQL support yet). So, I made some last week and it seems to work well. The solution is similar to the one mentioned above by @Peter-Hong, but in my case, there's needed to keep the migrations separate. So, I just used that next way.
Inside the prisma (in the project root) there are two directories:

prisma/
  db-1/
    migrations/
    schema.prisma

and

prisma/
  db-2/
    migrations/
    schema.prisma

prisma/db-1/schema.prisma

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

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

prisma/db-2/schema.prisma

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

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

Of course, to generate clients, run a migration, etc., there's a 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-2/schema.prisma --name initial
...

And importing in code this way:

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

export const db1 = new PrismaClient1()
export const db2 = new PrismaClient2()

I want to note that there were some problems with imports (mentioned by @fagkoz) when trying to make output directories as follows: node_modules/@prisma/client/db-1 node_modules/@prisma/client/db-2
So, it works properly when specifying different client outputs, like this: node_modules/@prisma-db-1/client node_modules/@prisma-db-2/client
Maybe it would help someone.

Do you know which code to use when not using migrations, but just using push? Normally I would call npx prisma db push, can this be used with this multi-database approach?

Thanks! @notflip i prefer this approach because of clean structure which separate each database migration folders into different places. It works like a charm.

Did you encounter a conflict in doing npx prisma migrate deploy?

[vatoer]

@AlecBlance did you succes to build this?

I failed when I do pnpm build on windows

and success when I build it on linux or Mac

[mckernanin]

@vatoer some recent dependency update has broken it, I've been using postgres and sqlite side by side for a year successfully and after doing a bunch of updates in my repo it is now broken (next app failing to import with pnpm)