MySQL is the world's most popular open-source relational database. Node.js can connect to MySQL using the mysql2 package - the modern, promise-based MySQL driver. This combination is widely used for building REST APIs, web applications, and backend services.
npm install mysql2const mysql = require('mysql2/promise');
// Create a connection pool (recommended over single connection)
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'your_password',
database: 'mydb',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0
});
module.exports = pool;const pool = require('./db');
async function main() {
// SELECT - Read all users
const [rows] = await pool.query('SELECT * FROM users');
console.log('All users:', rows);
// INSERT - Add a new user (use ? placeholders to prevent SQL injection)
const [result] = await pool.query(
'INSERT INTO users (name, email) VALUES (?, ?)',
['Alice', 'alice@example.com']
);
console.log('Inserted ID:', result.insertId);
// UPDATE - Update a user
await pool.query(
'UPDATE users SET name = ? WHERE id = ?',
['Alice Smith', result.insertId]
);
// DELETE - Remove a user
await pool.query('DELETE FROM users WHERE id = ?', [result.insertId]);
console.log('Done!');
}
main().catch(console.error);const express = require('express');
const pool = require('./db');
const app = express();
app.use(express.json());
// GET all users
app.get('/users', async (req, res) => {
const [rows] = await pool.query('SELECT * FROM users');
res.json(rows);
});
// GET user by ID
app.get('/users/:id', async (req, res) => {
const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [req.params.id]);
if (!rows.length) return res.status(404).json({ error: 'Not found' });
res.json(rows[0]);
});
// POST create user
app.post('/users', async (req, res) => {
const { name, email } = req.body;
const [result] = await pool.query(
'INSERT INTO users (name, email) VALUES (?, ?)', [name, email]
);
res.status(201).json({ id: result.insertId, name, email });
});
// PUT update user
app.put('/users/:id', async (req, res) => {
const { name, email } = req.body;
await pool.query('UPDATE users SET name=?, email=? WHERE id=?', [name, email, req.params.id]);
res.json({ message: 'Updated' });
});
// DELETE user
app.delete('/users/:id', async (req, res) => {
await pool.query('DELETE FROM users WHERE id = ?', [req.params.id]);
res.json({ message: 'Deleted' });
});
app.listen(3000, () => console.log('Server running on port 3000'));Create one MySQL pool during application startup and share it through the data-access layer. A pool reuses authenticated connections and limits database concurrency. Opening a connection inside every request adds handshake latency and can exhaust the server during a traffic spike; holding one checked-out connection forever defeats the pool.
Configure host, port, database, and credentials from validated environment values. Use a dedicated application account with only the required schema privileges. Set finite connection and acquisition limits from measured database capacity, not from the number of HTTP requests the server might receive.
Release a checked-out connection in a `finally` block. Handle startup and shutdown explicitly: reject readiness until a small database check succeeds, stop accepting new HTTP work during termination, let in-flight operations finish within a deadline, and close the pool. Log pool saturation without logging credentials or full queries containing private data.
Use placeholders for every value supplied outside trusted source code. Parameter binding separates data from SQL syntax and blocks ordinary injection through names, emails, IDs, search values, and dates. Placeholders do not safely substitute table names, column names, sort directions, or SQL fragments; choose those from a small application-owned allowlist.
Validate and normalize request values before the repository call. Convert numeric IDs deliberately, bound text length, parse dates, reject unknown fields, and distinguish absent values from explicit null. Parameterization protects query structure, while validation protects domain rules and resource use.
Select named columns instead of `SELECT *`, limit result size, and map driver rows into a response DTO. The database schema may contain password hashes, internal flags, or audit fields that must never cross the HTTP boundary. Return safe domain errors while recording a query name, duration, request ID, and database error code internally.
Use a transaction when several statements represent one business change, such as creating an order and reserving inventory. Check out one connection, begin the transaction, run every related statement on that same connection, commit only after all invariants pass, and roll back on failure. Queries sent through the general pool may use different connections and do not join the transaction.
Keep transactions short. Do not wait for user input, call a slow external service, or perform large unrelated reads while locks are held. When an external message must follow the commit, store an outbox record in the same transaction and dispatch it afterward so a process crash cannot commit data but lose the event.
Concurrent updates need an explicit rule. Use affected-row checks, unique constraints, conditional updates, or a version column to detect stale writes. A successful SQL statement is not proof that the intended row existed or that another request did not change it first.
Separate HTTP parsing from database work. The route validates headers and body shape, the service applies authorization and business rules, and the repository owns SQL. This keeps transactions and queries testable without constructing Express request objects and prevents every route from inventing its own error mapping.
Return `201 Created` for a successful create, `200` or `204` for an update according to the response contract, `404` when the authorized resource does not exist, `409` for a uniqueness or version conflict, and a safe `500` for unexpected failure. Check `affectedRows`; an update of zero rows must not be reported as successful.
Use cursor or keyset pagination for large tables and a stable indexed order. Bound page size and filters. For deletion, decide whether the domain requires hard delete, soft delete, retention, or an audit record. An idempotent delete can return a consistent outcome when the resource is already absent, but the API contract should state that choice.
Let the database enforce durable invariants with primary keys, foreign keys, unique constraints, nullability, and appropriate data types. Application validation improves feedback, but concurrent requests can pass the same pre-check; the constraint remains the final authority.
Create indexes from measured query shapes. A lookup by email needs a different index from a tenant-scoped recent-orders list. Inspect execution plans for slow queries and avoid functions or implicit conversions that prevent index use. More indexes increase write cost and storage, so every index needs a query it supports.
Apply schema migrations as versioned deployment artifacts. Prefer backward-compatible expand-and-contract changes during rolling releases: add the new shape, deploy code that supports both, backfill safely, switch reads, then remove the old shape later. Test migration and rollback or forward-fix behavior on representative data.
Test repositories against a real MySQL instance or an isolated database created for the suite. Cover empty results, duplicate keys, null values, transaction rollback, stale versions, connection loss, timeout, and pagination boundaries. A mock can verify service branching but cannot prove SQL syntax, collation, constraints, or driver conversion behavior.
In production, monitor query latency percentiles, error codes, transaction rollback, pool usage, acquisition wait, connection resets, deadlocks, and database CPU. Add request and operation identifiers so a slow HTTP call can be correlated with its repository operation without recording sensitive parameter values.
Set timeouts and cancellation expectations at every layer. A client disconnect does not guarantee MySQL stopped executing, so writes still need idempotency or conflict protection. Retry only failures known to be transient and only when repeating the operation is safe.
A transaction belongs to one checked-out connection. Begin, query, commit, and rollback through that same connection, then release it in `finally`. Running transaction statements through the pool can send them to different sessions and silently destroy the atomicity the code appears to promise. Keep the transaction short and never wait for unrelated network calls while locks are held.
Choose the isolation and locking behavior from the business invariant. A read followed by a write can lose an update when two requests race; use a conditional update, unique constraint, version column, or locking read as the case requires. Treat deadlocks and lock timeouts as expected concurrency outcomes. Retry only an idempotent transaction, with a limit and jitter, after rolling back the failed attempt.
Classify errors before mapping them to HTTP outcomes. Constraint violations may become a conflict or validation response, missing records may become not found, and connectivity failures should remain unavailable or internal failures. Do not expose SQL text, schema names, driver stacks, or credentials to clients. Preserve a safe cause and correlation ID for operations.
Test rollback after every write step, simultaneous updates to the same logical record, duplicate keys, pool exhaustion, a dropped connection, and process shutdown with borrowed connections. Verify the final database state, not just the response. An endpoint is transactionally correct only when its invariant survives those failure paths.
A pool reuses database connections instead of opening a new TCP connection for every request. That reduces latency and prevents spikes from exhausting MySQL connection limits.
The SQL string contains placeholders, and user values are passed separately. The driver escapes and binds those values instead of letting raw input become SQL syntax.
Raw MySQL errors can reveal table names, column names, SQL fragments, or infrastructure details. The handler should log the detailed error internally with request context, then return a safe client message and status code.
Explore 500+ free tutorials across 20+ languages and frameworks.