import sqlite3 from 'sqlite3';
import { readFileSync } from 'fs';
import { join, dirname } from 'path';
import { fileURLToPath } from 'url';
import { Logger } from '../utils/logger.js';
const __filename = fileURLToPath(import.meta.url);
const __dirname = dirname(__filename);
export class DatabaseConnection {
private static instance: DatabaseConnection;
private db: sqlite3.Database;
private logger: Logger;
private constructor() {
this.logger = Logger.getInstance();
const dbPath = process.env.DATABASE_PATH || 'data/billing.db';
this.db = new sqlite3.Database(dbPath);
this.db.run('PRAGMA journal_mode = WAL');
this.db.run('PRAGMA foreign_keys = ON');
this.logger.info('Database connection established', { path: dbPath });
}
public static async getInstance(): Promise<DatabaseConnection> {
if (!DatabaseConnection.instance) {
DatabaseConnection.instance = new DatabaseConnection();
await DatabaseConnection.instance.initializeSchema();
}
return DatabaseConnection.instance;
}
private async initializeSchema(): Promise<void> {
try {
const schemaPath = join(__dirname, 'schema.sql');
const schema = readFileSync(schemaPath, 'utf-8');
// Execute schema statements sequentially
const statements = schema.split(';').filter(stmt => stmt.trim());
for (const statement of statements) {
if (statement.trim()) {
await this.run(statement);
}
}
this.logger.info('Database schema initialized successfully');
} catch (error) {
this.logger.error('Failed to initialize database schema', { error });
throw error;
}
}
public getDatabase(): sqlite3.Database {
return this.db;
}
public close(): void {
this.db.close();
this.logger.info('Database connection closed');
}
public run(sql: string, params?: unknown[]): Promise<sqlite3.RunResult> {
return new Promise((resolve, reject) => {
this.db.run(sql, params || [], function(err) {
if (err) reject(err);
else resolve(this);
});
});
}
public get(sql: string, params?: unknown[]): Promise<unknown> {
return new Promise((resolve, reject) => {
this.db.get(sql, params || [], (err, row) => {
if (err) reject(err);
else resolve(row);
});
});
}
public all(sql: string, params?: unknown[]): Promise<unknown[]> {
return new Promise((resolve, reject) => {
this.db.all(sql, params || [], (err, rows) => {
if (err) reject(err);
else resolve(rows);
});
});
}
}
