MySQL queries examples in node.js

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:

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

How to get Request Origin in Express.js?

How to get the Request Host in express.js?

Translate »