Node.js 40 🟢 Connecting to MySQL using the mysql2 Driver
The mysql2 package is the successor to the original mysql driver, rewritten for performance and modern JavaScript support. It is mostly API-compatible with the older library, so migration is usually a matter of changing the import, but it adds features that the original lacks: native Promise support, prepared statements with automatic caching, and a more efficient protocol parser . For any new Node.js project that talks to MySQL, mysql2 is the recommended choice.
Like its PostgreSQL counterpart, mysql2 exposes two connection abstractions: a single Connection and a Pool. A connection is created with mysql.createConnection() and closed with connection.end(). A pool is created with mysql.createPool() and manages a set of connections, reusing them across queries. For applications that serve more than one request, the pool is the correct choice. Creating a new connection for every query involves a TCP handshake, authentication, and the creation of a server-side thread — overhead that a pool amortizes by keeping connections open .
The library supports both callback-based and Promise-based APIs. The callback API is the default export, and the Promise API is available at mysql2/promise. The Promise API integrates with async/await and is the modern approach. It returns a two-element array [rows, fields] from query execution, where rows contains the result data and fields contains metadata about the columns . The execute() method uses server-side prepared statements, which are both faster for repeated queries and the primary defense against SQL injection.
This chapter covers three areas. First, why mysql2 exists and how its connection model works — the difference between Connection and Pool, and when each is appropriate. Second, how to connect and query — the configuration options, the Promise API, prepared statements with execute(), and the ? placeholder syntax. Third, how to use the pool for transactions — checking out a connection, beginTransaction()/commit()/rollback(), and the release pattern that prevents pool exhaustion. The chapter ends with a complete example session, a quick reference, best practices, common pitfalls, real-world examples, and diagrams showing the pool lifecycle.
Key point: Connection is a single connection; Pool manages a set of connections. Use Pool for applications. Always use execute() with ? placeholders for parameterized queries to prevent SQL injection. For transactions, check out a connection with pool.getConnection(), run beginTransaction()/commit()/rollback(), and release it in a finally block.
Why mysql2 exists
The original-driver problem. The original mysql package was written in an era before Promises were standard in Node.js. Its API is callback-based, and wrapping it for async/await requires a library like mysql2/promise or util.promisify. The protocol parser was also slower than necessary. mysql2 was rewritten from scratch with a new protocol parser that is significantly faster, and it ships with a Promise wrapper built in . It is largely API-compatible with the original, so migration is straightforward, but the performance and ergonomics are better.
The prepared-statement problem. A prepared statement is a SQL template sent to MySQL once. MySQL parses it, validates it, and creates an execution plan. Subsequent executions of the same template with different parameters reuse the plan. This is faster for repeated queries, and it is the correct way to prevent SQL injection. The mysql2 library exposes prepared statements through the execute() method. It prepares the statement on first use, caches it, and reuses the prepared statement for subsequent calls with the same SQL . The original query() method does not use prepared statements; it escapes the parameters client-side and sends the full SQL string. Both are safe from injection when used with placeholders, but execute() is faster for repeated queries .
The connection-cost problem. Opening a MySQL connection is expensive. The client completes a TCP handshake, authenticates (often with caching_sha2_password), and the server spawns a thread to handle the connection. For a single query, this overhead can dominate the query time. A Pool keeps connections open and reuses them. The first query creates a connection; subsequent queries reuse it. The pool size limits how many connections are open at once, preventing the application from exhausting the server’s max_connections limit .
The transaction-isolation problem. A MySQL transaction is scoped to a single connection. START TRANSACTION, the statements, and COMMIT must all run on the same connection, or they are not part of the same transaction. The pool.execute() method does not guarantee this: it checks out a connection, runs the query, and releases it. For a transaction, the application must check out a connection explicitly with pool.getConnection(), run the transaction statements on that connection, and release it when done . This is the most common mistake with mysql2: using pool.execute() for START TRANSACTION and then a different pool.execute() for the statements, which runs them on different connections and outside the transaction.
The configuration-reuse problem. Hardcoding connection details in the source code makes the application environment-specific. The mysql2 library reads configuration from an options object, and the standard practice is to pass values from environment variables. The createPool() function accepts host, port, user, password, database, and many other options. It also accepts a uri option for a connection string . The configuration is external to the code, and the code is portable.
The trade-off. mysql2 is low-level. It returns rows as plain objects, with no type mapping beyond what it does by default. It does not validate that a query returns the columns you expect. It does not manage schema changes. It does not provide a query builder. The application is responsible for the SQL and for the shape of the results. The trade-off is between the control and predictability of raw SQL and the convenience of an ORM. For applications that need complex queries, precise control over transactions, and minimal abstraction overhead, mysql2 is the right choice.
a. Connection and Pool — the two connection models
A Connection is a single connection to MySQL. It is created with mysql.createConnection() and closed with connection.end(). It is appropriate for scripts, one-off tasks, and tests.
const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'secret',
database: 'mydb',
});
const [rows] = await connection.execute('SELECT NOW() as now');
console.log(rows[0].now);
await connection.end();The createConnection() method returns a Promise that resolves to a Connection. The execute() method sends a query and returns [rows, fields]. The end() method closes the connection. If the connection fails, createConnection() rejects, and the error must be caught .
A Pool manages a set of connections. It is appropriate for any application that serves multiple requests. The pool creates connections on demand, up to a maximum, and reuses them across queries.
const mysql = require('mysql2/promise');
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'secret',
database: 'mydb',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
enableKeepAlive: true,
keepAliveInitialDelay: 0,
});
const [rows] = await pool.execute('SELECT NOW() as now');
console.log(rows[0].now);The pool.execute() method checks out a connection, runs the query, and returns the connection to the pool. The connectionLimit option sets the maximum number of connections. The waitForConnections option makes the pool queue requests when all connections are in use, rather than throwing an error. The queueLimit option caps the queue size; 0 means unlimited . The enableKeepAlive option prevents stale connections from being closed by firewalls or the server’s wait_timeout .
The pool must be ended when the application shuts down. The pool.end() method closes all connections and returns a Promise that resolves when they are closed. In a server, this is typically called in the SIGTERM or SIGINT handler.
process.on('SIGTERM', async () => {
await pool.end();
process.exit(0);
});b. Querying with parameters
The execute() method takes a SQL string and an optional array of parameters. The SQL uses ? as the placeholder, and the parameters are substituted as data, not as SQL .
const [rows] = await pool.execute(
'SELECT * FROM users WHERE email = ? AND active = ?',
['alice@example.com', true]
);
console.log(rows);The result of execute() is a two-element array: [rows, fields]. The rows element is an array of the returned rows, each as a plain object. The fields element is an array of field metadata . For an INSERT, the rows element is a ResultSetHeader object with insertId and affectedRows properties.
const [result] = await pool.execute(
'INSERT INTO users (name, email) VALUES (?, ?)',
['Alice', 'alice@example.com']
);
console.log(result.insertId); // the generated id
console.log(result.affectedRows); // 1The execute() method uses prepared statements. MySQL parses the template once, caches the plan, and reuses it for subsequent executions with the same SQL. This is faster for repeated queries and is the primary defense against SQL injection. The ? placeholder separates the SQL template from the data, and the driver escapes the data before sending it .
The query() method is an alternative. It does not use prepared statements; it escapes the parameters client-side and sends the full SQL string. Both execute() and query() are safe from injection when used with placeholders. The difference is performance: execute() is faster for repeated queries because the plan is cached .
c. Transactions and connection checkout
A transaction must run on a single connection. The pool.execute() method does not guarantee this, because it may check out a different connection for each call. For a transaction, the application must check out a connection explicitly, run the transaction statements on that connection, and release it when done .
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
const [userResult] = await connection.execute(
'INSERT INTO users (name, email) VALUES (?, ?)',
['Bob', 'bob@example.com']
);
const userId = userResult.insertId;
await connection.execute(
'INSERT INTO orders (user_id, total) VALUES (?, ?)',
[userId, 99.99]
);
await connection.commit();
console.log('Transaction committed');
} catch (err) {
await connection.rollback();
console.error('Transaction rolled back', err);
throw err;
} finally {
connection.release();
}The pool.getConnection() method returns a Promise that resolves to a PoolConnection. The connection is checked out of the pool and is not returned until release() is called. The beginTransaction(), commit(), and rollback() methods manage the transaction. If an error occurs, the rollback() undoes the transaction, and the error is rethrown. The finally block ensures the connection is released even if the transaction fails .
The connection.release() method returns the connection to the pool. The connection is not closed; it is available for the next checkout. If the connection is not released, the connection leaks, and the pool eventually runs out of connections.
The same pattern applies to any series of queries that must run on the same connection. Session variables, temporary tables, and locks are scoped to a connection. Checking out a connection for a batch of related queries is the pattern that enables this.
Complete Example Session
// ============================================
// PART 1: A MINIMAL CONNECTION
// ============================================
const mysql = require('mysql2/promise');
const connection = await mysql.createConnection({
host: 'localhost',
user: 'root',
password: 'secret',
database: 'mydb',
});
const [rows] = await connection.execute('SELECT ? AS message', ['Hello']);
console.log(rows[0].message); // Hello
await connection.end();
// ============================================
// PART 2: A CONNECTION POOL
// ============================================
const pool = mysql.createPool({
host: 'localhost',
user: 'root',
password: 'secret',
database: 'mydb',
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
enableKeepAlive: true,
keepAliveInitialDelay: 0,
});
const [rows] = await pool.execute('SELECT NOW() as now');
console.log(rows[0].now);
// ============================================
// PART 3: ENVIRONMENT VARIABLE CONFIGURATION
// ============================================
const pool = mysql.createPool({
host: process.env.DB_HOST || 'localhost',
port: process.env.DB_PORT || 3306,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
connectionLimit: 20,
waitForConnections: true,
});
// ============================================
// PART 4: PARAMETERIZED QUERY
// ============================================
const [rows] = await pool.execute(
'SELECT * FROM users WHERE email = ?',
['alice@example.com']
);
// The ? placeholder is replaced with the value.
// The value is treated as data, not SQL.
// ============================================
// PART 5: INSERT WITH RESULT
// ============================================
const [result] = await pool.execute(
'INSERT INTO users (name, email) VALUES (?, ?)',
['Alice', 'alice@example.com']
);
console.log(result.insertId); // the generated id
console.log(result.affectedRows); // 1
// ============================================
// PART 6: UPDATE AND DELETE
// ============================================
const [updateResult] = await pool.execute(
'UPDATE users SET name = ? WHERE id = ?',
['Alice Smith', 1]
);
console.log(updateResult.affectedRows); // 1
const [deleteResult] = await pool.execute(
'DELETE FROM users WHERE id = ?',
[1]
);
console.log(deleteResult.affectedRows); // 1
// ============================================
// PART 7: TRANSACTION WITH CONNECTION CHECKOUT
// ============================================
const connection = await pool.getConnection();
try {
await connection.beginTransaction();
const [userResult] = await connection.execute(
'INSERT INTO users (name) VALUES (?)',
['Bob']
);
const userId = userResult.insertId;
await connection.execute(
'INSERT INTO orders (user_id, total) VALUES (?, ?)',
[userId, 99.99]
);
await connection.commit();
console.log('Transaction committed');
} catch (err) {
await connection.rollback();
console.error('Transaction rolled back', err);
throw err;
} finally {
connection.release();
}
// ============================================
// PART 8: THE db/index.js MODULE PATTERN
// ============================================
// A single module that wraps the pool and exports
// query and getConnection functions.
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,
queueLimit: 0,
});
module.exports = {
query: (sql, params) => pool.execute(sql, params),
getConnection: () => pool.getConnection(),
close: () => pool.end(),
};
// ============================================
// PART 9: THE POOL WITH KEEPALIVE
// ============================================
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,
maxIdle: 10,
idleTimeout: 60000,
queueLimit: 0,
enableKeepAlive: true,
keepAliveInitialDelay: 0,
});
// ============================================
// PART 10: GRACEFUL SHUTDOWN
// ============================================
process.on('SIGTERM', async () => {
console.log('Shutting down...');
await pool.end();
process.exit(0);
});The ten parts show a minimal connection, a connection pool, environment variable configuration, parameterized queries, insert with result, update and delete, transactions with connection checkout, the module pattern, the pool with keepalive, and graceful shutdown.
Quick Reference
Connection vs Pool
| Aspect | Connection | Pool |
|---|---|---|
| Connections | One | Many |
| Reuse | No | Yes |
| Use case | Scripts, tests | Applications |
| Transaction | Direct | Check out connection |
| Creation | mysql.createConnection() | mysql.createPool() |
| Cleanup | connection.end() | pool.end() |
Configuration Options
| Option | Default | Purpose |
|---|---|---|
host | localhost | Database host |
port | 3306 | Database port |
user | — | Database user |
password | — | Database password |
database | — | Database name |
connectionLimit | 10 | Max connections in pool |
waitForConnections | true | Queue requests when pool is full |
queueLimit | 0 | Max queued requests (0 = unlimited) |
enableKeepAlive | false | Prevent stale connections |
idleTimeout | 60000 | Close idle connections after |
multipleStatements | false | Disable for security |
Query Methods
| Method | Prepared Statement | Use Case |
|---|---|---|
execute() | Yes | Repeated queries, injection protection |
query() | No | One-off queries |
Result Object
| Property | Meaning |
|---|---|
rows | Array of returned rows |
fields | Array of field metadata |
insertId | Generated ID for INSERT |
affectedRows | Number of rows affected |
Best Practices
✅ Do This:
// Use a pool for applications
const pool = mysql.createPool({ connectionLimit: 10 }); // ✅
// Use execute() with ? placeholders
await pool.execute('SELECT * FROM users WHERE email = ?', [email]); // ✅
// Use transactions with a checked-out connection
const conn = await pool.getConnection();
try { await conn.beginTransaction(); ... } finally { conn.release(); } // ✅
// Enable keepalive to prevent stale connections
enableKeepAlive: true, keepAliveInitialDelay: 0 // ✅
// Disable multiple statements
multipleStatements: false // ✅
// Close the pool on shutdown
process.on('SIGTERM', async () => { await pool.end(); }); // ✅❌ Don’t Do This:
// Don't create a connection per request
app.get('/', async () => { const c = await mysql.createConnection(); }); // ❌
// Don't use pool.execute for transactions
await pool.execute('START TRANSACTION'); // different connection next // ❌
// Don't concatenate values into SQL
`SELECT * FROM users WHERE email = '${email}'` // injection // ❌
// Don't forget to release a checked-out connection
const conn = await pool.getConnection(); // never released // ❌
// Don't enable multiple statements
multipleStatements: true // allows '; DROP TABLE' // ❌
// Don't forget pool.end()
process.exit(0); // connections leak // ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Transaction does not work | pool.execute uses different connections | Check out connection with getConnection() |
| Connection leak | Connection not released | Always release() in finally |
| SQL injection | String concatenation | Use ? placeholders |
| Pool exhausted | No connectionLimit or too many checkouts | Set connectionLimit, release connections |
| Stale connection error | Firewall or server timeout | enableKeepAlive: true |
ECONNREFUSED | Wrong host/port or MySQL not running | Check config and MySQL status |
Access denied | Wrong credentials | Check user and password |
| Pool does not close | pool.end() not called | Call in shutdown handler |
Real-World Examples
1. Minimal Pool
const pool = mysql.createPool({ host, user, password, database });
const [rows] = await pool.execute('SELECT NOW()');2. Parameterized Query
await pool.execute('SELECT * FROM users WHERE id = ?', [id]);3. Insert with Result
const [result] = await pool.execute('INSERT INTO users (name) VALUES (?)', [name]);
console.log(result.insertId);4. Transaction
const conn = await pool.getConnection();
try { await conn.beginTransaction(); ... } finally { conn.release(); }5. Connection String
mysql.createPool({ uri: process.env.DATABASE_URL });6. Environment Variables
mysql.createPool({ host: process.env.DB_HOST, user: process.env.DB_USER });7. SSL Connection
mysql.createPool({ ssl: { rejectUnauthorized: true } });8. Pool with Keepalive
mysql.createPool({ enableKeepAlive: true, keepAliveInitialDelay: 0 });9. Graceful Shutdown
process.on('SIGTERM', async () => { await pool.end(); });10. Module Wrapper
// db/index.js
module.exports = { query: (sql, params) => pool.execute(sql, params) };Visual
Connection vs Pool
┌──────────────────────────────────────────────────────────────┐
│ CONNECTION vs POOL │
│ │
│ CONNECTION: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ App ──► Connection ──► MySQL │ │
│ │ │ │
│ │ One connection. Created, used, ended. │ │
│ │ No reuse. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
│ POOL: │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ App ──┬──► Connection 1 ──► MySQL │ │
│ │ ├──► Connection 2 ──► MySQL │ │
│ │ ├──► Connection 3 ──► MySQL │ │
│ │ └──► Connection N ──► MySQL │ │
│ │ │ │
│ │ Connections are reused. │ │
│ │ Pool manages the lifecycle. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘The Pool Lifecycle
┌──────────────────────────────────────────────────────────────┐
│ POOL LIFECYCLE │
│ │
│ mysql.createPool(config) │
│ │ │
│ ▼ │
│ pool.execute(sql, params) │
│ │ │
│ ├── Check for idle connection in pool │
│ │ │
│ ├── If found: reuse it │
│ │ │
│ ├── If not found and below connectionLimit: create new │
│ │ │
│ └── If at limit: wait (waitForConnections: true) │
│ │ │
│ ▼ │
│ Run query on connection │
│ │ │
│ ▼ │
│ Return connection to pool │
│ │ │
│ ▼ │
│ (Connection stays open for next query) │
│ │
│ pool.end() closes all connections. │
│ │
└──────────────────────────────────────────────────────────────┘Transaction with Checkout
┌──────────────────────────────────────────────────────────────┐
│ TRANSACTION WITH CHECKOUT │
│ │
│ pool.getConnection() │
│ │ │
│ ▼ │
│ conn = checked out connection │
│ │ │
│ ├── conn.beginTransaction() │
│ │ │
│ ├── conn.execute('INSERT ...') │
│ │ │
│ ├── conn.execute('INSERT ...') │
│ │ │
│ ├── conn.commit() or conn.rollback() │
│ │ │
│ ▼ │
│ conn.release() │
│ │ │
│ ▼ │
│ Connection returns to pool │
│ │
│ All statements run on the SAME connection. │
│ pool.execute() does NOT guarantee this. │
│ │
└──────────────────────────────────────────────────────────────┘Parameterized Query Flow
┌──────────────────────────────────────────────────────────────┐
│ PARAMETERIZED QUERY │
│ │
│ pool.execute( │
│ 'SELECT * FROM users WHERE email = ?', │
│ ['alice@example.com'] │
│ ) │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ SQL template: SELECT * FROM users WHERE email = ? │ │
│ │ Parameters: ['alice@example.com'] │ │
│ └──────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ MySQL parses the template with the placeholder. │
│ The parameter is substituted as DATA, not SQL. │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ Input: ' OR '1'='1 │ │
│ │ Result: SELECT * FROM users WHERE email = ? │ │
│ │ Parameters: ["' OR '1'='1"] │ │
│ │ │ │
│ │ The string is treated as a literal value. │ │
│ │ No SQL injection. │ │
│ └──────────────────────────────────────────────────────┘ │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
mysql2 | Node.js MySQL driver with Promise support |
Connection | Single connection |
Pool | Connection pool |
| Configuration | Object with host, user, password, database |
| Query | pool.execute(sql, params) |
| Parameters | ? placeholders |
| Transaction | pool.getConnection(), beginTransaction()/commit()/rollback() |
| Result | [rows, fields]; insertId, affectedRows |
| Cleanup | pool.end() |
| Keepalive | enableKeepAlive: true |
Key takeaways:
Poolis for applications;Connectionis for scripts. A pool reuses connections across queries, which is essential for performance and for avoiding connection exhaustion. A connection is a single connection that is created and ended for one-off tasks.execute()uses prepared statements;query()does not. Both are safe from injection when used with?placeholders.execute()is faster for repeated queries because MySQL caches the prepared statement plan.- Parameterized queries are mandatory for user input. The
?placeholders separate SQL from data, preventing injection. The parameters are passed as an array. - Transactions require a checked-out connection.
pool.execute()does not guarantee the same connection.pool.getConnection()checks out a connection, andbeginTransaction()/commit()/rollback()run on that connection. The connection must be released in afinallyblock. - Enable keepalive to prevent stale connections. Firewalls and the server’s
wait_timeoutcan close idle connections.enableKeepAlive: truesends periodic packets to keep the connection alive. - Disable multiple statements for security.
multipleStatements: falseprevents an injected; DROP TABLEfrom executing. It is disabled by default, but the configuration should be explicit. pool.end()closes all connections. It should be called on application shutdown, typically in aSIGTERMorSIGINThandler.- A single module wrapping the pool simplifies maintenance. A
db/index.jsthat exportsqueryandgetConnectioncentralizes logging, diagnostics, and configuration. The rest of the application imports from that module, not frommysql2directly.
Remember: The mysql2 driver is the modern foundation for MySQL access in Node.js. It is low-level and un-opinionated, which means it gives you the full power of SQL and the responsibility for using it correctly. A Pool manages connections and reuses them; a Connection is a single connection that must be closed. Prepared statements through execute() prevent injection and reuse query plans. Transactions require a checked-out connection, because they are scoped to a single connection. The pool is the unit of configuration and lifecycle, and it must be ended on shutdown. For applications that need precise control over SQL and transactions, mysql2 is the right tool. It does not hide the database; it exposes it.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!