Specify Postgres schema

[rickb777]

What do you want to change?

I am interested to use multiple schemas in Postgres. This can for example be used to handle multiple tenants in a single database. Although it's possible to share tables across tenants, it can be better to use more than one schema instead. This isolates each tenant's data from the others. It also reduces the performance costs of each tenant's db load from impacting other tenants because tables hold fewer lines.

Is there a way to use schemas via SQL statements in SQL?

Eg select * from tenant1.user,
where I can replace tenant1 accordingly?

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[rickb777]

Additionally, switching schemas would be a useful feature for integration tests that need to be isolated from each other. Creating temporary schemas, using them during a test run, then tearing them down afterwards is a useful technique to allow partitioning such that different tests don't interfere with each other.

[andrewmbenton]

Yes, we actually do something like this in https://github.com/sqlc-dev/sqlc/blob/main/internal/sqltest/pgx.go#L59.

[rickb777]

BTW the current workaround is to set up each tenant to have its own default schema in Postgres (i.e. per user role), and then use a separate connection pool with the credentials for each tenant.

This is clumsy and not ideal for the integration test use-casr.

[andrewmbenton]

You don't have to set up separate credentials to set up a unique schema for each tenant. But you will need separate connections per tenant if you want to scope access. See my answer below.

[andrewmbenton]

Unfortunately there is no way to parameterize a schema name in a query (see this SO thread).

But you can pass a schema name as a connection parameter, which will set the search_path. So presuming you have some way of setting up a separate schema per tenant, you can scope access to that schema at the connection level.

If you are trying to avoid separate connection pools per tenant, the only thing I can think of is to run SET SCHEMA 'tenant_x_schema_name'; before every query. But I haven't actually tried that and it seems error prone. (EDIT: I don't think this is safe, see @rickb777's comment below.)

In theory sqlc could probably generate code which does that (based on a configuration), but I'm not sure it's a good idea to add the complexity if most people prefer the connection-scoped search_path.

[rickb777]

When we've used this technique, we had to do a string replace on the queries, e.g.

SELECT * from public.account is mapped to SELECT * from tenant1.account

This is reliable, although it seems a bit clumsy.

I don't thing there's a reliable way to use the search_path without getting into concurrency issues because the search_path is a shared setting.

[andrewmbenton]

I think you are right about search_path. The only proper way to use it would be to set it on a connection pool and then maintain separate connection pools per tenant.

I don't think it's a good idea to have sqlc rewriting query strings at runtime due to the possibility of sql injection.