Node.js MySQL Transactions

How it works

The fixture begins a transaction, changes stock, then deliberately rejects the remaining workflow and rolls back. A later query must see the original quantity. Real concurrent reservations also need a locking or conditional-update strategy and a plan for deadlock retries. This single-connection example proves rollback of its own change; it is not a distributed transaction, failover test or claim of production concurrency coverage.

Run the code

Run this example locally with Node.js. The exact sources are reviewed on Node 22.15.1 and Node 24.22.1 unless a specialised lab states otherwise. Save the complete source as mysql-transactions.mjs in a separate empty exercises folder and run node mysql-transactions.mjs. There is no Node.js browser runner on Bhylu; the plain JavaScript playground does not provide these filesystem, server or process APIs. In that separate project, install the reviewed packages with npm install --save-exact mysql2@3.14.3. These are teaching dependencies, not changes to the Bhylu website package tree. This database example is reviewed on Node 22.15.1 against its named real database lab. Do not run it against an existing application database. Read the dedicated lab requirements below before execution.

Download the complete source. Ordinary Node scripts need no Apache, PHP or MySQL service. File examples own their temporary folders; network fixtures use loopback and available temporary ports.

Dedicated MySQL lab

Use Oracle MySQL 8.4.8 and mysql2 3.14.3 in a disposable instance with a loopback port other than 3306. The script requires BHYLU_NODE_DB_LAB=YES, BHYLU_MYSQL_PORT, BHYLU_MYSQL_USER and BHYLU_MYSQL_PASSWORD in its environment. Use a dedicated lab account permitted to create and remove only lab schemas on that disposable instance. Each script creates a random bhylu_node_ schema, seeds fictional records and drops that owned schema in finally. The review uses generated private credentials; none are included in the download. XAMPP MariaDB and your normal MySQL data are not the review target.

Example

import mysql from 'mysql2/promise';
import { randomBytes } from 'node:crypto';
const port = Number(process.env.BHYLU_MYSQL_PORT);
if (
  process.env.BHYLU_NODE_DB_LAB !== 'YES' ||
  !Number.isInteger(port) ||
  port < 1024 ||
  port > 65535 ||
  port === 3306 ||
  !process.env.BHYLU_MYSQL_USER ||
  !process.env.BHYLU_MYSQL_PASSWORD
)
  throw new Error('Use the dedicated disposable database lab');
const connection = await mysql.createConnection({
  host: '127.0.0.1',
  port,
  user: process.env.BHYLU_MYSQL_USER,
  password: process.env.BHYLU_MYSQL_PASSWORD,
  multipleStatements: false,
});
const schema = 'bhylu_node_' + randomBytes(8).toString('hex');
let created = false;
try {
  await connection.query('CREATE DATABASE ' + schema);
  created = true;
  await connection.query('USE ' + schema);
  await connection.query(
    'CREATE TABLE kits (id INT PRIMARY KEY, name VARCHAR(40) NOT NULL, available INT NOT NULL CHECK (available >= 0))',
  );
  await connection.execute('INSERT INTO kits VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)', [
    701,
    'tripod',
    3,
    702,
    'binoculars',
    2,
    703,
    'torch',
    0,
  ]);
  await connection.beginTransaction();
  try {
    await connection.execute(
      'UPDATE kits SET available = available - 1 WHERE id = ? AND available > 0',
      [701],
    );
    throw new Error('Checkout details rejected');
  } catch (error) {
    await connection.rollback();
    console.log(error.message);
  }
  const [rows] = await connection.execute('SELECT available FROM kits WHERE id = ?', [
    701,
  ]);
  console.log(rows[0].available);
} finally {
  try {
    if (created) await connection.query('DROP DATABASE ' + schema);
  } finally {
    await connection.end();
  }
}

Expected result

Checkout details rejected
3

The rejected workflow leaves the original three units available. A transaction does not undo an external email, socket message or file written outside the database; coordinate those side effects through an explicit delivery strategy if needed.