Support querying across multiple schemas

[pantharshit00]

Right now with Prisma, you can query across one schema at a time but in many cases, you need to query across multiple tables stored across multiple database schemas.

We need to make certain product decision to enable this like how we are going to add this schema metadata information to the model and other implementation details that are necessary to be handled for this.

[slavicdev34]

Hi @pantharshit00 do you have any update about this issue?

[pantharshit00]

Sorry, there are no updates on this right now.

[tylerben]

@pantharshit00 are there any plans to support this going forward? I might be misunderstanding things but is my understanding correct that Prisma currently only supports connecting to a single PostgreSQL schema?

In our database, we have tables and views split across multiple schemas that we need to be able to pull data from. We really want to use Prisma, but until there is support for connecting to multiple schemas in the same DB, it will not be possible for us to do so.

[alonbilu]

+1 for that. This item has been open for too long. I too use multiple schemas in a single project and in order to migrate completely to Prisma, this is a must.

[ruheni]

Hi @alonbilu, @martasko, and @tylerben 👋🏾

Could you kindly share your use cases, your current workaround for this, or anything you feel would be relevant to assist our product teams working on the feature? Your feedback will be highly appreciated 🙂

[slavicdev34]

Hi @ruheni,

I wanted to try Prisma at one project. I had a multi-tenant application and each tenant(user) has a few tables in his schema.

eg.: schema: tenant_1
tables: tableA, tableB, tableC...

then we had a "shared" schema which contained only 1 table
e.g: schema: shared
tables:: global_settings

When user_1 signs in he has read/write access to his tenant_1 schema and read access for shared schema.

At the moment solution for this is to create 2 Prisma instances one connected to tenant_1 schema and the second instance connected to shared schema. I think this solution is a little bit hacky and also I'm not able to do joins.

Thank you 😃

[ubyjvovk]

+1 for this, a pretty important use case for us.

[alonbilu]

I'm using different schemas to organize data. For example, I have the geo schema which includes IP mapping tables, gelocations etc that I'm sharing with multiple projects/APIs.
In addition that that, every project has it's own "public schema" which store all the projects' related data.

[tylerben]

I have a similar use case. On some projects we leverage multiple schemas to organize the data. For example, one schema may be used to house all tables and views associated with reporting while another may be used to house all tables and views associated with data importers.

Thanks much!

[PrimeDominus]

I too need this. I have separate postgres schemas for each customer.

[mark600211]

any update about this?

[dthyresson]

I needed to query across two schemas today and was able to use rawQuery to get the data I needed:

db.$queryRaw`
    SELECT a.*
    FROM "Activity" a
      JOIN public."Member" m on m.id = a."memberId"
      JOIN auth.users u on m.email = u.email
    WHERE m.email = ${context.currentUser.email}
    ORDER BY a."occurredAt" desc

Here I join member activity (from public) with the user data (that's in auth) for our known current user's email.

Worked great.

[kpdud]

Gotta say that this is a major bummer as we've designed a database with multiple schemas. This definitely should be implemented sometime and yeah, looks like raw queries are the only way of going about it for now.

Edit: Perhaps instead of creating a Prisma connection straight to the schema via URL, make it to the database and allow the user to choose the schema? So something on the lines of: prisma.schema('example_organisation').users.findUnique({ where: ... and if the schema does not exist, throw an error that could be handled by the dev, e.g. 'Schema not defined'?

Lets say an organisation uses a schema for each of their clients and each schema looks exactly the same i.e. it has the same tables with the same constraints, relations etc. It would be nice if we could define a e.g. customer.prisma schema and tell prisma that we wish to use that schema when dealing with certain schemas in out database, perhaps via something like prisma.schemas({ dbSchema: 'example_organisation', prismaSchema: 'customer' }).users.findUnique({ where: ... though there could be an issue with intellisense and type-checking during coding.

Just thought I'd float some ideas.

[kevinclarkadstech]

To me, this seems to be violating the rules of microservices, such as "no shared data context", and "discrete boundaries". This is bound to lead to development issues down the line as I have seen time and time again. So in my personal opinion, this feature should not have high priority. You should be able to create a Prisma schema/client per db schema. If you are using foreign key relations across database schemas, you DEFINITELY are violating the principles of microservices.

[josh-hemphill]

Though not everyone uses it for microservices; for instance, MS SQL environments often use schemas for security/access controls with each schema having different CRUD permissions for different clients/credentials. In this case it has nothing to do with separation of concern and just the restrictions a table needs.

[kevinclarkadstech]

We had our migration team workshop in the past two days, and the issue was discussed and prioritized amongs other work we need to do. I will write down what we discussed so you know it is not forgotten!

First we have our prioritization, which for now is something like:

1. Get MongoDB support for db pull, db push and migrate.
2. Get proper no foreign keys mode.
3. Support different kinds of index types. Including length limits, partial and full-text.
4. Querying across multiple schemas.

The PSL working group should decide first how we're supporting the index types, and after that, deciding on my suggestion for the multiple schemas support. The schema issue is a big one, but we found a way to make it a non-breaking change. There are three different ways on the PSL to make it happen:

Block:

schema foo {
    model A { .. }
    enum B { .. }
}

Prefix:

model foo.A { .. }
enum foo.B { .. }

Attribute:

model A {
    id Int @id
    @@schema("foo")
}

enum B {
    Meow
    @@schema("foo")
}

The idea how we're making this a non-breaking change for our users is that by default, we do not detect multiple schemas. On PostgreSQL the default schema is public and can be changed by setting the schema parameter. And on Microsoft SQL Server the default schema is dbo, and can be changed by setting the schema parameter accordingly.

The user could set multiple schemas here, as in schema=foo,bar, which would enable multiple schemas feature and include the new syntax to the data model. We might include syntax such as asterix to have all the schemas available, but nothing has been decided yet.

The problematic, breaking changes will happen in the Prisma Client. A schema can have the same name as a model, and different schemas can hold models with the same name, as long as they are not in the same schema. Therefore the public client api must address this somehow...

// The current API
let users = prisma.user.findMany()

// The new API with a schema
let users = prisma.schema.user.findMany()

As you can see, if the user switches to the multiple schemas mode, all calls to Prisma would break, meaning some refactoring is needed to get the project working again. If schemas and models can hold same names, and models between schemas can have the same names, some kind of namespacing is mandatory and this will be the biggest source of discussion when we're deciding on how to implement the feature.

Could you have it as db.user.findMany({
schema: "auth", // optional prop, default is default for database
});

Then it would not be a breaking change?

[danielwarke]

Hello, I'm having an issue using the multiSchema feature where a certain database schema will not introspect when in conjunction with other database schemas, and I cannot figure out why. With the code below, the Trips database schema will never be introspected. There are no error messages, but the tables defined in the Trips schema are not introspected into prisma models.

datasource db {
  provider = "sqlserver"
  url      = env("PRISMA_DATABASE_URL")
  schemas  = ["Appointments", "Trips", "core", "orders"]
}

However, when I change the schemas list to only include Trips, I am able to introspect the database schema successfully.

datasource db {
  provider = "sqlserver"
  url      = env("PRISMA_DATABASE_URL")
  schemas  = ["Trips"]
}

I'm using this as a workaround to copy the generated prisma models and then run prisma db pull with all 4 database schemas listed, and then pasting the missing Trips models. However, after doing this if I run prisma db pull again, it removes the Trips models.

[janpio]

Whoah, that is super weird @danielwarke. Can you open a standalone issue, fill all the fields of the bug template, so we can start digging into this? Thanks.

[danielwarke]

Whoah, that is super weird @danielwarke. Can you open a standalone issue, fill all the fields of the bug template, so we can start digging into this? Thanks.

#19677

[maxime4000]

Would love to have the feature available on mongodb

[marcusradell]

I am looking forward to getting schema support to be able to isolate schemas from each other to support modular monoliths and micro services that use the same database host.

I'd also love different schemas to be able to have tables with the same name.

I'd also love for the different schemas to be defined in different folders mixed in my code.

If this isn't supported, there are ways to manually prefix all tables with the schema name, and then isolate the schema by using TypeScript Pick<> type helper to pick out the schema models manually.

[janpio]

Hm, I am not sure if we have a proper issue for the approach you are describing @marcusradell - we have this one for cross schema queries and relations, where one app just stores its data in multiple schemas. And we have #12420, where the same Prisma schema should be deployed to different database schemas to achieve isolation of the data. Can you please open a new feature request and describe your use case a bit? Thanks.

[tgds]

We had our migration team workshop in the past two days, and the issue was discussed and prioritized amongs other work we need to do. I will write down what we discussed so you know it is not forgotten!
First we have our prioritization, which for now is something like:

1. Get MongoDB support for db pull, db push and migrate.
2. Get proper no foreign keys mode.
3. Support different kinds of index types. Including length limits, partial and full-text.
4. Querying across multiple schemas.

The PSL working group should decide first how we're supporting the index types, and after that, deciding on my suggestion for the multiple schemas support. The schema issue is a big one, but we found a way to make it a non-breaking change. There are three different ways on the PSL to make it happen:
Block:

schema foo {
    model A { .. }
    enum B { .. }
}

Prefix:

model foo.A { .. }
enum foo.B { .. }

Attribute:

model A {
    id Int @id
    @@schema("foo")
}

enum B {
    Meow
    @@schema("foo")
}

The idea how we're making this a non-breaking change for our users is that by default, we do not detect multiple schemas. On PostgreSQL the default schema is public and can be changed by setting the schema parameter. And on Microsoft SQL Server the default schema is dbo, and can be changed by setting the schema parameter accordingly.
The user could set multiple schemas here, as in schema=foo,bar, which would enable multiple schemas feature and include the new syntax to the data model. We might include syntax such as asterix to have all the schemas available, but nothing has been decided yet.
The problematic, breaking changes will happen in the Prisma Client. A schema can have the same name as a model, and different schemas can hold models with the same name, as long as they are not in the same schema. Therefore the public client api must address this somehow...

// The current API
let users = prisma.user.findMany()

// The new API with a schema
let users = prisma.schema.user.findMany()

As you can see, if the user switches to the multiple schemas mode, all calls to Prisma would break, meaning some refactoring is needed to get the project working again. If schemas and models can hold same names, and models between schemas can have the same names, some kind of namespacing is mandatory and this will be the biggest source of discussion when we're deciding on how to implement the feature.

Could you have it as db.user.findMany({ schema: "auth", // optional prop, default is default for database });

Then it would not be a breaking change?

That would be amazing, because that would allow to decide what schema to use at runtime.

Someone already mentioned a long time ago in this thread a use case that we have as well, which is having customer data separated by database schema. The tables in these account schemas are the same, so we could be doing something like:

prisma.model.findUnique({ where: {...}, schema: customerSchema })

Right now there's no way to use Prisma for such DB design.

[gruckion]

I found a solution.

1. Create a view in the public schema.

   create or replace view auth_users as
   select
     *
   from
     auth.users

2. Enable views preview feature.

   generator client {
     provider        = "prisma-client-js"
     output          = "../generated/prisma-client"
     // Note: `multiSchema` is not needed!
     previewFeatures = ["views"]
   }
   

3. Add view definition to schema.prisma.

   // Note: I add `@unique` attribute to `id` column.
   // https://www.prisma.io/docs/concepts/components/prisma-schema/views#adding-a-unique-identifier-to-an-introspected-view
   
   view AuthUser {
     instance_id                 String?   @db.Uuid
     id                          String    @id @unique @db.Uuid
     aud                         String?   @db.VarChar(255)
     role                        String?   @db.VarChar(255)
     email                       String?   @db.VarChar(255)
     encrypted_password          String?   @db.VarChar(255)
     email_confirmed_at          DateTime? @db.Timestamptz(6)
     invited_at                  DateTime? @db.Timestamptz(6)
     confirmation_token          String?   @db.VarChar(255)
     confirmation_sent_at        DateTime? @db.Timestamptz(6)
     recovery_token              String?   @db.VarChar(255)
     recovery_sent_at            DateTime? @db.Timestamptz(6)
     email_change_token_new      String?   @db.VarChar(255)
     email_change                String?   @db.VarChar(255)
     email_change_sent_at        DateTime? @db.Timestamptz(6)
     last_sign_in_at             DateTime? @db.Timestamptz(6)
     raw_app_meta_data           Json?
     raw_user_meta_data          Json?
     is_super_admin              Boolean?
     created_at                  DateTime? @db.Timestamptz(6)
     updated_at                  DateTime? @db.Timestamptz(6)
     phone                       String?   @unique
     phone_confirmed_at          DateTime? @db.Timestamptz(6)
     phone_change                String?   @default("")
     phone_change_token          String?   @default("") @db.VarChar(255)
     phone_change_sent_at        DateTime? @db.Timestamptz(6)
     confirmed_at                DateTime? @default(dbgenerated("LEAST(email_confirmed_at, phone_confirmed_at)"))     @db.Timestamptz(6)
     email_change_token_current  String?   @default("") @db.VarChar(255)
     email_change_confirm_status Int?      @default(0) @db.SmallInt
     banned_until                DateTime? @db.Timestamptz(6)
     reauthentication_token      String?   @default("") @db.VarChar(255)
     reauthentication_sent_at    DateTime? @db.Timestamptz(6)
     is_sso_user                 Boolean   @default(false)
     deleted_at                  DateTime? @db.Timestamptz(6)
   
     @@index([instance_id])
     @@map("auth_users")
   }
   

4. Execute prisma generate
5. Yay!

   const users = await db.authUser.findMany({});
   console.log({ users });

Note 1: The view will be deleted when you perform a prisma db push. Therefore, please manually add the view creation sql to the migration file. https://www.prisma.io/docs/concepts/components/prisma-schema/views#use-views-with-prisma-migrate-and-db-push

Note 2: I strongly discourage adding auth to multiSchema settings.

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
  schemas  = ["public", "auth"]
}

If you run prisma migrate reset in that state, the auth schema in Supabase will be broken. For example, the following functions will fail.

This is a much better solution thank you!

[conioX]

Any update for mysql?

[janpio]

Not at this time @conioX, we'll post an update here when we start work on it.

[leppaott]

Haven't tried to create migrations files with prisma migrate but seems the "non-main" schema must be then migrated with explicit schema names like: CREATE TABLE schema.table_name We tried to change search_path on the migration file but then it fails in the end with _prisma_migrations does not exist unless you switch back to the "main-schema" i.e. the one on the connection string.

[Aeolun]

@janpio At the risk of repeating a question often asked (though it seems not for the past few months or so). Is there a plan (and possibly schedule) to add this for MySQL? Right now we're using 8 different Prisma connections to all our databases but it's quite unwieldy, and we cannot query cross database.

[cipriancaba]

I found a solution.

@gruckion not sure how you came up with that piece of genius. Was about to mess with multiple schema bs, but using views is just beautiful. Much appreciated

[o5faruk]

I found a solution.

@gruckion not sure how you came up with that piece of genius. Was about to mess with multiple schema bs, but using views is just beautiful. Much appreciated

How do you reference it in other tables tho? Seems like it would be bottleneck since views are just saved queries

[cipriancaba]

I don't, in my usecase I just needed a joined table for which the view solves it really nice

[mtander]

Sorry to jump into a long thread. Can anyone help me determine if there is a way to rename a database schema in the prisma migration files?

[meshackm]

I am just getting into building multi-tenant apps. I plan is to isolate tenants with schemas. There is a shared schema where a new tenant's data is stored, a script then runs on insert to create a schema for that tenant. A subdomain would be issued to the tenant and it would be the same as their schema name. Having a way to select which schema to query from would be nice.