Sails.js is a web framework used to create enterprise-grade Node.js applications. If you're new to Sails check out my getting started tutorial.
When using Sails you can easily hook up different models to different data stores. For example you can build a user model that maps to a MySQL database and a product model that hooks to a MongoDB collection. All of this is possible.
Waterline ORM/ODM
Before we get started we should understand that Sails comes with a powerful ORM/ODM called Waterline. This is important because Waterline is a data store-agnostic tool that makes it much simpler to interact with one or more databases. You won't need to know any vendor specific integration code for you to be able to query the database.
Waterline uses the adapter concept. It does all the translations of your query so it can be understood by your datestore. As of now the community has contributed adapters for nearly every type of database you can think of. You can find a list of adapters here.
Getting Started
For this purposes of this demo we'll be using the SANE stack. SANE stands for Sails, Any database, Node and Ember. We'll just be focusing on the Sails part. If you rather not use the 'sane-cli' you can use Sails and just replace sane with sails.
$ sane new MultiDB -d mysql
This will simply create a new front and backend application for us. The '-d mysql' will setup the adapter for MySQL. At this point it's a good idea to check to make sure everything is working by running the command below.
$ sane up
At the time of this writing you might see an error from the client about 'ember-data' not being properly formatted. That's a known issue in 'ember-cli' version 0.2.0 and shouldn't cause any other issues.
Although it should be fixed if you see an error on the Sails server side that looks like this 'lodash/Lang/isObject' simply delete the 'sails-hook-dev' folder inside the node_modules under server. Then install the npm again with 'npm install balderdashy/sails-hook-dev --save'.
After the application starts correctly you'll need to setup the database. We need to take a look at the models.js file.
//
module.exports.models = {
// server/config/models.js
/***************************************************************************
* *
* Your app's default connection. i.e. the name of one of your app's *
* connections (see `config/connections.js`) *
* *
***************************************************************************/
connection: 'mysql',
/***************************************************************************
* *
* How and whether Sails will attempt to automatically rebuild the *
* tables/collections/etc. in your schema. *
* *
* See http://sailsjs.org/#/documentation/concepts/ORM/model-settings.html *
* *
***************************************************************************/
migrate: 'safe' //for NoSQL this should be 'safe' and for postgres or MySQL 'alter'
};
Here is where you set the connection. The 'migrate' is very important. You have three options, 'safe', 'alter', or 'drop'. Drop will simply drop the whole database every time Sails is ran. Alter will try to auto-migrate columns and keep the existing data. Safe never tries to migrate your data and should be used for production data. Waterline will only try to alter your data if it finds a corruption so you shouldn't have to worry too much about this. Typically I keep the option at 'alter' unless I'm in production.
Let's setup our database connection.
...
// server/config/connections.js
...
mysql: {
adapter: 'sails-mysql',
host: 'localhost',
user: 'root',
password: 'password',
database: 'sane'
},
In the connections.js file you'll need to set the adapter information. That's the basic setup of your database. Next you'll need to add the model.
What About Multiple Data Stores?
The easiest way to deal with multiple data stores is to not use the models.js file at all. First go into your models.js file and comment out 'migrate' and 'connection'.
Next go into the connections.js file and setup all the connections you need, just as we did with the MySQL example earlier. The file stubs out all the connections for you so just change the information shown.
Sails allows for connections to be setup in individual models. For example say we wanted to create a post model that was going to use our MySQL database while we wanted to create a comments model using Redis.
We'll need to generate these models first.
$ sane g resource post name:string title:string date:date
$ sane g resource comment name:string comment:string
Let's edit the model that was generated in the server for the post. We simply need to add the 'connection' property that corresponds to the connection we created in our connection.js file.
// server/api/models/Post.js
module.exports = {
connection:'mysql',
attributes: {
name : { type: 'string' },
title : { type: 'string' },
date : { type: 'date' }
}
};
And now our Comment file.
// server/api/models/Comment.js
module.exports = {
connection:'redis',
attributes: {
name : { type: 'string' },
comment : { type: 'string' }
}
};
That's it! Now the Comment model will use the 'redis' connection while the Post model will use the 'mysql' connection.
I Don't Want To Setup A Model
What if the database we are connecting with has 20 different tables and we need to do a fairly sophisticated query with several joins? Maybe this is the only query we need to do. Should we spend a bunch of time creating all the models and associations in Sails? It may not be necessary. Waterline has provided us with a simple work around called .query().
If we use our query function we don't need to setup the model. We can just leave it blank. We can then go into our controller and add a new action that will trigger the query.
// server/api/controllers/PostController.js
module.exports = {
info: function(req, res) {
Post.query('SELECT * FROM SOME.DATABASE_TRAIL', function(err, recordset) {
console.dir(recordset);
return res.send(recordset);
});
}
};
In the example above we are using the Post model to do a simple SQL query. It will then be sent to the console and as a response back to the server as JSON. The sails server will return the response at '/api/v1/Posts/info'.
Questions?
I'm still learning Sails however if you have any questions please leave a comment below.
Image credit to Waterline