Wasp•2y 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

kapa.ai•7/17/24, 7:23 AM

Hi @mm. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base.

kapa.ai•7/17/24, 7:23 AM

----------------------
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

name

, ,

schema

schema

, and

version

version

.

Here's an example of how you can define these in your

schema.prisma

schema.prisma

file:

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

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 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
----------------------

kapa.ai•7/17/24, 7:23 AM

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•7/17/24, 8:01 AM

In Wasp 0.14.0 which is getting released today, we are switching to directly using the

schema.prisma

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 attribute in our internal auth models.

You'll have to try it after today and let us know

mmOP•7/17/24, 2:50 PM

I will thank you!

Filip•7/17/24, 4:22 PM

@mm As you probably know, Waps 0.14.0 is out.

Let us know how we stand when you try it

neogeofun•7/17/24, 4:41 PM

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

martinsos•7/18/24, 10:24 AM

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•7/19/24, 7:01 AM

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•7/19/24, 2:44 PM

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•7/19/24, 4:14 PM

@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.

Mmartinsos Interesting! I imagine you can also then execute some kidn of bulk commands on t...

mmOP•7/19/24, 5:36 PM

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.

Nneogeofun @mm it is relevant only when your database model is enterprise level. There is n...

mmOP•7/19/24, 5:44 PM

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•7/19/24, 5:44 PM

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

martinsos•7/19/24, 6:10 PM

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.