How to set schema with node-postgres

Question

I'm trying to query against a schema called DOCUMENT in my postgres db using node-postgres.

I can't seem to get a query to run against the specified schema.

This query runs fine directly against postgres using psql

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

This code produces the error below

const query = `SELECT * FROM "DOCUMENT".document_metadata m 
               LEFT OUTER JOIN "DOCUMENT".document_attributes a 
               ON a.document_id = m.id`
const metadata = await db.query(query)

Error

error: relation "DOCUMENT.document_metadata" does not exist
    at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:602:11)
    at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:399:19)
    at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:121:22)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

I've also tried this

const query = `SET search_path TO 'DOCUMENT';
               SELECT * FROM document_metadata m 
               LEFT OUTER JOIN document_attributes a 
               ON a.document_id = m.id;`

const metadata = await db.query(query)

which produces the error

error: relation "document_metadata" does not exist
    at Connection.parseE (/usr/src/app/node_modules/pg/lib/connection.js:602:11)
    at Connection.parseMessage (/usr/src/app/node_modules/pg/lib/connection.js:399:19)
    at Socket.<anonymous> (/usr/src/app/node_modules/pg/lib/connection.js:121:22)
    at Socket.emit (events.js:189:13)
    at addChunk (_stream_readable.js:284:12)
    at readableAddChunk (_stream_readable.js:265:11)
    at Socket.Readable.push (_stream_readable.js:220:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

Update When I run these same queries directly in psql I see like 10 rows with table names. When I run this through my node code I see no rows:

const metadata4 = await db.query('SHOW search_path;')
console.log('search_path after setting = ', metadata4.rows) // prints [ { search_path: '"DOCUMENT"' } ]

const tables = await db.query(`SELECT * FROM information_schema.tables where table_schema = 'DOCUMENT';`)
console.log('tables = ', tables.rows) // prints []

score 8 · Answer 1 · answered Jul 16 '19 at 15:56

8

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)

answered Jul 16 '19 at 15:56

stackdave

6,655
9
37
56

Just tried this, still getting the error that the table doesnt exist :/ – Vincent Buscarello Jul 15 '20 at 06:13
@VincentBuscarello maybe you has problem with user rights? – stackdave Jul 16 '20 at 20:29
nope, if I hardcode the schema name I can run the query, but im trying to pass in the schema name to the query dynamically. https://stackoverflow.com/questions/62907228/sanitizing-user-inputs-when-the-user-input-is-a-schema-name-in-node-postgres – Vincent Buscarello Jul 17 '20 at 02:55
Awesome @stackdave it works flawlessly. But, do we have any alternative way of settings schema globally like setting it up in config object ? – Sudarshan Jul 18 '22 at 11:22

Attila Molnar · Answer 2 · 2020-01-23T22:39:54.317

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

score 3 · Answer 3 · answered Apr 27 '22 at 11:34

3

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

answered Apr 27 '22 at 11:34

Drilla

109
8

score 2 · Answer 4 · answered Oct 14 '21 at 09:36

2

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

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

answered Oct 14 '21 at 09:36

vargen_

2,590
3
24
30

How to set schema with node-postgres

4 Answers4

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

Linked