migration library: specify schema without search path DSN?

[nexovec]

Hi,
is it possible to make migrate do its migration inside a postgres schema and then connect to it with a client without actually spawning an entire new pgxpool(with search_path in the DSN string)?

[bgentry]

Search path is currently the only way for the migrator to execute migrations against an alternate schema. I think to do otherwise we would probably need to evolve some sort of string interpolation for schema into our migrations. Might be something we tackle when trying to solve alternate schemas more broadly (there are still a number of other compatibility issues mostly stemming from sqlc not offering a way to inject a schema name dynamically).

[nexovec]

@bgentry I think you can do this without modifying existing queries altogether using SET search_path TO <schema> statement (relevant postgres docs). Potentially you wouldn't have to even modify any existing queries with this. I just verified it also affects CREATE/ALTER TABLE. So you could just do that before you migrate and during client tx initialization.

[bgentry]

That’s going to be a session level change which persists on the connection even after it’s returned in the database pool, right? My understanding is the only way to reliably dynamically change the schema for specific queries is to inject it into the query string.

[nexovec]

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not.

There is a way to make it scoped to a transaction, is that applicable?