Sails.js - Postgresql Adapter multiple schemas

Question

I've been searching a lot about Sails.js multi tenancy capabilities and I know that such a feature is not yet implemented. My initial idea was to build multi tenant app by creating one database per tenant.

Since I realized that I can't do such a thing in Sails.js yet, I tried a different aproach by creating only one database ( POSTGRES ) but with lots of schemas, each one representing a tenant. My problem is that I can't/I dunno ( don't even know if that is possible in Sails/Postgres adapter ) how to dynamically ( on runtime ) define what schema a given object should query aganist, based on the logged user.

Has anyone faced a problem like this? How can I proceed?

Sorry for English and thanks.

Answer 1

In my experience adding in the model does not work. The only thing that worked for me was using the meta call to specify the schema.

await Users.create(newUser).meta({ schemaName: 'admin' });

A bit cumbersome, but it is working.

Hope this helps someone.

Answer 2

I thinks is an issue of the waterline sequel adapter, based in this answer.

The way to do it is add a property in the model

  meta: {
    schemaName: 'schema'
  },

but is not working, you can't define multiple schemas, only takes the user as an schema, if the property schema is set in true ins the config/models.js, the definition of a schema for every table is not working.

Answer 3

The clue is inside the sails-postgresql adapter code - several of its helpers include this bit:

var schemaName = 'public';
    if (inputs.meta && inputs.meta.schemaName) {
      schemaName = inputs.meta.schemaName;
    } else if (inputs.datastore.config && inputs.datastore.config.schemaName) {
      schemaName = inputs.datastore.config.schemaName;
    }

So indeed the driver is looking for a schema named public by default, unless a different value is provides via calls to meta() as described above, OR the schema name is configured application-wide.

To configure the schema name for all models, a schemaName property needs to be included in the configuration of the postgresql datastore, which occurs in datastore.js:

...
default: {
   adapter: 'sails-postgresql',
   url: 'postgresql://username:password@localhost:5432/your_database_name',
   schemaName: 'your_schema_name_here'
}

Once this is in place, you don't have to append meta({ schemaName: 'blah'}) to any of the queries. I struggled with this for a couple of days and have finally solved it in this manner.