Sequelize

Installation

npm install --save sequelize

drivers for databases:

 npm install --save pg pg-hstore #Postgres 
 npm install --save mysql2 
 npm install --save mariadb 
 npm install --save sqlite3 
 npm install --save tedious #Microsoft SQL Server

Establish Database Connection

in ./util/database.js

const Sequelize = require("sequelize");

const sequelize = new Sequelize("your_db_name", "your_username", "your_password", {
  dialect: "mysql",
  host: "localhost", //Optional 
});
module.exports = sequelize;

Define Models

in ./models/customer.js

const Sequelize = require("sequelize");
const sequelize = require("../util/database");

const Customer = sequelize.define("customer", {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true,
  },
  name: {
    type: Sequelize.STRING,
    allowNull: false,
  },
  email: {
    type: Sequelize.STRING,
    allowNull: false,
  },
});

module.exports = Customer;

in ./models/order.js

const Sequelize = require("sequelize");

const sequelize = require("../util/database");

const Order = sequelize.define("order", {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true,
  },
  total: {
    type: Sequelize.INTEGER,
    allowNull: false,
  },
});

module.exports = Order;

Create DB Tables from Sequelize Models

const sequelize = require("./util/database");

const Customer = require("./models/customer");
const Order = require("./models/order");

sequelize
  .sync()
  // .sync({force: true})
  .catch((err) => {
    console.log(err);
  });

Relations

const sequelize = require("./util/database");

const Customer = require("./models/customer");
const Order = require("./models/order");

Customer.hasMany(Order);

sequelize
  .sync()
  .catch((err) => {
    console.log(err);
  });

• The HasOne association
• The BelongsTo association
• The HasMany association
• The BelongsToMany association

onDelete and onUpdate

For example, to configure the ON DELETE and ON UPDATE behaviors, you can do:

Foo.hasOne(Bar, {
  onDelete: 'RESTRICT',
  onUpdate: 'RESTRICT'
});
Bar.belongsTo(Foo);

Many To Many

const Movie = sequelize.define('Movie', { name: DataTypes.STRING });
const Actor = sequelize.define('Actor', { name: DataTypes.STRING });
Movie.belongsToMany(Actor, { through: 'ActorMovies' });
Actor.belongsToMany(Movie, { through: 'ActorMovies' });

Lazy Loading example

const awesomeCaptain = await Captain.findOne({
  where: {
    name: "Jack Sparrow"
  }
});
// Do stuff with the fetched captain
console.log('Name:', awesomeCaptain.name);
console.log('Skill Level:', awesomeCaptain.skillLevel);
// Now we want information about his ship!
const hisShip = await awesomeCaptain.getShip();
// Do stuff with the ship
console.log('Ship Name:', hisShip.name);
console.log('Amount of Sails:', hisShip.amountOfSails);

Eager Loading Example

const awesomeCaptain = await Captain.findOne({
  where: {
    name: "Jack Sparrow"
  },
  include: Ship
});
// Now the ship comes with it
console.log('Name:', awesomeCaptain.name);
console.log('Skill Level:', awesomeCaptain.skillLevel);
console.log('Ship Name:', awesomeCaptain.ship.name);
console.log('Amount of Sails:', awesomeCaptain.ship.amountOfSails);

Writing Queries

In the following code snippet, we perform the following actions:

1. Insert a new customer
2. Insert a new order for this customer using the customerId foreign key.
3. Select all the orders for the current customer.

const sequelize = require("./util/database");

const Customer = require("./models/customer");
const Order = require("./models/order");

Customer.hasMany(Order);

let customerId = null;
sequelize
  .sync()
  .then((result) => {
    return Customer.create({name: "Chandler Bing", email: "cb@gmail.com"})
    console.log(result);
  })
  .then(customer => {
    customerId = customer.id;
    console.log("First Customer Created: ",customer);
    return customer.createOrder({total: 45});
  })
  .then(order => {
    console.log("Order is : ",order);
    return Order.findAll({ where: customerId});
  })
  .then(orders => {
    console.log("All the Orders are : ",orders);
  })
  .catch((err) => {
    console.log(err);
  });