Sequelize: Configure for any Framework

Sequelize is the most common node ORM for databases like MySQL, MariaDB, Postgres, Microsoft SQL Server, SQLite, etc. In this article, we are going to set up sequelize for any node framework.

There are a few different ways we can do this configuration (though they are mostly the same or work in the same way). This config will work with ExpressJS or any NodeJS project or framework (if that framework has any specific way for database configuration, use that). Here is our preferred way of configuration for Sequelize.

• Install sequelize library in your project:

npm install --save sequelize

• Install the database driver you need. If you need MySQL, then use the following command:

npm install --save mysql2

If any other drive needed to check the following link:

http://docs.sequelizejs.com/manual/getting-started.html

• Create a new directory named models for storing the models in your project root directory.

mkdir models

• Create model files in the models directory. We are going to create two models here named user and article.

touch models/user.js

touch models/article.js

• Create a file named sequelize.js in your root directory. This will contain your main sequelize configuration code.

touch sequelize.js

• Open your sequelize.js in your preferred code editor and add the following code.

// sequelize.js

const Sequelize = require('sequelize');
const UserModel = require('./models/user');
const ArticleModel = require('./models/article');

const sequelize = new Sequelize('database_name', 'database_username', 'database_user_password', {
  host: 'localhost',
  dialect: 'mysql',
  pool: {
    max: 10,
    min: 0,
    acquire: 50000,
    idle: 10000
  }
});

const User = UserModel(sequelize, Sequelize);
const Article = Article(sequelize, Sequelize);

sequelize.sync().then(() => {
  console.log(`DB sync complete`)
});

module.exports = {
  User,
  Article,
};

• Define your models:

// models/user.js

module.exports = (sequelize, DataTypes) => {
  return sequelize.define('user', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    username: DataTypes.STRING,
    password: DataTypes.STRING,
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    createdAt: {
      type: DataTypes.DATE,
      field: 'created_at',
    },
    updatedAt: {
      type: DataTypes.DATE,
      field: 'updated_at',
    },
  });
};

// models/article.js

module.exports = (sequelize, DataTypes) => {
  return sequelize.define('article', {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true
    },
    title: DataTypes.STRING,
    content: DataTypes.TEXT,
    createdAt: {
      type: DataTypes.DATE,
      field: 'created_at',
    },
    updatedAt: {
      type: DataTypes.DATE,
      field: 'updated_at',
    },
  });
};

For details about the data types available, use the following link of Sequelize documentation:

http://docs.sequelizejs.com/manual/data-types.html

• Import from sequelize where you want to use that model. and use that model in that file.

Like for ExpressJS, if my router file for the user is at routes/user.js, then:

// routes/user.js

var express = require('express');
var app = express();
const { User } = require('../sequelize');

// getting the list of all users
app.get('/', (req, res) => {
  User
  .findAll()
  .then(userList => res.json(userList));
});

// creating user
app.post('/', (req, res) => {
  User
  .create(req.body)
  .then(userData => res.json(userData));
});

You can use the article model in the same way.