MySQL with Express

Connecting With mysql2

mysql2 is a modern MySQL driver for Node.js, largely compatible with the older mysql package's API but faster and with built-in Promise support. Like other drivers, you should create a connection pool once at startup rather than a single connection per request.

Every query with user-supplied values must use parameterized placeholders (?), never string concatenation, to prevent SQL injection.

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

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
});

module.exports = pool;

mysql2/promise gives you an async/await-friendly API instead of the original callback-based interface.

Parameterized Queries

const [rows] = await pool.query(
  'SELECT * FROM users WHERE id = ?',
  [userId]
);

await pool.query(
  'INSERT INTO users (email, name) VALUES (?, ?)',
  [email, name]
);

• ? placeholders are automatically escaped and substituted safely, preventing SQL injection.
• pool.query() returns a [rows, fields] array; destructure just rows for most use cases.
• Never build SQL strings with template literals containing raw request data.
• Use pool.execute() instead of .query() for prepared statements that run repeatedly with different values.

MySQL (mysql2) Cheatsheet

Common query patterns using the mysql2 promise API.

A Full Express Route With MySQL

Wrapping pool queries in try/catch and forwarding errors keeps route handlers consistent with the error-handling patterns from earlier lessons.

router.get('/:id', async (req, res, next) => {
  try {
    const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [req.params.id]);
    if (rows.length === 0) return res.status(404).json({ error: 'User not found' });
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
});

router.post('/', async (req, res, next) => {
  try {
    const { email, name } = req.body;
    const [result] = await pool.query(
      'INSERT INTO users (email, name) VALUES (?, ?)',
      [email, name]
    );
    res.status(201).json({ id: result.insertId, email, name });
  } catch (err) {
    next(err);
  }
});

Using Transactions

When multiple related writes must all succeed or all fail together (like debiting one account and crediting another), wrap them in a transaction using a single connection checked out from the pool.

const connection = await pool.getConnection();
try {
  await connection.beginTransaction();
  await connection.query('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);
  await connection.query('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);
  await connection.commit();
} catch (err) {
  await connection.rollback();
  throw err;
} finally {
  connection.release();
}

Common Mistakes

• Building SQL queries with string concatenation or template literals, opening the door to SQL injection.
• Opening a new connection per request instead of using a shared pool.
• Forgetting connection.release() after manually checking out a connection for a transaction.
• Not wrapping multi-step writes in a transaction, risking partial, inconsistent updates.

Key Takeaways

• mysql2/promise provides an async/await-friendly driver for MySQL in Node.js.
• Always use ? parameterized placeholders instead of string-building queries.
• A connection pool, created once at startup, should back all your queries.
• Wrap related multi-step writes in a transaction to keep them atomic.