import { promises as fs } from 'fs';
import * as fsSync from 'fs';
import path from 'path';
import { logger } from '../utils/logger';
/**
* Unified database interface that abstracts better-sqlite3 and sql.js
*/
export interface DatabaseAdapter {
prepare(sql: string): PreparedStatement;
exec(sql: string): void;
close(): void;
pragma(key: string, value?: any): any;
readonly inTransaction: boolean;
transaction<T>(fn: () => T): T;
checkFTS5Support(): boolean;
}
export interface PreparedStatement {
run(...params: any[]): RunResult;
get(...params: any[]): any;
all(...params: any[]): any[];
iterate(...params: any[]): IterableIterator<any>;
pluck(toggle?: boolean): this;
expand(toggle?: boolean): this;
raw(toggle?: boolean): this;
columns(): ColumnDefinition[];
bind(...params: any[]): this;
}
export interface RunResult {
changes: number;
lastInsertRowid: number | bigint;
}
export interface ColumnDefinition {
name: string;
column: string | null;
table: string | null;
database: string | null;
type: string | null;
}
/**
* Factory function to create a database adapter
* Uses Supabase as the primary database backend
*/
export async function createDatabaseAdapter(dbPath: string): Promise<DatabaseAdapter> {
// Force Supabase usage
const supabaseUrl = "https://ntlxywzmwvrmdcotgpvs.supabase.co";
const supabaseKey = "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6Im50bHh5d3ptd3ZybWRjb3RncHZzIiwicm9sZSI6ImFub24iLCJpYXQiOjE3NTA5MjYzNjksImV4cCI6MjA2NjUwMjM2OX0.t6c0cWGKYYpQItwxvweK6fEx9zUD5sBfIPSJkgWy6K8";
if (process.env.MCP_MODE !== 'stdio') {
logger.info('Using Supabase as database backend');
}
const { createSupabaseAdapter } = await import('./supabase-adapter');
return await createSupabaseAdapter(supabaseUrl, supabaseKey);
// This code path should never be reached since we're forcing Supabase
throw new Error('SQLite adapters are disabled. This application requires Supabase as the database backend.');
}
/**
* Create better-sqlite3 adapter
*/
async function createBetterSQLiteAdapter(dbPath: string): Promise<DatabaseAdapter> {
try {
const Database = require('better-sqlite3');
const db = new Database(dbPath);
return new BetterSQLiteAdapter(db);
} catch (error) {
throw new Error(`Failed to create better-sqlite3 adapter: ${error}`);
}
}
/**
* Create sql.js adapter with persistence
*/
async function createSQLJSAdapter(dbPath: string): Promise<DatabaseAdapter> {
let initSqlJs;
try {
initSqlJs = require('sql.js');
} catch (error) {
logger.error('Failed to load sql.js module:', error);
throw new Error('sql.js module not found. This might be an issue with npm package installation.');
}
// Initialize sql.js
const SQL = await initSqlJs({
// This will look for the wasm file in node_modules
locateFile: (file: string) => {
if (file.endsWith('.wasm')) {
// Try multiple paths to find the WASM file
const possiblePaths = [
// Local development path
path.join(__dirname, '../../node_modules/sql.js/dist/', file),
// When installed as npm package
path.join(__dirname, '../../../sql.js/dist/', file),
// Alternative npm package path
path.join(process.cwd(), 'node_modules/sql.js/dist/', file),
// Try to resolve from require
path.join(path.dirname(require.resolve('sql.js')), '../dist/', file)
];
// Find the first existing path
for (const tryPath of possiblePaths) {
if (fsSync.existsSync(tryPath)) {
if (process.env.MCP_MODE !== 'stdio') {
logger.debug(`Found WASM file at: ${tryPath}`);
}
return tryPath;
}
}
// If not found, try the last resort - require.resolve
try {
const wasmPath = require.resolve('sql.js/dist/sql-wasm.wasm');
if (process.env.MCP_MODE !== 'stdio') {
logger.debug(`Found WASM file via require.resolve: ${wasmPath}`);
}
return wasmPath;
} catch (e) {
// Fall back to the default path
logger.warn(`Could not find WASM file, using default path: ${file}`);
return file;
}
}
return file;
}
});
// Try to load existing database
let db: any;
try {
const data = await fs.readFile(dbPath);
db = new SQL.Database(new Uint8Array(data));
logger.info(`Loaded existing database from ${dbPath}`);
} catch (error) {
// Create new database if file doesn't exist
db = new SQL.Database();
logger.info(`Created new database at ${dbPath}`);
}
return new SQLJSAdapter(db, dbPath);
}
/**
* Adapter for better-sqlite3
*/
class BetterSQLiteAdapter implements DatabaseAdapter {
constructor(private db: any) {}
prepare(sql: string): PreparedStatement {
const stmt = this.db.prepare(sql);
return new BetterSQLiteStatement(stmt);
}
exec(sql: string): void {
this.db.exec(sql);
}
close(): void {
this.db.close();
}
pragma(key: string, value?: any): any {
return this.db.pragma(key, value);
}
get inTransaction(): boolean {
return this.db.inTransaction;
}
transaction<T>(fn: () => T): T {
return this.db.transaction(fn)();
}
checkFTS5Support(): boolean {
try {
// Test if FTS5 is available
this.exec("CREATE VIRTUAL TABLE IF NOT EXISTS test_fts5 USING fts5(content);");
this.exec("DROP TABLE IF EXISTS test_fts5;");
return true;
} catch (error) {
return false;
}
}
}
/**
* Adapter for sql.js with persistence
*/
class SQLJSAdapter implements DatabaseAdapter {
private saveTimer: NodeJS.Timeout | null = null;
constructor(private db: any, private dbPath: string) {
// Set up auto-save on changes
this.scheduleSave();
}
prepare(sql: string): PreparedStatement {
const stmt = this.db.prepare(sql);
this.scheduleSave();
return new SQLJSStatement(stmt, () => this.scheduleSave());
}
exec(sql: string): void {
this.db.exec(sql);
this.scheduleSave();
}
close(): void {
this.saveToFile();
if (this.saveTimer) {
clearTimeout(this.saveTimer);
}
this.db.close();
}
pragma(key: string, value?: any): any {
// sql.js doesn't support pragma in the same way
// We'll handle specific pragmas as needed
if (key === 'journal_mode' && value === 'WAL') {
// WAL mode not supported in sql.js, ignore
return 'memory';
}
return null;
}
get inTransaction(): boolean {
// sql.js doesn't expose transaction state
return false;
}
transaction<T>(fn: () => T): T {
// Simple transaction implementation for sql.js
try {
this.exec('BEGIN');
const result = fn();
this.exec('COMMIT');
return result;
} catch (error) {
this.exec('ROLLBACK');
throw error;
}
}
checkFTS5Support(): boolean {
try {
// Test if FTS5 is available
this.exec("CREATE VIRTUAL TABLE IF NOT EXISTS test_fts5 USING fts5(content);");
this.exec("DROP TABLE IF EXISTS test_fts5;");
return true;
} catch (error) {
// sql.js doesn't support FTS5
return false;
}
}
private scheduleSave(): void {
if (this.saveTimer) {
clearTimeout(this.saveTimer);
}
// Save after 100ms of inactivity
this.saveTimer = setTimeout(() => {
this.saveToFile();
}, 100);
}
private saveToFile(): void {
try {
const data = this.db.export();
const buffer = Buffer.from(data);
fsSync.writeFileSync(this.dbPath, buffer);
logger.debug(`Database saved to ${this.dbPath}`);
} catch (error) {
logger.error('Failed to save database', error);
}
}
}
/**
* Statement wrapper for better-sqlite3
*/
class BetterSQLiteStatement implements PreparedStatement {
constructor(private stmt: any) {}
run(...params: any[]): RunResult {
return this.stmt.run(...params);
}
get(...params: any[]): any {
return this.stmt.get(...params);
}
all(...params: any[]): any[] {
return this.stmt.all(...params);
}
iterate(...params: any[]): IterableIterator<any> {
return this.stmt.iterate(...params);
}
pluck(toggle?: boolean): this {
this.stmt.pluck(toggle);
return this;
}
expand(toggle?: boolean): this {
this.stmt.expand(toggle);
return this;
}
raw(toggle?: boolean): this {
this.stmt.raw(toggle);
return this;
}
columns(): ColumnDefinition[] {
return this.stmt.columns();
}
bind(...params: any[]): this {
this.stmt.bind(...params);
return this;
}
}
/**
* Statement wrapper for sql.js
*/
class SQLJSStatement implements PreparedStatement {
private boundParams: any = null;
constructor(private stmt: any, private onModify: () => void) {}
run(...params: any[]): RunResult {
try {
if (params.length > 0) {
this.bindParams(params);
if (this.boundParams) {
this.stmt.bind(this.boundParams);
}
}
this.stmt.run();
this.onModify();
// sql.js doesn't provide changes/lastInsertRowid easily
return {
changes: 1, // Assume success means 1 change
lastInsertRowid: 0
};
} catch (error) {
this.stmt.reset();
throw error;
}
}
get(...params: any[]): any {
try {
if (params.length > 0) {
this.bindParams(params);
if (this.boundParams) {
this.stmt.bind(this.boundParams);
}
}
if (this.stmt.step()) {
const result = this.stmt.getAsObject();
this.stmt.reset();
return this.convertIntegerColumns(result);
}
this.stmt.reset();
return undefined;
} catch (error) {
this.stmt.reset();
throw error;
}
}
all(...params: any[]): any[] {
try {
if (params.length > 0) {
this.bindParams(params);
if (this.boundParams) {
this.stmt.bind(this.boundParams);
}
}
const results: any[] = [];
while (this.stmt.step()) {
results.push(this.convertIntegerColumns(this.stmt.getAsObject()));
}
this.stmt.reset();
return results;
} catch (error) {
this.stmt.reset();
throw error;
}
}
iterate(...params: any[]): IterableIterator<any> {
// sql.js doesn't support generators well, return array iterator
return this.all(...params)[Symbol.iterator]();
}
pluck(toggle?: boolean): this {
// Not directly supported in sql.js
return this;
}
expand(toggle?: boolean): this {
// Not directly supported in sql.js
return this;
}
raw(toggle?: boolean): this {
// Not directly supported in sql.js
return this;
}
columns(): ColumnDefinition[] {
// sql.js has different column info
return [];
}
bind(...params: any[]): this {
this.bindParams(params);
return this;
}
private bindParams(params: any[]): void {
if (params.length === 0) {
this.boundParams = null;
return;
}
if (params.length === 1 && typeof params[0] === 'object' && !Array.isArray(params[0]) && params[0] !== null) {
// Named parameters passed as object
this.boundParams = params[0];
} else {
// Positional parameters - sql.js uses array for positional
// Filter out undefined values that might cause issues
this.boundParams = params.map(p => p === undefined ? null : p);
}
}
/**
* Convert SQLite integer columns to JavaScript numbers
* sql.js returns all values as strings, but we need proper types for boolean conversion
*/
private convertIntegerColumns(row: any): any {
if (!row) return row;
// Known integer columns in the nodes table
const integerColumns = ['is_ai_tool', 'is_trigger', 'is_webhook', 'is_versioned'];
const converted = { ...row };
for (const col of integerColumns) {
if (col in converted && typeof converted[col] === 'string') {
converted[col] = parseInt(converted[col], 10);
}
}
return converted;
}
}
