[转] An Introduction to Sequelize.Js
http://docs.sequelizejs.com/en/latest/
https://github.com/sequelize/express-example
http://www.html-js.com/article/Front-end-mass-website-architecture-Sequelize-a-powerful-ORM-framework-for-NodeJS-object-relational-mapping
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.
1
2
3
4
5
6
7
8
9
10
|
// 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
// get dependencies var app = require( 'express' )(); var Sequelize = require( 'sequelize' ); // sequelize initialization // 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
// get dependencies var app = require( 'express' )(); var Sequelize = require( 'sequelize' ); // sequelize initialization // 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.
17
18
19
20
21
22
23
24
25
26
27
|
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
// get dependencies var express = require( "express" ); var Sequelize = require( "sequelize" ); var bodyParser = require( "body-parser" ); var app = express(); app.use(bodyParser()); // sequelize initialization // 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
// get dependencies var express = require( "express" ); var Sequelize = require( "sequelize" ); var bodyParser = require( "body-parser" ); var app = express(); app.use(bodyParser()); // sequelize initialization 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.
1
2
3
4
5
6
7
8
9
10
11
|
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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.