Node.js And MySQL Simple CRUD Examples

To create database in mysql via node.js:

CREATE DATABASE mydb
var mysql = require(‘mysql’);
var con = mysql.createConnection({
 host: “localhost”,
 user: “yourusername”,
 password: “yourpassword”
});
con.connect(function(err) {
 if (err) throw err;
 console.log(“Connected!”);
 con.query(“CREATE DATABASE mydb”, function (err, result) {
 if (err) throw err;
 console.log(“Database created”);
 });
});

To create table in mysql via node.js:

CREATE TABLE ‘CUSTOMERS’
var mysql = require(‘mysql’);
var con = mysql.createConnection({
 host: “localhost”,
 user: “yourusername”,
 password: “yourpassword”,
 database: “mydb”
});
con.connect(function(err) {
 if (err) throw err;
 console.log(“Connected!”);
 var sql = “CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))”;
 con.query(sql, function (err, result) {
 if (err) throw err;
 console.log(“Table created”);
 });
});

To put data into database:

const mysql=require(‘mysql’);
const db = mysql.createConnection({
host: ‘localhost’,
user: ‘root’,
password:’’,
database:’mydb’
})
con.connect(function(err) {
 if (err) throw err;
 console.log(“Connected!”);
 var sql = “INSERT INTO customers (name, address) VALUES (‘Company Inc’, ‘Highway 37’)”;
 con.query(sql, function (err, result) {
 if (err) throw err;
 console.log(“1 record inserted”);
 });
});

To read from database:

var mysql = require(‘mysql’);
var con = mysql.createConnection({
 host: “localhost”,
 user: “yourusername”,
 password: “yourpassword”,
 database: “mydb”
});
con.connect(function(err) {
 if (err) throw err;
 con.query(“SELECT * FROM customers”, function (err, result, fields) {
 if (err) throw err;
 console.log(result);
 });
});

To delete data from database:

DELETE OPERATION
var con = mysql.createConnection({
 host: “localhost”,
 user: “yourusername”,
 password: “yourpassword”,
 database: “mydb”
});
con.connect(function(err) {
 if (err) throw err;
 var sql = “DELETE FROM customers WHERE address = ‘Mountain 21’”;
 con.query(sql, function (err, result) {
 if (err) throw err;
 console.log(“Number of records deleted: “ + result.affectedRows);
 });
});

To update data in database:

UPDATE OPERATION
var mysql = require(‘mysql’);
var con = mysql.createConnection({
 host: “localhost”,
 user: “yourusername”,
 password: “yourpassword”,
 database: “mydb”
});
con.connect(function(err) {
 if (err) throw err;
 var sql = “UPDATE customers SET address = ‘Canyon 123’ WHERE address = ‘Valley 345’”;
 con.query(sql, function (err, result) {
 if (err) throw err;
 console.log(result.affectedRows + “ record(s) updated”);
 });
});