WRNexusJS/packages/db/test/db.test.ts

import { test, expect } from "bun:test";
import { mkdtempSync, writeFileSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";
import {
  v,
  table,
  createDb,
  createTableSql,
  parseMigration,
  migrate,
  status,
  rollback,
  parseQueries,
  generateQueriesFile,
} from "../src/index.ts";
import { sqlite } from "../src/adapters/sqlite.ts";
import { bunSql } from "../src/adapters/bunsql.ts";

const users = table<{ id: number; email: string; name: string; active: boolean }>("users", {
  id: v.id(),
  email: v.string().unique(),
  name: v.string(),
  active: v.boolean().default(true),
});

test("createTableSql renders dialect-specific DDL", () => {
  expect(createTableSql(users, "sqlite")).toContain("INTEGER PRIMARY KEY AUTOINCREMENT");
  const pg = createTableSql(users, "postgres");
  expect(pg).toContain("SERIAL PRIMARY KEY");
  expect(pg).toContain("BOOLEAN");
});

test("model.parse coerces DB rows to typed values", () => {
  const row = users.parse({ id: "1", email: "a@b.com", name: "Ann", active: 1 });
  expect(row.id).toBe(1);
  expect(row.active).toBe(true);
});

for (const [label, driver] of [
  ["bun:sqlite", () => sqlite()],
  ["Bun.sql/sqlite", () => bunSql("sqlite://:memory:", "sqlite")],
] as const) {
  test(`CRUD + transactions [${label}]`, async () => {
    const db = createDb(driver());
    await db.createTable(users);
    await db.exec("INSERT INTO users (email, name, active) VALUES (?, ?, ?)", [
      "a@b.com",
      "Ann",
      true,
    ]);
    // rollback
    try {
      await db.tx(async (t) => {
        await t.exec("INSERT INTO users (email, name) VALUES (?, ?)", ["x@y.com", "X"]);
        throw new Error("boom");
      });
    } catch {
      /* expected */
    }
    // commit
    await db.tx(async (t) => {
      await t.exec("INSERT INTO users (email, name) VALUES (?, ?)", ["c@d.com", "Cy"]);
    });
    const rows = await db.all("SELECT * FROM users ORDER BY id", [], users);
    expect(rows.map((r) => r.name)).toEqual(["Ann", "Cy"]);
    expect(typeof rows[0]!.active).toBe("boolean");
    db.close();
  });
}

test("migration runner: parse, migrate, status, rollback", async () => {
  const dir = mkdtempSync(join(tmpdir(), "wire-mig-"));
  writeFileSync(
    join(dir, "0001_init.sql"),
    "-- +up\nCREATE TABLE t (id INTEGER PRIMARY KEY, n TEXT);\n-- +down\nDROP TABLE t;",
  );
  const parsed = parseMigration(
    "0001_init",
    "-- +up\nCREATE TABLE t (id INTEGER);\n-- +down\nDROP TABLE t;",
  );
  expect(parsed.up).toContain("CREATE TABLE t");
  expect(parsed.down).toContain("DROP TABLE t");

  const db = createDb(sqlite());
  expect(await migrate(db, dir)).toEqual(["0001_init"]);
  expect(await migrate(db, dir)).toEqual([]); // idempotent
  expect((await status(db, dir))[0]).toEqual({ name: "0001_init", applied: true });
  const tablesAfter = await db.all<{ name: string }>(
    "SELECT name FROM sqlite_master WHERE type='table' AND name='t'",
  );
  expect(tablesAfter.length).toBe(1);
  expect(await rollback(db, dir)).toBe("0001_init");
  expect((await status(db, dir))[0]!.applied).toBe(false);
  db.close();
});

test("query generator infers params and result types", () => {
  const q = parseQueries(
    "-- name: GetByEmail :one\nSELECT * FROM users WHERE email = :email;\n" +
      "-- name: CountActive :one\nSELECT COUNT(*) AS n FROM users WHERE active = :active;\n" +
      "-- name: Create :exec\nINSERT INTO users (email, name) VALUES (:email, :name);",
  );
  expect(q.map((x) => x.name)).toEqual(["GetByEmail", "CountActive", "Create"]);
  const code = generateQueriesFile(q, [{ varName: "users", model: users }], "sqlite");
  expect(code).toContain("GetByEmail(db: Db, args: { email: string })");
  expect(code).toContain(
    "CountActive(db: Db, args: { active: boolean }): Promise<{ n: number } | null>",
  );
  expect(code).toContain(
    "Create(db: Db, args: { email: string; name: string }): Promise<ExecResult>",
  );
});

test("paginate returns a page window with correct metadata", async () => {
  const { paginate } = await import("../src/index.ts");
  const db = createDb(sqlite());
  await db.createTable(users);
  for (let i = 1; i <= 25; i++) {
    await db.exec("INSERT INTO users (email, name, active) VALUES (?, ?, ?)", [
      `u${i}@x.com`,
      `U${i}`,
      true,
    ]);
  }
  const p2 = await paginate(
    db,
    { sql: "SELECT * FROM users ORDER BY id", model: users },
    { page: 2, perPage: 10 },
  );
  expect(p2.total).toBe(25);
  expect(p2.totalPages).toBe(3);
  expect(p2.items.length).toBe(10);
  expect(p2.items[0]!.name).toBe("U11");
  expect(p2.hasNext).toBe(true);
  expect(p2.hasPrev).toBe(true);

  const p3 = await paginate(
    db,
    { sql: "SELECT * FROM users ORDER BY id" },
    { page: 3, perPage: 10 },
  );
  expect(p3.items.length).toBe(5);
  expect(p3.hasNext).toBe(false);
  await db.close();
});

test("loadRelated batches children onto parents (no N+1)", async () => {
  const { loadRelated } = await import("../src/index.ts");
  const db = createDb(sqlite());
  await db.exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
  await db.exec("CREATE TABLE posts (id INTEGER PRIMARY KEY, userId INTEGER, title TEXT)");
  await db.exec("INSERT INTO users (id, name) VALUES (1, 'Ann'), (2, 'Bob')");
  await db.exec(
    "INSERT INTO posts (id, userId, title) VALUES (1, 1, 'a'), (2, 1, 'b'), (3, 2, 'c')",
  );

  const parents = await db.all<{ id: number; name: string }>("SELECT * FROM users ORDER BY id");
  const withPosts = await loadRelated(db, parents, {
    table: "posts",
    foreignKey: "userId",
    as: "posts",
  });
  expect((withPosts[0]!.posts as unknown[]).length).toBe(2);
  expect((withPosts[1]!.posts as unknown[]).length).toBe(1);
  await db.close();
});

test("loadRelated rejects unsafe identifiers", async () => {
  const { loadRelated } = await import("../src/index.ts");
  const db = createDb(sqlite());
  await expect(
    loadRelated(db, [{ id: 1 }], {
      table: "posts; DROP TABLE users",
      foreignKey: "userId",
      as: "x",
    }),
  ).rejects.toThrow("Unsafe table name");
  await db.close();
});

test("sqliteSessionStore persists sessions (get/set/delete/gc)", async () => {
  const { sqliteSessionStore } = await import("../src/session-store.ts");
  const store = sqliteSessionStore(":memory:");
  expect(store.get("s1")).toBeUndefined();
  store.set("s1", { data: { user: 7 }, expiresAt: Date.now() + 10_000 });
  expect(store.get("s1")!.data).toEqual({ user: 7 });
  store.set("s1", { data: { user: 8 }, expiresAt: Date.now() + 10_000 }); // upsert
  expect(store.get("s1")!.data).toEqual({ user: 8 });
  store.set("old", { data: {}, expiresAt: Date.now() - 1 });
  store.gc!(Date.now());
  expect(store.get("old")).toBeUndefined();
  store.delete("s1");
  expect(store.get("s1")).toBeUndefined();
});