sql.js in-memory SQLite CRUD: working versions (tested 2026-10-15)

{
  "name": "sqljs-in-memory-queries",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {"sql.js":"1.15.0"},
  "devDependencies": {"@types/node":"26.6.5","tsx":"4.23.15"}
}

import initSqlJs, { type Database } from "sql.js";

export async function openNotesDb(existing?: Uint8Array) {
  const SQL = await initSqlJs();
  const db: Database = existing ? new SQL.Database(existing) : new SQL.Database();
  db.run(`CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT NOT NULL
  )`);
  return {
    add(title: string, body: string) {
      db.run("INSERT INTO notes (title, body) VALUES (?, ?)", [title, body]);
      const row = db.exec("SELECT last_insert_rowid() AS id")[0];
      return Number(row.values[0][0]);
    },
    list() {
      const result = db.exec("SELECT id, title, body FROM notes ORDER BY id");
      if (!result.length) return [] as { id: number; title: string; body: string }[];
      return result[0].values.map(([id, title, body]) => ({
        id: Number(id),
        title: String(title),
        body: String(body),
      }));
    },
    find(id: number) {
      const stmt = db.prepare("SELECT id, title, body FROM notes WHERE id = ?");
      stmt.bind([id]);
      if (!stmt.step()) {
        stmt.free();
        return null;
      }
      const row = stmt.getAsObject() as { id: number; title: string; body: string };
      stmt.free();
      return row;
    },
    export() {
      return db.export();
    },
    close() {
      db.close();
    },
  };
}

import { test } from "node:test";
import assert from "node:assert/strict";
import { openNotesDb } from "../src/db.ts";

test("crud and roundtrip export", async () => {
  const db = await openNotesDb();
  const id = db.add("hello", "world");
  assert.equal(id, 1);
  assert.deepEqual(db.find(1), { id: 1, title: "hello", body: "world" });
  assert.equal(db.list().length, 1);
  const buf = db.export();
  db.close();

  const restored = await openNotesDb(buf);
  assert.equal(restored.list()[0].title, "hello");
  restored.close();
});