Multi Schema(PostgreSQL)

Feature Request
1

Hi,

Currently the dmxDatabase.php file only looks at public schema which can be changed to show all schemas contents. But the queries generated by the framework does not include the schema name.

While one can change the search_path within Postgres, this does not work if there tables with the same name across multiple schemas. <- which is the case for a project.

So the question is, is there a way to use multiple schemas, or can i change something that adds the schema name to the table name when the metadata is refreshed?

Regards,

Nick

3

A friendly bump on this, @Teodor, @patrick,

Has more to do with that if it’s something simple to do with the framework, then i can continue with the current setup else i need to switch the objects to the public schema to continue.

Thanks in advance.

Regards,

Nick

4

The database connector was developed for simple databases normally used on the web. Schemas are not often used unless you have some very complex data structure. I will investigate if it would be possible to easily support schemas within our database connector.

1 Like
5

Thank you Patrick.

I know what to do to continue ans will wait for further comments.

Regards,

Nick

6

@patrick, with the latest update, which is awesome!, it seems to lent itself for multi schema capabilities. so i was wondering if you had time investigating this besides your busy schedule.

1 Like
7

+1 on getting some info on if/how Wappler can support multi schema postgres db structure

10

Currently we support only public schemas.

Having multiple schema support is really much more complex - as we have to change all our query builders to allow to first pick the schema picking before getting the tables. While usually you always have a single schema.

1 Like
11

For when and if this is looked into/designed please make it dynamic.

Not that I see a need for me but some multi-tenant apps rely on a schema per tenant so dynamic selection would be nice.

4 Likes
12

Also needing multi-schema support here. Bummer that it doesn’t seem that Wappler team will implement this. Is there another way around this, except having to remodel our entire database structure?

13

@JonL - I was looking at the Postgres multi schema and my question is:

  1. Do you want just to give the default schema name with the connection so is used to all objects (easy)
  2. Or specify schema per query, so you can build queries cross schema’s (difficult and maybe not worth)
14

Hi @george I still don’t have the need to use multiple schemas. My only comment was that if possible and if implemented make it so the schema name will accept bindings to make it dynamic.

I can only think of multi-tenancy when speaking about schemas.

I don’t know what non-standard db designs people are using that have this need.

15

hi @George,

Personally I would like to see by query basis(option 2), but on connection basis would work I guess, just means that one has to make a connection for each schema they want to access.

16

Are you using multiple schema’s in one site/app?

17

yes

18

And do you have joins between the schemas?

Can you explain the background of why having multiple schemas?

19

I do have joins between schemas, but the end result is then pushed to a (martialized) views in public to make it available for Wappler but still keep the majority of the DB design intact.

The reason is because I split-up the modules in the app and also do this in the database. this choice has been made during the design, as it’s not known how big the database will become so wanted to keep it clear and have a more manageable overview in the DB, modules are being made constantly and thus also schemas.

I know that this is not normal practice, but I can see also a way that I would use this as multi tenant style of setup in my environment.