Elysia Tools

Browse tools, examples, visual explainers, and topic guides from one clear workspace.

Top Categories

Hubs

Language
ZHESPTRUFRDEEN

Elysia Tools

Navigation

TypeScript

TypeScript: A null flag is not a health check, Reset only builder state, Reject an already-active state

Choose a code card, follow fixed inputs, and distinguish local calculations from real device or server behavior.

Samples

Entries inside this sample collection

CodetypescriptSimple

SQLite Connection

Connect to SQLite database using SQL.js and WebAssembly in browser environment

Difficulty

6/10

Estimated time

25 min

Tags

typescript, web, database

Prerequisites

Intermediate TypeScript, SQL.js, IndexedDB

typescript
// Web TypeScript SQLite Connection Examples
// Using SQL.js (SQLite compiled to WebAssembly) for browser-based database operations

// 1. SQLite Database Connection
class SQLiteDatabase {
  protected db: any = null;
  protected SQL: any = null;

  // Initialize SQL.js
  async initialize(): Promise<void> {
    // Load SQL.js from CDN
    const sqlJsConfig = await this.loadSQLJS();

    // Initialize SQL.js
    this.SQL = await sqlJsConfig;
    this.db = new this.SQL.Database();

    console.log('SQLite database initialized');
  }

  // Load SQL.js library
  private async loadSQLJS(): Promise<any> {
    // Dynamic import of SQL.js
    const module = await import('sql.js');
    return module;
  }

  // Create database from binary array
  async createFromBuffer(buffer: Uint8Array): Promise<void> {
    this.SQL = await this.loadSQLJS();
    this.db = new this.SQL.Database(buffer);
    console.log('Database created from buffer');
  }

  // Load database from file
  async loadFromFile(file: File): Promise<void> {
    const arrayBuffer = await file.arrayBuffer();
    const uint8Array = new Uint8Array(arrayBuffer);
    await this.createFromBuffer(uint8Array);
  }

  // Export database to binary
  exportDatabase(): Uint8Array {
    if (!this.db) {
      throw new Error('Database not initialized');
    }

    return this.db.export();
  }

  // Download database as file
  downloadDatabase(fileName: string = 'database.db'): void {
    const data = this.exportDatabase();
    const blob = new Blob([data], { type: 'application/x-sqlite3' });

    const url = URL.createObjectURL(blob);
    const link = document.createElement('a');
    link.href = url;
    link.download = fileName;
    document.body.appendChild(link);
    link.click();
    document.body.removeChild(link);
    URL.revokeObjectURL(url);
  }

  // Check if database is open
  isOpen(): boolean {
    return this.db !== null;
  }

  // Get raw SQL.js database handle
  getDB(): any {
    if (!this.db) {
      throw new Error('Database not initialized');
    }
    return this.db;
  }

  // Close database
  close(): void {
    if (this.db) {
      this.db.close();
      this.db = null;
      console.log('Database closed');
    }
  }
}

// 2. Database Schema Manager
class DatabaseSchema {
  private db: SQLiteDatabase;

  constructor(db: SQLiteDatabase) {
    this.db = db;
  }

  // Create table
  createTable(tableName: string, columns: Record<string, string>): void {
    const columnDefs = Object.entries(columns)
      .map(([name, type]) => `${name} ${type}`)
      .join(', ');

    const sql = `CREATE TABLE IF NOT EXISTS ${tableName} (${columnDefs})`;
    this.db.getDB().run(sql);

    console.log(`Table ${tableName} created`);
  }

  // Drop table
  dropTable(tableName: string): void {
    const sql = `DROP TABLE IF EXISTS ${tableName}`;
    this.db.getDB().run(sql);

    console.log(`Table ${tableName} dropped`);
  }

  // Check if table exists
  tableExists(tableName: string): boolean {
    const sql = `SELECT name FROM sqlite_master WHERE type='table' AND name=?`;
    const result = this.db.getDB().exec(sql, [tableName]);

    return result.length > 0;
  }

  // List all tables
  listTables(): string[] {
    const sql = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name";
    const result = this.db.getDB().exec(sql);

    if (result.length > 0) {
      return result[0].values.map((row: any) => row[0]);
    }

    return [];
  }

  // Get table schema
  getTableSchema(tableName: string): any[] {
    const sql = `PRAGMA table_info(${tableName})`;
    const result = this.db.getDB().exec(sql);

    if (result.length > 0) {
      return result[0].values.map((row: any) => ({
        cid: row[0],
        name: row[1],
        type: row[2],
        notNull: row[3] === 1,
        defaultValue: row[4],
        pk: row[5] === 1
      }));
    }

    return [];
  }
}

// 3. In-Memory Database
class InMemoryDatabase extends SQLiteDatabase {
  // Create temporary database
  static async create(): Promise<InMemoryDatabase> {
    const db = new InMemoryDatabase();
    await db.initialize();
    return db;
  }

  // Save state
  saveState(): Uint8Array {
    return this.exportDatabase();
  }

  // Restore state
  restoreState(data: Uint8Array): void {
    if (this.db) {
      this.db.close();
    }
    this.db = new this.SQL.Database(data);
  }
}

// 4. Persistent Database (IndexedDB)
class PersistentDatabase {
  private dbName: string;
  private storeName: string = 'sqlite_databases';
  private db: IDBDatabase | null = null;
  private sqlDB: SQLiteDatabase | null = null;

  constructor(dbName: string) {
    this.dbName = dbName;
  }

  // Initialize IndexedDB
  async initialize(): Promise<void> {
    return new Promise((resolve, reject) => {
      const request = indexedDB.open(this.dbName, 1);

      request.onerror = () => reject(request.error);
      request.onsuccess = () => {
        this.db = request.result;
        resolve();
      };

      request.onupgradeneeded = (event) => {
        const db = (event.target as IDBOpenDBRequest).result;
        if (!db.objectStoreNames.contains(this.storeName)) {
          db.createObjectStore(this.storeName);
        }
      };
    });
  }

  // Save SQLite database to IndexedDB
  async saveDatabase(name: string, sqlDB: SQLiteDatabase): Promise<void> {
    const data = sqlDB.exportDatabase();

    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction([this.storeName], 'readwrite');
      const store = transaction.objectStore(this.storeName);
      const request = store.put(data, name);

      request.onsuccess = () => resolve();
      request.onerror = () => reject(request.error);
    });
  }

  // Load SQLite database from IndexedDB
  async loadDatabase(name: string): Promise<SQLiteDatabase> {
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction([this.storeName], 'readonly');
      const store = transaction.objectStore(this.storeName);
      const request = store.get(name);

      request.onsuccess = async () => {
        const data = request.result;
        if (data) {
          const sqlDB = new SQLiteDatabase();
          await sqlDB.initialize();
          sqlDB.getDB().close();
          sqlDB['db'] = new sqlDB['SQL'].Database(data);
          resolve(sqlDB);
        } else {
          reject(new Error('Database not found'));
        }
      };

      request.onerror = () => reject(request.error);
    });
  }

  // List all saved databases
  async listDatabases(): Promise<string[]> {
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction([this.storeName], 'readonly');
      const store = transaction.objectStore(this.storeName);
      const request = store.getAllKeys();

      request.onsuccess = () => resolve(request.result as string[]);
      request.onerror = () => reject(request.error);
    });
  }

  // Delete database
  async deleteDatabase(name: string): Promise<void> {
    return new Promise((resolve, reject) => {
      const transaction = this.db!.transaction([this.storeName], 'readwrite');
      const store = transaction.objectStore(this.storeName);
      const request = store.delete(name);

      request.onsuccess = () => resolve();
      request.onerror = () => reject(request.error);
    });
  }

  // Close IndexedDB
  close(): void {
    if (this.db) {
      this.db.close();
      this.db = null;
    }
  }
}

// 5. Database Connection Pool (Simulated)
class DatabasePool {
  private pools: Map<string, SQLiteDatabase[]> = new Map();
  private maxSize: number = 5;

  // Create pool
  createPool(name: string, size: number = this.maxSize): void {
    const pool: SQLiteDatabase[] = [];

    for (let i = 0; i < size; i++) {
      // Would create connections here
      // In browser, this is simulated
    }

    this.pools.set(name, pool);
  }

  // Get connection from pool
  async getConnection(name: string): Promise<SQLiteDatabase | null> {
    const pool = this.pools.get(name);

    if (pool && pool.length > 0) {
      return pool.pop()!;
    }

    // Create new connection
    const db = new SQLiteDatabase();
    await db.initialize();
    return db;
  }

  // Return connection to pool
  returnConnection(name: string, db: SQLiteDatabase): void {
    const pool = this.pools.get(name);

    if (pool && pool.length < this.maxSize) {
      pool.push(db);
    } else {
      db.close();
    }
  }

  // Close all connections in pool
  closePool(name: string): void {
    const pool = this.pools.get(name);

    if (pool) {
      pool.forEach(db => db.close());
      pool.length = 0;
    }
  }

  // Close all pools
  closeAll(): void {
    this.pools.forEach((pool, name) => {
      this.closePool(name);
    });
    this.pools.clear();
  }
}

// 6. Database Health Monitor
class DatabaseHealthMonitor {
  private db: SQLiteDatabase;
  private metrics: Map<string, any> = new Map();

  constructor(db: SQLiteDatabase) {
    this.db = db;
  }

  // Get database size
  getDatabaseSize(): number {
    const data = this.db.exportDatabase();
    return data.length;
  }

  // Get database page size
  getPageSize(): number {
    const result = this.db.getDB().exec('PRAGMA page_size');
    return result.length > 0 ? result[0].values[0][0] : 0;
  }

  // Get page count
  getPageCount(): number {
    const result = this.db.getDB().exec('PRAGMA page_count');
    return result.length > 0 ? result[0].values[0][0] : 0;
  }

  // Get database version
  getDatabaseVersion(): string {
    const result = this.db.getDB().exec('SELECT sqlite_version()');
    return result.length > 0 ? result[0].values[0][0] : '';
  }

  // Check database integrity
  checkIntegrity(): boolean {
    const result = this.db.getDB().exec('PRAGMA integrity_check');
    return result.length > 0 && result[0].values[0][0] === 'ok';
  }

  // Get table row counts
  getTableRowCounts(): Map<string, number> {
    const counts = new Map<string, number>();
    const tables = this.db.getDB().exec("SELECT name FROM sqlite_master WHERE type='table'");

    if (tables.length > 0) {
      for (const table of tables[0].values) {
        const tableName = table[0];
        const result = this.db.getDB().exec(`SELECT COUNT(*) FROM ${tableName}`);
        if (result.length > 0) {
          counts.set(tableName, result[0].values[0][0]);
        }
      }
    }

    return counts;
  }

  // Get health report
  getHealthReport(): Record<string, any> {
    return {
      size: this.getDatabaseSize(),
      pageSize: this.getPageSize(),
      pageCount: this.getPageCount(),
      version: this.getDatabaseVersion(),
      integrity: this.checkIntegrity(),
      tableCounts: Object.fromEntries(this.getTableRowCounts())
    };
  }
}

// Usage Examples
async function demonstrateSQLiteConnection() {
  console.log('=== Web TypeScript SQLite Connection Examples ===\n');

  // 1. Initialize in-memory database
  console.log('--- 1. Initialize In-Memory Database ---');
  const db1 = await InMemoryDatabase.create();
  console.log(`Database open: ${db1.isOpen()}`);

  // 2. Create schema
  console.log('\n--- 2. Create Schema ---');
  const schema = new DatabaseSchema(db1);
  schema.createTable('users', {
    id: 'INTEGER PRIMARY KEY AUTOINCREMENT',
    name: 'TEXT NOT NULL',
    email: 'TEXT UNIQUE',
    created_at: 'DATETIME DEFAULT CURRENT_TIMESTAMP'
  });

  const tables = schema.listTables();
  console.log(`Tables: ${tables.join(', ')}`);

  // 3. Get table schema
  console.log('\n--- 3. Get Table Schema ---');
  const tableSchema = schema.getTableSchema('users');
  tableSchema.forEach(col => {
    console.log(`  ${col.name}: ${col.type}`);
  });

  // 4. Persistent database
  console.log('\n--- 4. Persistent Database ---');
  const persistentDB = new PersistentDatabase('MyAppDB');
  await persistentDB.initialize();
  await persistentDB.saveDatabase('main_db', db1);
  console.log('Database saved to IndexedDB');

  const savedDBs = await persistentDB.listDatabases();
  console.log(`Saved databases: ${savedDBs.join(', ')}`);

  // 5. Health monitor
  console.log('\n--- 5. Health Monitor ---');
  const monitor = new DatabaseHealthMonitor(db1);
  const health = monitor.getHealthReport();
  console.log('Health Report:');
  console.log(`  Size: ${health.size} bytes`);
  console.log(`  Version: ${health.version}`);
  console.log(`  Integrity: ${health.integrity}`);

  // 6. Export and download
  console.log('\n--- 6. Export Database ---');
  db1.downloadDatabase('my_database.db');

  // Cleanup
  db1.close();
  persistentDB.close();

  console.log('\n=== All SQLite Connection Examples Completed ===');
}

// Export functions
export { SQLiteDatabase, DatabaseSchema, InMemoryDatabase, PersistentDatabase, DatabasePool, DatabaseHealthMonitor };
export { demonstrateSQLiteConnection };
CodetypescriptIntermediate

SQL Query Execution

Execute SELECT, INSERT, UPDATE, DELETE queries with parameter binding and result handling

Difficulty

7/10

Estimated time

30 min

Tags

typescript, web, database, sql

Prerequisites

Intermediate TypeScript, SQL knowledge, Design patterns

typescript
// Web TypeScript SQL Query Execution Examples
// Using SQL.js for executing SQL queries in browser environment

// 1. Query Builder
class QueryBuilder {
  private table: string = '';
  private selectColumns: string[] = ['*'];
  private whereConditions: string[] = [];
  private joinClauses: string[] = [];
  private orderByClause: string = '';
  private limitValue: number = 0;
  private offsetValue: number = 0;
  private bindParams: any[] = [];

  // Select table
  from(table: string): this {
    this.table = table;
    return this;
  }

  // Select columns
  select(...columns: string[]): this {
    this.selectColumns = columns;
    return this;
  }

  // Where clause
  where(condition: string, ...params: any[]): this {
    this.whereConditions.push(condition);
    this.bindParams.push(...params);
    return this;
  }

  // Join clause
  join(table: string, onCondition: string): this {
    this.joinClauses.push(`JOIN ${table} ON ${onCondition}`);
    return this;
  }

  // Left join
  leftJoin(table: string, onCondition: string): this {
    this.joinClauses.push(`LEFT JOIN ${table} ON ${onCondition}`);
    return this;
  }

  // Order by
  orderBy(column: string, direction: 'ASC' | 'DESC' = 'ASC'): this {
    this.orderByClause = `ORDER BY ${column} ${direction}`;
    return this;
  }

  // Limit
  limit(count: number): this {
    this.limitValue = count;
    return this;
  }

  // Offset
  offset(count: number): this {
    this.offsetValue = count;
    return this;
  }

  // Build query
  build(): { sql: string; params: any[] } {
    const sql = `SELECT ${this.selectColumns.join(', ')} FROM ${this.table}`;

    const parts = [sql];

    if (this.joinClauses.length > 0) {
      parts.push(this.joinClauses.join(' '));
    }

    if (this.whereConditions.length > 0) {
      parts.push(`WHERE ${this.whereConditions.join(' AND ')}`);
    }

    if (this.orderByClause) {
      parts.push(this.orderByClause);
    }

    if (this.limitValue > 0) {
      parts.push(`LIMIT ${this.limitValue}`);
    }

    if (this.offsetValue > 0) {
      parts.push(`OFFSET ${this.offsetValue}`);
    }

    return {
      sql: parts.join(' '),
      params: this.bindParams
    };
  }

  // Reset builder
  reset(): this {
    this.table = '';
    this.selectColumns = ['*'];
    this.whereConditions = [];
    this.joinClauses = [];
    this.orderByClause = '';
    this.limitValue = 0;
    this.offsetValue = 0;
    this.bindParams = [];
    return this;
  }
}

// 2. Database Query Executor
class QueryExecutor {
  private db: any;

  constructor(db: any) {
    this.db = db;
  }

  // Execute SELECT query
  select(sql: string, params: any[] = []): any[] {
    try {
      const results = this.db.exec(sql, params);

      if (results.length > 0) {
        const columns = results[0].columns;
        const values = results[0].values;

        return values.map((row: any[]) => {
          const obj: Record<string, any> = {};
          columns.forEach((col: string, index: number) => {
            obj[col] = row[index];
          });
          return obj;
        });
      }

      return [];
    } catch (error) {
      console.error('Query error:', error);
      throw error;
    }
  }

  // Execute SELECT and return single row
  selectOne(sql: string, params: any[] = []): any | null {
    const results = this.select(sql, params);
    return results.length > 0 ? results[0] : null;
  }

  // Execute INSERT query
  insert(table: string, data: Record<string, any>): number {
    const columns = Object.keys(data);
    const values = Object.values(data);
    const placeholders = columns.map(() => '?').join(', ');

    const sql = `INSERT INTO ${table} (${columns.join(', ')}) VALUES (${placeholders})`;

    try {
      this.db.run(sql, values);
      return this.getLastInsertId();
    } catch (error) {
      console.error('Insert error:', error);
      throw error;
    }
  }

  // Execute batch INSERT
  batchInsert(table: string, dataList: Record<string, any>[]): number {
    if (dataList.length === 0) return 0;

    const columns = Object.keys(dataList[0]);
    const placeholders = columns.map(() => '?').join(', ');

    const sql = `INSERT INTO ${table} (${columns.join(', ')}) VALUES (${placeholders})`;

    let insertedCount = 0;

    try {
      this.db.run('BEGIN TRANSACTION');

      for (const data of dataList) {
        const values = columns.map(col => data[col]);
        this.db.run(sql, values);
        insertedCount++;
      }

      this.db.run('COMMIT');
      return insertedCount;
    } catch (error) {
      this.db.run('ROLLBACK');
      console.error('Batch insert error:', error);
      throw error;
    }
  }

  // Execute UPDATE query
  update(table: string, data: Record<string, any>, where: string, whereParams: any[] = []): number {
    const setClause = Object.keys(data)
      .map(key => `${key} = ?`)
      .join(', ');

    const values = [...Object.values(data), ...whereParams];
    const sql = `UPDATE ${table} SET ${setClause} WHERE ${where}`;

    try {
      this.db.run(sql, values);
      return this.getChanges();
    } catch (error) {
      console.error('Update error:', error);
      throw error;
    }
  }

  // Execute DELETE query
  delete(table: string, where: string, params: any[] = []): number {
    const sql = `DELETE FROM ${table} WHERE ${where}`;

    try {
      this.db.run(sql, params);
      return this.getChanges();
    } catch (error) {
      console.error('Delete error:', error);
      throw error;
    }
  }

  // Execute raw SQL
  execute(sql: string, params: any[] = []): any[] {
    return this.select(sql, params);
  }

  // Get last insert ID
  getLastInsertId(): number {
    const result = this.db.exec('SELECT last_insert_rowid()');
    return result.length > 0 ? result[0].values[0][0] : 0;
  }

  // Get number of affected rows
  getChanges(): number {
    const result = this.db.exec('SELECT changes()');
    return result.length > 0 ? result[0].values[0][0] : 0;
  }

  // Count rows
  count(table: string, where: string = '', params: any[] = []): number {
    let sql = `SELECT COUNT(*) as count FROM ${table}`;

    if (where) {
      sql += ` WHERE ${where}`;
    }

    const result = this.selectOne(sql, params);
    return result ? result.count : 0;
  }

  // Check if exists
  exists(table: string, where: string, params: any[] = []): boolean {
    const count = this.count(table, where, params);
    return count > 0;
  }
}

// 3. Repository Pattern
class Repository {
  protected executor: QueryExecutor;
  protected tableName: string;

  constructor(db: any, tableName: string) {
    this.executor = new QueryExecutor(db);
    this.tableName = tableName;
  }

  // Find by ID
  findById(id: number): any | null {
    return this.executor.selectOne(
      `SELECT * FROM ${this.tableName} WHERE id = ?`,
      [id]
    );
  }

  // Find all
  findAll(limit: number = 100, offset: number = 0): any[] {
    return this.executor.select(
      `SELECT * FROM ${this.tableName} LIMIT ? OFFSET ?`,
      [limit, offset]
    );
  }

  // Find where
  findWhere(where: string, params: any[]): any[] {
    return this.executor.select(
      `SELECT * FROM ${this.tableName} WHERE ${where}`,
      params
    );
  }

  // Find one where
  findOneWhere(where: string, params: any[]): any | null {
    return this.executor.selectOne(
      `SELECT * FROM ${this.tableName} WHERE ${where}`,
      params
    );
  }

  // Insert
  create(data: Record<string, any>): number {
    return this.executor.insert(this.tableName, data);
  }

  // Update
  update(id: number, data: Record<string, any>): number {
    return this.executor.update(
      this.tableName,
      data,
      'id = ?',
      [id]
    );
  }

  // Delete
  delete(id: number): number {
    return this.executor.delete(
      this.tableName,
      'id = ?',
      [id]
    );
  }

  // Count
  count(): number {
    return this.executor.count(this.tableName);
  }

  // Exists
  exists(id: number): boolean {
    return this.executor.exists(this.tableName, 'id = ?', [id]);
  }
}

// 4. Active Record Pattern
class ActiveRecord {
  protected db: any;
  protected tableName: string = '';
  protected data: Record<string, any> = {};
  protected isNew: boolean = true;

  constructor(db: any, data: Record<string, any> = {}) {
    this.db = db;
    this.data = { ...data };
    if (data.id) {
      this.isNew = false;
    }
  }

  // Get attribute
  get(key: string): any {
    return this.data[key];
  }

  // Set attribute
  set(key: string, value: any): void {
    this.data[key] = value;
  }

  // Save
  save(): number {
    const executor = new QueryExecutor(this.db);

    if (this.isNew) {
      const id = executor.insert(this.tableName, this.data);
      this.data.id = id;
      this.isNew = false;
      return id;
    } else {
      executor.update(
        this.tableName,
        this.data,
        'id = ?',
        [this.data.id]
      );
      return this.data.id;
    }
  }

  // Delete
  delete(): number {
    if (this.isNew) return 0;

    const executor = new QueryExecutor(this.db);
    return executor.delete(this.tableName, 'id = ?', [this.data.id]);
  }

  // Refresh
  refresh(): void {
    if (this.isNew) return;

    const executor = new QueryExecutor(this.db);
    const data = executor.selectOne(
      `SELECT * FROM ${this.tableName} WHERE id = ?`,
      [this.data.id]
    );

    if (data) {
      this.data = data;
    }
  }

  // Convert to JSON
  toJSON(): Record<string, any> {
    return { ...this.data };
  }
}

// 5. Query Logger
class QueryLogger {
  private executor: QueryExecutor;
  private logs: Array<{ sql: string; params: any[]; time: number }> = [];

  constructor(executor: QueryExecutor) {
    this.executor = executor;
  }

  // Execute with logging
  select(sql: string, params: any[] = []): any[] {
    const start = performance.now();
    const results = this.executor.select(sql, params);
    const time = performance.now() - start;

    this.logs.push({ sql, params, time });
    console.log(`[SELECT] ${sql} - ${time.toFixed(2)}ms`);

    return results;
  }

  // Get slow queries
  getSlowQueries(threshold: number = 100): Array<{ sql: string; params: any[]; time: number }> {
    return this.logs.filter(log => log.time > threshold);
  }

  // Get query statistics
  getStatistics(): Record<string, any> {
    const totalTime = this.logs.reduce((sum, log) => sum + log.time, 0);
    const avgTime = totalTime / this.logs.length;
    const maxTime = Math.max(...this.logs.map(log => log.time));

    return {
      totalQueries: this.logs.length,
      totalTime: totalTime.toFixed(2),
      averageTime: avgTime.toFixed(2),
      maxTime: maxTime.toFixed(2),
      slowQueries: this.getSlowQueries().length
    };
  }

  // Clear logs
  clear(): void {
    this.logs = [];
  }
}

// 6. Data Mapper
class DataMapper {
  private executor: QueryExecutor;

  constructor(db: any) {
    this.executor = new QueryExecutor(db);
  }

  // Map rows to entities
  mapToEntities<T>(rows: any[], entityClass: new () => T): T[] {
    return rows.map(row => this.mapToEntity(row, entityClass));
  }

  // Map row to entity
  mapToEntity<T>(row: any, entityClass: new () => T): T {
    const entity = new entityClass();
    Object.assign(entity, row);
    return entity;
  }

  // Execute query and map to entities
  queryAndMap<T>(sql: string, params: any[], entityClass: new () => T): T[] {
    const rows = this.executor.select(sql, params);
    return this.mapToEntities(rows, entityClass);
  }
}

// Usage Examples
async function demonstrateSQLQueryExecution() {
  console.log('=== Web TypeScript SQL Query Execution Examples ===\n');

  // Simulate database connection
  const mockDB = {
    exec: (sql: string, params: any[] = []) => {
      // Mock implementation
      console.log(`Executing: ${sql}`);
      return [];
    },
    run: (sql: string, params: any[] = []) => {
      console.log(`Running: ${sql}`);
    }
  };

  const executor = new QueryExecutor(mockDB);

  // 1. Query builder
  console.log('--- 1. Query Builder ---');
  const builder = new QueryBuilder();
  const query = builder
    .select('id', 'name', 'email')
    .from('users')
    .where('age > ?', 18)
    .where('status = ?', 'active')
    .orderBy('name', 'ASC')
    .limit(10)
    .build();

  console.log(`SQL: ${query.sql}`);
  console.log(`Params: ${query.params}`);

  // 2. Repository
  console.log('\n--- 2. Repository Pattern ---');
  class UserRepository extends Repository {
    findByEmail(email: string) {
      return this.findOneWhere('email = ?', [email]);
    }

    findActiveUsers() {
      return this.findWhere('status = ?', ['active']);
    }

    searchByName(keyword: string) {
      return this.findWhere('name LIKE ?', [`%${keyword}%`]);
    }
  }

  const userRepo = new UserRepository(mockDB, 'users');
  console.log('Repository created for users table');

  // 3. Query logging
  console.log('\n--- 3. Query Logging ---');
  const logger = new QueryLogger(executor);
  // logger.select('SELECT * FROM users');

  console.log('\n=== All SQL Query Execution Examples Completed ===');
}

// Export functions
export { QueryBuilder, QueryExecutor, Repository, ActiveRecord, QueryLogger, DataMapper };
export { demonstrateSQLQueryExecution };
CodetypescriptComplex

Transaction Handling

Manage database transactions with BEGIN, COMMIT, ROLLBACK and isolation levels

Difficulty

8/10

Estimated time

35 min

Tags

typescript, web, database, transactions

Prerequisites

Advanced TypeScript, Database transactions, ACID principles

typescript
// Web TypeScript Transaction Handling Examples
// Using SQL.js for transaction management in browser environment

// 1. Transaction Manager
class TransactionManager {
  private db: any;
  private inTransaction: boolean = false;
  private savepoints: string[] = [];

  constructor(db: any) {
    this.db = db;
  }

  // Begin transaction
  begin(): void {
    if (this.inTransaction) {
      throw new Error('Transaction already in progress');
    }

    this.db.run('BEGIN TRANSACTION');
    this.inTransaction = true;
    console.log('Transaction started');
  }

  // Commit transaction
  commit(): void {
    if (!this.inTransaction) {
      throw new Error('No transaction in progress');
    }

    this.db.run('COMMIT');
    this.inTransaction = false;
    this.savepoints = [];
    console.log('Transaction committed');
  }

  // Rollback transaction
  rollback(): void {
    if (!this.inTransaction) {
      throw new Error('No transaction in progress');
    }

    this.db.run('ROLLBACK');
    this.inTransaction = false;
    this.savepoints = [];
    console.log('Transaction rolled back');
  }

  // Create savepoint
  createSavepoint(name: string): void {
    if (!this.inTransaction) {
      throw new Error('No transaction in progress');
    }

    this.db.run(`SAVEPOINT ${name}`);
    this.savepoints.push(name);
    console.log(`Savepoint ${name} created`);
  }

  // Release savepoint
  releaseSavepoint(name: string): void {
    if (!this.savepoints.includes(name)) {
      throw new Error(`Savepoint ${name} not found`);
    }

    this.db.run(`RELEASE SAVEPOINT ${name}`);
    this.savepoints = this.savepoints.filter(sp => sp !== name);
    console.log(`Savepoint ${name} released`);
  }

  // Rollback to savepoint
  rollbackToSavepoint(name: string): void {
    if (!this.savepoints.includes(name)) {
      throw new Error(`Savepoint ${name} not found`);
    }

    this.db.run(`ROLLBACK TO SAVEPOINT ${name}`);
    this.savepoints = this.savepoints.filter(sp => sp !== name);
    console.log(`Rolled back to savepoint ${name}`);
  }

  // Execute in transaction
  async transaction<T>(callback: () => T): Promise<T> {
    this.begin();

    try {
      const result = callback();
      this.commit();
      return result;
    } catch (error) {
      this.rollback();
      throw error;
    }
  }

  // Check if in transaction
  isInTransaction(): boolean {
    return this.inTransaction;
  }

  // Get active savepoints
  getSavepoints(): string[] {
    return [...this.savepoints];
  }
}

// 2. Unit of Work Pattern
class UnitOfWork {
  private db: any;
  private transaction: TransactionManager;
  private newEntities: Map<string, any[]> = new Map();
  private modifiedEntities: Map<string, any[]> = new Map();
  private deletedEntities: Map<string, any[]> = new Map();

  constructor(db: any) {
    this.db = db;
    this.transaction = new TransactionManager(db);
  }

  // Register new entity
  registerNew(tableName: string, entity: any): void {
    if (!this.newEntities.has(tableName)) {
      this.newEntities.set(tableName, []);
    }
    this.newEntities.get(tableName)!.push(entity);
  }

  // Register modified entity
  registerModified(tableName: string, entity: any): void {
    if (!this.modifiedEntities.has(tableName)) {
      this.modifiedEntities.set(tableName, []);
    }
    this.modifiedEntities.get(tableName)!.push(entity);
  }

  // Register deleted entity
  registerDeleted(tableName: string, entity: any): void {
    if (!this.deletedEntities.has(tableName)) {
      this.deletedEntities.set(tableName, []);
    }
    this.deletedEntities.get(tableName)!.push(entity);
  }

  // Commit all changes
  commit(): void {
    this.transaction.begin();

    try {
      // Insert new entities
      for (const [tableName, entities] of this.newEntities) {
        for (const entity of entities) {
          const executor = new QueryExecutor(this.db);
          const columns = Object.keys(entity);
          const values = Object.values(entity);
          const placeholders = columns.map(() => '?').join(', ');
          const sql = `INSERT INTO ${tableName} (${columns.join(', ')}) VALUES (${placeholders})`;
          this.db.run(sql, values);
        }
      }

      // Update modified entities
      for (const [tableName, entities] of this.modifiedEntities) {
        for (const entity of entities) {
          const executor = new QueryExecutor(this.db);
          const columns = Object.keys(entity).filter(k => k !== 'id');
          const values = columns.map(k => entity[k]);
          const setClause = columns.map(k => `${k} = ?`).join(', ');
          const sql = `UPDATE ${tableName} SET ${setClause} WHERE id = ?`;
          this.db.run(sql, [...values, entity.id]);
        }
      }

      // Delete entities
      for (const [tableName, entities] of this.deletedEntities) {
        for (const entity of entities) {
          const executor = new QueryExecutor(this.db);
          const sql = `DELETE FROM ${tableName} WHERE id = ?`;
          this.db.run(sql, [entity.id]);
        }
      }

      this.transaction.commit();
      this.clean();
    } catch (error) {
      this.transaction.rollback();
      throw error;
    }
  }

  // Rollback all changes
  rollback(): void {
    this.transaction.rollback();
    this.clean();
  }

  // Clean tracked entities
  private clean(): void {
    this.newEntities.clear();
    this.modifiedEntities.clear();
    this.deletedEntities.clear();
  }

  // Check if has changes
  hasChanges(): boolean {
    return this.newEntities.size > 0 ||
           this.modifiedEntities.size > 0 ||
           this.deletedEntities.size > 0;
  }
}

// 3. Transaction Isolation Levels
class TransactionIsolation {
  private db: any;

  constructor(db: any) {
    this.db = db;
  }

  // Set isolation level (simulated for SQL.js)
  setIsolationLevel(level: 'DEFERRED' | 'IMMEDIATE' | 'EXCLUSIVE'): void {
    const sql = `BEGIN ${level} TRANSACTION`;
    this.db.run(sql);
    console.log(`Isolation level set to ${level}`);
  }

  // Read uncommitted (simulated)
  readUncommitted(callback: () => void): void {
    console.log('Starting READ UNCOMMITTED transaction');
    this.begin();
    try {
      callback();
      this.commit();
    } catch (error) {
      this.rollback();
      throw error;
    }
  }

  // Read committed (simulated)
  readCommitted(callback: () => void): void {
    console.log('Starting READ COMMITTED transaction');
    this.begin();
    try {
      callback();
      this.commit();
    } catch (error) {
      this.rollback();
      throw error;
    }
  }

  // Repeatable read (simulated)
  repeatableRead(callback: () => void): void {
    console.log('Starting REPEATABLE READ transaction');
    this.begin();
    try {
      callback();
      this.commit();
    } catch (error) {
      this.rollback();
      throw error;
    }
  }

  // Serializable (simulated)
  serializable(callback: () => void): void {
    console.log('Starting SERIALIZABLE transaction');
    this.begin();
    try {
      callback();
      this.commit();
    } catch (error) {
      this.rollback();
      throw error;
    }
  }

  private begin(): void {
    this.db.run('BEGIN IMMEDIATE TRANSACTION');
  }

  private commit(): void {
    this.db.run('COMMIT');
  }

  private rollback(): void {
    this.db.run('ROLLBACK');
  }
}

// 4. Nested Transactions
class NestedTransaction {
  private db: any;
  private transactionStack: TransactionManager[] = [];
  private depth: number = 0;

  constructor(db: any) {
    this.db = db;
  }

  // Begin nested transaction
  begin(): void {
    if (this.depth === 0) {
      // Top-level transaction
      const tx = new TransactionManager(this.db);
      tx.begin();
      this.transactionStack.push(tx);
    } else {
      // Nested transaction using savepoint
      const parent = this.transactionStack[this.depth - 1];
      const savepointName = `sp_${this.depth}`;
      parent.createSavepoint(savepointName);
      this.transactionStack.push(parent);
    }

    this.depth++;
    console.log(`Transaction depth: ${this.depth}`);
  }

  // Commit current level
  commit(): void {
    if (this.depth === 0) {
      throw new Error('No transaction to commit');
    }

    this.depth--;

    if (this.depth === 0) {
      // Commit top-level transaction
      const tx = this.transactionStack.pop()!;
      tx.commit();
    } else {
      // Release savepoint
      const parent = this.transactionStack[this.depth - 1];
      const savepointName = `sp_${this.depth}`;
      parent.releaseSavepoint(savepointName);
      this.transactionStack.pop();
    }

    console.log(`Transaction depth: ${this.depth}`);
  }

  // Rollback current level
  rollback(): void {
    if (this.depth === 0) {
      throw new Error('No transaction to rollback');
    }

    this.depth--;

    if (this.depth === 0) {
      // Rollback top-level transaction
      const tx = this.transactionStack.pop()!;
      tx.rollback();
    } else {
      // Rollback to savepoint
      const parent = this.transactionStack[this.depth - 1];
      const savepointName = `sp_${this.depth}`;
      parent.rollbackToSavepoint(savepointName);
      this.transactionStack.pop();
    }

    console.log(`Transaction depth: ${this.depth}`);
  }

  // Get current depth
  getDepth(): number {
    return this.depth;
  }
}

// 5. Transaction Logger
class TransactionLogger {
  private transaction: TransactionManager;
  private logs: Array<{ event: string; timestamp: number }> = [];

  constructor(transaction: TransactionManager) {
    this.transaction = transaction;
  }

  // Begin with logging
  begin(): void {
    this.log('BEGIN');
    this.transaction.begin();
  }

  // Commit with logging
  commit(): void {
    this.log('COMMIT');
    this.transaction.commit();
  }

  // Rollback with logging
  rollback(): void {
    this.log('ROLLBACK');
    this.transaction.rollback();
  }

  // Log event
  private log(event: string): void {
    this.logs.push({
      event,
      timestamp: Date.now()
    });
    console.log(`[TX LOG] ${event} at ${new Date().toISOString()}`);
  }

  // Get transaction history
  getHistory(): Array<{ event: string; timestamp: number }> {
    return [...this.logs];
  }

  // Clear logs
  clear(): void {
    this.logs = [];
  }
}

// 6. Distributed Transaction Coordinator (Simulated)
class DistributedTransactionCoordinator {
  private participants: Map<string, any> = new Map();
  private prepared: Set<string> = new Set();

  // Register participant
  registerParticipant(name: string, db: any): void {
    this.participants.set(name, db);
    console.log(`Participant ${name} registered`);
  }

  // Two-phase commit: prepare phase
  async prepare(): Promise<boolean> {
    console.log('Starting prepare phase...');

    for (const [name, db] of this.participants) {
      try {
        db.run('BEGIN TRANSACTION');
        // Simulate prepare
        this.prepared.add(name);
        console.log(`${name} prepared`);
      } catch (error) {
        console.error(`${name} prepare failed`, error);
        await this.rollback();
        return false;
      }
    }

    return true;
  }

  // Two-phase commit: commit phase
  async commit(): Promise<void> {
    console.log('Starting commit phase...');

    for (const [name, db] of this.participants) {
      if (this.prepared.has(name)) {
        try {
          db.run('COMMIT');
          console.log(`${name} committed`);
        } catch (error) {
          console.error(`${name} commit failed`, error);
        }
      }
    }

    this.prepared.clear();
  }

  // Two-phase commit: rollback
  async rollback(): Promise<void> {
    console.log('Starting rollback...');

    for (const [name, db] of this.participants) {
      try {
        db.run('ROLLBACK');
        console.log(`${name} rolled back`);
      } catch (error) {
        console.error(`${name} rollback failed`, error);
      }
    }

    this.prepared.clear();
  }

  // Execute distributed transaction
  async execute(callback: () => Promise<void>): Promise<boolean> {
    const prepared = await this.prepare();

    if (!prepared) {
      return false;
    }

    try {
      await callback();
      await this.commit();
      return true;
    } catch (error) {
      console.error('Distributed transaction failed', error);
      await this.rollback();
      return false;
    }
  }
}

// Query Executor helper
class QueryExecutor {
  constructor(private db: any) {}

  select(sql: string, params: any[] = []): any[] {
    const results = this.db.exec(sql, params);
    if (results.length > 0) {
      const columns = results[0].columns;
      const values = results[0].values;
      return values.map((row: any[]) => {
        const obj: Record<string, any> = {};
        columns.forEach((col: string, index: number) => {
          obj[col] = row[index];
        });
        return obj;
      });
    }
    return [];
  }
}

// Usage Examples
async function demonstrateTransactionHandling() {
  console.log('=== Web TypeScript Transaction Handling Examples ===\n');

  // Mock database
  const mockDB = {
    exec: (sql: string, params?: any) => {
      console.log(`Executing: ${sql}`);
      return [];
    },
    run: (sql: string, params?: any) => {
      console.log(`Running: ${sql}`);
    }
  };

  // 1. Basic transaction
  console.log('--- 1. Basic Transaction ---');
  const txManager = new TransactionManager(mockDB);
  txManager.begin();
  console.log(`In transaction: ${txManager.isInTransaction()}`);
  txManager.commit();

  // 2. Transaction with callback
  console.log('\n--- 2. Transaction with Callback ---');
  try {
    await txManager.transaction(() => {
      console.log('Executing operations in transaction...');
      mockDB.run('INSERT INTO users (name) VALUES (?)', ['Alice']);
      mockDB.run('INSERT INTO users (name) VALUES (?)', ['Bob']);
      return 'success';
    });
  } catch (error) {
    console.error('Transaction failed:', error);
  }

  // 3. Savepoints
  console.log('\n--- 3. Savepoints ---');
  txManager.begin();
  mockDB.run('INSERT INTO users (name) VALUES (?)', ['Charlie']);

  txManager.createSavepoint('sp1');
  mockDB.run('INSERT INTO users (name) VALUES (?)', ['David']);

  txManager.rollbackToSavepoint('sp1');
  txManager.commit();

  // 4. Nested transactions
  console.log('\n--- 4. Nested Transactions ---');
  const nestedTx = new NestedTransaction(mockDB);
  nestedTx.begin();
  console.log(`Depth: ${nestedTx.getDepth()}`);

  nestedTx.begin();
  console.log(`Depth: ${nestedTx.getDepth()}`);

  nestedTx.commit();
  nestedTx.commit();

  // 5. Unit of Work
  console.log('\n--- 5. Unit of Work ---');
  const uow = new UnitOfWork(mockDB);
  uow.registerNew('users', { name: 'Eve' });
  uow.registerNew('users', { name: 'Frank' });

  if (uow.hasChanges()) {
    console.log('Committing changes...');
    // uow.commit();
  }

  console.log('\n=== All Transaction Handling Examples Completed ===');
}

// Export functions
export { TransactionManager, UnitOfWork, TransactionIsolation, NestedTransaction, TransactionLogger, DistributedTransactionCoordinator };
export { demonstrateTransactionHandling };

Tools

Tools frequently paired with this sample

Related

Related sample collections

Related

Related workflows

Tool usage guide

Sample-focused context for search and workflow understanding

Overview

The three exercises isolate different helpers. They use invented values, not information read from a person, browser or server.

Read data and format helpers without database or crypto execution

Do not launch the full entry point. No device, network, permission, authentication, browser, file or callback operation is exercised. Complete cards may still need dependency, syntax and integration repairs.

Read data and format helpers without database or crypto execution
CardReading exercise
SQLite ConnectionA null flag is not a health check
SQL Query ExecutionReset only builder state
Transaction HandlingReject an already-active state

How it works

  1. 1Choose a code card, follow fixed inputs, and distinguish local calculations from real device or server behavior.

Edge cases & common failures

  • Scope and limits

    Do not launch the full entry point. No device, network, permission, authentication, browser, file or callback operation is exercised. Complete cards may still need dependency, syntax and integration repairs.

Examples

1. A null flag is not a health check

How to use

On paper, inspect isOpen in “SQLite Connection”. Substitute: db=null; then synthetic db={}.

Outcome

Expected source trace: false; true. The method only compares db with null. A synthetic non-null object passes this test without being a usable database. No library loading, database creation or query is performed.

2. Reset only builder state

How to use

On paper, inspect QueryBuilder.reset in “SQL Query Execution”. Substitute: prior builder state arbitrary; inspect reset assignments.

Outcome

Expected source trace: table=""; selectColumns=["*"]; conditions/joins/params=[]; limit=0; offset=0. Follow assignments in reset, without building or executing a query. Arrays are replaced by empty arrays except selectColumns, which returns to the wildcard. The method returns the same builder object for chaining.

3. Reject an already-active state

How to use

On paper, inspect TransactionManager.begin in “Transaction Handling”. Substitute: inTransaction=true.

Outcome

Expected source trace: Error("Transaction already in progress"); db.run calls=0. Substitute the Boolean flag only. The error is selected before db.run and before changing state. No transaction begins, no callback runs and no database handle is needed.

Hub

Infer JSON Schema from Samples and Spreadsheets

Derive a reviewable JSON Schema draft from representative JSON samples or XLSX rows, then inspect types, requiredness, nulls, and outliers before treating it as a contract.

Hub

Population Ecology, Biodiversity, and Food Web Calculators

Use educational ecology models to explore population growth, carrying capacity, species interactions, sampled biodiversity, and trophic energy transfer.

Hub

Authorized Webhook Capture and Endpoint Trust Checks

Capture and replay controlled webhook requests, inspect delivery evidence, and check the receiver HTTPS certificate without treating a valid certificate as proof of business trust.

Hub

Database Schema Migration

Plan a safe database migration by normalizing source data, reviewing schema changes, validating relationships, and preparing rollback assets.

Home
Tools3216
Samples593
Visualizations473
Viewers103
Hubs239
Tags333
Images, Audio & Video657

Image, audio, and video processing, conversion, and optimization tools

Math, Date & Finance647

Calculators, numerics, date logic, statistics, and finance tools

Design & Color298

Color, layout, graphics, visual styling, and design helper tools

Text & Writing209

Writing, cleanup, formatting, extraction, and text analysis tools

Developer & Web194

Developer utilities, networking, web debugging, and automation helpers

Conversion & Encoding175

Format, file, archive, unit, and encoding conversion tools

Lead Generation Quiz Launch and Contact Handoff
Memorial Drafts: Family Review and Print Handoff
Private Habit Records: Backup, Restore and Sharing Review
Article to X and LinkedIn Drafts: Fact Review and Handoff
Markdown Interactive Deck Review and Offline Handoff

© 2026 Elysia Tools. All rights reserved.

DocsPricingAboutTerms of ServicePrivacy PolicyCookiesContact

Development

Database Backup Generator

Generate SQL backup scripts for database tables

Development

Database Schema Diff

Compare two database schemas and generate migration-oriented change reports

Development

Database Migration Generator

Generate SQL migration scripts for database schema changes

Data Processing

CSV to Database Migration Planner

Infer a relational schema from CSV data and generate create-table plus ALTER migration plans for PostgreSQL, MySQL, SQLite, or SQL Server

Development

SQL Performance Analyzer

Analyze SQL queries for potential performance issues

Format Conversion

CSV Flavor to SQL INSERT Emitter

Parse CSV/Excel-CSV/TSV (auto dialect, RFC 4180) into MySQL/PostgreSQL/SQLite/SQL Server INSERT batches with type inference and ON CONFLICT/IGNORE

Development

SQL Explain Plan Visualizer

Parse EXPLAIN / EXPLAIN ANALYZE output (PostgreSQL/MySQL/SQLite) into a cost tree, flag estimated-vs-actual row divergence, and suggest indexes

Development

SQL Formatter

Format SQL queries

Java

Android Database Java Samples

Android Java database examples including SQLite connection, SQL queries, and transaction processing

Kotlin

Android Database Kotlin Samples

Android Kotlin database examples including SQLite connection, SQL queries, and transaction processing

Database ORMs

Drizzle ORM Samples

Drizzle ORM lightweight TypeScript ORM examples including schema definition, queries, migrations, relationships, and database patterns

Objective-C

macOS Database Objective-C Samples

macOS Objective-C database examples including SQLite CRUD operations, queries, transactions, and connection management

Swift

macOS Database Swift Samples

macOS Swift database examples including SQLite connection, query execution, and transaction processing

Data Formats

SQL Scripts Samples

SQL query examples and database scripts for various scenarios and use cases (primarily MySQL syntax)