Node.js MySQL Delete Example

In the post Node.js MySQL Update Example we saw examples of updating records in a table using Node.js and MySQL. In this post we'll see examples of deleting records from table using Node.js and MySQL. We'll be using the Promise Based API provided by MySQL2 library and a connection pool to connect to database in the examples provided here.

Table used

Table used for the example is Employee table with columns as- id, name, age, join_date.


CREATE TABLE `node`.`employee` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `join_date` DATE NOT NULL,
  `age` INT NULL,
  PRIMARY KEY (`id`));

Node.js delete record MySQL example

We'll keep the DB connection configuration in a separate file that can be used wherever it is needed by importing it.

util\database.js

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
    host: 'localhost',
    user: 'root',
    password: 'admin',
    database: 'node',
    waitForConnections: true, 
    connectionLimit: 10,
  });

module.exports = pool;

As you can see pool object is exported here so that it can be used in other files.

app.js

In this file let's create a function to delete Employee record from employee table in MySQL DB.

const pool = require('./util/database');

async function deleteEmployee(id){
  // TODO Verify if emp with the same id exists or not.. 
  // For that select query to get by id can be used

  const sql = "DELETE FROM EMPLOYEE where id = ?";
  const values = [id];
    try{
      const conn = await pool.getConnection();
      const [result, fields] = await conn.execute(sql, values);
      console.log(result);
      console.log(fields);
      conn.release();
    }catch(err){
      console.log(err);
    }
}

deleteEmployee(1);

Important points to note here-

deleteEmployee() function is an async function as we are using async/await (Promise based API) rather than callback-based API.
We are using await with pool.getConnection() method.
deleteEmployee() function takes one argument where it expects an id for the employee to be deleted.
Delete query is created as a parameterized query, where id is passed later. By having this kind of prepared statement makes our code more generic to be used with any id and also gives better performance.
By using array destructuring we get the returned values for result and fields.
fields variable contains extra meta data about results, if available.
result contains a ResultSetHeader object, which provides details about the operation executed by the server.
After the task, connection is released using conn.release() which means connection goes back to the pool.

On running the file-

>node app.js

ResultSetHeader {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  info: '',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 0
}

undefined

That's all for this topic Node.js MySQL Delete Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!

Related Topics

You may also like-

Tech Tutorials

Monday, August 26, 2024