Node.js MySQL Update Example

In the post Node.js MySQL Insert Example we have seen how to do an insert in MySQL DB from Node.js application. In this post we'll see examples of updating records in a 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.

Refer Node.js - MySQL Connection Pool to get more information about using MySQL Connection Pool with Node.js

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 update 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 update Employee record in MySQL.

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

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

  const sql = "UPDATE EMPLOYEE set name = ?, join_date = ?, age = ? where id = ?";
  const values = [emp.name, emp.joinDate, emp.age, emp.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);
    }
}

//calling function with employee object
updateEmployee({name:'Ishan', joinDate:'2024-04-23', age:28, id:1})

Important points to note here-

1. updateEmployee() function is an async function as we are using async/await (Promise based API) rather than callback-based API.
2. We are using await with pool.getConnection() method.
3. updateEmployee() function takes one argument where it expects an employee object to be passed.
4. Using that object, appropriate values are passed to the prepared statement to update a record.
5. By using array destructuring we get the returned values for result and fields.
6. fields variable contains extra meta data about results, if available.
7. result contains a ResultSetHeader object, which provides details about the operation executed by the server.
8. 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: 'Rows matched: 1  Changed: 1  Warnings: 0',
  serverStatus: 2,
  warningStatus: 0,
  changedRows: 1
}

Undefined

If you want to get the number of rows which are updated you can get it using result.affectedRows from the result object.

You can check the DB to see the updated values.

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

---

Related Topics

1. Node.js MySQL Select Statement Example
2. Node.js MySQL Delete Example
3. Node.js - Connect to MySQL Promise API

You may also like-

1. Node.js path.basename() Method With Examples
2. __dirname and __filename in Node.js
3. Appending a File in Node.js
4. Creating HTTP server in Node.js
5. Service in Angular With Examples
6. Java Stream - count() With Examples
7. Why main Method static in Java
8. Named Tuple in Python