NodeJS Postgres switch working schema

6
votes


I'm trying to connect to my postgres database.

Everything is working fine, but in my database I've got 2 schemas. One of them is created by default by PgAdmin (public), and the second one, ZTB, which I have created.

When I connect to my database using NodeJS, it by default connects to the "public" schema, and therefore I cannot query tables from "ZTB".

Is there any solution to switch the current working schema in the NodeJS server?

Regards

3
node.jspostgresqlschema
Welcome to stackoverflow, Rafael. Here's your guide on How to create a Minimal, Complete, and Verifiable example. Can you tell us which PG module you are using and the connection string you are forming? Relevant snippets of your implementation will help us help you. – benSooraj
set search_path ... postgresql.org/docs/current/static/… – a_horse_with_no_name
I resolved it myself. client.query("SET search_path TO 'ZTB';"); did the job. Update: exactly as @a_horse_with_no_name said. – Rafael
@Rafael can you post your comment as an answer? – Matt Bond

3 Answers

1
votes

I found solution myself - client.query("SET search_path TO 'ZTB';"); did the job exactly as @a_horse_with_no_name said later.

0
votes

First of all, change your schema name to lower case. Having upper case may create problems.

Next, whatever you want to query just do [your schema name].[your table name], and you should be fine.

client.query("select * from ztb.[your table name]"))
0
votes

If you using pg-promise, then choosing the schema is available right out of the box.

Within Initialization Options, there is option schema that supports flexible values:

const pgp = require('pg-promise')({
    schema: ['public', 'ZTB'] // access tables from "public" + "ZTB"
});

And you can set schema even to a function that returns the schema(s).