sails.js - I want to add DB connection dynamically after sails lift

Question

During sails lift I don't yet have all the connection information for my DB.

Is there a way to either have config values dependent on promises or dynamically create a connection after sails lift has completed?

I would obviously have to add a policy or hook to handle requests to routes needing the model if it wasn't available yet, but at this point I don't see how to even let the sails lift until I already know the connection info (it must be in the configs).

I'm hoping I'm missing a way to dynamically create connections and wire models to them.

Mannerheim

Answer 1

Yes; two things in sails.js allow you to do this. One currently exists, and one is upcoming.

1. https://github.com/sgress454/sails-hook-autoreload. This module watches for config file changes on disk and will re-load your ORM models when a file changes.

2. I am working on this exact feature right now, and my plan is to publish my work at the end of next week. I agree that it will be very useful.

The API will allow you to define new Models and Connections in the database on the fly. sails.js lifecycle callbacks handle updating the ORM and adapters and so forth. It is event-based and will allow you to manually fire events to update the ORM/Connections, like so:

• sails.emit('hook:dynamic-orm:reload')

Is this what you need?

Organize

If I'm understanding you right, then I think option 2 could be exactly what I'm looking for. In my case I'm trying to avoid storing the password on disk, so I'm not sure option 1 (autoreload) will suffice. That said, I'll take a look at how it's implemented to see if I may be able to use a similar technique. With the work you're doing for option 2 would I be able to provide most of the config data at lift, but then add the password in later? (without storing on disk) – Mannerheim

I'm not sure what you mean by "add the password in later". But overall yes; you can mix/match defining connections in the config files as well as storing them in the database. It'll be ready very soon – Organize

Thanks. I look forward to it. Please reply to this question once your done (even if it's buggy :)). I'd love to get my hands on it. – Mannerheim

Unfortunately, my team and I are no longer maintaining Sails.js. We've redirected our efforts toward github.com/trailsjs/trails – Organize

Answer 2

Update: In Sails v1.0 / Waterline v0.13, this can be accomplished by accessing the stateless, underlying driver; e.g. sails.getDatastore().driver. This can be used in any database adapter that supports the new stateless driver interface, including MySQL, PostgreSQL, and MongoDB.

---

Prior to Sails v1.0, this was not officially supported in Sails or Waterline directly, but depending on your use case there are a couple of good solutions for this. If your use case is a handful of dynamic connections for the purpose of development (e.g. in an auto-reload plugin), and you're willing to live on the edge, you can take advantage of a private API as an immediate-term workaround: sails.hook.orm.reload(). However you definitely don't want to use that in production since it literally flushes the entire ORM.

If you are going to be dealing with a larger number (let's say > 10 unique configurations) of runtime-dynamic datastore configurations during the lifetime of the running Node process, that's a different story. In that case, I would recommend using the relevant raw driver (e.g. https://github.com/felixge/node-mysql) to summon/release those dynamic connections from a pool directly via a service. You can still use your normal models in your app for connections which are static-- you will just be best off implementing dynamic database connections separately in your service. For example, if you were building a hosted version of phpMyAdmin you might use a lower-level NPM package to dynamically fetch information about users' tables, but you'd still probably want to have Account and Database models that refer to tables/collections stored in your own database.

A more integrated solution for Sails is in the works. This ability to tap into the raw connection lifecycle and access it from userland is a prerequisite for built-in transaction support, which is something we expect to land in Sails/Waterline some time in the second half of 2016. In the mean time, if you encapsulate your logic to summon/release connections via service methods as suggested above, you'll have a working solution for now and your business logic should be more or less future proof (when you upgrade, you'll just need to swap out the implementation in your service). Hope that helps!

Yaekoyael

By the way, if you're working on a scenario somewhere in the middle-- e.g. dynamically generated one-off (white-label) deployments with dynamic configuration-- then you can also implement a solution that preempts the configuration loader in Sails. @4lpine wrote up a great walkthrough of this solution here: ash.zi.vc/sails/2016/02/02/… – Yaekoyael

Here is an example of one way to go about this in Sails v1: github.com/balderdashy/sails-docs/blob/… – Yaekoyael

has any functionality added for this specific use case in sails? can you please provide dicument link. – Unrivalled

Answer 3

I have found a workaround for MySql DB

Important: In my case, I will be changing database but all database would have the same schema only difference is in their name and data they contain and make sure to add any error handling you need

In config/connection.js -------- disable Pooling

 mysql_database: {
   adapter: 'sails-mysql',
   host: 'localhost',
   user: 'root', //optional
   password: '12345', //optional
   database: 'db1', //optional
   pool: false
 },

Now navigate to

node_modules/sails-mysql/lib/connections/spawn.js

Add connectionObject.config = sails.SwitchDbConfig

connectionObject.config = sails.SwitchDbConfig

var conn = mysql.createConnection(connectionObject.config);
  conn.connect(function (err) {
    afterwards(err, conn);
  });

Now Finally Set sails.SwitchDbConfig form anywhere (service , controller ,etc) as

sails.SwitchDbConfig = { 
            pool: false,
            connectionLimit: 5,
            waitForConnections: true,
            adapter: 'sails-mysql',
            host: 'localhost',
            user: 'root',
            password: '12345',
            database: sails.DB_NAME,
            identity: 'mysql_database' 
          }

And at last if you find something wrong for needs to be updated .... please ping

Baxy

but you are making changes to node_modules. How do you plan to distribute those changes in a team or in multiple environments? – Limbate