Wasp•6mo ago

Multi-Schema support for Postgres

How do I configure postgres multi schema support? Here's the link to the prisma docs: https://www.prisma.io/docs/orm/prisma-schema/data-model/multi-schema

12 Replies

kapa.ai•6mo ago

Hi @mm. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave: ---------------------- Wasp uses Prisma to interact with the database and it allows you to define PostgreSQL extensions that should be enabled for your database. You can define each extension with a dict that includes the following fields: name, map, schema, and version. Here's an example of how you can define these in your schema.prisma file:

extensions = [hstore(schema: "myHstoreSchema"), pg_trgm, postgis(version: "2.1")]

extensions = [hstore(schema: "myHstoreSchema"), pg_trgm, postgis(version: "2.1")]

In the app.db.prisma.dbExtensions field, you can enable PostgreSQL extensions. Here's an example:

app MyApp {  
 // ...  
 db: {  
 system: PostgreSQL,  
 prisma: {  
 clientPreviewFeatures: ["postgresqlExtensions"]  
 dbExtensions: [  
 { name: "hstore", schema: "myHstoreSchema" },  
 { name: "pg_trgm" },  
 { name: "postgis", version: "2.1" },  
 ]  
 }  
 }  
}

app MyApp {  
 // ...  
 db: {  
 system: PostgreSQL,  
 prisma: {  
 clientPreviewFeatures: ["postgresqlExtensions"]  
 dbExtensions: [  
 { name: "hstore", schema: "myHstoreSchema" },  
 { name: "pg_trgm" },  
 { name: "postgis", version: "2.1" },  
 ]  
 }  
 }  
}

However, for multi-schema support specifically, there is currently no direct information available in the provided sources. You might want to refer to the Prisma documentation for more details on multi-schema support: Prisma Multi-Schema Docs (https://www.prisma.io/docs/orm/prisma-schema/data-model/multi-schema) Sources: - Wasp Docs - The app.db Field Part 2 (https://wasp-lang.dev/docs/data-model/backends#the-appdb-field) - Wasp Docs - Prisma Configuration (https://wasp-lang.dev/docs/data-model/backends#prisma-configuration) ---------------------- Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: ---------------------- Sorry @mm, I seem to be having a bit of trouble. Don't worry though, I've pinged my support and they will try to fix me up! :technologist: Perhaps try again in 15mins?

miho•6mo ago

In Wasp 0.14.0 which is getting released today, we are switching to directly using the schema.prisma file. However, I'm not sure if our auth system which injects extra models will be able to handle this since it won't put the @@schema attribute in our internal auth models. You'll have to try it after today and let us know 🙈

mmOP•6mo ago

I will thank you!

Filip•6mo ago

@mm As you probably know, Waps 0.14.0 is out. Let us know how we stand when you try it 😄

neogeofun•6mo ago

You can do whatever updates are necessary by doing manual migrations. But I am curious, why do you need schemas?

martinsos•6mo ago

Hm I am also interested, I never used this feature: what is the benefit of it @mm , what do you find it useful for?

mmOP•6mo ago

I haven't tried it yet, ongoing migration to 0.14 , @martinsos I think this is great way to organize the tables. For example in the opensaas template, you can organize the tables by schema for each sample app/purpose/category: Auth - all the tables related to authentication/registration etc AIScheduler - all the tables related to the sample app SubApp1 - tables for SubApp1 and so on I just learned about this recently, I've only scratched the surface of postgres

martinsos•6mo ago

Interesting! I imagine you can also then execute some kidn of bulk commands on these groups of tables or something hm. So it should work in Wasp -> I saw that in Prisma you need to list the schemas you want included under one of the config blocks. The main thing is that Wasp in the background adds some of its own Prisma models (for auth) and they don't have any db schema assigned, so will those get included then? I would hope so.

neogeofun•6mo ago

@mm it is relevant only when your database model is enterprise level. There is no need for schemas when all you have is a few dozen tables, which should be more than enough for most projects you'd want to build using Wasp.

mmOP•6mo ago

Yes I haven't had much time to explore these stuffs but I will definitely give an update if I do. I'm only referring to putting custom tables in schemas, because we are building an app that consists of several sub apps, or tools. Well, maybe since we are in the early stage of wasp, and there are upcoming major updates like using full TS. Maybe for now it's not ideal, But I believe it's a good thing to consider as well. It's also a good thing that we are able to organize tables, for example if you have a big existing project built on wasp and a newly hire, aside from looking at the schema.prisma they can look at the overview of the database using tools like pgadmin. there is a clear separation of concern But it's fine even if it doesn't work.

MEE6•6mo ago

Wohooo @mm, you just became a Waspeteer level 2!

martinsos•6mo ago

Yeah ok you are both right, probably not as important for smaller projects, but on the other hand, why not use it if it you want that extra level of description and it is already there, plus what you said is potentialy interesting, having ultiple projects sharing the schema. From the Wasp side, it is interesting to discuss beause we want to support Prismat he best we can, so better to look into it earlier so we are ready as Wasp advances.

Gaming

Programming

Multi-Schema support for Postgres