Postconditions: what we check

• connect · connection-failure

  error
  WhenCannot connect (wrong credentials, host unreachable, etc.)
  ThrowsError with code 'ECONNREFUSED', 'ER_ACCESS_DENIED_ERROR', etc.
  Required handlingCaller MUST catch connection errors. Common error codes: - ECONNREFUSED: MySQL server not running - ER_ACCESS_DENIED_ERROR: Wrong username/password - ETIMEDOUT: Network timeout Implement retry with exponential backoff for transient issues.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• query · syntax-error

  error
  WhenSQL syntax error
  ThrowsError with code 'ER_PARSE_ERROR' or similar
  Required handlingCaller MUST validate SQL syntax before execution. DO NOT retry - indicates SQL syntax error. Check error.sqlMessage for details.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• query · constraint-violation

  error
  WhenUnique constraint, foreign key, or NOT NULL violation
  ThrowsError with code 'ER_DUP_ENTRY', 'ER_NO_REFERENCED_ROW', 'ER_BAD_NULL_ERROR'
  Required handlingCaller MUST handle constraint violations: - ER_DUP_ENTRY (1062): Duplicate key violation - ER_NO_REFERENCED_ROW_2 (1452): Foreign key constraint - ER_BAD_NULL_ERROR (1048): NOT NULL constraint Extract details from error.sqlMessage. DO NOT retry without fixing data.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[2]
• query · connection-error

  error
  WhenConnection lost during query execution
  ThrowsError with code 'PROTOCOL_CONNECTION_LOST', 'ECONNRESET'
  Required handlingCaller MUST handle connection errors. Connection may be lost due to timeout or server restart. Implement retry with exponential backoff.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• query · table-not-found

  error
  WhenTable or view does not exist
  ThrowsError with code 'ER_NO_SUCH_TABLE' (1146)
  Required handlingCaller MUST verify table exists before querying. DO NOT retry - indicates schema mismatch or missing migration.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[2]
• execute · syntax-error

  error
  WhenSQL syntax error in prepared statement
  ThrowsError with code 'ER_PARSE_ERROR' or similar
  Required handlingCaller MUST validate SQL syntax. Prepared statements prevent SQL injection but not syntax errors. DO NOT retry - fix SQL syntax.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• execute · constraint-violation

  error
  WhenUnique, foreign key, or NOT NULL constraint violation
  ThrowsError with code 'ER_DUP_ENTRY', 'ER_NO_REFERENCED_ROW_2', 'ER_BAD_NULL_ERROR'
  Required handlingCaller MUST handle constraint violations gracefully. DO NOT retry without changing violating data.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[2]
• execute · connection-error

  error
  WhenConnection lost or timeout
  ThrowsError with code 'PROTOCOL_CONNECTION_LOST', 'ETIMEDOUT'
  Required handlingCaller MUST handle connection errors. May be transient - implement retry logic.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• getConnection · pool-exhausted

  error
  WhenAll connections in pool are busy and timeout exceeded
  ThrowsError with message 'Pool is closed' or timeout error
  Required handlingCaller MUST handle pool exhaustion. Root causes: 1. Connections not released (forgot connection.release()) 2. Pool size too small for load 3. Queries taking too long ALWAYS release connections in finally block.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• getConnection · connection-failure

  error
  WhenCannot establish connection from pool
  ThrowsError with code 'ECONNREFUSED', 'ETIMEDOUT'
  Required handlingCaller MUST handle connection failures. Implement retry with exponential backoff.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• beginTransaction · transaction-start-failure

  error
  WhenCannot start transaction (connection error, etc.)
  ThrowsError with connection-related codes
  Required handlingCaller MUST catch transaction start errors. Network errors may be transient and retriable.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• commit · commit-failure

  error
  WhenTransaction commit fails (constraint violation, connection lost, etc.)
  ThrowsError with various codes depending on failure reason
  Required handlingCaller MUST catch commit errors. If commit fails, transaction is rolled back. Caller should handle rollback appropriately.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• rollback · rollback-failure

  error
  WhenRollback fails (connection lost, etc.)
  ThrowsError with connection-related codes
  Required handlingCaller MUST catch rollback errors. Even rollback can fail due to connection issues. Log rollback failures for investigation.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[1]
• createConnection · createconnection-host-unreachable

  error
  WhenMySQL server is not running, host is wrong, or port is unreachable
  ThrowsPromise rejects with Error: ECONNREFUSED (connect ECONNREFUSED 127.0.0.1:3306)
  Required handlingWrap await createConnection() in try-catch. Handle ECONNREFUSED at application startup and retry with backoff. In containerized deployments, add readiness checks before attempting connection.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[3]
• createConnection · createconnection-access-denied

  error
  WhenUsername, password, or database name is not authorized on the MySQL server
  ThrowsPromise rejects with Error: ER_ACCESS_DENIED_ERROR (MySQL errno 1045, SQLSTATE 28000)
  Required handlingWrap await createConnection() in try-catch. Check err.code === 'ER_ACCESS_DENIED_ERROR' and log a clear error message. Verify credentials in environment variables before deployment. Do not expose err.message to users.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[3]
• createConnection · createconnection-too-many-connections

  error
  WhenMySQL server has reached its max_connections limit
  ThrowsPromise rejects with Error: ER_CON_COUNT_ERROR (MySQL errno 1040, SQLSTATE 08004)
  Required handlingWrap await createConnection() in try-catch. Implement connection pooling (use createPool() instead of createConnection() per-request) to avoid exhausting the server's connection limit. Retry with exponential backoff when ER_CON_COUNT_ERROR.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilitysilent
  Sources[3]
• end · end-pool-connection-error

  warning
  WhenPool.end() called while queries are in-flight or connections fail to close
  ThrowsPromise rejects with PROTOCOL_CONNECTION_LOST or connection close error
  Required handlingWrap pool.end() or connection.end() in try-catch in shutdown handlers (SIGTERM, process.on('exit')). Log the error but do not re-throw — the goal is graceful shutdown, not failing on cleanup.
  costlowin prodimmediate exceptionusers seeservice unavailablevisibilitysilent
  Sources[4]
• end · end-called-on-closed-connection

  warning
  Whenpool.end() called while pool.getConnection() calls are still pending
  ThrowsPending getConnection() calls receive Error: 'Pool is closed.'
  Required handlingDrain in-flight requests before calling pool.end(). In graceful shutdown, stop accepting new requests first, then wait for in-flight handlers to complete, then call pool.end(). Wrap pool.end() in try-catch.
  costlowin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[4]
• prepare · prepare-sql-syntax-error

  error
  WhenSQL template passed to prepare() has a syntax error
  ThrowsPromise rejects with Error: ER_PARSE_ERROR (MySQL errno 1064)
  Required handlingWrap await prepare() in try-catch. Validate SQL templates in CI/CD rather than at runtime. Check err.code === 'ER_PARSE_ERROR' to distinguish syntax errors from connection failures.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[5]
• prepare · prepare-connection-error

  error
  WhenConnection drops between createConnection() and prepare(), or is invalidated by changeUser()
  ThrowsPromise rejects with PROTOCOL_CONNECTION_LOST or ECONNRESET
  Required handlingWrap await prepare() in try-catch. Re-prepare statements after any connection reset or changeUser(). Do not cache PreparedStatementInfo objects across connection lifecycle events.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilitysilent
  Sources[5]
• PreparedStatementInfo.execute · prepared-statement-constraint-violation

  error
  WhenBound parameters violate a database constraint (unique, FK, NOT NULL)
  ThrowsPromise rejects with ER_DUP_ENTRY (1062), ER_NO_REFERENCED_ROW_2 (1452), or ER_BAD_NULL_ERROR (1048)
  Required handlingWrap await stmt.execute() in try-catch. Check err.code to distinguish constraint violations (do not retry without fixing data) from connection errors (may retry).
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[6][2]
• PreparedStatementInfo.execute · prepared-statement-stale-after-reconnect

  error
  WhenPreparedStatementInfo used after connection reset, changeUser(), or reconnection
  ThrowsPromise rejects with unknown statement ID error or PROTOCOL_CONNECTION_LOST
  Required handlingDo not cache PreparedStatementInfo objects across connection lifecycle events. Re-call connection.prepare() after any changeUser(), reset(), or reconnection. Wrap await stmt.execute() in try-catch.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilitysilent
  Sources[7][6]
• changeUser · changeuser-access-denied

  error
  WhenNew user credentials are invalid or not authorized for the target database
  ThrowsPromise rejects with ER_ACCESS_DENIED_ERROR (errno 1045); connection marked fatal (err.fatal = true)
  Required handlingWrap await connection.changeUser() in try-catch. On ER_ACCESS_DENIED_ERROR, destroy the connection and create a new one — the connection is permanently unusable after a fatal changeUser error. Do not return it to the pool.
  costhighin prodimmediate exceptionusers seeservice unavailablevisibilitysilent
  Sources[8]
• changeUser · changeuser-prepared-statements-invalidated

  warning
  WhenchangeUser() called on a connection that has cached PreparedStatementInfo objects
  ThrowsDoes not throw on changeUser() itself, but subsequent stmt.execute() throws unknown statement ID error
  Required handlingAfter calling changeUser(), invalidate all cached PreparedStatementInfo objects for that connection. Re-call connection.prepare() before using any statement on the connection after changeUser().
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilitysilent
  Sources[8]
• ping · ping-connection-dead

  error
  WhenConnection to MySQL is no longer alive (server closed due to wait_timeout or network failure)
  ThrowsPromise rejects with PROTOCOL_CONNECTION_LOST or ECONNRESET
  Required handlingWrap await connection.ping() in try-catch. In health check handlers, catch the error and return a 503/unhealthy status rather than letting it propagate as a 500. After a failed ping, destroy the connection and establish a new one.
  costlowin prodimmediate exceptionusers seeservice unavailablevisibilityvisible
  Sources[5]
• reset · reset-connection-dead

  error
  WhenThe connection is no longer alive (server closed due to wait_timeout, network failure, or the connection was destroyed) when reset() is called. The COM_RESET_CONNECTION packet cannot be sent on a dead socket.
  ThrowsPromise rejects with PROTOCOL_CONNECTION_LOST, ECONNRESET, or ECONNREFUSED. Same error codes as ping() — the connection is permanently unusable after this error.
  Required handlingWrap await connection.reset() in try-catch. On error, destroy the connection and create a new one — do not attempt to reuse. In pool scenarios, call connection.destroy() rather than connection.release() when reset() fails. const connection = await pool.getConnection(); try { await connection.reset(); // Use connection... } catch (resetErr) { connection.destroy(); // NOT release() — this connection is broken throw resetErr; } finally { // Only release if no error (destroy() handles cleanup on error) }
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilitysilent
  Sources[5][9]
• reset · reset-invalidates-prepared-statements

  warning
  Whenreset() is called on a connection that has cached PreparedStatementInfo objects (created via connection.prepare()). reset() sends COM_RESET_CONNECTION which causes the MySQL server to invalidate all prepared statements for this connection. The mysql2 client also clears its local _statements LRU cache on reset. Any PreparedStatementInfo objects held by the caller are now stale and will fail when execute() is called on them.
  Throwsreset() itself succeeds (resolves). Subsequent stmt.execute() on a stale PreparedStatementInfo rejects with unknown statement ID error or PROTOCOL_CONNECTION_LOST.
  Required handlingAfter calling reset(), invalidate and discard all PreparedStatementInfo objects associated with that connection. Re-call connection.prepare() before executing any statements on the connection. await connection.reset(); // All PreparedStatementInfo from before reset() are now invalid. // Re-prepare any statements you need. const stmt = await connection.prepare('SELECT * FROM users WHERE id = ?'); const [rows] = await stmt.execute([userId]); await stmt.close(); Note: connection.execute() (not prepare()) uses an auto-managed LRU cache that mysql2 clears automatically on reset() — only manually prepared statements via connection.prepare() require manual re-preparation.
  costmediumin prodimmediate exceptionusers seeservice unavailablevisibilitysilent
  Sources[9][7]
• PreparedStatementInfo.close · prepared-statement-close-missing

  warning
  Whenconnection.prepare() is called but statement.close() is never called after the prepared statement is no longer needed. Each unclosed prepared statement occupies a slot on the MySQL server. MySQL's global max_prepared_stmt_count (default: 16,382) limits the total number of prepared statements across all connections. Long-running services that repeatedly call connection.prepare() without close() will eventually exhaust this limit.
  ThrowsOnce max_prepared_stmt_count is reached, new connection.prepare() calls reject with ER_MAX_PREPARED_STMT_COUNT_REACHED (MySQL errno 1461): "Can't create more than max_prepared_stmt_count statements (current value: 16382)"
  Required handlingAlways call statement.close() after finishing with a manually-prepared statement. Use try-finally to ensure close() is called even when execute() throws: const stmt = await connection.prepare('INSERT INTO events (type, payload) VALUES (?, ?)'); try { await stmt.execute([eventType, JSON.stringify(payload)]); } finally { await stmt.close(); // Always close — fire-and-forget, always resolves } Prefer connection.execute() (auto-cached LRU, auto-closes evicted statements) over connection.prepare() unless you need explicit statement lifecycle control. The auto-cache manages closing evicted statements transparently. For batch operations where a statement is reused many times, prepare() + close() is more efficient than execute() (which re-parses the statement cache on each call). In these cases, always close in finally.
  costmediumin proddelayed failureusers seeservice unavailablevisibilitysilent
  Sources[6][10][7][11]

Sources

Every postcondition cites at least one of these. Grouped by source type; numbered to match the footnotes above.

Research notes

Curator notes from SOURCES.md captured when the profile was written so you can verify the reasoning, not just the rules.

import mysql from 'mysql2/promise';
const connection = await mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test'
});

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

// DANGEROUS - DO NOT DO THIS
const [rows] = await connection.query(`SELECT * FROM users WHERE id = ${userId}`);

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

{
  code: 'ER_DUP_ENTRY',
  errno: 1062,
  sqlState: '23000',
  sqlMessage: "Duplicate entry 'john@example.com' for key 'email'",
  sql: 'INSERT INTO users (email) VALUES (?)'
}

try {
  await connection.execute('INSERT INTO users (email) VALUES (?)', [email]);
} catch (error) {
  if (error.code === 'ER_DUP_ENTRY') {
    // Handle duplicate: return existing user or show error
    console.log('User already exists');
  }
  throw error;
}

const connection = await pool.getConnection();
try {
  await connection.beginTransaction();

  // Execute multiple queries
  await connection.execute('INSERT INTO orders (user_id) VALUES (?)', [userId]);
  await connection.execute('UPDATE inventory SET quantity = quantity - 1 WHERE id = ?', [itemId]);

  await connection.commit();
} catch (error) {
  await connection.rollback();
  throw error;
} finally {
  connection.release(); // CRITICAL: Always release connection
}

const pool = mysql.createPool(config).promise();

// Method 1: Direct pool execution (connection managed automatically)
const [rows] = await pool.execute('SELECT * FROM users WHERE id = ?', [id]);

// Method 2: Manual connection for transactions
const connection = await pool.getConnection();
try {
  await connection.beginTransaction();
  // ... queries
  await connection.commit();
} catch (error) {
  await connection.rollback();
  throw error;
} finally {
  connection.release(); // REQUIRED
}

// WRONG - Vulnerable to SQL injection
await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

// CORRECT - Use placeholders
await pool.execute('SELECT * FROM users WHERE email = ?', [email]);

// WRONG - Connection leaked if error occurs
const connection = await pool.getConnection();
await connection.execute('SELECT * FROM users');
connection.release();

// CORRECT - Always release in finally
const connection = await pool.getConnection();
try {
  await connection.execute('SELECT * FROM users');
} finally {
  connection.release();
}

// WRONG - Unhandled ER_DUP_ENTRY crashes server
await connection.execute('INSERT INTO users (email) VALUES (?)', [email]);

// CORRECT - Handle duplicate gracefully
try {
  await connection.execute('INSERT INTO users (email) VALUES (?)', [email]);
} catch (error) {
  if (error.code === 'ER_DUP_ENTRY') {
    return { error: 'Email already registered' };
  }
  throw error;
}

// LESS SECURE - Parameters serialized client-side
await pool.query('SELECT * FROM users WHERE id = ?', [id]);

// MORE SECURE - Parameters serialized server-side
await pool.execute('SELECT * FROM users WHERE id = ?', [id]);

// WRONG - pool.end() closes pool while queries running
pool.query('SELECT ...');
pool.end(); // Kills active queries

// CORRECT - Wait for queries to finish
await pool.query('SELECT ...');
await pool.end();

try {
  const connection = await mysql.createConnection(config);
} catch (err) {
  console.log('Connection error:', err.code); // ECONNREFUSED, etc.
}

try {
  const [rows] = await connection.execute('SELECT * FROM users');
} catch (err) {
  console.log('Query error:', err.code, err.sqlMessage);
}

{
  code: 'ER_DUP_ENTRY',           // Error code
  errno: 1062,                    // MySQL error number
  sqlState: '23000',              // SQL standard error code
  sqlMessage: 'Duplicate entry...', // MySQL error message
  sql: 'INSERT INTO...'           // Original SQL query
}

import mysql, { ConnectionOptions, PoolOptions } from 'mysql2/promise';

const config: PoolOptions = {
  host: 'localhost',
  user: 'root',
  database: 'test',
  connectionLimit: 10
};

const pool = mysql.createPool(config);