Perform CRUD operations using SQL queries (Create, Read, Update, Delete).
CRUD is Create, Read, Update, and Delete. These four basic functions represent the essential types
of operations that can be performed on data stored within a database.
To perform operation on MySql database using node js we have to create a connection to databse
create in MySQL
1. Create a database
CREATE DATABASE dbtest;
2. Connection setting of Node.js application to a MySql Database
Following script connect a Node .js application to a MySql database(dbtest) running on
localhost, authenticates using the root user .
let mysql = require('mysql2');
let con = mysql.createConnection({
host: "localhost",
user: "root",
password: "System@123",
database: "dbtest"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
con.end(function(err) {
if (err) throw err;
console.log("Connection end");
});
After Creating connection to database we can perform CURD operation
3. Create Operation
In database , a create operation is used to define new database structures such as
Creating a new table
Creating a new database
Creating index ,views, triggers etc.
3.1 Create table
Syntax to create a table in mysql
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatypeconstraints,
column3 datatypeconstraints,
....
);
Creating a table using Node js
let mysql = require('mysql2');
let con = mysql.createConnection({
host: "localhost",
user: "root",
password: "System@123",
database: "dbtest"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
let createt = "CREATE TABLE student (id INT, name VARCHAR(255))";
con.query(createt, function (err, result) {
if (err) throw err;
console.log("Table created");
});
con.end(function(err) {
if (err) throw err;
console.log("Connection end");
});
Insert Operation
Insert operation is used to add new records into a database table.
Syntax :
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
You can add multiple records at once using syntax
INSERT INTO table_name (column1, column2)
VALUES (value1a, value2a),
(value1b, value2b),
(value1c, value2c);
Inserting a Record using nodejs
let mysql = require('mysql2');
let con = mysql.createConnection({
host: "localhost",
user: "root",
password: "System@123",
database: "dbtest"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
let sql = "INSERT INTO student (id, name) VALUES (1, 'Aman')";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("1 record inserted");
});
con.end(function(err) {
if (err) throw err;
console.log("Connection end");
});
Output
Inserting Multiple record using nodejs
let mysql = require('mysql2');
let con = mysql.createConnection({
host: "localhost",
user: "root",
password: "System@123",
database: "dbtest"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
let sql = "INSERT INTO student (id, name) VALUES ?";
let values = [
[2, 'Raman'],
[3, 'Naman'],
];
con.query(sql, [values], function (err, result) {
if (err) throw err;
console.log("Number of records inserted: " + result.affectedRows);
});
con.end(function(err) {
if (err) throw err;
console.log("Connection end");
});
Read Operation
Select statement is used to read data from a table in the database
Syntax
To read specific columns
SELECT column1, column2, ...
FROM table_name;
To read all the columns:
SELECT * FROM table name;
Reading data from table using nodejs
let mysql = require('mysql2');
let con = mysql.createConnection({
host: "localhost",
user: "root",
password: "System@123",
database: "dbtest"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
con.query("SELECT * FROM student", function (err, result, fields) {
if (err) throw err;
console.log(result);
});
con.end(function(err) {
if (err) throw err;
console.log("Connection end");
});
UPDATE Operation
Update statement is used to modify existing records
Syntax is
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Update operation using nodejs
et mysql = require('mysql2');
let con = mysql.createConnection({
host: "localhost",
user: "root",
password: "System@123",
database: "dbtest"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
let sql = "UPDATE student SET name = 'Chaman' WHERE id = 3";
con.query(sql, function (err, result) {
if (err) throw err;
console.log(result.affectedRows + " record(s) updated");
});
console.log("After update operation");
con.query("SELECT * FROM student", function (err, result, fields) {
if (err) throw err;
console.log(result);
});
con.end(function(err) {
if (err) throw err;
console.log("Connection end");
});
DELETE Operation
The DELETE statement is used to delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;
Delete operation using nodejs
let mysql = require('mysql2');
let con = mysql.createConnection({
host: "localhost",
user: "root",
password: "System@123",
database: "dbtest"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
let sql = "DELETE FROM student WHERE name = 'Chaman'";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Number of records deleted: " + result.affectedRows);
});
console.log("After Delete operation");
con.query("SELECT * FROM student", function (err, result, fields) {
if (err) throw err;
console.log(result);
});
con.end(function(err) {
if (err) throw err;
console.log("Connection end");
});
Delete a Table
You can delete an existing table by using the "DROP TABLE" statement:
Syntax
DROP TABLE table_name;
Drop Table using nodejs
let mysql = require('mysql2');
let con = mysql.createConnection({
host: "localhost",
user: "root",
password: "System@123",
database: "dbtest"
});
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
});
let sql = "DROP TABLE student1";
con.query(sql, function (err, result) {
if (err) throw err;
console.log("Table deleted");
});
con.end(function(err) {
if (err) throw err;
console.log("Connection end");
});
