Tuesday, August 04 2020

Sequelize ORM Tutorial with Node.js and Express

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

Sequelize Tutorial 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
});

Share Your Thoughts

phpexpertise

I’m Blogger and Programming Blog, Tutorials, PHP, MySQL, jQuery, Laravel, Wordpress and Codeigniter