An Introduction to Sequelize.Js

As a modern Java or C# developer you have been exposed to JavaScript in your day to day work, but did you know that you can use it on the server-side as well? If you want to use the same technology that we regularly use in client-side you can try Node.js. If you want to know how to use server side JavaScript to talk to database this article is for you. After reading this article you will able to create a simple Node.js application which can talk to the database by using the ORM Sequelize.js.

What is Node.js ?
Node.js is an JavaScript platform that can be used to develop server-side applications. Node.js is created by Ryan Dahl using Google V8 engine as the compiler. Node.js applications are designed to maximize throughput and efficiency, using non-blocking I/O and asynchronous events. Many JavaScript libraries have been introduced to Node.js to support server-side functionality. Sequelize.js is one JavaScript library among many that can be used to talk databases.

What is Sequelize.js ?
Sequelize.js is an ORM (Object/Relational Mapper) which provides easy access to MySQL, MariaDB, SQLite or PostgreSQL databases by mapping database entries to objects and vice versa. It has very powerful migrations mechanism that can transform existing database schema into a new version. It also provides database synchronization mechanisms that can create database structure by specifying the model structure.

Although Sequelize.js is new library, it have been battle tested. Currently web sites like Shutterstock, Clevertech, Metamarkets and Innofluence have announced that they are using Sequelize.js as ORM of their website.

Why do we need learn Sequelize.js ?
Since Node.js is build on the top of JavaScript. It has all the characteristics that JavaScript has. As web developers we are familiar with JavaScript so it is very easy to understand the Node.js and Sequelize.js.

Sequelize.js is an young ORM that has all features that any ORM has and it is being actively contributed by open source developers. Basically Sequelize.js has good support for database synchronization, eager loading, associations, transactions and migrations.

Another main advantage of Sequelize.js is it easy to test. we can use Mocha like frameworks to run tests. We can have two database one for tests and one for develop environments, as we will see in this article.

How to get started ?
We can dowload install Node.js form http://nodejs.org/ web site. Then run
mkdir PasswordTracker
cd PasswordTracker
npm init


This command will initialize node project in the PasswordTracker folder. It will ask certain details that related to application going to develop. The most important information it ask for is “entry point”. It is needed to provide location of the main JavaScript file that start the server. We usually give “server.js” or “index.js”.

Then run “npm install express --save" for install Express.js which is a minimal and flexible node.js web application framework, providing a robust set of features for building single and multi-page, and hybrid web applications.

Then we need to add the dependency of Express.js to our main JavaScript file. In this case we called it as “server.js”. To test over server we have add the simple routing function that return JSON object. Then we start server under port 5000.

// get dependencies
var app = require("express")();

// handle request and response
app.get("/";, function(req, res) {
    res.send({name:"Hello Wolrd"});
});

// initializing a port
app.listen( 5000);

After we have added this code to Server.js we can start server by running
npm start
Then output can be seen as JSON by visiting the http://localhost:5000
Since this article is about Sequelize.js let’s move on to it.

We can install sequelize.js by running
npm install sequelize --save

Then you can install connectors for the database by typing

npm install pg --save or npm install mysql --save

For this tutorial we use postgreSQl so I run “npm install pg –save”.
We need to create the database for the our application in PostgreSQl

You can check the database connection string by running following code.

// get dependencies
var app = require('express')();
var Sequelize = require('sequelize');

// sequelize initialization
var sequelize = new Sequelize("postgres://username:password@localhost:5432/dbname");

// check database connection
sequelize.authenticate().complete(function(err) {
    if (err) {
      console.log('Unable to connect to the database:', err);
    } else {
      console.log('Connection has been established successfully.');
    }
});

// initializing a port
app.listen(5000);

Data insertion and retrieval
There are in build functions to data insert and data retrieval. We can use create function to insert data into database.

// get dependencies
var app = require('express')();
var Sequelize = require('sequelize');

// sequelize initialization
var sequelize = new Sequelize("postgres://username:password@localhost:5432/dbname");

// model definition
var User = sequelize.define("User", {
    username: Sequelize.STRING,
    password: Sequelize.STRING
});

//sync the model with the database
sequelize.sync({ force: true }).success(function(err) {
    // insert new user
    User.create({
        username: "john",
        password: "a_secret";
    }).success(function(user) {
        // you can now access the newly created user via the variable user
        console.log(user);
    });
});

// initializing a port
app.listen(5000);

More details on model define: http://sequelizejs.com/docs/latest/models
More details on data insertion: http://sequelizejs.com/docs/latest/instances

In the above code, the call to sequelize.sync is used to sync the model with the database. The force option used to drop and recreate the database all the time we start the application. Although force true is bad for production code it is very use full for the integration tests.

Now we can use the find method to data retrieval.

User.create({
        username: "john",
        password: "a_secret"
    }).success(function() {
        User.find({
            where: { username: 'john' }
        }).success(function(john) {
            console.log('Hello ' + john.username + '!');
            console.log('All attributes of john:', john.values);
        });
    });

More details on data retrival: http://sequelizejs.com/docs/latest/models#finders

Expose as web service
we need to install body-parser to parse json from http request.
npm install body-parser --save

Now i create two function that process get and post then i bind it to the express to expose it as web service.

The req and res parameters of the function will be automatically passed by the Express.js

// get dependencies
var express = require("express");
var Sequelize = require("sequelize");
var bodyParser = require("body-parser");
var app = express();
app.use(bodyParser());

// sequelize initialization
var sequelize = new Sequelize("postgres://username:password@localhost:5432/dbname");
// model definition
var User = sequelize.define("User", {
    username: Sequelize.STRING,
    password: Sequelize.STRING
});

var createUser = function (req, res) {
    var newUser={
        username: req.body.username,
        password:req.body.password
    }
    User.create(newUser).success(function () {
        res.send(200);
    });
};

var getUser = function (req, res) {
    User.findAll().success(function (users) {
       res.send(users);
    });
};

sequelize.sync().success(function (err) {
    app.get("/users", getUser);
    app.post("/users", createUser);
    // initializing a port
    app.listen(5000);
});

Now we can create users by generating POST request using Fiddler.

Then we can list the created users as JSON by typing “http://localhost:5000/users” in the browser.

Refactor
To achieve separation of concerns we need to refactor above code little bit more. We can use Revealing Module Pattern to separate the database access logic. Then we can easily inject the test database connection when testing.

server.js
Remove all the data access logic and model definitions. Add dependency to the data access logic file with the injection of the sequelize database connection.

// get dependencies
var express = require("express");
var Sequelize = require("sequelize");
var bodyParser = require("body-parser");
var app = express();
app.use(bodyParser());

// sequelize initialization
var sequelize = new Sequelize("postgres://username:password@localhost:5432/dbname");
var userService= require("./userService";)(sequelize);

//sync the model with the database
sequelize.sync().success(function (err) {
    app.get("/users", userService.get);
    app.post("/users", userService.create);
    app.listen(5000);
});

model.js
Extract the model definitions to separate file.

var Sequelize = require('sequelize');

module.exports = function (sequelize) {
    var User = sequelize.define("User", {
        username: Sequelize.STRING,
        password: Sequelize.STRING
    });
    return {
        User: User
    };
};

userService.js
Logic for data access are separated to a different file. We can grow this as the data access layer grows.

module.exports = function (sequelize) {
    var model = require("./model")(sequelize);
    var User = model.User;
    return {
        create: function (req, res) {
            var newUser = {
                username: req.body.username,
                password: req.body.password
            }
            User.create(newUser).success(function () {
                res.send(200);
            });
        },
        get: function (req, res) {
            User.findAll().success(function (users) {
                res.send(users);
            });
        }
    };
};

Test driven development
Although we write code first we can use TDD (Test Driven Development). Since JavaScript is a dynamic language writing test is super easy. We can use duck typing methods to mock request and response.
First we need to install mocha and expect as dev dependencies.
npm install mocha --save-dev
npm install expect.js --save-dev
By default when we run mocha it looks test files inside the test folder, so we need to create test folder and add userService.spec.js to that folder.
Inside that file we need to add required dependencies. Then we can call the “describe” function with callback function. In side callback function we can call “beforeEach” function that runs before each test, so we have called “sequelize.sync” to provide cleaned database before each test.

var expect = require("expect.js");
var Sequelize = require("sequelize");
var sequelize = new Sequelize("postgres://username:password@localhost:5432/testdbname", {logging: false});
var model = require("./../model")(sequelize);
var userService = require("./../userService")(sequelize);

describe("userService", function () {
    var mockResponse = function (callback) { return { send: callback }; };
    var newUser = { username: "Johne", password:"imjohne" };

    beforeEach(function (done) {
        sequelize.sync({ force: true}).success(function () { done(); });
    });

    it("should find created users", function (done) {
        //arrange
        model.User.create(newUser).success(function () {
            //act
            userService.get({}, mockResponse(function (data) {
                //assert
                expect(data[0].username).to.eql(newUser.username);
                done();
            }))
        })
    });
    it("should create user", function (done) {
        //arrange
        var req = { body: newUser };
        //act
        userService.create(req, mockResponse(function (statusCode) {
            //assert
            expect(statusCode).to.eql(200);
            done();
        }))
    });
});

To run mocha test we can run
node node_modules\mocha\bin\mocha --watch
Migrations

we can initialize the migrations by running
node node_modules\sequelize\bin\sequelize --init

It will create the migration folder and config.json.we need to give database details on that config.json. Then we can add migration file by running

node node_modules\sequelize\bin\sequelize -c file_name

It creates a skeleton for typical migration file. We need add the table creation or change details to inside up method and add code that use to undo the change need to add down method.

module.exports = {
  up: function(migration, DataTypes, done) {
    migration.createTable('Users', {
        id: {
          type: DataTypes.INTEGER,
          primaryKey: true,
          autoIncrement: true
        },
        createdAt: {
          type: DataTypes.DATE
        },
        updatedAt: {
          type: DataTypes.DATE
        },
        username: { type: DataTypes.STRING,  allowNull: false },
        password: { type: DataTypes.DATE,    allowNull: false }
    }).complete(done);
  },
  down: function(migration, DataTypes, done) {
    migration.dropTable('Users').complete(done);
  }
};

We can run pending migration
node node_modules\sequelize\bin\sequelize -m
Undo last migration
node node_modules\sequelize\bin\sequelize -u

More details of migration: http://sequelizejs.com/docs/latest/migrations

Conclusion

In this article I have showed how you can use Sequelize.js in a Node.js application. Source code of the application is available on github at https://github.com/milinaudara/PasswordTracker. I have pushed each step as a commit. The next time i’ll write about more advance topics on Sequelize.js and Node.js. I hope this article has inspired you to try out Node.js and Sequelize.js for great server side fun.