-
Notifications
You must be signed in to change notification settings - Fork 923
Specify Postgres schema #2635
-
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
Beta Was this translation helpful? Give feedback.
All reactions
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...
Replies: 4 comments 6 replies
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
Yes, we actually do something like this in https://github.com/sqlc-dev/sqlc/blob/main/internal/sqltest/pgx.go#L59.
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
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.
Beta Was this translation helpful? Give feedback.
All reactions
-
Hi, could you elaborate more on the string replace step? In which phase you do that? I'm relatively new to sqlc and would like to do some multi-tenancy with shared instance, separate schema/database thing without using separate connection. Appreciate any pointers!
Beta Was this translation helpful? Give feedback.
All reactions
-
Beta Was this translation helpful? Give feedback.
All reactions
-
Oh never mind. I've figured it out myself. Sorry for bothering you all :)
*For anyone wondering, you can create a wrapper around DBTX interface generated by sqlc and implement the string replace there.
Beta Was this translation helpful? Give feedback.
All reactions
-
👍 4