Implementation of Node connection to MySQL query transaction processing

D:\nodejs_mysql>npm init -y # Quickly create package management files

D:\nodejs_mysql>npm i mysql -D # Install mysql dependency package

const mysql = require("mysql");

// Create a mysql connection pool const pool = mysql.createPool({
  host: "127.0.0.1", // The address 127.0.0.1 for connecting to the database is the local mysql
  user: "root", // Username for connecting to the database root has the highest authority password: "", // Password for connecting to the database database: "apm", // Database name for operation port: "3306", // Port number for connecting to MySQL multipleStatements: true, // This item can be ignored if you want to execute multiple SQL statements at once });

function select() {
  // Request to connect to mysql
  pool.getConnection((err, connection) => {
    // Report an error if the connection is not successful if (err) throw err;
    // Get a connection object and call the query method to execute the SQL statement let sql = "select * from goods";
    // Run the SQL statement query The second parameter is the parameter required by the SQL statement, you can leave it blank connection.query(sql, (errors, results, fields) => {
      // Release the connection connection.release();
      // If there is an error when running the sql statement, throw an error if (errors) throw errors;
      console.log(results);
      console.log(fields);
    });
  });
}

select();

// index.js
// ... Ignore the previous code to create a MySQL connection pool function query(sql, params) {
  return new Promise((resolve, reject) => {
    pool.getConnection((err, connection) => {
      // Report an error if the connection is not successful if (err) return reject(err);
      // Get a connection object and call the query method to execute the SQL statement // Run the SQL statement query The second parameter is the parameter required by the SQL statement, which can be omitted connection.query(sql, params, (errors, results, fields) => {
        // Release the connection connection.release();
        // If there is an error in running the sql statement, throw an error if (errors) return reject(errors);
        resolve(results);
      });
    });
  });
}

query("select * from goods", null).then((result) => {
  console.log(result);
});
module.exports = {
  query,
};

// data.js
const index = require("./index.js");
var sql = "select * from goods";
index
  .query(sql, null)
  .then((result) => {
    // do anything ....
  })
  .catch((err) => {
    // error
    console.log(err);
  });

// index.js

// .... Part of the code to create a pool /**
 * mysql transaction processing * @param {Array} sqls sql statement to be executed * @param {Array} params corresponding to the parameters of the above sql statement * @returns {Promise} returns a Promise
 */
function transaction(sqls, params) {
  return new Promise((resolve, reject) => {
    pool.getConnection(function (err, connection) {
      // Connection failed promise returns failure directly if (err) {
        return reject(err);
      }
      // If the statement and parameter number do not match, promise returns failure directly if (sqls.length !== params.length) {
        connection.release(); // Release return reject(new Error("The statement does not match the passed value"));
      }
      // Start executing transaction connection.beginTransaction((beginErr) => {
        // Failed to create transaction if (beginErr) {
          connection.release();
          return reject(beginErr);
        }
        console.log("Start executing transactions, a total of " + sqls.length + " statements");
        // Return a promise array let funcAry = sqls.map((sql, index) => {
          return new Promise((sqlResolve, sqlReject) => {
            const data = params[index];
            connection.query(sql, data, (sqlErr, result) => {
              if (sqlErr) {
                return sqlResolve(sqlErr);
              }
              sqlReject(result);
            });
          });
        });
        // Use the all method to check the status of each promise in it Promise.all(funcAry)
          .then((arrResult) => {
            // If each sql statement is executed successfully, it will come to this point. Here, the transaction needs to be committed, and the previous sql execution will take effect. // Commit the transaction connection.commit(function (commitErr, info) {
              if (commitErr) {
                // Transaction submission failed console.log("Transaction submission failed: " + commitErr);
                // Transaction rollback, previously executed SQL statements are invalid connection.rollback(function (err) {
                  if (err) console.log("Rollback failed: " + err);
                  connection.release();
                });
                // Return promise failure status return reject(commitErr);
              }

              connection.release();
              // The transaction successfully returns the result of each sql execution as an array structure resolve(arrResult);
            });
          })
          .catch((error) => {
            // If one of the multiple SQL statements reports an error during execution, rollback directly connection.rollback(function () {
              console.log("sql execution failed: " + error);
              connection.release();
              reject(error);
            });
          });
      });
    });
  });
}
module.exports = {
  transaction,
};

// data.js
const index = require("./index.js");
var sqls = [
  "delete from goods where goods_id = ?", // delete statement "update goods set num = ? where goods_id = ?;", // update statement];
var params = [
  [1], // parmas is in array format and corresponds to the sql statement in sqls? [5, 3],
];

index
  .transaction(sqls, params)
  .then((arrResult) => {
    // do anything ....
  })
  .catch((err) => {
    // error
    console.log(err);
  });