What is Sequelize?

Here is an official definition of Sequelize

 

Sequelize is a promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite, Microsoft SQL Server, Amazon Redshift, Snowflake’s Data Cloud, DB2, and IBM i. It features solid transaction support, relations, eager and lazy loading, read replication, and more.

I started learning about NodeJS Sequelize just now and had an idea to share it on Medium. So here we go

In this post, I will be doing the following set of tasks

1. Connecting NodeJS Application with MySQL DB
2. Creating Models
3. Building relationships between models(tables)
4. Adding dummy Data
5. Fetching data

So let's get started!

Here I'm taking an example of customer and order in the e-commerce application. One customer can have many orders this kind of relationship I'm adding for the DB table.

First of all, install Sequelize in your current project. Please note we will be using MySQL as a dialect for Sequelize so we have to install the mysql2 package as well.

npm install mysql2 sequelize  --save

Here is my project structure for this application

app.js
db
  -index.jsmodels
  - index.js
  - user.model.js
  - order.model.js
package.json

1. Connecting NodeJS Application with MySQL DB

Now create a file inside db/index.js inside your current project

const sequelize = require('sequelize');

const Sequelize = new sequelize.Sequelize(
    "<DB Name>",
    "<DB User>",
    "<DB Password>",
    {
        host: '<DB Host>',
        dialect: "mysql"
    });

module.exports = Sequelize;

2. Creating Models

Now create models. So first we will be creating a model for the User inside models/user.model.js

const sequelize = require('../db');
const Sequelize = require('sequelize');

const User = sequelize.define('user', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    name: {
        type: Sequelize.STRING
    }
});

module.exports = User;

Here we have taken an instance of Sequelize connection and created a table on that instance with define method. define method takes two parameters, the first parameter is the table name and the second parameter is the table entity. Here we have created two columns A. id: which is of type INTEGER, primary key, autoincrement, and B. name: this will be the name of the user. You can add as many columns you want but I'm making this table simple

Now create another model as discussed earlier for the order inside models/order.model.js

const sequelize = require('../db');
const Sequelize = require('sequelize');

const Order = sequelize.define('order', {
    order_id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    subtotal: {
        type: Sequelize.INTEGER
    }
});

module.exports = Order;

3. Building relationships between models(tables) & Adding dummy/Fetching Data with the Promise chain

Now it's time to build relationships between models. So we will be doing that in another file models/index.js

const sequelize = require('../db');
const Sequelize = require('sequelize');

const User = require('../models/user.model');
const Order = require('../models/order.model');

User.hasMany(Order);

sequelize
    .sync({ force: true })
    .then(conn => {
        return User.create({name: "Ajay"})
    })
    .then(user => {
        return Promise.all([
            Order.create({subtotal: 500, userId: user.id}),
            Order.create({subtotal: 600, userId: user.id}),
            Order.create({subtotal: 700, userId: user.id})
        ])
    })
    .then(order => {
        return User.findAll();
    })
    .then(users => {
        console.log(users)
    })
    .catch(err => {
        console.log(err.toString())
    })

We have first taken both models that we have created and built one to many relationship on the User & Order table.

After that, we have used the sync method on the DB instance. Its job is to create all the tables which we have defined so far. you can also provide {force: true} in sync method which will basically drop tables every time you run your application. Please note I have declared all model instances before calling the sync method that is necessary so tell Sequelize that here are our models and please run the database query for that.

In the end, I'm adding app.js and package.json to make sure I'm not missing anything to run this application

app.js

const express = require('express');
const db = require('./models');

const app = express();


app.listen(process.env.PORT || 3000, function () {
    console.log('app is listing to port 3000')
})

package.json

{
  "name": "nodejs-sequelize",
  "version": "1.0.0",
  "dependencies": {
    "express": "^4.17.3",
    "sequelize": "latest",
    "mysql2": "latest"
  }
}

now run your application with the node app.js command from the root directory of your project but make sure you have run npm install first to make sure package dependencies are installed.

npm install && node app.js

now check your DB it will have 2 tables with the columns with have defined in the sequelize model. You will get the following output

Now check your database

mysql> show tables;
+--------------------------+
| Tables_in_test_sequelize |
+--------------------------+
| orders                   |
| users                    |
+--------------------------+
2 rows in set (0.00 sec)

Great! we have successfully created an application to create/add/get data from the database with sequelize.

Thanks for reading.