Sequalize

Introduction

• It is an object-relational mapping library that it does all sql command behind the scene to map the database table to the javascript object without writing sql command, but do database operation with help of helper function

• In general, It is needed to create a modal that maps to database table , and create the instance based on that, so that new record will be added into table

• It is also allowed to execute function to perform CRUD operation, but also can check the association between the table

Connect to mysql

// The connection is still established by using mysql2 library behind the scene

const Sequelize = require('sequelize');

const sequelize = new Sequelize('node-complete', 'root', 'nodecomplete', {
  dialect: 'mysql',
  host: 'localhost'
});

module.exports = sequelize;

Modal Definition

• Define the modal where the type should be matched with table schema

const Sequelize = require('sequelize');

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

const Product = sequelize.define('product', {
  id: {
    type: Sequelize.INTEGER,
    autoIncrement: true,
    allowNull: false,
    primaryKey: true
  },
  title: Sequelize.STRING,
  price: {
    type: Sequelize.DOUBLE,
    allowNull: false
  },
  imageUrl: {
    type: Sequelize.STRING,
    allowNull: false
  },
  description: {
    type: Sequelize.STRING,
    allowNull: false
  }
});

module.exports = Product;

Create table

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

// Create the table based on the modal definition if the mapped table doesn't exist
sequelize.sync();

CRUD Operation

const Product = require('../models/product');

// Fetch all products
Product.findAll(
    // {
        // where:{
            // ...
        }
    // }
).then(products =>{
    // ...
}).catch(err=>{
    // ...
});

// Find by id
Product.findById(prodId).then(product =>{
    // ...
}).catch(err=>{
    // ...
});


// Create
Product.create({
    title: title,
    price: price,
    // ...
})


// Update
Product.findById(prodId)
.then(product => {
      product.title = updatedTitle;
      product.price = updatedPrice;
      product.description = updatedDesc;
      product.imageUrl = updatedImageUrl;
      return product.save();
});

// Delete
Product.findById(prodId).then(product => product.destroy());

Associations

• Create the relationship between tables by creating foreign key automatically

const Product = require('./models/product');
const User = require('./models/user');
const Cart = require('./models/cart');
const CartItem = require('./models/cart-item');
const Order = require('./models/order');
const OrderItem = require('./models/order-item');


// One to one relation
User.hasOne(Cart);
// Create user id automatically to the cart table as a foreign key
Cart.belongsTo(User);
const user = User.create({...});
// Create new record with user id as a foreign key
user.createCart({..});
user.getCart();

// One to many relation
User.hasMany(Product);
// When user is deleted, the related products are also be deleted due to cascade policy   
Product.belongsTo(User, { constraints: true, onDelete: 'CASCADE' });
const user = User.create({...});
user.getProducts();
user.createProduct();

// Many to many relationship
// Cart item will be created , containing the key of cartid and product id
Cart.belongsToMany(Product, { through: CartItem });
Product.belongsToMany(Cart, { through: CartItem });
cart.getProducts();
cart.addProducts();
product.getCarts();