need connection support currentSchema

[rendongsc]

Hope that through the configuration can be specified default schema, like this
var cn = {
host: 'localhost',
port: 5432,
database: 'MyDB',
user: 'user1',
password: 'pw',
currentSchema:'C0001'
};
thanks!

[alainib]

me too it will be great ! thanks

[joskuijpers]

You can set the schema using a query:
SET SCHEMA 'schemaname'.

I think it will be better if you do this yourself, instead that we code this query into the library.

@brianc opinion?

[bramkoot]

Fair point. Could you suggest at what point you should run that query? This should run for every new connection that is setup, but before any other queries are executed. On/pool/connect doesn't seem to support an async/callback function so I'm curious what the best approach would be.

[hoegaarden]

Hi @bramkoot,
There is https://gist.github.com/hoegaarden/47f38c9b65844d78dba7 - that's pretty old and probably should be adapted. However I used that exactly to run some stuff once for each (new) connection.
Maybe that helps ... ?

[bramkoot]

Thanks, that was a nice idea. I've made another implementation that works for now, but it feels very hacky. For anyone who is interested, this is what I used;

const oldVersion = pg.Pool.prototype._create
pg.Pool.prototype._create = function (cb) {
	oldVersion.apply(this, [(err, client) => {
		if (err) cb(err)

		// setup client
		client.query('SET search_path TO users')
			.then(_args => {
				cb(null, client)
			})
			.catch(cb)
	}])
}

I'm using this in conjunction with pg-async and that works as expected. I would still be very interested in a proper implementation.

[hoegaarden]

I guess @brianc is happy to review a pull request ;)

[charmander]

See also #1393 (comment) to set arbitrary options.

[guikubivan]

Not sure how you would use @charmander's solution to set the search path, but the next comment worked for me: #1393 (comment)

pool.on('connect', (client) => {
  client.query(`SET search_path TO ${schema}, public`);
});

[safi2510]

Try this:

https://stackoverflow.com/a/58326477/11584759

[vitaly-t]

@safi2510 I added my own answer there also ;)

[aksel]

Here's how we send various custom options to a client, following @charmander's example.

class EnhancedClient extends Client {
  getStartupConf() {
    if (process.env.PG_OPTIONS) {
      try {
        const options = JSON.parse(process.env.PG_OPTIONS);
        return {
          ...super.getStartupConf(),
          ...options,
        };
      } catch (e) {
        console.error(e);
        // Coalesce to super.getStartupConf() on parse error
      }
    }

    return super.getStartupConf();
  }
}

const pool = new Pool({ Client: EnhancedClient });

In this example, PG_OPTIONS is stringified JSON, e.g. provided via the command line, a PM2 ecosystem file, etc.

For example, PG_OPTIONS='{"search_path":"some_schema"}' node app.js

[PathAbhi]

How about simply setting process.env.PGOPTIONS="-c search_path=some_schema"?
This seems to be working for me (v8.3.3), but can someone confirm that all clients created by the pool would default to the same schema?

process.env.PGOPTIONS="-c search_path=some_schema"

const pool = new Pool();

[zachsa]

Hi, I am finding that when I create a client with a login that I have previously set the search path for, that client.query seems to ignore this when running queries. #2419

[yf-hk]

any updates?

[bhanson-techempower]

Here's another option:

const pool = new Pool({
  options: "-c search_path=some_schema",
});

[brianc]

what @bhanson-techempower said is how I handle this in our own apps. I haven't checked to see if current_schema is a connection option you can send over the protocol (I probably should) but its pretty easy to just set options to it and use it that way. If you wanna do a PR that adds support for that option w/ a test to cover it I'm 100% down to merge it!