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();
        
        });
    });
};