As it can be seen in the following screenshot, the current project database (postgresql) named default has these 4 schema - public, appcompany1, appcompany2 and appcompany3.

They share some common tables. Right now, when I want to fetch data for customers, I write a query like this:

query getCustomerList {
    customer {
        customer_id
        ...
        ...
    }
}

And it fetches the required data from public schema.

But according to the requirements, depending on user interactions in front-end, that query will be executed for appcompanyN (N=1,2,3,..., any positive integer). How do I achieve this goal?

NOTE: Whenever the user creates a new company, a new schema is created for that company. So the total number of schema is not limited to 4.

CodePudding user response：

I suspect that you see a problem where it does not exists actually.

Everything is much simpler than maybe it seems.

A. Where all those tables?

There are a lot of schemas with identical (or almost identical) objects inside them.

All tables are registered in hasura.

Hasura can't register different tables with the same name, so by default names will be [schema_name]_[table_name] (except for public)

So table customer will be registered as:

• customer (from public)
• appcompany1_customer
• appcompany2_customer
• appcompany3_customer

It's possible to customize entity name in GraphQL-schema with "Custom GraphQL Root Fields".

B. The problem

But according to the requirements, depending on user interactions in front-end, that query will be executed for appcompanyN (N=1,2,3,..., any positive integer). How do I achieve this goal?

There are identical objects that differs only with prefixes with schema name.

So solutions are trivial

1. Dynamic GraphQL query

Application stores templates of GraphQL-queries and replaces prefix with real schema name before request.

E.g.

query getCustomerList{
   [schema]_customer{
   }
}

substitute [schema] with appcompany1, appcompany2, appcompanyZ and execute.

2. SQL view for all data

If tables are 100% identical then it's possible to create an sql view as:

CREATE VIEW ALL_CUSTOMERS
AS
SELECT 'public' as schema,* FROM public.customer
UNION ALL 
SELECT 'appcompany1' as schema,* FROM appcompany1.customer
UNION ALL
SELECT 'appcompany2' as schema,* FROM appcompany2.customer
UNION ALL
....
SELECT `appcompanyZ',* FROM appcompanyZ.customer

This way: no need for dynamic query, no need to register all objects in all schemas.

You need only to register view with combined data and use one query

query{
query getCustomerList($schema: string) {
   all_customer(where: {schema: {_eq: $schema}}){
     customer_id
   }
}

About both solutions: it's hard to call them elegant.

I myself dislike them both ;)

So decide yourself which is more suitable in your case.