NodeJs with MySql using Sequelize

13 / Oct / 2014 by Kashish Gupta 4 comments

This time I am going to explain a very basic CRUD operation on MySql database via NodeJs using Sequelize. Here Sequelize become’s bridge between NodeJs and MySql database, its a Object Relational Mapping Library written on javascript.

Lets quickly learn the things with the help of Examples and we are using Item Table to perform all CRUD operation :-

– Install Dependence first


npm install mysql

npm install sequelize

Setting up the connection with mysql


//Including dependency
var Sequelize = require("sequelize");

//Setting up the config
var sequelize = new Sequelize('your-database-name', 'db-username', 'db-password', {
    host: "localhost",
    port: 3306,
    dialect: 'mysql'
});

— Check connection status


//Checking connection status
sequelize.authenticate().complete(function (err) {
 if (err) {
    console.log('There is connection in ERROR');
 } else {
    console.log('Connection has been established successfully');
 }
});

Example-1 Create Table: Item using Node.Js


//Create Item Table Structure
var Item = sequelize.define('Item', {
    id: Sequelize.STRING,
    name:Sequelize.STRING,
    description: Sequelize.STRING,
    qty: Sequelize.INTEGER
});

//Applying Item Table to database
sequelize.sync({force:true}).complete(function (err) {
 if(err){
    console.log('An error occur while creating table');
 }else{
    console.log('Item table created successfully');
 }
});

Example-2 Inserting data into Table: Item using Node.Js


//There is two way of inserting data into database
//One way: Forming object from modal
var item1 = Item.build({
    id: 1,
    name:'Laptop',
    description: 'Acer 2340TL',
    qty: 23
});
//Inserting Data into database
item1.save().complete(function (err) {
 if (err) {
    console.log('Error in Inserting Record');
 } else {
    console.log('Data successfully inserted');
 }
});

//Other way: Immediate insertion of data into database
sequelize.sync().success(function () {
  Item.create({
     id: 2,
     name:'Cell Phone',
     description: 'Sony',
     qty: 20
  }).success(function (data) {
  console.log(data.values)
 })
});

Example-3 Reading data from Table: Item using Node.Js


//Reading All Data
Item.find({}).complete(function (err,data) {
    console.log(data);
});
//With where condition
    Item.find({where:{name:'Laptop'}}).complete(function (err, data) {
   console.log(data);
});

Example-4 Updating data from Table: Item using Node.Js


//Updating Laptop to Computer
Item.find({where:{name:'Laptop'}}).complete(function (err, data) {
  if(err){
    console.log(err);
  }
  if(data){
    data.updateAttributes({
    name:'Computer'
  }).success(function (data1) {
    console.log(data1);
  })
 }
});

Example-5 Delete data from Table: Item using Node.Js


//Delete All Computer Records
Item.find({where: {name: 'Computer'}}).complete(function (err, data) {
   if (err) {
      console.log(err);
   } else {
      data.destroy({}).success(function (err, data) {
   if(err){
      console.log(err);
   }else{
      console.log(data);
   }
  })
 }
 console.log(data);
});

  • You can find the above code on GITHUB click me to view/download code and don’t forget to update config according to your mysql database.
  • There are many other ways to achieve these similar CRUD operation. You can refer Sequelize docs to explore more.
FOUND THIS USEFUL? SHARE IT

comments (4)

Leave a comment -