Preview feature feedback: Prisma multi schema support (multiSchema)

[janpio]

Here are details on how the preview feature for multi schema support currently works in Prisma: #1122 (comment)

Please share your feedback about the multiSchema functionality released in v4.3.0 in this issue.

• If you encounter a bug, please open a bug report in this repo.
• If the feature is working well for you, please share this in a comment below or leave a 👍 on this issue.

If you have any questions, don't hesitate to ask them in the #prisma-client channel in the Prisma Slack.

[Faithfinder]

Well, not sure why you need to list the schemas in datasource. Just marking the models should be enough?

[shaked420]

Hi,

I tried using the multischema feature as explained,
In my case, I have two tables with the same names in two different schemas.
This resulted in the following error when trying to generate the client:
error: The model “users” cannot be defined because a model with that name already exists. --> schema.prisma:432 | 431 | 432 | model users { |
Does the new feature support duplicated names of tables or fields across schemas?
Is there something I can do to overcome this problem?

Thanks!

[janpio]

This will work later @shaked420 - it is a known limitation right now. You will need give the models different names, and then use @@schema and @@map to point to the correct schema and table for each. (Fields are not affected by this at all.)

[rroline]

Really appreciate you guys working so hard on this. Any idea when CockroachDB will be supported?

[eglove]

I must be missing something obvious. Introspection, migrate, and push aren't supported. So all we can do is generate a client from manually written schema?

[eglove]

Really appreciate you guys working so hard on this. Any idea when CockroachDB will be supported?

For the record, I think Prisma will always struggle with non-relational databases. With things like EdgeDB and SurrealDB on the horizon with their own SDK's, Sanity.io GROQ like languages, built in GraphQL APIs, etc, there will likely be less of a need for ORMs in the next generation of databases.

In regards to Prisma now on multi-schemas it seems the experimentation is limited. I definitely understand the effort needed here. Big changes to the underlying engine are likely needed. So all I can really do is some side experimentation, it's difficult to just start using this in a side project and put this to any real test.

[janpio]

@rroline Soon, same as all the others of our supported databases.

@eglove Correct, you can generate a Client, run your queries and report to us if those all work correctly. Then we can fix any problems before moving on to the other product surface.

[CoreyBin]

Awesome Feature, I would like to ask how long it will support Migrate, I think it`s a important feature

[janpio]

We are working on that right now, you can follow other issues with the topic: multiSchema label if you want to be updated: https://github.com/prisma/prisma/issues?q=is%3Aopen+label%3A%22topic%3A+multiSchema%22+sort%3Aupdated-desc (Probably not in the next release, but hopefully soon after)

[janpio]

@shaked420 This problem should be fixed now.
@rroline CockroachDB should now be supported.
@eglove All the things you mentioned are now supported.
@JarynChen Works now.

In general the multiSchema feature should now generally be considered done, besides support for MySQL (tracked in #16943) and any potential bugs or defects - but we are not aware of anything significant right now.

So please test it, and ler us know how it goes!

[rroline]

@shaked420 This problem should be fixed now.

@rroline CockroachDB should now be supported.

@eglove All the things you mentioned are now supported.

@JarynChen Works now.

In general the multiSchema feature should now generally be considered done, besides support for MySQL (tracked in #16943) and any potential bugs or defects - but we are not aware of anything significant right now.

So please test it, and ler us know how it goes!

This is fantastic news! Thank you for the update and for all of the hard work! Excited to dive in and start checking it out!

[albertilagan]

@janpio any news on when will this get exposed on @prisma/generator-helper's GeneratorOptions?

[janpio]

What exactly would you expect there @albertilagan? datasources, datamodel and dmmf should already include all necessary information - or do they not? If so, best open a new issue and we can prioritize this as a improvement to the preview feature.

[albertilagan]

What exactly would you expect there @albertilagan? datasources, datamodel and dmmf should already include all necessary information - or do they not? If so, best open a new issue and we can prioritize this as a improvement to the preview feature.

Hmm, I was expecting it to have a schema prop in DMMF.Model interface.

interface Model {
        name: string;
        dbName: string | null;
        schema: string | null; <--- this one
        fields: Field[];
        uniqueFields: string[][];
        uniqueIndexes: uniqueIndex[];
        documentation?: string;
        primaryKey: PrimaryKey | null;
        [key: string]: any;
    }

I'm using a generator named prisma-dbml-generator and atm it does not support multiSchema.
In order to generate a proper multi-schema dbml file we need to define it when generating the table instance.
i.e.

Table identity.User {
  id Int [pk, increment]
  createdAt DateTime [default: `now()`, not null]
  updatedAt DateTime [not null]
  email String [unique, not null]
  name String
  posts Post [not null]
  profile Profile
}

[janpio]

Thanks for pointing that out, we will look into that @albertilagan. Might have missed this.

[ludufre]

Hey guys. I managed to merge two different MySQL schemas manually (added specifics @schemas("..."), previewFeature, etc) and apparently it generated correctly and I'm able to use it. So, for MySQL only db pull is missing, is that right or is there still some incompatibility with MySQL that I haven't noticed yet?

[janpio]

We don't really know, because we are not testing for it until #16943 has been fully implemented. But good signal, that things already partially work 😆

[shellscape]

I tried this for the first time today because I have a single foreign key in my primary schema that references another. I was pretty dismayed to find out that prisma generated a prisma-schema for the entire secondary schema. I don't want the client that's generated to have access to that at all. This use case involves a foreign key to a supabase auth schema, which supabase explicitly mentions many times throughout documentation that the recommendation is not to write to that schema as its managed.

Please don't tell me I have to add some kind of magical ignore tag to every single table in that secondary schema. That would be a pretty poor DX.

[erfaneslami]

I am utilizing Prisma with SQL Server and have two schemas in my database. However, when I provide a schema array in the datasource, Prisma only generates a model for the first index of the schema array

[Hadi-bakhshi]

I am utilizing Prisma with SQL Server and have two schemas in my database. However, when I provide a schema array in the datasource, Prisma only generates a model for the first index of the schema array

I do too, I'm trying to use prisma's new feature which is provided to support multi schema but as you said it returns only the 0 index of the schema array
This is driving me crazy and no one has mentioned that.

[janpio]

Can you define what "first index of the schema array" or "0 index of the schema array" means @erfaneslami @Hadi-bakhshi? Can you maybe illustrate it with an example? That would be helpful.

@shellscape That is why we do preview features, or ask for input for features before we build them: We require the input of the community to know about the things they deem essential. Being able to only include individual tables from the second schema has not explicitly come up (often) in that context, although generally being able to only manage parts of a database is sometimes mentioned (and there are issues for it) - but not often enough that it would have made it to the top of our list of things to implement yet.

[shellscape]

FWIW I haven't worked on a team that hasn't had their database spread out across multiple schemas in the last 10 years. It's incredibly common and I hope that factors into your planning and decision making.

(Perhaps Prisma is for more simple database needs and I'm trying to force it to be a more advanced tool than it is)

[erfaneslami]

@janpio yes sure here's an example of my code

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

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
  schemas  = ["Person", "dbo"]
}

when i use command 'npx prisma db pull' it only create model for Person schema , and if i remove the Person from array , only pulls dbo

i have lots of schemas on my database and i want to pull them all

[rahrang]

@janpio is there a timeline on promoting multiSchema to general availability? i'd like to use it in production but i'm concerned that it's still a preview feature after almost a full year. thanks!

[janpio]

No, currently there is not.

If you are ok with the feedback that is being gathered here and under topic: multiSchema, you can totally use it in Production already - many do. But for us to remove the preview feature, we would actually need to understand and fix all the reported bugs and problems that do exist (see the link), and right now that is not on the top of our list.

[reza-ebrahimi]

multiSchema feature is not working with PostgreSQL database.
Related issue:
[multiSchema previewFeature] The database schema is not empty error

[leppaott]

multiSchema feature is not working with PostgreSQL database. Related issue: [multiSchema previewFeature] The database schema is not empty error

This: there seems to be issues with migrator. Of course would be nice that multiSchemas were allowed to be imported from other schema.prismas that we wouldn't need to copy-paste such "shared" schemas. Not only allowing multi-schema but common single-source-of-truth schemas. But maybe that's not needed to make this generally available.