Getting Started with Sequelize for Nodejs Applications

ORM or Object Relation Mapping is a process of mapping between objects and relation database systems. An ORM acts like an interface between two system. ORM provide advantages for developers from basic ones like saving time and effort and rather focusing on business logic. The code is robust instead of redundant. ORM helps in managing queries for multiple tables in an effective manner. Lastly, an ORM (like sequelize) is capable to connect with different databases (which comes in handy when switching from one database to another).

Sequelize is a promise-based ORM for Node.js. Sequelize is easy to learn and has dozens of cool features like synchronization, association, validation, etc. It also has support for PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL. I am assuming you have some form of SQL database service started on your machine. I am currently using MySQL.

Sequelize is available via npm.

Copy
$ npm install --save sequelize

# And one of the following:
$ npm install --save pg pg-hstore
$ npm install --save mysql // For both mysql and mariadb dialects
$ npm install --save sqlite3
$ npm install --save tedious // MSSQL

Sequelize does setup a connection between the rest api/application and your SQL database. To setup basic connection between the two:

Copy
1const sequelize = new Sequelize('database', 'username', 'password', {
2  host: 'localhost',
3  //choose anyone between them
4  dialect: 'mysql' | 'mariadb' | 'sqlite' | 'postgres' | 'mssql',
5
6  // To create a pool of connections
7  pool: {
8    max: 5,
9    min: 0,
10    idle: 10000
11  },
12
13  // For SQLite only
14  storage: 'path/to/database.sqlite'
15});

For the sake brevity, I like to divide code into modules. After all, the Unix philosophy of one program/module should do one thing is major part of the philosophy behind writing code in JavaScript (and using Node.js as a server side platform) these days.

I start with config.json/config.js file in the root of my application/api folder in which I define the general constraints needed to setup the connection with database:

Copy
1{
2  "development": {
3    "username": "root",
4    "password": "root",
5    "database": "articles",
6    "host": "localhost",
7    "dialect": "mysql"
8  },
9  "test": {
10    "username": "root",
11    "password": "root",
12    "database": "articles",
13    "host": "127.0.0.1",
14    "dialect": "mysql"
15  },
16  "production": {
17    "username": "root",
18    "password": "root",
19    "database": "articles",
20    "host": "127.0.0.1",
21    "dialect": "mysql"
22  }
23}

You can do this in your .env file if you like to follow that pattern. For more info on this see [dotenv](https://www.npmjs.com/package/dotenv).

After defining the configuration variables, in my models/ folder or where I define schema of tables in the database at application level, I create the connection in an index.js file:

Copy
1'use strict';
2
3const fs = require('fs');
4const path = require('path');
5const Sequelize = require('sequelize');
6const basename = path.basename(module.filename);
7const env = process.env.NODE_ENV || 'development';
8const config = require(__dirname + '/config.json')[env];
9const db = {};
10
11if (config.use_env_variable) {
12  const sequelize = new Sequelize(process.env[config.use_env_variable]);
13} else {
14  const sequelize = new Sequelize(
15    config.database,
16    config.username,
17    config.password,
18    config
19  );
20}
21
22fs.readdirSync(__dirname)
23  .filter(file => {
24    return (
25      file.indexOf('.') !== 0 && file !== basename && file.slice(-3) === '.js'
26    );
27  })
28  .forEach(file => {
29    const model = sequelize['import'](path.join(__dirname, file));
30    db[model.name] = model;
31  });
32
33Object.keys(db).forEach(modelName => {
34  if (db[modelName].associate) {
35    db[modelName].associate(db);
36  }
37});
38
39sequelize
40  .authenticate()
41  .then(() => {
42    console.log('Connection has been established successfully.');
43  })
44  .catch(err => {
45    console.log('Unable to connect to the database:', err);
46  });
47
48db.sequelize = sequelize;
49db.Sequelize = Sequelize;
50
51// Import Models such that I can use them in the api just by importing 'db'
52db.user = require('./user')(sequelize, Sequelize);
53db.admin = require('./admin')(sequelize, Sequelize);
54db.articles = require('./articles')(sequelize, Sequelize);
55
56module.exports = db;

It’s important to notice that I am exposing db object which contains every model/table schema definition. From now, I just have to import the db object to apply operations on specific database tables using it.

This setup can be auto-generated with the help of Sequelize CLI tool that helps in bootstrapping a new project in an effective manner (like the above) and handle database migrations directly from the terminal.

Sequelize is feature rich ORM for Node.js. It has a documentation that at times may not provide direct solutions to your problems but there always Github issues for that. What I like about is its Promise based control flow. Coming from NoSQL background (and using MongoDB), understanding Sequelize really took less time. Most of the query based models are quite similar to that in MongoDB (especially the CRUD operations). I am looking for a brighter, more improved documentation and ease of support from Sequelize.