CRUD in Node.js

It’s quite fun to learn stuff by examples, today I explored Node.js and made CRUD example.

In this post you will see very basic implementation of CRUD in Node.js, for those who don’t know what is Node.js. It is an open source server side environment. That’s allows to run Javascript on the server.

CRUD code example requires following node packages:

  • mysql
  • randomstring

Run following commands in project folder.

npm i randomstring
npm i mysql

Create a db.js file to place mysql connection configuration.

var mysql = require("mysql");
exports.dbConnection = function() {
  return mysql.createConnection({
    host: "0.0.0.0",
    user: "root",
    password: "hello-password",
    port: "3306",
    database: "test"
  });
};

Now let’s create crud.js that contains CRUD actions (CREATE/READ/UPDATE/DELETE)

var randomstring = require("randomstring");
var db = require("./db");

let con = db.dbConnection();

con.connect(function(err) {
  if (err) throw err;
});

function all() {
  let sql = "Select * from users";
  con.query(sql, function(err, users) {
    if (err) throw err;
    users.forEach((user, i) => {
      console.log(user);
    });
  });
}

function get(id) {
  let sql = "Select * from test.users where id=" + id;
  con.query(sql, function(err, users) {
    if (err) throw err;
    users.forEach((user, i) => {
      console.log(user);
    });
  });
}

function insert() {
  let username = randomstring.generate(12);
  var sql =
    "INSERT INTO users (username, email) VALUES ('" +
    username +
    "', '" +
    username +
    "@test.dk')";
  con.query(sql, function(err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
}

function update(id, username) {
  var sql = "UPDATE users SET username = '" + username + "' WHERE id =" + id;
  con.query(sql, function(err, result) {
    if (err) throw err;
    console.log(result.affectedRows + " record(s) updated");
  });
}

function remove(id) {
  var sql = "DELETE FROM users WHERE id =" + id;
  con.query(sql, function(err, result) {
    if (err) throw err;
    console.log("Number of records deleted: " + result.affectedRows);
  });
}

insert();
all();

update(1, randomstring.generate(12));
remove(1);

Users table create query:

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

About the author

Danyal

I'm working as programmer since 2002. Born, grew up, studied and worked in Pakistan now Im located in Denmark since 2006.

View all posts