Sequelize ORM Tutorial with Node.js and Express

In this tutorial explains about How to integrate Sequelize ORM in your application with node js.

What is Sequelize

Sequelize is a promise-based ORM for Node.js. It supports various databases like PostgreSQL, MySQL, MSSQL, and SQLite. This features are transaction support, relations and read replications.

Install Sequelize

We initialize a Node application and install Sequelize and MySQL adapter.

$ node -v
$ npm init

// Using NPM
$ npm install --save sequelize

Install one of the following database driver in your application.

$ npm install --save pg pg-hstore
$ npm install --save mysql2
$ npm install --save sqlite3
$ npm install --save tedious // MSSQL

Setting up a connection

After the installation steps, create an connection with following contents. Here we can create an connection in two ways.

First method, we can customize the connection pool in models/db.js.

const Sequelize = require('sequelize');
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql', //|'sqlite'|'postgres'|'mssql'
  operatorsAliases: false,

  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  },

  // SQLite only
  storage: 'path/to/database.sqlite'
});

Second one you can simply use a connection uri in models/db.js

const Sequelize = require('sequelize');
const sequelize = new Sequelize('mysql://user:[email protected]:3306/dbname');

Test the connection

You can use the .authenticate() function like this to test the connection.

sequelize
  .authenticate()
  .then(() => {
    console.log('Connection has been established successfully.');
  })
  .catch(err => {
    console.error('Unable to connect to the database:', err);
  });

Added above code at the end of the models/db.js file.

Create a Model

Models are defined with following contents in models/user.model.js

const { Sequelize, DataTypes } = require('sequelize');
module.exports = (Sequelize, DataTypes) => {
const User = Sequelize.define('user', {
  id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
  name: {
    type: DataTypes.STRING
  },
  email: {
    type: DataTypes.STRING
  },
 password: {
    type: DataTypes.STRING
  }
});
return User;
}

force: true will drop the table if it already exists. This method is recommended for development mode.

User.sync({force: true}).then(() => {
  // Table created
  return User.create({
    name: 'John',
    email: '[email protected]',
    password: '123456'
  });
});

How to write a query

This helps you how to make the standard CRUD queries.

Simple INSERT Query

// Create a new user
const user = await User.create({ name: "John", email: "[email protected]", password: "12346" });
console.log("PK ID:", user.id);

Simple SELECT queries

// Find all users
const users = await User.findAll();
console.log("All users:", JSON.stringify(users, null, 2));

Simple UPDATE queries

await User.update({ password: "67895" }, {
  where: {
    email: '[email protected]'
  }
});

Simple DELETE queries

await User.destroy({
  where: {
    email: '[email protected]'
  }
});

Use below code for Truncate the table.

await User.destroy({
  truncate: true
});