Can I set the search_path (or default schema) for a connection in pg-promise?

Question

It would be nice if I could set the default schema to use for a connection using pg-promise.

What I've tried

I've seen that it was possible in some languages to add ?searchpath=<schema> to the connection string to set <schema> as the default schema for subsequent queries, but that does not work in pg-promise.

I've also tried something like this:

const database = pgp(connectionString); // connectionString defined earlier, obviously
database.none('SET search_path TO ${schema}`, { schema: '<schema>' });

...but that, too, seems not to affect following queries.

I also found this related question which doesn't have an accepted answer and in the comments seems to suggest either setting it at the user level, which I don't have the rights to do, or the above, which doesn't seem to work for me.

And I found this question that ended up having a different problem as root cause.

In other words: is there anything else I can try, or should one of the solutions I mentioned above work after all, and if so, how?

Answer 1

I also found this related question which doesn't have an accepted answer and seems to suggest the above in the comments, which doesn't seem to work for me.

Why didn't it? It is the right solution, setting the schema search path at the user level. You should persevere in that direction.

pg-promise does let you set the schema at run-time, within event connect, but you will end up executing an extra query on every fresh connection:

const schema = 'my_schema';

const initOptions = {
    connect: (client, dc, isFresh) => {
        if(isFresh) {
            client.query(`SET search_path TO ${schema}`);
        }
    }
};

const pgp = require('pg-promise')(initOptions);

The code example above is a bit of a hack that pg-promise allows, and not a common practice of using the library, as it executes a query against the driver directly, and without any error handling, because handling error there won't have any effect for tasks or transactions. So you have to be careful to avoid executing any queries here that may emit an error.

UPDATE-1

Version 8.3.0 of the library started supporting this feature automatically:

const initOptions = {
    schema: 'my_schema' // can also be an array of strings
};

const pgp = require('pg-promise')(initOptions);

UPDATE-2

Version 8.3.2 started supporting also a callback function that takes the database context (see Database constructor), so it can return the schema(s) depending on the context, making it possible to correctly set schema when working with different databases in one module.

const initOptions = {
    schema: dc => {
        if(dc === /* whatever database context was used */) {
            return 'my_schema';
        }
        // other provisions, if multiple databases are used.

        // or can return nothing, if no schema change is needed.
    }
};

const pgp = require('pg-promise')(initOptions);

UPDATE-3

Version 8.4.0 replaced parameter isFresh with useCount for event connect, so it is the easiest for dynamic schema(s) to just use option schema.