NodeJs with MySql using Sequelize

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.