About Sequelize

It is a promise-based NodeJS ORM for connecting with various databases like SQLite, MySQL, Postgre, etc. In this blog post we will focus on models and seeders only.

npm i sequelize

About Sequelize CLI

It is a command line tool for generating files, seeding records in database, etc.

npm i sequelize-cli

Let’s focus on generating model file, By the way, if you haven’t initialized sequelize project yet then go to the project repository and run sequelize init command. It will do the following actions described below.

  • It will create a config.json in config folder
  • It will create index.js in models folder
/************************** config.json *********************/
{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "operatorsAliases": false
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "operatorsAliases": false
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "mysql",
    "operatorsAliases": false
  }
}
/****************** index.js **********************/

'use strict';

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = require(__dirname + '/../config/config.json')[env];
const db = {};

let sequelize;
if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, config);
}

fs
  .readdirSync(__dirname)
  .filter(file => {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(file => {
    const model = sequelize['import'](path.join(__dirname, file));
    db[model.name] = model;
  });

Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;

module.exports = db;

Explaining the above code.

  • It creates a sequelize object with the configuration stored in config.js
    • sequelize = new Sequelize(process.env[config.use_env_variable], config);
  • It reads file in the current directory i.e models then imports it.

Let’s create a new model file using the cli command

sequelize model:generate --name UserAddressInfo --attributes addressType:string,addressLine:string,city:string,region:string,state:string,country:string,zip:string,refUserId:integer

This command will create two files, first in models and second in migrations.

/***************** useraddressinfo.js in models ****************/
'use strict';
module.exports = (sequelize, DataTypes) => {
  const UserAddressInfo = sequelize.define('UserAddressInfo', {
    addressType: DataTypes.STRING,
    addressLine: DataTypes.STRING,
    city: DataTypes.STRING,
    region: DataTypes.STRING,
    state: DataTypes.STRING,
    country: DataTypes.STRING,
    zip: DataTypes.STRING,
    refUserId: DataTypes.INTEGER
  }, {});
  UserAddressInfo.associate = function(models) {
    // associations can be defined here
  };
  return UserAddressInfo;
};
/*************** <random>-create-user-address-info.js in migrations *****/
'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('UserAddressInfos', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      addressType: {
        type: Sequelize.STRING
      },
      addressLine: {
        type: Sequelize.STRING
      },
      city: {
        type: Sequelize.STRING
      },
      region: {
        type: Sequelize.STRING
      },
      state: {
        type: Sequelize.STRING
      },
      country: {
        type: Sequelize.STRING
      },
      zip: {
        type: Sequelize.STRING
      },
      refUserId: {
        type: Sequelize.INTEGER
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('UserAddressInfos');
  }
};

Now go to index.js in models and insert a line db.UserAddress = require('./useraddressinfo')(sequelize, Sequelize); just before the module.exports = db; This line acts as a dependency injection where we are injecting a sequelize object (which holds the connection to the database) and the Sequelize module.

Few Commonly used DataTypes are

  • DataTypes.INTEGER
  • DataTypes.BIGINT
  • DataTypes.DATE
  • DataTypes.DATEONLY
  • DataTypes.DECIMAL
  • DataTypes.STRING
  • DataTypes.BLOB
  • DataTypes.BLOB(‘tiny’)
  • DataTypes.BLOB(‘medium’)
  • DataTypes.BLOB(‘long’)
  • DataTypes.BOOLEAN
  • And also multiple ranges type

Now create a seed file to put some dummy data

sequelize seed:generate --name UserAddressInfo

it will create a seed file in seeders folder

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    /*
      Add altering commands here.
      Return a promise to correctly handle asynchronicity.

      Example:
      return queryInterface.bulkInsert('People', [{
        name: 'John Doe',
        isBetaMember: false
      }], {});
    */
  },

  down: (queryInterface, Sequelize) => {
    /*
      Add reverting commands here.
      Return a promise to correctly handle asynchronicity.

      Example:
      return queryInterface.bulkDelete('People', null, {});
    */
  }
};

Now insert the below commands in up() function for creating two records inside the UserAddressInfos table.

await queryInterface.bulkInsert('UserAddressInfo', [{
        addressType: "permanent address",
        addressLine: "line 1",
        city: "city 1",
        region: "region 1",
        state: "state 1",
        country: "country 1",
        zip: "12345678",
        refUserId: "1"
      },
      {
        addressType: "permanent address",
        addressLine: "line 1",
        city: "city 2",
        region: "region 2",
        state: "state 2",
        country: "country 2",
        zip: "12345678",
        refUserId: "1"
      }
    ], {});

This post is just a small example of using sequelize. For more visit to https://sequelize.org/

This article has 1 comments

  1. Pingback: Sequelize NodeJS CRUD | Awesome Blogs