In the Following, we have examples of INSERT, SELECT, UPDATE and DELETE queries necessary to operate all CRUD operations: Create, Read, Update and Delete. We will write our queries to get data or make changes to a table named “Employee”. To interact with the database, we will use connection Pooling but you can use the same queries when using single connection.
Related Articles:
- Automating Your MySQL Database with Triggers.
- How to use ON DELETE CASCADE in MySQL.
- MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler.
- Using MySQL Event Scheduler: A Complete Guide.
- How to create MySQL database using node.js.
- How to interact with MySQL database using async/await promises in node.js ?
- How to use Sequelize async/await to interact with MySQL database in Node.js.
- MANY-TO-MANY Association in MYSQL Database using Sequelize async/await with Node.js.
- ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js
- ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js.
- Complete JWT Authentication and Authorization System for MySQL/Node.js API.
- How to Build a Complete API for User Login and Authentication using MySQL and Node.js.
- How to store Session in MySQL Database using express-mysql-session.
- Complete JWT Authentication and Authorization System for MySQL/Node.js API
You can create using the following query:
CREATE TABLE Employee (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
position varchar(255) NOT NULL,
wage INT NOT NULL,
PRIMARY KEY (id));
MySQL query example: SELECT
- The first SELECT query gets all employees from table Employee.
- The second SELECT query gets a specific employee with an ID: id.
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: process.env.CONNECTION_LIMIT,
password: process.env.DB_PASS,
user: process.env.DB_USER,
database: process.env.MYSQL_DB,
host: process.env.DB_HOST,
port: process.env.DB_PORT
});
getAllEmployees = () =>{
return new Promise((resolve, reject)=>{
pool.query('SELECT * FROM Employee ', (error, employees)=>{
if(error){
return reject(error);
}
return resolve(employees);
});
});
};
getOneEmployee = (id) =>{
return new Promise((resolve, reject)=>{
pool.query('SELECT * FROM Employee WHERE id= ?', [id], (error, employee)=>{
if(error){
return reject(error);
}
return resolve(employee);
});
});
};
MySQL query example: INSERT
- We use INSERT query to post a new employee into the Employee table.
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: process.env.CONNECTION_LIMIT,
password: process.env.DB_PASS,
user: process.env.DB_USER,
database: process.env.MYSQL_DB,
host: process.env.DB_HOST,
port: process.env.DB_PORT
});
insertEmployee = (name, position, wage) =>{
return new Promise((resolve, reject)=>{
pool.query('INSERT INTO Employee (name, position, wage) VALUES (?, ?, ?)', [name, position, wage], (error, result)=>{
if(error){
return reject(error);
}
return resolve(result.insertId);
});
});
};
MySQL query example: UPDATE
- the UPDATE query is used to make changes to a specific employee with an ID: id.
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: process.env.CONNECTION_LIMIT,
password: process.env.DB_PASS,
user: process.env.DB_USER,
database: process.env.MYSQL_DB,
host: process.env.DB_HOST,
port: process.env.DB_PORT
});
updateEmployee = (name, position, wage, id) =>{
return new Promise((resolve, reject)=>{
pool.query('UPDATE Employee SET name = ?, position= ?, wage= ? WHERE id = ?', [name, position, wage, id], (error)=>{
if(error){
return reject(error);
}
return resolve();
});
});
};
MySQL query example: DELETE
- To delete definitely an employee with an ID: id from the Employee table we use the following query.
const mysql = require('mysql');
const pool = mysql.createPool({
connectionLimit: process.env.CONNECTION_LIMIT,
password: process.env.DB_PASS,
user: process.env.DB_USER,
database: process.env.MYSQL_DB,
host: process.env.DB_HOST,
port: process.env.DB_PORT
});
deleteEmployee = (id) =>{
return new Promise((resolve, reject)=>{
pool.query('DELETE Employee WHERE id= ?', [id], (error)=>{
if(error){
return reject(error);
}
return resolve();
});
});
};
ou might also like:
Automating Your MySQL Database with Triggers.
How to use ON DELETE CASCADE in MySQL.
MySQL + Event Scheduler: How to delete data from MYSQL table using Event Scheduler.
Using MySQL Event Scheduler: A Complete Guide.
Node.js + MySQL : Add Forgot/Reset Password to Login-Authentication System.
How to Build a Complete API for User Login and Authentication using MySQL and Node.js.
How to store Session in MySQL Database using express-mysql-session.
How to interact with MySQL database using async/await promises in node.js ?
How to use Sequelize async/await to interact with MySQL database in Node.js.
MANY-TO-MANY Association in MYSQL Database using Sequelize async/await with Node.js.
ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js
ONE-TO-ONE Association in MYSQL Database using Sequelize async/await with Node.js.
How to add Routes to insert data into MySQL database-related tables in Node.js API?
Example How to use initialize() Function in Node.js/Express API .
Why is Connection Pooling better than Single Connection?.
How to create MySQL database using node.js.
Nodemailer + Gmail: How to Send Emails from Node.js API using Gmail.
Node.js + Nodemailer : How to send Emails via SMTP with Nodemailer