[v2.0.0-alpha.3] Hasura fails to track tables with the same name from different databases

[jmsegrev]

We currently have a multi-tenant application were each tenant has its own Postgres database. This is exposed via a Graphql API, the URL path /tenant-id/graphql determines which tenant you are trying to reach. Currently in Hasura [v2.0.0-alpha.3] its not possible to track tables that share the same name from multiple databases. I'm assuming this will be supported (likely by prefixing the graphql operations), but what about this kind multi-tenant model, were the tenants share the same data structure, but need to be completely isolated (sometimes data has to be geographically allocated in different areas due to some laws). Is filtering the graphql database by header or URL param in the picture?

[amnaveenriaz]

@tirumaraiselvan Is there a PR/RFC for fixing the issue with tracking tables with same name from multiple databases. Most databases will have at least one table name that matches.

Similar to schema, can we add the databaseName_schemaName_tableName as the naming standard for all below routes.

Also provide the option to give Custom GraphQL Root Fields when creating a table itself and not just in Modify tab.

[piaste]

👍🏻

Unless I'm missing something, Hasura 2 cannot run multi-tenancy at all until this issue is addressed. (Multi-tenancy was the #1 use-case mentioned in the announcement blog post).

We tried setting unique name for all the routes in @amnaveenriaz 's screenshot above, but it wasn't enough. When we tried to track the same table from an additional database, there was still a conflict in the {schema name}_{table name}_connection field of the selection set, and we couldn't figure out a way to assign custom names to that particular field.

[cj]

I have just run into this issue myself, is there a known work around, or is there something in the works to address this?

[tirumaraiselvan]

Hey folks, we are trying to make the method mentioned here: https://github.com/hasura/graphql-engine/issues/6648#issuecomment-796389528 work for tables from different databases as well!

[dmoverton]

ddad668f0708aa8158fff0bf0f9c438e41d93e5f fixes the bug that was preventing having two tables with the same name in different database sources. However it is still necessary to manually set a custom table name and/or root field names for one of the tables.

My next task will be to modify the default root field names to include the database and schema names, as suggested in https://github.com/hasura/graphql-engine/issues/6648#issuecomment-796389528

[niranjan94]

This issue is currently a deal-breaker for us at canopy.cloud preventing us from considering Hasura for our tech-stack re-architecture. We have a multi-tenant setup where each of our users have a dedicated database (on an RDS PostgreSQL instance). And all of these databases have the same set of tables and hence we're currently unable to connect them to Hasura.

Would it be possible to provide an approximate timeline on when we could expect this issue to be fixed ? I see that ddad668f0708aa8158fff0bf0f9c438e41d93e5f has been merged into the v2.1 branch. So, am I right in assuming that this would be rolled out as a part of the 2.1 update ?

Thanks a lot :)

[dmoverton]

@niranjan94 https://github.com/hasura/graphql-engine/commit/ddad668f0708aa8158fff0bf0f9c438e41d93e5f was included in v2.0.0. This allows you to set a custom_name for an individual table. I think this should be sufficient for you to work around your issue with different databases having the same table name.

We are currently working on a more general solution where you can set customizations at the database source level so you can automatically apply them to all tables within a database. That is not quite ready for release, but should be ready soon.

[niranjan94]

@dmoverton oh that's great. Sorry I hadn't looked properly 😄 thought it will be available only in the next versions. Thanks for pointing it out.

Tried setting a custom name and I'm able to successfully connect to multiple databases :)

[kade-d]

Is filtering the database in the picture? I am also in the same boat as @niranjan94 where each user has its own "clone" of a database. For our use case, this would ideally be filtered based on a claim in a JWT.

In my case it is not feasible to rename each table for every user/table combination. I see that renaming at the database source level is an option. Is it possible for this to be combined with filtering?

[niranjan94]

Like @kade-d suggests, some sort of schema selection based on a claim would be awesome. Similar to @kade-d 's use case, we have a database per user containing same named tables. And while custom names work and we're able to connect to multiple databases, querying the API isn't a good experience from the client-side. As now due to the number databases we might have (thousands or 10s of thousands) each with its own set of tables, the GraphQL schema becomes huge and as a result impossible for the frontend clients to use the schema to power query validations or auto-complete on the client side.

Having a consistent schema but database selection based on claims would make this entire experience much smoother for multi-tenant use-cases like ours.

😄

[delcaos]

How do you access the screen from https://github.com/hasura/graphql-engine/issues/6648#issuecomment-796389528 ? I am trying to add another database with conflicting table names, and unable to figure out how to resolve it

[jordan-lumley]

Any updates on this?

[ChristianMP]

How do you access the screen from #6648 (comment) ? I am trying to add another database with conflicting table names, and unable to figure out how to resolve it

Custom GraphQL Root Fields under the modify tab for tables

[ChristianMP]

@tirumaraiselvan Is there a PR/RFC for fixing the issue with tracking tables with same name from multiple databases. Most databases will have at least one table name that matches.

Similar to schema, can we add the databaseName_schemaName_tableName as the naming standard for all below routes.

Also provide the option to give Custom GraphQL Root Fields when creating a table itself and not just in Modify tab.

And when tracking a table

[fluidai]

Was there any update on this issue to start including the database name in the graphql request to be able to manage multiple tenants each with their own postgres database and same table names

[ayazemregm]

We managed to use namespaces. It is actually graphql types that we needed to change to make this work. We usually have 1 server for testing multiple products. Later on we migrate the current data model to actual production. While it does not matter that much I really do not want to keep graphql types with prefixes. Prod db is standalone so types can be actual names instead of prefixed ones. Any workaround or tips migrating these?

[DilwoarH]

I managed to find a workaround for this. I ended up adding a namespace. This allows you to add the same database table names from each database

[intel352]

I managed to find a workaround for this. I ended up adding a namespace. This allows you to add the same database table names from each database

I'm testing Hasura Cloud right now. Multiple databases, each with schema named public, adding a table named blog to the second database errors saying relation already exists. This is despite namespacing being configured. This is not resolved.

[ayazemregm]

I managed to find a workaround for this. I ended up adding a namespace. This allows you to add the same database table names from each database

I'm testing Hasura Cloud right now. Multiple databases, each with schema named public, adding a table named blog to the second database errors saying relation already exists. This is despite namespacing being configured. This is not resolved.

You need to add prefix for schema names as well.

[SamirTalwar]

Closing this issue as the solution is to add prefixes using the established mechanisms.

In addition, Hasura DDN supports a much more flexible mechanism for this, allowing you to name things whatever you want. Please check it out!