better-sqlite3 transactions with Zod-validated inputs: working versions (tested 2026-10-15)

{
  "name": "better-sqlite3-zod-transactions",
  "private": true,
  "type": "module",
  "scripts": {
    "test": "node --import tsx --test test/*.test.ts"
  },
  "dependencies": {
    "better-sqlite3": "13.0.3",
    "zod": "4.6.7"
  },
  "devDependencies": {
    "tsx": "4.23.15",
    "@types/node": "26.6.5",
    "@types/better-sqlite3": "9.6.0"
  }
}

import Database from "better-sqlite3";
import { z } from "zod";

export const Transfer = z.object({
  from: z.string().min(1),
  to: z.string().min(1),
  cents: z.number().int().positive(),
}).refine((t) => t.from !== t.to, { message: "from and to must differ", path: ["to"] });

const AccountRow = z.object({ id: z.string(), balance_cents: z.number().int() });
export type Account = z.infer<typeof AccountRow>;

export class InsufficientFunds extends Error {}

/** A tiny double-entry ledger on better-sqlite3 using synchronous transactions. */
export function openLedger(file = ":memory:") {
  const db = new Database(file);
  db.pragma("journal_mode = WAL");
  db.pragma("foreign_keys = ON");
  db.exec(`
    CREATE TABLE IF NOT EXISTS accounts (id TEXT PRIMARY KEY, balance_cents INTEGER NOT NULL CHECK (balance_cents >= 0));
    CREATE TABLE IF NOT EXISTS entries (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      account_id TEXT NOT NULL REFERENCES accounts(id),
      delta_cents INTEGER NOT NULL
    );
  `);

  const insertAccount = db.prepare("INSERT INTO accounts (id, balance_cents) VALUES (?, ?)");
  const getAccount = db.prepare("SELECT id, balance_cents FROM accounts WHERE id = ?");
  const adjust = db.prepare("UPDATE accounts SET balance_cents = balance_cents + @delta WHERE id = @id");
  const addEntry = db.prepare("INSERT INTO entries (account_id, delta_cents) VALUES (@id, @delta)");

  const account = (id: string): Account | undefined => {
    const row = getAccount.get(id);
    return row ? AccountRow.parse(row) : undefined;
  };

  // db.transaction wraps the function: it commits on return and rolls back if it throws.
  const transferTx = db.transaction((t: z.infer<typeof Transfer>) => {
    const from = account(t.from);
    if (!from || !account(t.to)) throw new Error("unknown account");
    if (from.balance_cents < t.cents) throw new InsufficientFunds(`${t.from} has ${from.balance_cents}`);
    adjust.run({ id: t.from, delta: -t.cents });
    addEntry.run({ id: t.from, delta: -t.cents });
    adjust.run({ id: t.to, delta: t.cents });
    addEntry.run({ id: t.to, delta: t.cents });
  });

  // Nested transactions become savepoints: one bad transfer in a batch can roll back the whole batch.
  const batchTx = db.transaction((items: unknown[]) => {
    for (const item of items) transferTx(Transfer.parse(item));
    return items.length;
  });

  return {
    db,
    open: (id: string, cents = 0) => insertAccount.run(id, cents),
    account,
    transfer: (input: unknown) => transferTx(Transfer.parse(input)),
    batch: (items: unknown[]) => batchTx(items),
    entryCount: () => (db.prepare("SELECT COUNT(*) AS n FROM entries").get() as { n: number }).n,
    close: () => db.close(),
  };
}

import { test } from "node:test";
import assert from "node:assert/strict";
import { ZodError } from "zod";
import { openLedger, InsufficientFunds } from "../src/ledger.ts";

function setup() {
  const l = openLedger();
  l.open("alice", 10_00);
  l.open("bob", 0);
  return l;
}

test("a transfer moves money and writes two entries", () => {
  const l = setup();
  l.transfer({ from: "alice", to: "bob", cents: 250 });
  assert.equal(l.account("alice")?.balance_cents, 750);
  assert.equal(l.account("bob")?.balance_cents, 250);
  assert.equal(l.entryCount(), 2);
  l.close();
});

test("insufficient funds rolls back with no partial writes", () => {
  const l = setup();
  assert.throws(() => l.transfer({ from: "bob", to: "alice", cents: 1 }), InsufficientFunds);
  assert.equal(l.account("alice")?.balance_cents, 1000);
  assert.equal(l.entryCount(), 0);
  l.close();
});

test("zod rejects bad input before touching the database", () => {
  const l = setup();
  assert.throws(() => l.transfer({ from: "alice", to: "alice", cents: 5 }), ZodError);
  assert.throws(() => l.transfer({ from: "alice", to: "bob", cents: 1.5 }), ZodError);
  assert.equal(l.entryCount(), 0);
  l.close();
});

test("a failing item rolls back the whole batch", () => {
  const l = setup();
  assert.throws(() => l.batch([
    { from: "alice", to: "bob", cents: 400 },
    { from: "alice", to: "bob", cents: 400 },
    { from: "alice", to: "bob", cents: 400 },
  ]), InsufficientFunds);
  assert.equal(l.account("alice")?.balance_cents, 1000);
  assert.equal(l.account("bob")?.balance_cents, 0);
  assert.equal(l.entryCount(), 0);
  assert.equal(l.batch([{ from: "alice", to: "bob", cents: 100 }, { from: "bob", to: "alice", cents: 40 }]), 2);
  assert.equal(l.account("bob")?.balance_cents, 60);
  l.close();
});

test("the CHECK constraint is a last line of defence", () => {
  const l = setup();
  assert.throws(() => l.db.prepare("UPDATE accounts SET balance_cents = -1 WHERE id = 'bob'").run(), /CHECK constraint failed/);
  l.close();
});