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?

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?

• javascript
• postgresql
• pg-promise
• node-postgres

Share Follow edited Apr 17, 2018 at 16:15 vitaly-t 26k1717 gold badges127127 silver badges150150 bronze badges asked Jan 18, 2017 at 22:58 Emmanuel CamposEmmanuel Campos 72633 gold badges99 silver badges2424 bronze badges 5

• 1 Default schema is controlled by the server, not by the client, i.e. the library doesn't use public by default, it uses whatever is set as the default on the server. – vitaly-t Commented Jan 18, 2017 at 23:13
• Also see this: stackoverflow./questions/2875610/… – vitaly-t Commented Jan 18, 2017 at 23:20
• 2 You can set the schema search path at the user level: see answer http://stackoverflow./questions/2951875/postgresql-how-do-i-set-the-search-path-at-the-user-level – Tim Child Commented Jan 18, 2017 at 23:27
• Ah that's why it's not in the connection parameters. Ok thank you. The link you sent me was really helpful also.So my solution was to set the schema on server start using the SET search_path query. – Emmanuel Campos Commented Jan 18, 2017 at 23:28
• Alternatively, you can have the library inject the schema name automatically. See pg-promise-demo. – vitaly-t Commented Jan 18, 2017 at 23:32

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 8

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 so without persisting the change, you might want to set the schema(s) dynamically, just for the current process.

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. */
  
          /* can return null/undefined, if no schema change is needed. */
      }
  };
  
  const pgp = require('pg-promise')(initOptions);