How to dynamically connect to databases in Sails.js

Question

I am rebuilding a legacy PHP application using nodejs and I choose sailsjs after about a week of exploration. Everything was fine until I realised that in sails you only have access to session variables in your controllers. Thus, I cannot make dynamic connections with the model.

The legacy app uses PHP session global variables to connect to different postgresql databases after authentication and I can't seem to find a work around on the web. I have seen similar questions on sails around the web. Any node/sails/database geeks around?

I am thinking of using sails for authentication since it already works and using expressjs to implement services that my sails app would then call by sending db connection params in some form of microservice architecture.

Currenttly running sails on localhost:1337 and my express on localhost:3000. Is this a good way to go about?

what do you mean? you can set different connections in yourconfig/connections.js and set the connection for each model. — Luis González
– Luis González, Commented Jul 19, 2016 at 8:18
@LuisGonzález . I know about that, I have a connection for a database that is used for authentication(default database). After authentication, I need to connect to different databases to pull data based on the current user. In my model, how do I dynamically tell my models to connect to a particular database. Current, when I do console.log of my connections, it is showing the default database. — ArrowHead
– ArrowHead, Commented Jul 19, 2016 at 8:25

Luis González · Accepted Answer · 2016-07-19 09:06:07Z

1

I think you can't dynamically change the connection property defined in models. However you could try not setting any connection in models which connection will dynamically change, and set the default config connection depending on the user. Some thing like:

User.find(1, function(err, user){

    connection = select_connection_for_phones(user);

    // changing the model connection dinamically
    // You should not define any connection in the model.
    sails.config.models.connection = connection;
    Phone.find({user : user.id }, do_something_else);

});

That way, your select_connection_for_phones will receive the user and return the string connection, you set the global config connection for models, and after that you start the query. If your model Phone has no connection defined, it will use the default.

The inverse way, could be something like:

User.find(1, function(err, user){

    connection = select_connection_for_phones(user);

    // changing properties of the postgresql connection.
    // You must define all your models with this connection.
    sails.config.connections.postgresql.host = host_from_user(user);
    sails.config.connections.postgresql.port = port_from_user(user);
    Phone.find({user : user.id }, do_something_else);

});

In this case you should set the models with the same connections, and you dynamically change the connection's properties.

edited Jul 19, 2016 at 9:06

answered Jul 19, 2016 at 8:24

Luis González

3,6591 gold badge29 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ArrowHead Over a year ago

The problem is not the use of different drivers. I am using one driver (postgresql). I need database connections based on the current user. In sails if you don't specify "connection", in the model, it uses the default database. I want to dynamically be able to change that. based on the current authenticated user.

ArrowHead Over a year ago

Thanks for your suggestion, unfortunately, this doesn't resolve the issue. Even when I overwrite the connection properties, it still tries to pull data from the default database specified. I have looked at similar questions on stackover and around the web, it seems to be an open/common problem with sails. Thanks

ArrowHead · Accepted Answer · 2016-07-21 13:04:39Z

1

I am answering my own question here, similar questions have been asked here and none of the answers provided seems to work. Instead of using waterline in my models, I have used successfully the pg module,

$ npm install pg --save
var pg = require('pg');

in this way, I can dynamically pass the connection string from my controller to my models and disregard the waterline ORM.

I created a sails service to get and set the database connection string for each session.

Then for each query I pass the connection string stored in session around :

var sessionConnection = ConnectionsService.getSessionConnection(req,res);

  pg.connect(sessionConnection, function(err, client, done) {
     // do connection stuff and queries here

  });

Hope this helps others

edited Jul 21, 2016 at 13:04

answered Jul 19, 2016 at 12:19

ArrowHead

6096 silver badges17 bronze badges

3 Comments

chintan adatiya Over a year ago

have you found any better way around?

ArrowHead Over a year ago

I have since moved on from this project , but this solution worked for me.

chintan adatiya Over a year ago

yup this will require to do connection on each actions and we have close db connection also manually, that's why I was asking.

Fernando Franco · Accepted Answer · 2017-03-16 15:12:07Z

0

I create a sails adapter from sails-postgresql v0.11.4 with extras functions. https://www.npmjs.com/package/sails-postgresql-pp

answered Mar 16, 2017 at 15:12

Fernando Franco

532 silver badges6 bronze badges

Collectives™ on Stack Overflow

How to dynamically connect to databases in Sails.js

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related