/**
* mysql-mcp - Connection Pool Manager
*
* Wraps mysql2 connection pooling with health monitoring,
* statistics tracking, and graceful shutdown support.
*/
import mysql from "mysql2/promise";
import type { Pool, PoolConnection } from "mysql2/promise";
import type { PoolConfig, PoolStats, HealthStatus } from "../types/index.js";
import { PoolError, ConnectionError } from "../types/index.js";
import { logger } from "../utils/logger.js";
/**
* Connection pool configuration with defaults
*/
export interface ConnectionPoolConfig {
host: string;
port: number;
user: string;
password: string;
database: string;
pool?: PoolConfig;
ssl?: boolean | mysql.SslOptions;
charset?: string;
timezone?: string;
connectTimeout?: number;
}
/**
* Connection pool wrapper with statistics and health monitoring
*/
export class ConnectionPool {
private pool: Pool | null = null;
private config: ConnectionPoolConfig;
private stats: PoolStats = {
total: 0,
active: 0,
idle: 0,
waiting: 0,
totalQueries: 0,
};
private isShuttingDown = false;
constructor(config: ConnectionPoolConfig) {
this.config = config;
}
/**
* Initialize the connection pool
*/
async initialize(): Promise<void> {
if (this.pool) {
logger.warn("Connection pool already initialized");
return;
}
const poolConfig = this.config.pool ?? {};
try {
this.pool = mysql.createPool({
host: this.config.host,
port: this.config.port,
user: this.config.user,
password: this.config.password,
database: this.config.database,
// Pool options with defaults
connectionLimit: poolConfig.connectionLimit ?? 10,
waitForConnections: poolConfig.waitForConnections ?? true,
queueLimit: poolConfig.queueLimit ?? 0,
// Connection options
connectTimeout: this.config.connectTimeout ?? 10000,
charset: this.config.charset ?? "utf8mb4",
timezone: this.config.timezone ?? "local",
// SSL - convert boolean to mysql2 compatible format
// mysql2 expects string | SslOptions | undefined, not boolean
ssl:
typeof this.config.ssl === "boolean"
? this.config.ssl
? {}
: undefined
: this.config.ssl,
// Other options
enableKeepAlive: poolConfig.enableKeepAlive ?? true,
keepAliveInitialDelay: poolConfig.keepAliveInitialDelay ?? 0,
// Namedplaceholders for better parameter handling
namedPlaceholders: false,
// Promise wrapper
Promise: Promise,
});
// Test the connection
const connection = await this.pool.getConnection();
connection.release();
logger.info("Connection pool initialized", {
host: this.config.host,
port: this.config.port,
database: this.config.database,
connectionLimit: poolConfig.connectionLimit ?? 10,
});
} catch (error) {
const err = error as Error;
throw new ConnectionError(
`Failed to initialize connection pool: ${err.message}`,
{
host: this.config.host,
port: this.config.port,
database: this.config.database,
},
);
}
}
/**
* Get a connection from the pool
*/
async getConnection(): Promise<PoolConnection> {
if (!this.pool) {
throw new PoolError("Connection pool not initialized");
}
if (this.isShuttingDown) {
throw new PoolError("Connection pool is shutting down");
}
try {
const connection = await this.pool.getConnection();
this.stats.active++;
return connection;
} catch (error) {
const err = error as Error;
throw new PoolError(`Failed to get connection: ${err.message}`);
}
}
/**
* Release a connection back to the pool
*/
releaseConnection(connection: PoolConnection): void {
try {
connection.release();
this.stats.active = Math.max(0, this.stats.active - 1);
} catch (error) {
logger.error("Error releasing connection", { error: String(error) });
}
}
/**
* Execute a query using a pooled connection
* Returns full result tuple [rows, fields] for compatibility with rawQuery
*/
async query<T = unknown>(
sql: string,
params?: unknown[],
): Promise<[T, mysql.FieldPacket[]]> {
if (!this.pool) {
throw new PoolError("Connection pool not initialized");
}
this.stats.totalQueries++;
try {
const result = await this.pool.query(sql, params);
return result as [T, mysql.FieldPacket[]];
} catch (error) {
const err = error as Error & { code?: string };
throw new PoolError(`Query failed: ${err.message}`, { sql }, err.code);
}
}
/**
* Execute a query and return full result with metadata
*/
async execute<T = unknown>(
sql: string,
params?: unknown[],
): Promise<[T, mysql.FieldPacket[]]> {
if (!this.pool) {
throw new PoolError("Connection pool not initialized");
}
this.stats.totalQueries++;
try {
const result = await this.pool.execute(sql, params);
return result as [T, mysql.FieldPacket[]];
} catch (error) {
const err = error as Error & { code?: string };
throw new PoolError(`Execute failed: ${err.message}`, { sql }, err.code);
}
}
/**
* Get pool statistics
*/
getStats(): PoolStats {
if (!this.pool) {
return this.stats;
}
// mysql2 pool doesn't expose detailed stats directly
// We track what we can
return {
...this.stats,
total: this.config.pool?.connectionLimit ?? 10,
idle: (this.config.pool?.connectionLimit ?? 10) - this.stats.active,
};
}
/**
* Check pool health
*/
async checkHealth(): Promise<HealthStatus> {
if (!this.pool) {
return {
connected: false,
error: "Pool not initialized",
};
}
const startTime = Date.now();
try {
const connection = await this.pool.getConnection();
try {
// Ping to verify connection
await connection.ping();
// Get server version
const [rows] = await connection.query("SELECT VERSION() as version");
const result = rows as { version: string }[];
const version = result[0]?.version;
const latencyMs = Date.now() - startTime;
return {
connected: true,
latencyMs,
version,
poolStats: this.getStats(),
};
} finally {
connection.release();
}
} catch (error) {
return {
connected: false,
latencyMs: Date.now() - startTime,
error: String(error),
poolStats: this.getStats(),
};
}
}
/**
* Gracefully shutdown the pool
*/
async shutdown(): Promise<void> {
if (!this.pool) {
return;
}
this.isShuttingDown = true;
logger.info("Shutting down connection pool...");
try {
await this.pool.end();
this.pool = null;
logger.info("Connection pool shut down successfully");
} catch (error) {
logger.error("Error shutting down connection pool", {
error: String(error),
});
throw error;
}
}
/**
* Check if pool is initialized
*/
isInitialized(): boolean {
return this.pool !== null;
}
/**
* Check if pool is shutting down
*/
isClosing(): boolean {
return this.isShuttingDown;
}
}
