[Feature Request] Switch between postgres schemas for multi-tenant use-cases

[nadavhames]

Is your feature request related to a problem? Please describe.
For multi-tenant applications, where there is one tenant per postgres schema, a common requirement is switching the schema based on the tenant. See kysley docs here

Describe the solution you'd like
An api to switch schemas so that in both the query builder api and also the prisma api the sql ends up qualified with the proper schema name/search path. For cross-schema queries, overriding this should work too as mentioned in kysely docs. This means that the db client instance and thereby the connection pool would be shared across all schemas, which is efficient.

A strategy might be that when switching to a schema, it affects prisma query api calls and the kysely query builder calls, but only in kysely can the schema be changed within a single query to do cross schema joins. To enable this in prisma api, an optional schema argument would need to be added somehow.

Describe alternatives you've considered
Create one postgres client per tenant and cache them in memory, disposing them when they go out of use. Each request gets a client along with its db connection pool depending on the tenant id. For low amount of tenants this is ok, but high usage will have a large number of open conn pools which may not be ideal.

I am open to thoughts, ideas on this topic and how best to support it here and in general.