Can we create db connections dynamically in sails.js?

[Aswathy Raju]

Hi ,

Is there any way to create db connections dynamically and then create a model , based on the input values that we provide dynamically 

eg .through form submission I would like to specify the different database hostname, table name and want to create sails models and apis .

Is that possible in sailsjs. Please help me

Thanks,

[Maxime Bret]

Did you look about connection setting for models ? You can handle several connections but I'm not sure it's enough for your needs. Do you need something really dynamic about your hostname etc ? 

[Aswathy Raju]

Hi  Maxime Bret,

Yes , I want to add  host name and all other connection info dynamically.

Is there any solution for this ?

Thanks

[Festus Yeboah]

Hello, Did you find a working solution for this problem? It seems to be a common problem with sails. I have several questions and I have hit the same wall. 

[kyle travelet]

I did something very similar on an express app.  I'll try to walk you through it as best as possible.  Hopefully you can port something like this to a sails app.  For the project that I needed this in I didnt use sails for this very reason and a few other.  Right tool for the job kind of thing.   Also there is a lot of logic missing below that I purposely cut out to keep it simple.  

1. Created a file named server.json.

2. Created route to add settings, /settings/addServer

router.post('/addServer', function(req, res, next) {
    var aes256 = require('nodejs-aes256');
    var config = require('../config/config');
    var serverFile = require('../config/server.json');
    var fs = require('fs');
    var AESKEY = config.nodejsaes256.KEY;
    var name, FQDN, port, username, password;


    name = req.body.name;
    FQDN = req.body.FQDN;
    port = req.body.port;
    username = req.body.username;
    password = aes256.encrypt(AESKEY, req.body.password);


    serverFile.push({
        name: name,
        FQDN: FQDN,
        port: port,
        username: username,
        password: password
    });


    fs.writeFile("./config/server.json", JSON.stringify(serverFile,null,4);
}

4. Then whenever you would read from the database you would do the opposite as above.  For example with mysql:

router.post('/doSomething', function(req, res, next) {
    var aes256 = require('nodejs-aes256');
    var config = require('../config/config');
    var serverFile = require('../config/server.json');
    var fs = require('fs');
    var AESKEY = config.nodejsaes256.KEY;
    var mysql      = require('mysql');


    var server = {};


    for ( var i = 0; i < serverFile.length; i++ ) {
        // You must pass serverName here from req.body or somewhere else
        if ( serversFile[i].name == serverName ) {
            server.name = servers[i].name;
            server.FQDN = servers[i].FQDN;
            server.port = servers[i].port;
            server.username = servers[i].username;
            server.password = aes256.decrypt(AESKEY, servers[i].password);
        }
    }


    var connection = mysql.createConnection({
      host     : server.FQDN,
      user     : server.username,
      password : server.password
    });


    connection.connect();


}

Hope this helps someone out there.  I love sails when I can use it, but sometimes its not the right framework.  There is no single framework that can do absolutely everything.

Cheers!

[mikermcneil]

The go-to answer for right now is here: http://stackoverflow.com/a/34845674/486547

That said, we've done some work on this recently, and it's planned for v1.  Most of our focus has been on using static datastores and getting dynamic access to connections from there (e.g. for transactions), but as a result, you can also dynamically create and destroy connection pools, called managers (see https://github.com/node-machine/driver-interface/blob/930d870f09de07eb5cd27b0f5e9b1f10449af0fa/machines/create-manager.js)

If you're willing to live on the edge a bit, you can actually try out this new way of doing things today (here's MySQL, for example: https://github.com/mikermcneil/machinepack-mysql/blob/0e5e8849cffe1486e9d65f5395ca2adf1843443a/machines/create-manager.js)