NodeJs with MySql using Sequelize

13 / Oct / 2014 by Kashish Gupta 8 comments

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

Let us quickly learn the things with the help of Examples and we will use Item Table to perform all CRUD operation:-

— Install Dependence first

[js]

npm install mysql

npm install sequelize

[/js]

Setting up the connection with mysql

[js]

//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’
});

[/js]

— Check connection status

[js]

//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’);
}
});

[/js]

Example-1 Create Table: Item using Node.Js

[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’);
}
});

[/js]

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

[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)
})
});

[/js]

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

[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);
});

[/js]

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

[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);
})
}
});

[/js]

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

[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);
});

[/js]

  • 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 similar CRUD operation. You can refer Sequelize docs to explore more.
FOUND THIS USEFUL? SHARE IT

comments (8)

  1. Arga Aditya

    You might want to update the tutorial. Most of the syntax are deprecated. For instance, Sequelize now use promise in sync. So, instead of sync.complete(), it is now using sync().then(function(){}).catch(function(error){})

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *