import mysql from 'mysql2/promise';
import { RowDataPacket, FieldPacket } from 'mysql2';
import { DatabaseConfig } from './config.js';
export interface QueryResult {
rows: any[];
fields?: FieldPacket[] | undefined;
affectedRows?: number;
insertId?: number | undefined;
}
export class MySQLConnection {
private pool?: mysql.Pool;
private config: DatabaseConfig;
constructor(config: DatabaseConfig) {
this.config = config;
}
async connect(): Promise<void> {
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,
ssl: this.config.ssl,
// Connection pool configuration
connectionLimit: 10, // Maximum number of connections in the pool
queueLimit: 0, // No limit for queued connection requests
// Enable multiple statements for security (disabled by default)
multipleStatements: false,
// Connection idle timeout
idleTimeout: 60000, // Close connections that have been idle for 60 seconds
});
// Test the pool by getting a connection
const connection = await this.pool.getConnection();
connection.release();
console.error('MySQL connection pool created successfully');
} catch (error) {
const errorMessage = error instanceof Error ? error.message : 'Unknown connection error';
throw new Error(`Failed to create MySQL connection pool: ${errorMessage}`);
}
}
async disconnect(): Promise<void> {
if (this.pool) {
await this.pool.end();
this.pool = undefined;
console.error('MySQL connection pool closed');
}
}
async executeQuery(query: string): Promise<QueryResult> {
if (!this.pool) {
throw new Error('MySQL connection pool not initialized');
}
let connection: mysql.PoolConnection | undefined;
try {
// Get connection from pool
connection = await this.pool.getConnection();
const [rows, fields] = await connection.execute(query);
return {
rows: Array.isArray(rows) ? rows : [],
fields: fields || undefined,
};
} catch (error) {
const errorMessage = error instanceof Error ? error.message : 'Unknown query error';
throw new Error(`Query execution failed: ${errorMessage}`);
} finally {
// Always release the connection back to the pool
if (connection) {
connection.release();
}
}
}
async listTables(database?: string): Promise<string[]> {
const dbName = database || this.config.database;
if (!dbName) {
throw new Error('No database specified');
}
const query = `SHOW TABLES FROM \`${dbName}\``;
const result = await this.executeQuery(query);
// Extract table names from result
return result.rows.map((row: any) => {
const key = Object.keys(row)[0]; // Get the first column name
return row[key];
});
}
async describeTable(table: string, database?: string): Promise<any[]> {
const dbName = database || this.config.database;
if (!dbName) {
throw new Error('No database specified');
}
const query = `DESCRIBE \`${dbName}\`.\`${table}\``;
const result = await this.executeQuery(query);
return result.rows;
}
async listDatabases(): Promise<string[]> {
const query = 'SHOW DATABASES';
const result = await this.executeQuery(query);
// Extract database names from result
return result.rows.map((row: any) => {
const key = Object.keys(row)[0]; // Get the first column name
return row[key];
}).filter((db: string) => {
// Filter out system databases
return !['information_schema', 'mysql', 'performance_schema', 'sys'].includes(db);
});
}
async testConnection(): Promise<boolean> {
try {
if (!this.pool) {
return false;
}
// Test pool by getting and releasing a connection
const connection = await this.pool.getConnection();
await connection.ping();
connection.release();
return true;
} catch (error) {
return false;
}
}
}