import mysql from 'mysql2/promise';
import { config } from '../config/index.js';
import { QueryResult, QueryOptions, DatabaseInfo, TableInfo } from '../types/index.js';
import { logger } from '../utils/logger.js';
export class DatabaseManager {
private pool: mysql.Pool;
private static instance: DatabaseManager;
private constructor() {
const connectionConfig: mysql.PoolOptions = {
user: config.database.user,
password: config.database.password,
database: config.database.database,
connectionLimit: config.server.poolSize,
...(config.database.socketPath
? { socketPath: config.database.socketPath }
: { host: config.database.host, port: config.database.port }
),
};
this.pool = mysql.createPool(connectionConfig);
logger.info('Database pool created successfully');
}
public static getInstance(): DatabaseManager {
if (!DatabaseManager.instance) {
DatabaseManager.instance = new DatabaseManager();
}
return DatabaseManager.instance;
}
public async executeQuery(
sql: string,
params: any[] = [],
options: QueryOptions = {}
): Promise<QueryResult> {
try {
logger.debug(`Executing query: ${sql}`, { params });
const connection = await this.pool.getConnection();
try {
// Set query timeout if specified
const timeout = options.timeout || config.server.queryTimeout;
const [rows, fields] = await Promise.race([
connection.execute(sql, params),
new Promise((_, reject) =>
setTimeout(() => reject(new Error(`Query timeout after ${timeout}ms`)), timeout)
)
]) as [any, any];
const result: QueryResult = {
rows: Array.isArray(rows) ? rows : [],
fields: fields || [],
};
// Add metadata for INSERT/UPDATE/DELETE operations
if (typeof rows === 'object' && 'affectedRows' in rows) {
result.affectedRows = rows.affectedRows;
}
if (typeof rows === 'object' && 'insertId' in rows) {
result.insertId = rows.insertId;
}
logger.debug('Query executed successfully', {
rowCount: result.rows.length,
affectedRows: result.affectedRows
});
return result;
} finally {
connection.release();
}
} catch (error) {
logger.error('Query execution failed', { sql, params, error });
throw error;
}
}
public async getDatabases(): Promise<DatabaseInfo[]> {
const result = await this.executeQuery('SHOW DATABASES');
const databases: DatabaseInfo[] = [];
for (const row of result.rows) {
const dbName = row.Database;
if (!['information_schema', 'mysql', 'performance_schema', 'sys'].includes(dbName)) {
const tables = await this.getTables(dbName);
databases.push({
name: dbName,
tables: tables.map(t => t.name)
});
}
}
return databases;
}
public async getTables(database?: string): Promise<TableInfo[]> {
const dbName = database || config.database.database;
if (!dbName) {
throw new Error('Database name is required');
}
const result = await this.executeQuery(`SHOW TABLES FROM \`${dbName}\``);
const tables: TableInfo[] = [];
for (const row of result.rows) {
const tableName = Object.values(row)[0] as string;
const tableInfo = await this.getTableInfo(tableName, dbName);
tables.push(tableInfo);
}
return tables;
}
public async getTableInfo(tableName: string, database?: string): Promise<TableInfo> {
const dbName = database || config.database.database;
if (!dbName) {
throw new Error('Database name is required');
}
// Get columns
const columnsResult = await this.executeQuery(
`SHOW FULL COLUMNS FROM \`${dbName}\`.\`${tableName}\``
);
const columns = columnsResult.rows.map(row => ({
name: row.Field,
type: row.Type,
nullable: row.Null === 'YES',
defaultValue: row.Default,
extra: row.Extra,
key: row.Key
}));
// Get indexes
const indexesResult = await this.executeQuery(
`SHOW INDEX FROM \`${dbName}\`.\`${tableName}\``
);
const indexMap = new Map();
indexesResult.rows.forEach(row => {
if (!indexMap.has(row.Key_name)) {
indexMap.set(row.Key_name, {
name: row.Key_name,
columns: [],
unique: row.Non_unique === 0
});
}
indexMap.get(row.Key_name).columns.push(row.Column_name);
});
const indexes = Array.from(indexMap.values());
return {
name: tableName,
columns,
indexes,
foreignKeys: [] // TODO: Implement foreign key detection
};
}
public async close(): Promise<void> {
await this.pool.end();
logger.info('Database pool closed');
}
}
