Explicitly set user schema in Bookshelf for PG

Question

In our dev environment, each dev has their own schema to replicate the stage and prod databases. Need to be able to pass user schema as part of the client config. Here's the config...

"client": {
    "username":       "user_login",
    "password":       "user_password",
    "host":           "db_host",
    "port":           5432,
    "database":       "db_name",
    "dialect":        "pg",
    "schema":         "user_schema"
}

...and here's the instantiation...

var Knex = require('knex')({
             client: client.dialect,
             connection: {
                host: client.host,
                user: client.username,
                password: client.password,
                database: client.database,
                searchPath: client.schema
           });
var Bookshelf = require('bookshelf')(Knex);

Inspecting the Bookshelf and Knex objects, I can't determine anywhere which schema is being used. I had to dig into the SchemaCompiler_PG.prototype.hasTable method, where I happened to find it in an array on a query response object, which includes a property of table_schema, happily set to public. I tried to set it to my schema, but nothing succeeded.

So we set default search paths for each user login.

ALTER ROLE user_login SET search_path TO 'user_schema'

Inspecting the response object, table_schema was set to my search path. But when another dev tried to run the code on her machine (local instances of Node and PG), that response object was set to MY schema. We can find no way to manually set it.

No idea what kind of black magic that would be, but need to know how to set the user schema for Bookshelf/Knex/PG.

Answer 1

While I haven't been able to find an answer for the original question, how to set the PG schema, I was able to resolve the issue at hand. Turned out to be a permissions issue in PG. Ensure that all users had r/w perms in their respective search_path and all was well.

No error messages that I could find anywhere within Bookshelf, Knex or PG client pointed to this as a possible culprit, so hopefully this helps somebody.