NodeJs : CRUD operations on a SQL database

Doing CRUD (CReate, Update, Delete) operations on a database is necessary part in a normal web application. Here is how it can be done using powerful node.Js framework.

First a connection has to be made in-order to communicate and execute queries on a database (sql database). Establishing a connection with a SQL data base with Node.Js is explained in my previous blog post .

After establishing the connection we just have to execute SQL queries on database and get the results.

Here the table we are changing is "tblstudents" and it has 3 columns "Id", "Name" and "TotalMarks"


Here is the SQL query for create that table in your database

CREATE TABLE tblStudents (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(50),
  totalMarks int(3),
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT INTO tblStudents (id, name, totalMarks) VALUES
(1, 'Jasmine', 57),
(2, 'Jay', 93),
(3, 'Jim', 72),
(4, 'Lesley', 68);

Create

var student = { Name: 'Ted', TotalMarks: 25 };
con.query('INSERT INTO tblstudents SET ?', student, function(err,res){
  if(err) throw err;

  console.log('Last insert ID:', res.insertId);
});

One Coolest thing in Node.Js is we can create our new entry as an object and we directly inject that object in to SQL query

Update

con.query(
  'UPDATE tblstudents SET TOTALMARKS = ? Where ID = ?',
  [47, 0],
  function (err, result) {
    if (err) throw err;

    console.log('Changed ' + result.changedRows + ' rows');
  }
);

When we do an Update on a table we replace needed details for query with "?" mark and pass required details as an array in to the second parameter of the query() method.

Delete

con.query(
  'DELETE FROM tblstudents WHERE id = ?',
  [0],
  function (err, result) {
    if (err) throw err;

    console.log('Deleted ' + result.affectedRows + ' rows');
  }
);

Make an Delete Query also same as making a Update query.