How to set schema with node-postgres

just execute in a separated line:

db.query("SET search_path TO 'DOCUMENT';")

DOCUMENT is the name of your schema, then execute the normal query:

const query = `SELECT * FROM document_metadata m 
               LEFT OUTER JOIN document_attributes a 
               ON a.document_id = m.id;`

db.query(query)

if someone still interested in, you can do that using connection string and passing options, not just schema.

postgres://postgres:postgres@localhost:5432/my_db?options=-c search_path=my_schema

Maybe the uppercase is the problem. Did you tried with a lowercase schema name? Or add quotation marks around the table names:

const query = 'SELECT * FROM "DOCUMENT"."document_metadata" m 
               LEFT OUTER JOIN "DOCUMENT"."document_attributes" a 
               ON a.document_id = m.id'

Define the schema in an environment variable to the Client/Pool

Anyway, if you would like to define the schema in node js and no need to switching between different schemas, then this solution is a good choice. The default schema can come from environment variable.

const Pool = require('pg').Pool
const Client = require('pg').Client

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,
  user: 'postgres',
  host: 'localhost',
  database: 'postgres',
  password: 'postgres',
  port: 5432
})

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":"DOCUMENT"}' node app.js

I start from windows command prompt (sorry for that :) )

SET PG_OPTIONS={"search_path":"DOCUMENT"}
node app.js

When you create the DB URL, you can append the schema to it like so:

`postgres://${dbUser}:${dbPass}@${dbHost}:${dbPort}/${dbName}?schema=${schema}`

Issue #1123 in node-postgres, which has been open since 2016, is a request to add support to the connection request to specify the current schema.

This obviously doesn't solve your issue in the immediate term, but if this feature request ever gets implemented, it could be used assuming all queries should be against the given schema.