Configure database adapters in Sails

Aug 10, 2014


Hi all,

Today we will deal with configuring database adapters in Sails . It is easy to configure a database with Sails as it gives custom adapters . We have models with us , only thing we need to do is to create an adapter for MySQL,PostgreSQl, or mongoDB which ever we use . Here we will deal with configuring the adapter for MySQL.

Database Configuration

Before we start with adapters , we can configure the database for MySQL as follows .

I’ve my MySQL database with username : root and password: root .

ConfigureDBAdaptersinSails-1

Create a database as follows.

CREATE DATABASE maangatuts;

To use that database execute the following command.

USE maangatuts;

Once you do this the result will be as seen below.

ConfigureDBAdaptersinSails-2

Configure adapters in Sails

Now you can configure your adapter. For that open the file connections.js located in app/config/connections.js. You might have a common doubt that even before configuring the database we were able to use the apis and data were stored accordingly . How is this possible ? This is because sails use Waterline which has a localDisk storage which is provided to minimize efforts during development . On production you have to configure it with a database.

Here we use MySQL as storage database. On connections.js file you can see the following lines.

  someMysqlServer: {
    adapter: 'sails-mysql',
    host: 'YOUR_MYSQL_SERVER_HOSTNAME_OR_IP_ADDRESS',
    user: 'YOUR_MYSQL_USER',
    password: 'YOUR_MYSQL_PASSWORD',
    database: 'YOUR_MYSQL_DB'
  },

Change this to your configuration as follows.

  localhostMysqlServer: {
    adapter: 'sails-mysql',
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'maangatuts'
  },

Initial configuration for MySQL adapter is now complete . Now you have to go back to the models and configure each model with the adapter. We will now configure this adapter in our Employee model (app/api/models/Employee.js).

Add the following line to the file .

connection: 'localMysqlServer',

The file now looks like this.

/**
* Employee.js
*
* @description :: TODO: You might write a short summary of how this model works and what it represents here.
* @docs        :: http://sailsjs.org/#!documentation/models
*/

module.exports = {
  connection: 'localMysqlServer',
  attributes: {
  		
  		name:{
  			type:"string",
  			required:true,
		    minLength: 2
  		},
  		empnum:{
  			type:"string",
  			required:true
  		},
  		email:{
  			type:"email",
  			required:"true",
  			unique: true
  		}
  		
  }
};

Now try to lift the app . You may get an error as shown below.

ConfigureDBAdaptersinSails-3

This is because the sails-mysql adapter is not installed on your app.Install it by typing..

npm install sails-mysql@0.10.x

Now try lifting the app . The app will be lifted properly . Check the employees present there. You will get a null value there . See the screenshot below.

ConfigureDBAdaptersinSails-4

Now check the database whether the model is created as a table or not .

ConfigureDBAdaptersinSails-5

If you get a screen as shown above then the connection is done successfully. Now try adding employees.

ConfigureDBAdaptersinSails-6

Check whether it is updated in Database you have configured.

ConfigureDBAdaptersinSails-7

Yay! It works . You can add multiple adapters also. If you have two MySQL databases, you can try adding the code shown below.

module.exports.connections = {
  localMysql: {
    adapter: 'sails-mysql',
    user: 'root',
    host: 'localhost',
    database: 'someDbase'
  },
  remoteMysql: {
    adapter: 'sails-mysql',
    user: 'remoteUser',
    password: 'remotePassword',
    host: 'http://remote-mysql-host.com',
    database: 'remoteDbase'
  }
};

Note :If any connection to an adapter is used by a model, then all connections to that adapter will be loaded on sails.lift, whether or not models are actually using them. It is therefore good practice to comment out any connection configurations that you aren’t using!

Hey! that marks the end of this tutorial . We’ll meet in the next tutorial in Sails-Angular Series. Bye then :)