Connection Pooling Setup

const mysql = require('mysql2/promise'); // Connection pool configuration const poolConfig = { host: 'localhost', user: 'app_user', password: 'secure_password', database: 'myapp', // Pool settings connectionLimit: 20, // Maximum connections queueLimit: 0, // Unlimited queue acquireTimeout: 60000, // 60 seconds timeout: 60000, // 60 seconds reconnect: true, // Auto-reconnect // Connection settings charset: 'utf8mb4', timezone: 'Z', // Performance settings multipleStatements: false, dateStrings: false, debug: false }; // Create connection pool const pool = mysql.createPool(poolConfig); // Usage example async function getUserById(id) { const connection = await pool.getConnection(); try { const [rows] = await connection.execute( 'SELECT * FROM users WHERE id = ?', [id] ); return rows[0]; } finally { connection.release(); // Return connection to pool } } // Graceful shutdown process.on('SIGINT', async () => { console.log('Closing connection pool...'); await pool.end(); process.exit(0); });

const { Pool } = require('pg'); // Connection pool configuration const poolConfig = { host: 'localhost', port: 5432, user: 'app_user', password: 'secure_password', database: 'myapp', // Pool settings max: 20, // Maximum connections min: 5, // Minimum connections idleTimeoutMillis: 30000, // Close idle connections after 30s connectionTimeoutMillis: 2000, // Connection timeout // Connection settings ssl: false, application_name: 'myapp', // Performance settings statement_timeout: 0, query_timeout: 0, keepAlive: true, keepAliveInitialDelayMillis: 0 }; // Create connection pool const pool = new Pool(poolConfig); // Usage example async function getUserById(id) { const client = await pool.connect(); try { const result = await client.query( 'SELECT * FROM users WHERE id = $1', [id] ); return result.rows[0]; } finally { client.release(); // Return connection to pool } } // Pool event handlers pool.on('connect', (client) => { console.log('New client connected to pool'); }); pool.on('error', (err) => { console.error('Unexpected error on idle client', err); }); // Graceful shutdown process.on('SIGINT', async () => { console.log('Closing connection pool...'); await pool.end(); process.exit(0); });

from sqlalchemy import create_engine from sqlalchemy.pool import QueuePool import pymysql # Connection pool configuration DATABASE_URL = "mysql+pymysql://app_user:secure_password@localhost/myapp" engine = create_engine( DATABASE_URL, # Pool settings pool_size=20, # Maximum connections max_overflow=10, # Additional connections beyond pool_size pool_timeout=30, # Seconds to wait for connection pool_recycle=3600, # Recycle connections after 1 hour pool_pre_ping=True, # Validate connections before use # Connection settings echo=False, # Log SQL statements echo_pool=False, # Log pool events # Performance settings connect_args={ "charset": "utf8mb4", "autocommit": False, "use_unicode": True } ) # Usage example from sqlalchemy.orm import sessionmaker SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine) def get_user_by_id(user_id): db = SessionLocal() try: result = db.execute( "SELECT * FROM users WHERE id = %s", (user_id,) ) return result.fetchone() finally: db.close() # Context manager for automatic cleanup from contextlib import contextmanager @contextmanager def get_db(): db = SessionLocal() try: yield db finally: db.close()

// HikariCP Configuration HikariConfig config = new HikariConfig(); // Database connection settings config.setJdbcUrl("jdbc:mysql://localhost:3306/myapp"); config.setUsername("app_user"); config.setPassword("secure_password"); // Pool settings config.setMaximumPoolSize(20); // Maximum connections config.setMinimumIdle(5); // Minimum idle connections config.setConnectionTimeout(30000); // Connection timeout (30s) config.setIdleTimeout(600000); // Idle timeout (10 minutes) config.setMaxLifetime(1800000); // Connection max lifetime (30 minutes) // Connection settings config.setDriverClassName("com.mysql.cj.jdbc.Driver"); config.addDataSourceProperty("cachePrepStmts", "true"); config.addDataSourceProperty("prepStmtCacheSize", "250"); config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); config.addDataSourceProperty("useServerPrepStmts", "true"); config.addDataSourceProperty("useLocalSessionState", "true"); config.addDataSourceProperty("rewriteBatchedStatements", "true"); config.addDataSourceProperty("cacheResultSetMetadata", "true"); config.addDataSourceProperty("cacheServerConfiguration", "true"); config.addDataSourceProperty("elideSetAutoCommits", "true"); config.addDataSourceProperty("maintainTimeStats", "false"); // Create data source HikariDataSource dataSource = new HikariDataSource(config); // Usage example public class UserRepository { private final HikariDataSource dataSource; public UserRepository(HikariDataSource dataSource) { this.dataSource = dataSource; } public User getUserById(int id) throws SQLException { String sql = "SELECT * FROM users WHERE id = ?"; try (Connection connection = dataSource.getConnection(); PreparedStatement statement = connection.prepareStatement(sql)) { statement.setInt(1, id); ResultSet resultSet = statement.executeQuery(); if (resultSet.next()) { return new User( resultSet.getInt("id"), resultSet.getString("name"), resultSet.getString("email") ); } } return null; } }