Triplit client sqlite

client.ts

import { open } from '@op-engineering/op-sqlite';
import { GenericSQLiteStorage, SQLiteAdapter } from './storage/sqlite';
import { OPSQLiteAdapter } from './storage/op-sqlite-adapter';

const cacheDb = open({ name: 'cacheDB' });
const outboxDb = open({ name: 'outboxDB' });
const cacheStorage = new GenericSQLiteStorage(new OPSQLiteAdapter(cacheDb));
// Uncomment line below to "reset" the data in the cache
// cacheDb.execute('DELETE FROM data;');
const outboxStorage = new GenericSQLiteStorage(new OPSQLiteAdapter(outboxDb));
// Uncomment line below to "reset" the data in the outbox
// outboxDb.execute('DELETE FROM data;');

export const client = new TriplitClient({
  storage: {
    cache: cacheStorage,
    outbox: outboxStorage,
  },
});

op-sqlite-adapter.ts

import { OPSQLiteConnection } from '@op-engineering/op-sqlite';
import { SQLiteAdapter } from './sqlite';

export class OPSQLiteAdapter implements SQLiteAdapter {
  constructor(private db: OPSQLiteConnection) {}
  execute(sql: string, args?: any[] | undefined) {
    return this.db.execute(sql, args);
  }
  resultToRows(result: any): { key: string; value: string }[] {
    if (!result.rows) return [];
    return result.rows._array as { key: string; value: string }[];
  }
  write(writes: { inserts?: [string, string][]; deletes?: string[] }) {
    for (const kv of writes.inserts || []) {
      this.db.execute(`insert or replace into data values (?, ?)`, kv);
    }
    for (const key of writes.deletes || []) {
      this.db.execute(`delete from data where key = ?`, [key]);
    }
  }
  close() {
    this.db.close();
  }
}

sqlite.ts

import {
  TupleStorageApi,
  KeyValuePair,
  ScanStorageArgs,
  WriteOps,
  encodeTuple,
  decodeTuple,
} from '@triplit/db/node_modules/@triplit/tuple-database';

export class GenericSQLiteStorage implements TupleStorageApi {
  constructor(private adapter: SQLiteAdapter) {
    this.adapter.execute(
      `create table if not exists data (key text primary key, value text)`
    );
  }
  scan(args: ScanStorageArgs = {}): KeyValuePair[] {
    // Bounds.
    let start = args.gte ? encodeTuple(args.gte) : undefined;
    let startAfter: string | undefined = args.gt
      ? encodeTuple(args.gt)
      : undefined;
    let end: string | undefined = args.lte ? encodeTuple(args.lte) : undefined;
    let endBefore: string | undefined = args.lt
      ? encodeTuple(args.lt)
      : undefined;

    const sqlArgs = [start, startAfter, end, endBefore, args.limit].filter(
      Boolean
    );
    const where = [
      start ? 'key >= ?' : undefined,
      startAfter ? 'key > ?' : undefined,
      end ? 'key <= ?' : undefined,
      endBefore ? 'key < ?' : undefined,
    ]
      .filter(Boolean)
      .join(' and ');

    let sqlQuery = `select * from data`;
    if (where) {
      sqlQuery += ' where ';
      sqlQuery += where;
    }
    sqlQuery += ' order by key';
    if (args.reverse) {
      sqlQuery += ' desc';
    }
    if (args.limit) {
      sqlQuery += ` limit ?`;
    }
    const result = this.adapter.execute(sqlQuery, sqlArgs);
    const data = this.adapter.resultToRows(result);
    return data.map((kv) => {
      return {
        key: decodeTuple(kv.key),
        value: JSON.parse(kv.value),
      } as KeyValuePair;
    });
  }
  commit(writes: WriteOps): void {
    this.adapter.write({
      inserts: writes?.set?.map<[string, string]>((kv) => [
        encodeTuple(kv.key),
        JSON.stringify(kv.value),
      ]),
      deletes: writes?.remove?.map((key) => encodeTuple(key)),
    });
  }
  close(): void {
    this.adapter.close();
  }
}

export interface SQLiteAdapter {
  resultToRows(result: any): { key: string; value: string }[];
  write(writes: { inserts?: [string, string][]; deletes?: string[] }): void;
  execute(sql: string, args?: any[]): any;
  close(): void;
}