An Introduction to Sequelize.Js

logoAs 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.
fedller
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.

Advertisements

4 thoughts on “An Introduction to Sequelize.Js

  1. This is a great introduction to Sequelize, which uses my favorite tools aka NodeJS and Postgres. There are so many tutorials with MongoDB, when Postgres is so much more powerful and the documentation on Sequelize needs reviewing, your post clearly fills a gap.

    I use pg.js, the pure javascript pg driver as being on windows it avoids me downloading unnecessary things that Microsoft forces you to use.

    Sequelize doesn’t support everything yet, but it would be great to see how we can fully exploit the different dataTypes of Postgres, notably arrays and Hstore and the fact that Postgres could be used both as a traditional Relational database and a document/key-value store

    Then Postgres has introduced Plv8js, the procedural language, so potentially we could have javascript all the way.

    I would be interested to hear your opinion on how or what would be the best way to create nested objects with Sequelize and Postgres, I presume using associations, but which ones and how?. In particular, I have been trying to reproduce these tutorials http://scotch.io/tutorials/javascript/easy-node-authentication-setup-and-local and http://sahatyalkabov.com/create-a-tv-show-tracker-using-angularjs-nodejs-and-mongodb/
    Your help/views would be appreciated! Keep up the good work!!

  2. Thanks for this wonderful Sequelize intro! As I am new to a stack comprised of MySQL, Express.js, AngularJS, and Node.js this will most likely prove to be very helpful on a web app I’m developing. Off I go to code with my new found knowledge!

    Thanks again and all the best!
    Cameron

  3. Code error on first code block. // get dependencies
    app.get(“/”;, function(req, res) {
    should be
    app.get(“/”, function(req, res) {
    without the ; semin-colon

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s