How to set schema in pg-promise

Question

I was searching the documentation of pg-promise specifically in the creation of the client. But I wasn't able to find the option to set the default schema to be used in the connection, it always uses public schema. How do I set it?

Answer 1

Normally, one sets the default schema(s) for the database or the role, as explained here:

• Permanently Set Postgresql Schema Path

It is only if you want to do it without persisting the change, you might want to set the schema(s) dynamically, just for the current process.

You can do so within pg-promise, by overriding event connect, and then executing the required query directly, for each 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);

And starting from version 8.3.0, the library supports option schema within Initialization Options:

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

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

making it easier to set the dynamic schema(s).

Examples

• Making your own schema visible along with the default public schema:

  const initOptions = {
      schema: ['public', 'my_schema'] /* make both schemas visible */
  };
  
  const pgp = require('pg-promise')(initOptions);
  

• Using the callback to set schema based on the database context (see Database constructor):

  const initOptions = {
      schema: dc => {
          if(dc === /* whatever database context was used */) {
              return 'my_schema'; /* or an array of strings */
          }
          /* other provisions, if multiple databases are used. */
  
          /* or can return nothing, if no schema change is needed. */
      }
  };
  
  const pgp = require('pg-promise')(initOptions);
  

UPDATE

Version 8.4.0 replaced parameter isFresh with useCount for event connect, so it is best to just use the latest version and option schema.