abode/test/backends/sqlite/sql.test.ts

import { describe, it } from "node:test";
import assert from "node:assert/strict";
import {
  sql,
  catSql,
  joinSql,
  calcUpdates,
  unsafeSql,
} from "../../../src/db/sqlite/sql.js";

describe("sql template tag", () => {
  it("produces correct sql and empty vars for plain text", () => {
    const result = sql`SELECT 1`;
    assert.equal(result._sql, "SELECT 1");
    assert.deepEqual(result._vars, []);
  });

  it("binds text args with ?", () => {
    const result = sql`WHERE name = ${{ text: "alice" }}`;
    assert.equal(result._sql, "WHERE name = ?");
    assert.deepEqual(result._vars, ["alice"]);
  });

  it("binds uuid args with ? and converts to Buffer", () => {
    const uuid = "12345678-1234-1234-1234-123456789abc";
    const result = sql`WHERE uid = ${{ uuid }}`;
    assert.equal(result._sql, "WHERE uid = ?");
    assert.equal(result._vars.length, 1);
    assert.ok(result._vars[0] instanceof Buffer, "uuid is stored as Buffer");
  });

  it("binds jsonb args with jsonb(?) wrapper", () => {
    const result = sql`SET flags = ${{ jsonb: { admin: true } }}`;
    assert.equal(result._sql, "SET flags = jsonb(?)");
    assert.deepEqual(result._vars, [JSON.stringify({ admin: true })]);
  });

  it("binds date args with datetime(?, ...) wrapper", () => {
    const date = "2024-01-01T00:00:00.000Z";
    const result = sql`SET ts = ${{ date }}`;
    assert.ok(result._sql.startsWith("SET ts = datetime("), result._sql);
    assert.equal(result._vars.length, 1);
  });

  it("binds int args with ?", () => {
    const result = sql`LIMIT ${{ int: 10 }}`;
    assert.equal(result._sql, "LIMIT ?");
    assert.deepEqual(result._vars, [10]);
  });

  it("throws for non-integer int value", () => {
    assert.throws(() => sql`LIMIT ${{ int: 10.5 }}`, /Not an integer/);
  });

  it("emits NULL for null args", () => {
    const result = sql`= ${{ null: true }}`;
    assert.equal(result._sql, "= NULL");
    assert.deepEqual(result._vars, []);
  });

  it("splices nested SqlCode", () => {
    const inner = sql`AND x = ${{ text: "foo" }}`;
    const outer = sql`WHERE 1=1 ${inner}`;
    assert.equal(outer._sql, "WHERE 1=1 AND x = ?");
    assert.deepEqual(outer._vars, ["foo"]);
  });

  it("handles multiple args", () => {
    const result = sql`INSERT INTO t(a,b) VALUES(${{ text: "x" }}, ${{ int: 42 }})`;
    assert.equal(result._sql, "INSERT INTO t(a,b) VALUES(?, ?)");
    assert.deepEqual(result._vars, ["x", 42]);
  });
});

describe("catSql", () => {
  it("concatenates sql and vars", () => {
    const a = sql`SELECT * FROM t`;
    const b = sql` WHERE x = ${{ text: "y" }}`;
    const result = catSql(a, b);
    assert.equal(result._sql, "SELECT * FROM t WHERE x = ?");
    assert.deepEqual(result._vars, ["y"]);
  });
});

describe("joinSql", () => {
  it("joins multiple parts with separator", () => {
    const parts = [
      sql`a = ${{ text: "1" }}`,
      sql`b = ${{ text: "2" }}`,
      sql`c = ${{ text: "3" }}`,
    ];
    const result = joinSql(parts, sql`, `);
    assert.equal(result._sql, "a = ?, b = ?, c = ?");
    assert.deepEqual(result._vars, ["1", "2", "3"]);
  });

  it("returns single part unchanged (no separator)", () => {
    const result = joinSql([sql`x = ${{ int: 1 }}`], sql`, `);
    assert.equal(result._sql, "x = ?");
    assert.deepEqual(result._vars, [1]);
  });
});

describe("calcUpdates", () => {
  it("returns only keys present in the object", () => {
    const calc = calcUpdates({
      name: (v: string) => sql`name = ${{ text: v }}`,
      email: (v: string) => sql`email = ${{ text: v }}`,
    });
    const updates = calc({ name: "alice" });
    assert.equal(updates.length, 1);
    assert.equal(updates[0]._sql, "name = ?");
    assert.deepEqual(updates[0]._vars, ["alice"]);
  });

  it("returns all keys when all are present", () => {
    const calc = calcUpdates({
      a: (v: string) => sql`a = ${{ text: v }}`,
      b: (v: string) => sql`b = ${{ text: v }}`,
    });
    const updates = calc({ a: "x", b: "y" });
    assert.equal(updates.length, 2);
  });

  it("returns empty array when no keys match", () => {
    const calc = calcUpdates({
      name: (v: string) => sql`name = ${{ text: v }}`,
    });
    const updates = calc({});
    assert.equal(updates.length, 0);
  });
});

describe("unsafeSql", () => {
  it("wraps a raw sql string with no vars", () => {
    const result = unsafeSql("CREATE TABLE t (id INTEGER)");
    assert.equal(result._sql, "CREATE TABLE t (id INTEGER)");
    assert.deepEqual(result._vars, []);
  });
});