When developing applications with Node.js that interact with a MySQL database, understanding how to manage database connections is crucial for performance and resource efficiency. The mysql module provides two primary methods for this purpose: mysql.createConnection() for direct, single-use connections, and mysql.createPool() for managing a pool of connections.

Direct Database Connections with createConnection

The createConnection method is suitable for scripts or applications where a database connection is needed for a short, isolated operation and then immediately closed. This approach establishes a new connection for each request and tears it down once the operation is complete.

Connection Lifecycle:

1. Instantiation: An individual connection object is created using mysql.createConnection(options). The options object includes database credentials such as host, port, user, password, and database.
2. Establishment: The connection to the MySQL server is initiated with connection.connect(callback). This method attempts to open a communication channel.
3. Operation: Once connected, SQL queries can be executed using connection.query(sql, callback).
4. Termination: After all operations are complete, the connection should be explicitly closed using connection.end(callback) or connection.destroy(). Failing to close connections can lead to resource leaks and exhaustion of database server limits.

Example Implementation:

const mysql = require('mysql');

// Database configuration for a single connection
const dbConfig = {
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'your_password',
    database: 'sample_db'
};

// Create a new connection instance
const dbClient = mysql.createConnection(dbConfig);

// Connect to the database
dbClient.connect(err => {
    if (err) {
        console.error('Failed to connect to MySQL:', err.stack);
        return;
    }
    console.log('Successfully connected to database. Connection ID:', dbClient.threadId);

    // Execute a simple query
    dbClient.query('SELECT 1 + 1 AS calculation', (queryError, results) => {
        if (queryError) {
            console.error('Error during query execution:', queryError.stack);
        } else {
            console.log('Query result:', results[0].calculation);
        }

        // Always close the connection after operations are done
        dbClient.end(endError => {
            if (endError) {
                console.error('Error closing database connection:', endError.stack);
            } else {
                console.log('Database connection closed.');
            }
        });
    });
});

Connection Pooling with createPool

For server-side applications, such as web services or APIs, repeatedly creating and closing connections for every client request is inefficient and resource-intensive. Connection pooling addresses this by maintaining a set of ready-to-use database connections. When an application needs a connection, it requests one from the pool. After use, the connection is returned to the pool for reuse, avoiding the overhead of establishing a new connection.

Key Benefits of Connection Pooling:

• Performance: Reduces latency by eliminating the overhead of connection establishment for each request.
• Resource Management: Prevents resource exhaustion on the database server by limiting the number of open connections.
• Stability: Improves application stability under high load.

Pool Lifecycle:

1. Pool Creation: A connection pool is initialized once, typically at application startup, using mysql.createPool(options). The options object for a pool includes standard connection properties plus pool-specific settings like connectionLimit (maximum number of connections in the pool), waitForConnections, and queueLimit.
2. Connection Acquisition: When an operation requires database access, a connection is obtained from the pool using pool.getConnection(callback). If no connections are available and waitForConnections is true (default), the request will wait until one becomes free or queueLimit is reached.
3. Operation: The acquired connection object can then be used to execute queries, just like a direct connection.
4. Connection Release: Crucially, after completing database operations, the connection must be returned to the pool using connection.release(). This makes the connection available for other requests.
5. Pool Shutdown: When the application shuts down, the entire pool should be closed using pool.end(callback) to terminate all idle connections gracefully.

Example Implementation:

const mysql = require('mysql');

// Database configuration for the connection pool
const poolOptions = {
    connectionLimit: 10, // Maintain a maximum of 10 connections simultaneously
    host: 'localhost',
    port: 3306,
    user: 'root',
    password: 'your_password',
    database: 'sample_db'
};

// Create a connection pool once, typically at application startup
const appConnectionPool = mysql.createPool(poolOptions);

// Function to execute a query using the connection pool
function executePooledQuery(sqlQuery, callback) {
    appConnectionPool.getConnection((err, pooledConnection) => {
        if (err) {
            console.error('Error acquiring connection from pool:', err.stack);
            // If an error occurs here, no connection was obtained, so no need to release.
            return callback(err);
        }
        console.log('Connection acquired from pool. ID:', pooledConnection.threadId);

        pooledConnection.query(sqlQuery, (queryErr, rows) => {
            // Always release the connection back to the pool after use
            pooledConnection.release();
            console.log('Connection released back to pool.');

            if (queryErr) {
                console.error('Error executing pooled query:', queryErr.stack);
                return callback(queryErr);
            }
            callback(null, rows);
        });
    });
}

// Example usage of the pooled query function
executePooledQuery('SELECT current_timestamp() AS currentTime', (err, data) => {
    if (err) {
        console.error('Query failed:', err);
    } else {
        console.log('Pooled query result:', data[0].currentTime);
    }

    // In a real application, you'd close the pool when your application is shutting down,
    // not after a single query. This demonstrates how to close it.
    appConnectionPool.end(poolEndErr => {
        if (poolEndErr) {
            console.error('Error closing connection pool:', poolEndErr.stack);
        } else {
            console.log('Connection pool closed.');
        }
    });
});