Database schema with sails.js and sails-postgresql

Question

Is there any way to set database schema with sails-postgresql waterline adapter?

By default postgres adapter allways choose default public schema in database but I want to connect it to another schema.

For example I have database dev, schema test in database dev and table users in schema test. Now I want to select all data from table users, in sql syntax I can simply write:

SELECT * FROM test.users

How to make it work in sails ?

When I write a model that uses postgres adapter, method Users.find() will look for the table users in default public schema. I want to change it to look in schema test without interactions with my postgres database.

Is it possible?

Penetrant

Answer 1

There is support for this, although it is as-yet undocumented. You can set the schema name for a model using the meta.schemaName property, eg:

module.exports = {

  tableName: 'users',
  meta: {
     schemaName: 'test'
  },

  attributes: {
     ...
  }

};

Update

It turns out this functionality was essentially broken for several versions, but it has been revamped and released in Sails-Postgresql v0.11.0. The syntax is the same as above. The main caveat is that it will not work with multiple Waterline models sharing the same tableName.

Monkeypot

Did this work for you? It still writes to public schema, regardless of the schemaName set to the model. Am I missing anything? – Aerugo

check my answer Diego – Nancee

Answer 2

It appears that this is a bit buggy on the latest 1.0.3 version, but I found a way to accomplish it, by doing :

postgresql: {
    adapter: require('sails-postgresql'),
    url: 'postgresql://postgres:blablabla@localhost:5432/simplerp',
    schemaName: 'radius',
}

On your config/datastores.js file.

Peace, out!

Nancee