Database Operations

/**
 * Opens a database for manipulation. Most users will call this to get started.
 * @param config - Database configuration including filename, mode, and driver
 * @returns Promise resolving to Database instance
 */
function open<
  Driver extends sqlite3.Database = sqlite3.Database,
  Stmt extends sqlite3.Statement = sqlite3.Statement
>(config: ISqlite.Config): Promise<Database<Driver, Stmt>>;

interface Config {
  /** Database filename, ":memory:" for in-memory, or "" for anonymous disk-based */
  filename: string;
  /** One or more of sqlite3.OPEN_READONLY, sqlite3.OPEN_READWRITE, sqlite3.OPEN_CREATE */
  mode?: number;
  /** Database driver (e.g., sqlite3.Database or sqlite3.cached.Database) */
  driver: any;
}

import { open } from "sqlite";
import sqlite3 from "sqlite3";

// Basic connection
const db = await open({
  filename: "./mydb.sqlite",
  driver: sqlite3.Database
});

// In-memory database
const memDb = await open({
  filename: ":memory:",
  driver: sqlite3.Database
});

// With caching enabled
const cachedDb = await open({
  filename: "./mydb.sqlite",
  driver: sqlite3.cached.Database
});

// With explicit mode
const readOnlyDb = await open({
  filename: "./mydb.sqlite",
  mode: sqlite3.OPEN_READONLY,
  driver: sqlite3.Database
});

class Database<
  Driver extends sqlite3.Database = sqlite3.Database,
  Stmt extends sqlite3.Statement = sqlite3.Statement
> {
  /** Opens the database connection */
  open(): Promise<void>;
  /** Closes the database connection */
  close(): Promise<void>;
  /** Returns the underlying sqlite3 Database instance */
  getDatabaseInstance(): Driver;
  /** Configure database options like busyTimeout */
  configure(option: ISqlite.ConfigureOption, value: any): any;
  /** Event handler when verbose mode is enabled */
  on(event: string, listener: (...args: any[]) => void): void;
}

type ConfigureOption = 'trace' | 'profile' | 'busyTimeout';

class Database<Driver, Stmt> {
  /**
   * Execute SQL query without retrieving results (INSERT, UPDATE, DELETE, DDL)
   * @param sql - SQL query string or sql-template-strings object
   * @param params - Parameters to bind to the query
   * @returns Promise with execution result including lastID and changes
   */
  run(sql: ISqlite.SqlType, ...params: any[]): Promise<ISqlite.RunResult<Stmt>>;

  /**
   * Execute query and return first result row
   * @param sql - SQL query string
   * @param params - Parameters to bind to the query
   * @returns Promise resolving to first row or undefined
   */
  get<T = any>(sql: ISqlite.SqlType, ...params: any[]): Promise<T | undefined>;

  /**
   * Execute query and return all result rows
   * @param sql - SQL query string
   * @param params - Parameters to bind to the query
   * @returns Promise resolving to array of all rows
   */
  all<T = any[]>(sql: ISqlite.SqlType, ...params: any[]): Promise<T>;

  /**
   * Execute query and call callback for each result row
   * @param sql - SQL query string
   * @param params - Parameters (last parameter must be callback)
   * @returns Promise resolving to number of rows processed
   */
  each<T = any>(sql: ISqlite.SqlType, ...params: any[]): Promise<number>;

  /**
   * Execute multiple SQL statements
   * @param sql - SQL statements separated by semicolons
   * @returns Promise that resolves when all statements complete
   */
  exec(sql: ISqlite.SqlType): Promise<void>;
}

// Run INSERT/UPDATE/DELETE
const result = await db.run(
  "INSERT INTO users (name, email) VALUES (?, ?)",
  "Alice",
  "alice@example.com"
);
console.log(`Inserted row ID: ${result.lastID}`);

// Get single row
const user = await db.get<{id: number, name: string, email: string}>(
  "SELECT * FROM users WHERE id = ?",
  1
);

// Get all rows
const users = await db.all<{id: number, name: string}[]>(
  "SELECT id, name FROM users ORDER BY name"
);

// Process rows one by one
const count = await db.each(
  "SELECT * FROM large_table",
  (err, row) => {
    if (err) throw err;
    console.log(row);
  }
);

// Execute multiple statements
await db.exec(`
  CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT, content TEXT);
  CREATE INDEX idx_posts_title ON posts(title);
`);

class Database<Driver, Stmt> {
  /**
   * Prepare a SQL statement for repeated execution
   * @param sql - SQL query string
   * @param params - Optional parameters to bind immediately
   * @returns Promise resolving to Statement instance
   */
  prepare(sql: ISqlite.SqlType, ...params: any[]): Promise<Statement<Stmt>>;
}

// Prepare statement for repeated use
const stmt = await db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");

// Execute multiple times
await stmt.run("Alice", "alice@example.com");
await stmt.run("Bob", "bob@example.com");
await stmt.run("Charlie", "charlie@example.com");

// Clean up
await stmt.finalize();

class Database<Driver, Stmt> {
  /**
   * Load a compiled SQLite extension
   * @param path - Filename of the extension to load
   * @returns Promise that resolves when extension is loaded
   */
  loadExtension(path: string): Promise<void>;
}

class Database<Driver, Stmt> {
  /**
   * Serialize database operations (currently not implemented)
   * Use getDatabaseInstance().serialize() instead
   * @throws Error indicating method is not implemented
   */
  serialize(): never;

  /**
   * Parallelize database operations (currently not implemented)
   * Use getDatabaseInstance().parallelize() instead  
   * @throws Error indicating method is not implemented
   */
  parallelize(): never;
}

// Use underlying driver for serialize/parallelize
const db = await open({ filename: ":memory:", driver: sqlite3.Database });
const driver = db.getDatabaseInstance();

// Serialize operations
driver.serialize(() => {
  driver.run("CREATE TABLE test (id INTEGER PRIMARY KEY)");
  driver.run("INSERT INTO test VALUES (1)");
  driver.run("INSERT INTO test VALUES (2)");
});

// Parallelize operations  
driver.parallelize(() => {
  for (let i = 0; i < 10; i++) {
    driver.run("INSERT INTO test VALUES (?)", i);
  }
});

// Positional parameters
await db.get("SELECT * FROM users WHERE id = ?", 1);
await db.get("SELECT * FROM users WHERE id = ? AND active = ?", [1, true]);

// Named parameters
await db.get("SELECT * FROM users WHERE id = :id", { ":id": 1 });
await db.get("SELECT * FROM users WHERE name = :name AND email = :email", {
  ":name": "Alice",
  ":email": "alice@example.com"
});

// sql-template-strings support
import SQL from "sql-template-strings";
const name = "Alice";
const user = await db.get(SQL`SELECT * FROM users WHERE name = ${name}`);

interface RunResult<Stmt extends sqlite3.Statement = sqlite3.Statement> {
  /** Statement object (automatically finalized after run) */
  stmt: Statement<Stmt>;
  /** Row ID of inserted row (INSERT statements only) */
  lastID?: number;
  /** Number of rows changed (UPDATE/DELETE statements only) */
  changes?: number;
}

interface SqlObj {
  sql: string;
  params?: any[];
}

interface SQLStatement {
  sql: string;
  values?: any[];
}

type SqlType = SQLStatement | string;