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.

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


Translate »