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.

score 1 · Accepted Answer · answered Jun 01 '18 at 12:38

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.

answered Jun 01 '18 at 12:38

George Bell

48
3

I've not really tested cause I'm no longer working with Sails, but I'll accept your answer in case someone still looking for some solution. Thanks anyway – Pavarine Jun 01 '18 at 16:35

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

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.

edited May 23 '17 at 12:22

Community

1
1

answered Sep 01 '15 at 18:13

sebmaldo

59
7

Sails.js is such a fantastic framework, but since I realized that I can't develop a real multi tenant app, I was forced to stop my works with it and switch to another framework. I think that the Sails.js devs should put some more effort in that. – Pavarine Sep 01 '15 at 23:58
I disagree, is a neat framework but is not just a ORM database adapter, you can replace waterline it if you want, and that just make it really powerfull and decopled. – sebmaldo Sep 02 '15 at 00:09
I just remember this article, there is not a single strategy to develop a multi tenant data architecture, [link](https://msdn.microsoft.com/en-us/library/aa479086.aspx) – sebmaldo Sep 02 '15 at 00:30

score 0 · Answer 3 · answered Oct 03 '19 at 16:01

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.

Sails.js - Postgresql Adapter multiple schemas

3 Answers3