feature: allow changing of "public" schema with env variable

[phated]

While trying to add Hasura onto an existing DB, I recently found that all tables within any schema other than public are prefixed with that schema's name. e.g. hasura_users

I think it'd be really helpful for adding Hasura to an existing DB if an environment variable could be set that would replace the default public schema as the unprefixed queries. e.g. HASURA_DEFAULT_SCHEMA=hasura which would then unprefix any tables in the hasura schema - making the above just users.

[jasonmccallister]

We recently ran into a requirement to use this same feature. However, when we looked at implementing Hasura on a shared RDS we realized that we have multiple schemas for multiple apps (hopefully Hasura).

It would be nice to support the following:

1. Allow setting the default schema using an environment variable (see above)
2. The hdb_catalog and hdb_views would also need to change to support a single database with multiple Hasura apps. This might not need an environment variable and should maybe use a namespace from HASURA_DEFAULT_SCHEMA var?

[hedgecheap]

Hi guys! Another year down the track. Any news on this feature?

[jozef-slezak]

@rakeshkky, it would really help to have HASURA_DEFAULT_SCHEMA=public. I believe it should be easy to implement. What is the chance of merging a pull request in case of contribution?

[michael-land]

Any updates?

[0x777]

@daniel-chambers Given the changes in graphql-engine since this issue has been created, I think it makes more sense for this to be per 'source' customization configuration. Essentially extend the SourceCustomization type with say something like default_schema (please feel free to name it more aptly)? When Nothing, it is interpreted differently by different backends - public on Postgres, dbo on SQL Server.

data SourceCustomization = SourceCustomization
  { _scRootFields :: !(Maybe RootFieldsCustomization),
    _scTypeNames :: !(Maybe SourceTypeCustomization),
    _scDefaultSchema :: !(Maybe Text)
  }
  deriving (Eq, Show, Generic)

[daniel-chambers]

I've done a bit of investigation into this issue and it's a bit more complicated than it seems. The default schema public is currently hardcoded and is used for a few things. One major thing is root field name generation (ie. do not include the schema in the name if it is the default), and the other major place is when the user specifies a database object without specifying the schema (ie via the metadata API, the default is assumed in these cases). There are other more minor places (such as in textual rendering of object references) too.

I think it's reasonable to assume that if the user customises the default schema, they would expect all the places we are assuming public would change to assume their specified schema.

Making the root field name generation drop a user specified schema from the name shouldn't be too difficult. Some more extensive changes will be required to use custom defaulting for database object references that don't specify a schema. This is because we currently hardcode public during JSON deserialisation, and we'd need to rework some of the types to remove this hardcoding in deserialization and read the default value from metadata configuration instead. This will probably be non-trivial, and some more investigation here is warranted.

@0x777 and I also discussed the best location for the default schema configuration value (re his post above). We now think that the best place for the default schema property is in the Backend-specific SourceConnConfiguration type. This is because not all backends may have the concept of a schema, but also because, given the default schema's use outside of the GraphQL schema (ie. in the DB object references), it more correctly belongs in the connection configuration than the source customization structure.

[ivan-kleshnin]

I think it'd be really helpful for adding Hasura to an existing DB if an environment variable could be set that would replace the default public schema as the unprefixed queries. e.g. HASURA_DEFAULT_SCHEMA=hasura which would then unprefix any tables in the hasura schema - making the above just users.

Instead of adding new variables it may be more convenient to support schema query param at connection URLs:

HASURA_GRAPHQL_METADATA_DATABASE_URL: postgresql://username:pass@hostname:5432/database?schema=hasura
PG_DATABASE_URL: postgresql://username:pass@hostname:5432/database?schema=public

Prisma uses schema, PG supports currentSchema – I dunno why the difference and which name will be the best exactly.

[muei]

Any some update or todo?

[manasag]

As per Daniel's comment, this change is not straightforward; requires extensive changes to critical part of the code and hence not prioritised.

Hasura V3 has been re-architectured, to be a stateless engine, which doesn't require a database at all. V3 engine is built to take metadata file as an input on startup; and the startup time has been designed to be extremely fast, even with large metadata files. The metadata authoring now resides outside of the core engine. To know more about Hasura V3, please follow this guide.