import sqlite3 from 'sqlite3';
import { open } from 'sqlite';
import mysql from 'mysql2/promise';
import pg from 'pg';
import sql from 'mssql';
import type { DatabaseConfig, QueryResult, ConnectionInfo, DatabaseConnection } from './type.js';
export class DatabaseManager {
private connections: Map<string, DatabaseConnection> = new Map();
async connect(connectionId: string, config: DatabaseConfig): Promise<void> {
try {
let connection: any;
switch (config.type) {
case 'sqlite':
if (!config.filename) {
throw new Error('SQLite requires filename parameter');
}
connection = await open({
filename: config.filename,
driver: sqlite3.Database
});
break;
case 'mysql':
connection = await mysql.createConnection({
host: config.host || 'localhost',
port: config.port || 3306,
user: config.username,
password: config.password,
database: config.database,
ssl: config.ssl ? { rejectUnauthorized: false } : undefined
});
break;
case 'postgresql':
const { Client } = pg;
connection = new Client({
host: config.host || 'localhost',
port: config.port || 5432,
user: config.username,
password: config.password,
database: config.database,
ssl: config.ssl ? { rejectUnauthorized: false } : undefined
});
await connection.connect();
break;
case 'mssql':
const sqlConfig: sql.config = {
server: config.host || 'localhost',
port: config.port || 1433,
database: config.database,
user: config.username,
password: config.password,
options: {
encrypt: config.ssl || false,
trustServerCertificate: config.trustServerCertificate || false
}
};
connection = await sql.connect(sqlConfig);
break;
default:
throw new Error(`Unsupported database type: ${config.type}`);
}
this.connections.set(connectionId, { connection, type: config.type });
} catch (error) {
const errorMessage = error instanceof Error ? error.message : 'Unknown error';
throw new Error(`Failed to connect to database: ${errorMessage}`);
}
}
async executeQuery(connectionId: string, query: string, params: any[] = []): Promise<QueryResult> {
const conn = this.connections.get(connectionId);
if (!conn) {
throw new Error(`No connection found with ID: ${connectionId}`);
}
const startTime = Date.now();
let result: QueryResult;
try {
switch (conn.type) {
case 'sqlite':
const sqliteResult = await conn.connection.all(query, params);
result = {
rows: sqliteResult,
rowCount: sqliteResult.length,
executionTime: Date.now() - startTime,
columns: sqliteResult.length > 0 ? Object.keys(sqliteResult[0]) : []
};
break;
case 'mysql':
const [mysqlRows] = await conn.connection.execute(query, params);
result = {
rows: Array.isArray(mysqlRows) ? mysqlRows : [],
rowCount: Array.isArray(mysqlRows) ? mysqlRows.length : 0,
executionTime: Date.now() - startTime,
columns: Array.isArray(mysqlRows) && mysqlRows.length > 0 ? Object.keys(mysqlRows[0]) : []
};
break;
case 'postgresql':
const pgResult = await conn.connection.query(query, params);
result = {
rows: pgResult.rows,
rowCount: pgResult.rowCount || 0,
executionTime: Date.now() - startTime,
columns: pgResult.fields ? pgResult.fields.map((f: any) => f.name) : []
};
break;
case 'mssql':
const mssqlResult = await conn.connection.request().query(query);
result = {
rows: mssqlResult.recordset || [],
rowCount: mssqlResult.rowsAffected?.[0] || mssqlResult.recordset?.length || 0,
executionTime: Date.now() - startTime,
columns: mssqlResult.recordset?.columns ? Object.keys(mssqlResult.recordset.columns) : []
};
break;
default:
throw new Error(`Unsupported database type: ${conn.type}`);
}
} catch (error) {
const errorMessage = error instanceof Error ? error.message : 'Unknown error';
throw new Error(`Query execution failed: ${errorMessage}`);
}
return result;
}
async disconnect(connectionId: string): Promise<void> {
const conn = this.connections.get(connectionId);
if (!conn) return;
try {
switch (conn.type) {
case 'sqlite':
await conn.connection.close();
break;
case 'mysql':
await conn.connection.end();
break;
case 'postgresql':
await conn.connection.end();
break;
case 'mssql':
await conn.connection.close();
break;
}
} catch (error) {
console.error(`Error disconnecting from ${connectionId}:`, error);
}
this.connections.delete(connectionId);
}
getConnectionInfo(): ConnectionInfo[] {
return Array.from(this.connections.entries()).map(([id, conn]) => ({
id,
type: conn.type
}));
}
async getTableSchema(connectionId: string, tableName?: string): Promise<QueryResult> {
const conn = this.connections.get(connectionId);
if (!conn) {
throw new Error(`No connection found with ID: ${connectionId}`);
}
let query: string;
let params: any[] = [];
switch (conn.type) {
case 'sqlite':
query = tableName
? "PRAGMA table_info(?);"
: "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';";
params = tableName ? [tableName] : [];
break;
case 'mysql':
query = tableName
? "DESCRIBE ?;"
: "SHOW TABLES;";
params = tableName ? [tableName] : [];
break;
case 'postgresql':
query = tableName
? `SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = $1 ORDER BY ordinal_position;`
: `SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';`;
params = tableName ? [tableName] : [];
break;
case 'mssql':
query = tableName
? `SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '${tableName}' ORDER BY ORDINAL_POSITION;`
: `SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';`;
break;
default:
throw new Error(`Schema inspection not supported for ${conn.type}`);
}
return await this.executeQuery(connectionId, query, params);
}
}
