SQLite sample for nodejs

sqlite-sample.mjs

import { DatabaseSync } from 'node:sqlite';
const isMemory = true;
const path = isMemory ? ':memory:' : 'path/to/file';
const database = new DatabaseSync(path);


export class ChangeEventTable {
  /** @type {DatabaseSync} */
  db;

  static tableName = 'change_events';

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

  /**
   * 
   * @param {{tableName:string, idColumnName?:string, parentIdColumnName?:string}} target 
   * @returns 
   */
  adopt(target) {
    if(Array.isArray(target)) {
      targetTableName.forEach(v => this.adopt(v));
      return this;
    }
    const targetTableName = target.tableName;
    const idColumnName = target.idColumnName || '_id';
    const parentIdColumnName = target.parentIdColumnName || idColumnName;

    this.db.exec(`
      CREATE TABLE IF NOT EXISTS ${ChangeEventTable.tableName} (
        _id INTEGER PRIMARY KEY,
        target_table TEXT NOT NULL,
        target_id TEXT NOT NULL,
        target_parent_id TEXT NOT NULL,
        event_type TEXT NOT NULL,
        event_at TEXT DEFAULT (datetime(CURRENT_TIMESTAMP, 'localtime')) NOT NULL
      );
    `);
    this.db.exec(`
      CREATE TRIGGER table_after_insert_${targetTableName}
      AFTER INSERT ON ${targetTableName}
      FOR EACH ROW
      BEGIN
        INSERT INTO ${ChangeEventTable.tableName} (event_type, target_table, target_id, target_parent_id)
        VALUES ('INSERT', '${targetTableName}', NEW.${idColumnName}, NEW.${parentIdColumnName});
      END;

      CREATE TRIGGER table_after_update_${targetTableName}
      AFTER UPDATE ON ${targetTableName}
      FOR EACH ROW
      BEGIN
        INSERT INTO ${ChangeEventTable.tableName} (event_type, target_table, target_id, target_parent_id)
        VALUES ('UPDATE', '${targetTableName}', OLD.${idColumnName}, OLD.${parentIdColumnName});
      END;

      CREATE TRIGGER table_after_delete_${targetTableName}
      AFTER DELETE ON ${targetTableName}
      FOR EACH ROW
      BEGIN
        INSERT INTO ${ChangeEventTable.tableName} (event_type, target_table, target_id, target_parent_id)
        VALUES ('DELETE', '${targetTableName}', OLD.${idColumnName}, OLD.${parentIdColumnName});
      END;
    `);

    return this;
    
  }

  selectAll() {
    const query = this.db.prepare(`SELECT * FROM change_events`);
    return query.all();
  }
  remove(id) {
    const deleteTask = this.db.prepare(`DELETE FROM change_events WHERE _id = ?`);
    deleteTask.run(id);
  }

  onChange(cb) {
    setInterval(() => {
      const events = this.selectAll();
      events.forEach(event => {
        try {
          cb(event);
          console.log(JSON.parse(JSON.stringify(event)));
          this.remove(event._id);
        } catch {}
        
      });
    }, 1000);
  }
}

class Table {
  tableName;
  /** @type {DatabaseSync} */
  db;
  createDate;
  constructor(tableName, db, createDate = () => Date.now()) {
    this.tableName = tableName;
    this.db = db;
    this.createDate = createDate;
    console.log(this.tableName);
  }
  init() {
    database.exec(`
      CREATE TABLE IF NOT EXISTS ${this.tableName} (
      _id INTEGER PRIMARY KEY,
      contents JSON NOT NULL,
      created_at INTEGER NOT NULL,
      modified_at INTEGER
    );
    `);
  }
  insert(contents) {
    const date = this.createDate();
    const insertTask = this.db.prepare(`
      INSERT INTO ${this.tableName} (contents, created_at, modified_at) 
      VALUES (?, ?, ?)`
    );
    const result = insertTask.run(JSON.stringify(contents), date, date);
    const id = result.lastInsertRowid;
    return id;
  }
  update(id, contents) {
    const date = this.createDate();
    const updateTask = this.db.prepare(`
      UPDATE ${this.tableName} 
      SET contents = ?, modified_at = ? 
      WHERE _id = ?`
    );
    updateTask.run(JSON.stringify(contents), date, id);
  }
  
  selectAll() {
    const query = this.db.prepare(`SELECT * FROM ${this.tableName}`);
    return query.all();
  }
  
  static init = `
  CREATE TABLE IF NOT EXISTS events (
    _id INTEGER PRIMARY KEY,
    target_table TEXT NOT NULL,
    target_id INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    event_at INTEGER NOT NULL
  );
  `.trim();
}

const taskTable = new Table('tasks', database);
taskTable.init();

const changeEvent = new ChangeEventTable(database);
changeEvent.adopt({tableName:taskTable.tableName});
changeEvent.onChange((event) => {});

taskTable.insert({ title: 'Buy groceries' });
taskTable.insert({ title: 'Clean the house' });
taskTable.update(1, { title: 'Buy groceries and cook dinner' });

// console.log(changeEvent.selectAll());