{"id":15741,"date":"2014-10-13T23:48:38","date_gmt":"2014-10-13T18:18:38","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=15741"},"modified":"2016-12-19T15:33:43","modified_gmt":"2016-12-19T10:03:43","slug":"nodejs-with-mysql","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/nodejs-with-mysql\/","title":{"rendered":"NodeJs with MySql using Sequelize"},"content":{"rendered":"

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

Let us quickly learn the things with the help of Examples and we will use Item Table to perform all CRUD operation:-<\/p>\n

— Install Dependence first<\/em><\/p>\n

[js]<\/p>\n

npm install mysql<\/p>\n

npm install sequelize<\/p>\n

[\/js]<\/p>\n

— Setting up the connection with mysql<\/em><\/p>\n

[js]<\/p>\n

\/\/Including dependency
\nvar Sequelize = require(&quot;sequelize&quot;);<\/p>\n

\/\/Setting up the config
\nvar sequelize = new Sequelize(‘your-database-name’, ‘db-username’, ‘db-password’, {
\n host: &quot;localhost&quot;,
\n port: 3306,
\n dialect: ‘mysql’
\n});<\/p>\n

[\/js]<\/p>\n

—\u00a0Check connection status<\/p>\n

[js]<\/p>\n

\/\/Checking connection status
\nsequelize.authenticate().complete(function (err) {
\n if (err) {
\n console.log(‘There is connection in ERROR’);
\n } else {
\n console.log(‘Connection has been established successfully’);
\n }
\n});<\/p>\n

[\/js]<\/p>\n

Example-1\u00a0<\/strong><\/em>Create<\/strong> Table: Item<\/strong> using Node.Js<\/p>\n

[js]<\/p>\n

\/\/Create Item Table Structure
\nvar Item = sequelize.define(‘Item’, {
\n id: Sequelize.STRING,
\n name:Sequelize.STRING,
\n description: Sequelize.STRING,
\n qty: Sequelize.INTEGER
\n});<\/p>\n

\/\/Applying Item Table to database
\nsequelize.sync({force:true}).complete(function (err) {
\n if(err){
\n console.log(‘An error occur while creating table’);
\n }else{
\n console.log(‘Item table created successfully’);
\n }
\n});<\/p>\n

[\/js]<\/p>\n

Example-2 Inserting<\/strong> data into\u00a0<\/em>Table: Item<\/strong> using Node.Js<\/p>\n

[js]<\/p>\n

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

\/\/Other way: Immediate insertion of data into database
\nsequelize.sync().success(function () {
\n Item.create({
\n id: 2,
\n name:’Cell Phone’,
\n description: ‘Sony’,
\n qty: 20
\n }).success(function (data) {
\n console.log(data.values)
\n })
\n});<\/p>\n

[\/js]<\/p>\n

Example-3 Reading<\/strong>\u00a0data from\u00a0<\/em>Table: Item<\/strong> using Node.Js<\/p>\n

[js]<\/p>\n

\/\/Reading All Data
\nItem.find({}).complete(function (err,data) {
\n console.log(data);
\n});
\n\/\/With where condition
\n Item.find({where:{name:’Laptop’}}).complete(function (err, data) {
\n console.log(data);
\n});<\/p>\n

[\/js]<\/p>\n

Example-4 Updating<\/strong>\u00a0data from\u00a0<\/em>Table: Item<\/strong> using Node.Js<\/p>\n

[js]<\/p>\n

\/\/Updating Laptop to Computer
\nItem.find({where:{name:’Laptop’}}).complete(function (err, data) {
\n if(err){
\n console.log(err);
\n }
\n if(data){
\n data.updateAttributes({
\n name:’Computer’
\n }).success(function (data1) {
\n console.log(data1);
\n })
\n }
\n});<\/p>\n

[\/js]<\/p>\n

Example-5 Delete<\/strong>\u00a0data from\u00a0<\/em>Table: Item<\/strong> using Node.Js<\/p>\n

[js]<\/p>\n

\/\/Delete All Computer Records
\nItem.find({where: {name: ‘Computer’}}).complete(function (err, data) {
\n if (err) {
\n console.log(err);
\n } else {
\n data.destroy({}).success(function (err, data) {
\n if(err){
\n console.log(err);
\n }else{
\n console.log(data);
\n }
\n })
\n }
\n console.log(data);
\n});<\/p>\n

[\/js]<\/p>\n