Connecting to MySQL

So far your data has lived in arrays that vanish when the server restarts. Real apps keep data in a database. MySQL is one of the most used databases in the world, and MariaDB, which comes with XAMPP, is a close relative that works the same way for everything in this lesson.

In this lesson you will connect to MySQL from Node.js with the mysql2 package, run queries safely with placeholders, insert and update rows, group changes in a transaction, and connect a database to an Express API. You should know basic SQL (SELECT, INSERT, UPDATE); the SQL tutorial covers it.

Set up a database

Start MySQL (in XAMPP, click Start next to MySQL), open phpMyAdmin or the mysql command-line client, and run:

CREATE DATABASE shop CHARACTER SET utf8mb4;
USE shop;

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(40) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  sku VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO products (name, category, price, stock, sku) VALUES
  ('Cotton kurta', 'clothing', 799.00, 25, 'CLO-101'),
  ('Cricket bat', 'sports', 2499.00, 8, 'SPO-201'),
  ('Steel tiffin', 'kitchen', 450.00, 40, 'KIT-301');

In your Node.js project, install the driver and put the connection details in .env (never in code):

npm install mysql2

# .env
DB_HOST=127.0.0.1
DB_USER=root
DB_PASSWORD=
DB_NAME=shop

XAMPP's root user has an empty password, which is fine on your own laptop only. On a server, create a separate MySQL user with a strong password and only the permissions the app needs.

A connection pool

Opening a database connection takes time. A pool keeps a few connections open and lends one to each query, then takes it back. Create one pool for the whole app in its own module:

// db.js
import mysql from "mysql2/promise";

export const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  connectionLimit: 10,
  decimalNumbers: true, // return DECIMAL columns as numbers, not strings
});

Import from "mysql2/promise" to get the async/await version. connectionLimit is the most connections the pool will open at once. Without decimalNumbers: true, a DECIMAL column such as price comes back as the string '799.00', because a JavaScript number cannot hold every possible DECIMAL exactly. For prices up to a few crore, numbers are fine; for accounting software, keep strings or store paise as whole numbers.

Use 127.0.0.1 rather than localhost for the host. On some systems localhost resolves to the IPv6 address ::1, where MySQL may not be listening.

Running queries

// queries.js  (run with: node --env-file=.env queries.js)
import { pool } from "./db.js";

// 1. Read many rows
const [rows] = await pool.execute(
  "SELECT id, name, price FROM products WHERE category = ? AND price <= ? ORDER BY price",
  ["kitchen", 1000],
);
console.log(rows);

// 2. Insert and get the new id
const [result] = await pool.execute(
  "INSERT INTO products (name, category, price, stock, sku) VALUES (?, ?, ?, ?, ?)",
  ["Brass diya set", "home", 349, 60, "HOM-401"],
);
console.log(`Inserted id ${result.insertId}, rows affected: ${result.affectedRows}`);

// 3. Update
const [update] = await pool.execute("UPDATE products SET stock = stock - ? WHERE id = ?", [2, 2]);
console.log(`Updated ${update.affectedRows} row(s)`);

// 4. Read one row
const [[bat]] = await pool.execute("SELECT name, stock FROM products WHERE id = ?", [2]);
console.log(bat);

// 5. A duplicate value breaks the UNIQUE rule
try {
  await pool.execute(
    "INSERT INTO products (name, category, price, sku) VALUES (?, ?, ?, ?)",
    ["Another kurta", "clothing", 899, "CLO-101"],
  );
} catch (err) {
  console.log(err.code, err.errno);
}

await pool.end();

[ { id: 3, name: 'Steel tiffin', price: 450 } ]
Inserted id 4, rows affected: 1
Updated 1 row(s)
{ name: 'Cricket bat', stock: 6 }
ER_DUP_ENTRY 1062

Key ideas:

• Every ? is a placeholder. The values go in the array, in the same order.
• execute() returns an array [rows, fields]. For SELECT, rows is an array of objects; const [[bat]] takes the first row directly. For INSERT, UPDATE and DELETE you get a result object with insertId and affectedRows.
• Database errors have a code such as ER_DUP_ENTRY (a UNIQUE value already exists). Check it to send a helpful response such as 409 Conflict.
• pool.end() closes all connections so a script can exit. A server keeps the pool open.

SQL injection and why placeholders matter

Never build SQL by joining strings with user input. Here is what an attacker can do with a search box:

import { pool } from "./db.js";

const userInput = "x' OR '1'='1";

// DANGEROUS: user input glued into the SQL text
const [leaked] = await pool.query(`SELECT name FROM products WHERE sku = '${userInput}'`);
console.log(`String building returned ${leaked.length} rows`);

// SAFE: the value is sent separately from the SQL
const [safe] = await pool.execute("SELECT name FROM products WHERE sku = ?", [userInput]);
console.log(`Placeholder returned ${safe.length} rows`);

await pool.end();

String building returned 4 rows
Placeholder returned 0 rows

The quote in the input closed the string in the SQL, and OR '1'='1' made the condition always true, so every row was returned. With the same trick an attacker could read user tables or delete data. This attack is called SQL injection. With execute(), the SQL and the values travel separately (a prepared statement), so the input is only ever treated as a value.

pool.query() also accepts ? placeholders and escapes the values safely. The danger is never the function; it is putting user input into the SQL string yourself.

Transactions

Placing an order means two steps: check the stock, then reduce it. If two customers buy the last bat at the same moment, or the server crashes between the steps, the data can go wrong. A transaction groups several queries so they either all succeed (commit) or all are undone (rollback). All queries in a transaction must use the same connection, so take one from the pool:

import { pool } from "./db.js";

async function placeOrder(productId, qty) {
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    const [[product]] = await conn.execute(
      "SELECT name, price, stock FROM products WHERE id = ? FOR UPDATE",
      [productId],
    );
    if (!product) throw new Error("No such product");
    if (product.stock < qty) throw new Error(`Only ${product.stock} ${product.name} left`);

    await conn.execute("UPDATE products SET stock = stock - ? WHERE id = ?", [qty, productId]);
    await conn.commit();
    return `Ordered ${qty} x ${product.name} for ₹${product.price * qty}`;
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release(); // give the connection back to the pool
  }
}

console.log(await placeOrder(3, 2));
try {
  await placeOrder(2, 50);
} catch (err) {
  console.log(`Order failed: ${err.message}`);
}
await pool.end();

Ordered 2 x Steel tiffin for ₹900
Order failed: Only 6 Cricket bat left

FOR UPDATE locks the row until the transaction ends, so a second order for the same product waits instead of reading an old stock value. The finally block always releases the connection, even after an error; forgetting it slowly uses up the pool until the app freezes.

Using the pool in Express

With Express 5, async route handlers can query the database directly, and unexpected errors reach your error handler:

// app.js  (run with: node --env-file=.env app.js)
import express from "express";
import { pool } from "./db.js";

const app = express();
app.use(express.json());

app.get("/api/products", async (req, res) => {
  const maxPrice = Number(req.query.maxPrice) || 1_000_000;
  const [rows] = await pool.execute(
    "SELECT id, name, category, price, stock FROM products WHERE price <= ? ORDER BY price",
    [maxPrice],
  );
  res.json(rows);
});

app.post("/api/products", async (req, res) => {
  const { name, category, price, stock = 0, sku } = req.body;
  try {
    const [result] = await pool.execute(
      "INSERT INTO products (name, category, price, stock, sku) VALUES (?, ?, ?, ?, ?)",
      [name, category, price, stock, sku],
    );
    res.status(201).json({ id: result.insertId, name, category, price, stock, sku });
  } catch (err) {
    if (err.code === "ER_DUP_ENTRY") return res.status(409).json({ error: `SKU ${sku} already exists` });
    throw err; // anything else goes to the error handler
  }
});

app.listen(3000, () => console.log("Products API with MySQL on http://localhost:3000"));

Posting the same new product twice gives 201 and then 409 { error: 'SKU CLO-102 already exists' }. In a real app, validate req.body with a schema first, as in Validating Input.

Many teams also use a query builder or ORM (object-relational mapper) such as Prisma, Drizzle, Knex or Sequelize, which generate SQL for you. They are worth learning later, but knowing the plain driver first helps you understand and debug them.

Common mistakes

• Building SQL with template strings and user input. Always use ? placeholders.
• Creating a new connection per request. Create one pool when the app starts and reuse it.
• Forgetting conn.release() after getConnection(). Use try...finally.
• Writing the password in code. Read it from environment variables.
• Using localhost and getting ECONNREFUSED ::1:3306. Use 127.0.0.1.
• Expecting numbers from DECIMAL columns. They come back as strings unless you set decimalNumbers: true.

Key points

• Use mysql2/promise and one shared connection pool created with createPool().
• pool.execute(sql, values) runs a prepared statement; ? placeholders prevent SQL injection.
• SELECT gives [rows]; INSERT/UPDATE/DELETE give a result with insertId and affectedRows.
• Check err.code, for example ER_DUP_ENTRY, to turn database errors into 409 and similar responses.
• Use a transaction on one connection (beginTransaction, commit, rollback, release) when several changes must succeed together.

app.delete("/api/products/:id", async (req, res) => {
  const [result] = await pool.execute("DELETE FROM products WHERE id = ?", [Number(req.params.id)]);
  if (result.affectedRows === 0) return res.status(404).json({ error: "Product not found" });
  res.sendStatus(204);
});

app.patch("/api/products/:id/stock", async (req, res) => {
  const change = Number(req.body.change);
  if (!Number.isInteger(change)) return res.status(400).json({ error: "change must be a whole number" });

  const [result] = await pool.execute(
    "UPDATE products SET stock = stock + ? WHERE id = ? AND stock + ? >= 0",
    [change, Number(req.params.id), change],
  );
  if (result.affectedRows === 0) {
    return res.status(400).json({ error: "Product not found or not enough stock" });
  }
  const [[product]] = await pool.execute("SELECT id, name, stock FROM products WHERE id = ?", [Number(req.params.id)]);
  res.json(product);
});

PATCH /api/products/1/stock {"change":-3} -> 200 {"id":1,"name":"Cotton kurta","stock":22}
PATCH /api/products/1/stock {"change":-100} -> 400 {"error":"Product not found or not enough stock"}
DELETE /api/products/6 -> 204
DELETE /api/products/6 -> 404 {"error":"Product not found"}