MySQL With Node.js

Connecting to MySQL With mysql2

mysql2 is a fast, widely adopted MySQL driver for Node.js with built-in support for promises and connection pooling. You create a connection pool once at startup, then run queries against it, letting the pool manage individual connections for you.

MySQL, being relational, requires an existing table schema, typically managed with SQL migration files, before you can insert or query rows. This lesson assumes a simple products table already exists.

import mysql from '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,
});

const [rows] = await pool.query('SELECT * FROM products WHERE id = ?', [1]);
console.log(rows[0]);

mysql2/promise returns an array of [rows, fields], most code only needs rows, which you get by destructuring the first element.

Parameterized Queries

pool.query('SELECT * FROM users WHERE email = ?', [email]);
pool.query('INSERT INTO users (name, email) VALUES (?, ?)', [name, email]);
pool.query('UPDATE users SET name = ? WHERE id = ?', [name, id]);

• ? placeholders are automatically escaped by the driver, never build queries with string interpolation instead.
• Placeholder values are passed as a separate array, in the same order they appear in the query.
• pool.query() returns a promise when using the mysql2/promise import.
• Named placeholders are also supported for more complex queries with many parameters.

MySQL (mysql2) Cheatsheet

Common query patterns using the mysql2 promise API.

Preventing SQL Injection

SQL injection happens when untrusted input is inserted directly into a query string, letting an attacker change the query's meaning entirely. Parameterized queries (? placeholders) prevent this by sending values separately from the query structure, the driver escapes them safely before they ever reach the database.

// Dangerous: string concatenation
const query = `SELECT * FROM users WHERE email = '${email}'`; // never do this

// Safe: parameterized query
const [rows] = await pool.query('SELECT * FROM users WHERE email = ?', [email]);

Even if email came from a trusted internal source, always default to parameterized queries as a consistent habit, it costs nothing and closes off an entire vulnerability class.

Using Transactions

When multiple related writes must all succeed or all fail together, transferring funds between two accounts, for example, wrap them in a transaction so a failure partway through rolls back every change instead of leaving data inconsistent.

const connection = await pool.getConnection();

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

Common Mistakes

• Building queries with string concatenation or template literals instead of parameterized placeholders.
• Forgetting to release a connection back to the pool after a manual getConnection() call.
• Not using transactions for multi-step writes that must succeed or fail together.
• Hardcoding database credentials instead of reading them from environment variables.

Key Takeaways

• mysql2/promise is a fast, promise-based MySQL driver with built-in connection pooling.
• Always use ? parameterized queries, never string concatenation, to prevent SQL injection.
• pool.query() returns rows destructured from a [rows, fields] array.
• Transactions ensure multiple related writes succeed or fail together as a unit.