Build your personal knowledge base with Trilium Notes
TypeScript electronelectron-appknowledge-baseknowledge-graphknowledge-managementknowledge-management-graphlocal-firstnote-managmentnote-takernote-takingnotebooknotesnotes-apppersonal-knowledge-basepersonal-wikiscriptableself-hostedself-hostingwiki
import { beforeAll, describe, expect, it } from "vitest";
import { getContext } from "../context.js";
import { getLog } from "../log.js";
import { getSql } from "./index.js";
import { SqlService, type SqlServiceParams } from "./sql.js";
// Standalone runs core in a Web Worker, whose scope has `self`; the Node server suite has none.
const isBrowserRuntime = typeof self !== "undefined";
let tableCounter = 0;
/**
* Creates a fresh isolated temp table for write tests. Each test gets its own
* table name so that the shared in-memory fixture DB (one copy per spec file)
* doesn't leak rows between `it()`s.
*/
function createTempTable(columns = "id INTEGER PRIMARY KEY, name TEXT, val INTEGER"): string {
tableCounter++;
const name = `sql_spec_tbl_${tableCounter}`;
getSql().executeScript(`CREATE TEMP TABLE "${name}" (${columns})`);
return name;
}
describe("SqlService (real DB)", () => {
beforeAll(() => {
// Touching getSql() throws if core wasn't initialised; assert the
// suite-level fixture really booted the service.
expect(getSql()).toBeDefined();
});
describe("insert / replace / upsert", () => {
it("inserts a row and returns the last insert rowid, joining columns and values", () => {
const table = createTempTable();
const rowid = getSql().insert(table, { name: "alpha", val: 10 });
expect(typeof rowid).toBe("number");
const row = getSql().getRow<{ name: string; val: number }>(
`SELECT name, val FROM "${table}" WHERE rowid = ?`,
[rowid]
);
expect(row).toEqual({ name: "alpha", val: 10 });
});
it("returns undefined and does not insert when the record is empty", () => {
const table = createTempTable();
const result = getSql().insert(table, {});
expect(result).toBeUndefined();
expect(getSql().getValue<number>(`SELECT COUNT(*) FROM "${table}"`)).toBe(0);
});
it("replace overwrites a conflicting primary key via INSERT OR REPLACE", () => {
const table = createTempTable();
getSql().insert(table, { id: 1, name: "first", val: 1 });
const rowid = getSql().replace(table, { id: 1, name: "second", val: 2 });
expect(typeof rowid).toBe("number");
const rows = getSql().getRows<{ name: string }>(`SELECT name FROM "${table}"`);
expect(rows).toEqual([{ name: "second" }]);
});
it("upsert inserts then updates on conflict and coerces booleans to 0/1", () => {
const table = createTempTable("id INTEGER PRIMARY KEY, name TEXT, flag INTEGER");
getSql().upsert(table, "id", { id: 5, name: "orig", flag: true });
expect(getSql().getValue<number>(`SELECT flag FROM "${table}" WHERE id = 5`)).toBe(1);
getSql().upsert(table, "id", { id: 5, name: "updated", flag: false });
const row = getSql().getRow<{ name: string; flag: number }>(
`SELECT name, flag FROM "${table}" WHERE id = 5`
);
// Conflict path updated the existing row in place; boolean false -> 0.
expect(row).toEqual({ name: "updated", flag: 0 });
expect(getSql().getValue<number>(`SELECT COUNT(*) FROM "${table}"`)).toBe(1);
});
it("upsert is a no-op for an empty record", () => {
const table = createTempTable();
getSql().upsert(table, "id", {});
expect(getSql().getValue<number>(`SELECT COUNT(*) FROM "${table}"`)).toBe(0);
});
});
describe("row readers", () => {
it("getRow / getRowOrNull / getValue / getColumn / getRows read a seeded table", () => {
const table = createTempTable();
getSql().insert(table, { name: "a", val: 1 });
getSql().insert(table, { name: "b", val: 2 });
expect(getSql().getValue<number>(`SELECT COUNT(*) FROM "${table}"`)).toBe(2);
expect(
getSql().getColumn<string>(`SELECT name FROM "${table}" ORDER BY val`)
).toEqual(["a", "b"]);
const all = getSql().getRows<{ name: string }>(
`SELECT name FROM "${table}" ORDER BY val`
);
expect(all.map((r) => r.name)).toEqual(["a", "b"]);
const first = getSql().getRowOrNull<{ name: string }>(
`SELECT name FROM "${table}" ORDER BY val LIMIT 1`
);
expect(first).toEqual({ name: "a" });
const single = getSql().getRow<{ name: string }>(
`SELECT name FROM "${table}" WHERE val = ?`,
[2]
);
expect(single).toEqual({ name: "b" });
});
it("keeps a plucking reader from changing what the same query returns to a row reader", () => {
// The statement cache is keyed by query text and both pluck and raw stick to a statement
// once set, so a plucking read used to leave every later read of the byte-identical query
// returning bare values instead of rows — for the rest of the process, and wherever in the
// codebase that query happened to be repeated.
const table = createTempTable();
getSql().insert(table, { name: "a", val: 1 });
const query = `SELECT name FROM "${table}" WHERE val = ?`;
expect(getSql().getValue<string>(query, [1])).toBe("a");
expect(getSql().getRow<{ name: string }>(query, [1])).toEqual({ name: "a" });
expect(getSql().getRowOrNull<{ name: string }>(query, [1])).toEqual({ name: "a" });
expect(getSql().getRows<{ name: string }>(query, [1])).toEqual([{ name: "a" }]);
// ...and in the other order, so neither mode is merely winning by going first.
const reverse = `SELECT name FROM "${table}" WHERE val = ? /* reverse */`;
expect(getSql().getRows<{ name: string }>(reverse, [1])).toEqual([{ name: "a" }]);
expect(getSql().getColumn<string>(reverse, [1])).toEqual(["a"]);
expect(getSql().getRow<{ name: string }>(reverse, [1])).toEqual({ name: "a" });
});
it("getRowOrNull returns null when no rows match", () => {
const table = createTempTable();
expect(getSql().getRowOrNull(`SELECT name FROM "${table}" WHERE val = ?`, [999])).toBeNull();
});
it("getMap maps the first column to the second column", () => {
const table = createTempTable();
getSql().insert(table, { name: "k1", val: 100 });
getSql().insert(table, { name: "k2", val: 200 });
const map = getSql().getMap<string, number>(`SELECT name, val FROM "${table}"`);
expect(map).toEqual({ k1: 100, k2: 200 });
});
it("getRawRows returns positional arrays (raw mode) rather than keyed objects", () => {
const table = createTempTable();
getSql().insert(table, { name: "x", val: 7 });
const raw = getSql().getRawRows<[string, number]>(`SELECT name, val FROM "${table}"`);
expect(raw).toEqual([["x", 7]]);
});
it("getRawRowsBulk returns the same positional arrays getRawRows does", () => {
const table = createTempTable();
getSql().insert(table, { name: "x", val: 7 });
getSql().insert(table, { name: null, val: 0 });
getSql().insert(table, { name: `quote" and \\ backslash and ünïcode ☃`, val: -3 });
const columns = ["name", "val"];
const from = /*sql*/`FROM "${table}" ORDER BY rowid`;
expect(getSql().getRawRowsBulk<[string, number]>(columns, from)).toEqual(
getSql().getRawRows<[string, number]>(`SELECT ${columns.join(", ")} ${from}`)
);
});
it("getRawRowsBulk returns an empty array when nothing matches, and binds params", () => {
const table = createTempTable();
getSql().insert(table, { name: "keep", val: 1 });
getSql().insert(table, { name: "drop", val: 2 });
const name = ["name"];
expect(getSql().getRawRowsBulk(name, /*sql*/`FROM "${table}" WHERE val > 99`)).toEqual([]);
expect(getSql().getRawRowsBulk(name, /*sql*/`FROM "${table}" WHERE val = ?`, [1]))
.toEqual([["keep"]]);
});
it("iterateRows yields rows lazily", () => {
const table = createTempTable();
getSql().insert(table, { name: "i1", val: 1 });
getSql().insert(table, { name: "i2", val: 2 });
const names: string[] = [];
for (const row of getSql().iterateRows<{ name: string }>(
`SELECT name FROM "${table}" ORDER BY val`
)) {
names.push(row.name);
}
expect(names).toEqual(["i1", "i2"]);
});
});
describe("statement cache (stmt)", () => {
it("returns the same cached statement for an identical query string", () => {
const sql = getSql();
const a = sql.stmt("SELECT 1");
const b = sql.stmt("SELECT 1");
expect(a).toBe(b);
});
it("reads one query in every shape, whichever was asked for first", () => {
// Statements are shared and their output mode sticks, so the shape a read gets has to come
// from the read rather than from whatever the last one left behind. Keeping the modes in
// separate cache entries was not enough: the browser provider caches by query text too, and
// handed both entries the same statement.
const table = createTempTable();
getSql().insert(table, { name: "a", val: 1 });
const query = `SELECT name, val FROM "${table}"`;
expect(getSql().getColumn<string>(query)).toEqual(["a"]);
expect(getSql().getRawRows<[string, number]>(query)).toEqual([["a", 1]]);
expect(getSql().getRows<{ name: string; val: number }>(query)).toEqual([{ name: "a", val: 1 }]);
expect(getSql().getValue<string>(query)).toBe("a");
expect(getSql().getRow<{ name: string; val: number }>(query)).toEqual({ name: "a", val: 1 });
});
});
describe("getManyRows / executeMany (??? placeholder expansion)", () => {
it("getManyRows expands ??? into named params and chunks beyond the 100-param limit", () => {
const table = createTempTable();
const total = 250;
for (let i = 0; i < total; i++) {
getSql().insert(table, { name: `n${i}`, val: i });
}
const ids = Array.from({ length: total }, (_, i) => i);
const rows = getSql().getManyRows<{ val: number }>(
`SELECT val FROM "${table}" WHERE val IN (???)`,
ids
);
// All chunks (100 + 100 + 50) are concatenated into one result set.
expect(rows.length).toBe(total);
expect(new Set(rows.map((r) => r.val)).size).toBe(total);
});
it("getManyRows returns an empty array for an empty param list", () => {
const table = createTempTable();
expect(getSql().getManyRows(`SELECT val FROM "${table}" WHERE val IN (???)`, [])).toEqual([]);
});
it("executeMany runs the query for each chunk of params", () => {
const table = createTempTable("id INTEGER PRIMARY KEY, val INTEGER");
for (let i = 0; i < 150; i++) {
getSql().insert(table, { id: i, val: i });
}
const ids = Array.from({ length: 150 }, (_, i) => i);
getSql().executeMany(`DELETE FROM "${table}" WHERE val IN (???)`, ids);
expect(getSql().getValue<number>(`SELECT COUNT(*) FROM "${table}"`)).toBe(0);
});
});
describe("fillParamList", () => {
it("truncates and fills the param_list temp table with a de-duplicated set", () => {
getSql().fillParamList(["p1", "p2", "p2", "p3"]);
const ids = getSql()
.getColumn<string>("SELECT paramId FROM param_list ORDER BY paramId");
expect(ids).toEqual(["p1", "p2", "p3"]);
});
it("does nothing for an empty array (leaving any prior contents intact)", () => {
getSql().fillParamList(["only"]);
getSql().fillParamList([]);
// The early-return for an empty list skips the DELETE, so the
// previous single entry is still present.
expect(getSql().getColumn<string>("SELECT paramId FROM param_list")).toEqual(["only"]);
});
it("accepts a Set as input", () => {
getSql().fillParamList(new Set(["s1", "s2"]));
expect(
getSql().getColumn<string>("SELECT paramId FROM param_list ORDER BY paramId")
).toEqual(["s1", "s2"]);
});
});
describe("execute", () => {
it("runs a write and reports the number of changed rows", () => {
const table = createTempTable();
getSql().insert(table, { name: "a", val: 1 });
getSql().insert(table, { name: "b", val: 1 });
const res = getSql().execute(`UPDATE "${table}" SET val = 99 WHERE val = ?`, [1]);
expect(res.changes).toBe(2);
expect(getSql().getValue<number>(`SELECT COUNT(*) FROM "${table}" WHERE val = 99`)).toBe(2);
});
});
describe("wrap (error handling)", () => {
it("rethrows real SQL errors", () => {
expect(() => getSql().getRow("SELECT * FROM table_that_does_not_exist_xyz")).toThrow();
});
it("returns null instead of throwing when the connection is reported closed", () => {
const sql = getSql();
const result = sql.wrap("SELECT 1", () => {
throw new Error("The database connection is not open");
});
expect(result).toBeNull();
});
});
describe("transactional", () => {
it("commits the changes made inside the callback and returns its value", () => {
const table = createTempTable();
const ret = getContext().init(() =>
getSql().transactional(() => {
getSql().insert(table, { name: "committed", val: 1 });
return "done";
})
);
expect(ret).toBe("done");
expect(getSql().getValue<number>(`SELECT COUNT(*) FROM "${table}"`)).toBe(1);
});
it("rolls back all changes and rethrows when the callback throws", () => {
const table = createTempTable();
getSql().insert(table, { name: "before", val: 1 });
expect(() =>
getContext().init(() =>
getSql().transactional(() => {
getSql().insert(table, { name: "doomed", val: 2 });
throw new Error("boom");
})
)
).toThrow("boom");
// The pre-existing row survives, the in-transaction insert is rolled back.
const rows = getSql().getColumn<string>(`SELECT name FROM "${table}" ORDER BY name`);
expect(rows).toEqual(["before"]);
});
});
describe("transactionalAsync", () => {
it("commits an async callback and returns its resolved value", async () => {
const table = createTempTable();
const ret = await getContext().init(() =>
getSql().transactionalAsync(async () => {
getSql().insert(table, { name: "async", val: 1 });
return 42;
})
);
expect(ret).toBe(42);
expect(getSql().getValue<number>(`SELECT COUNT(*) FROM "${table}"`)).toBe(1);
});
it("rolls back and rethrows when the async callback rejects", async () => {
const table = createTempTable();
getSql().insert(table, { name: "keep", val: 1 });
await expect(
getContext().init(() =>
getSql().transactionalAsync(async () => {
getSql().insert(table, { name: "discard", val: 2 });
throw new Error("async-boom");
})
)
).rejects.toThrow("async-boom");
const rows = getSql().getColumn<string>(`SELECT name FROM "${table}" ORDER BY name`);
expect(rows).toEqual(["keep"]);
});
});
describe("disableSlowQueryLogging", () => {
it("sets the flag for the duration of the callback and restores the prior value", () => {
getContext().init(() => {
const ctx = getContext();
expect(ctx.get("disableSlowQueryLogging")).toBeFalsy();
const inner = getSql().disableSlowQueryLogging(() => {
return getContext().get("disableSlowQueryLogging");
});
expect(inner).toBe(true);
// Restored back to the original (undefined/false) value afterwards.
expect(ctx.get("disableSlowQueryLogging")).toBeFalsy();
});
});
it("restores the flag even if the callback throws", () => {
getContext().init(() => {
expect(() =>
getSql().disableSlowQueryLogging(() => {
throw new Error("inner-fail");
})
).toThrow("inner-fail");
expect(getContext().get("disableSlowQueryLogging")).toBeFalsy();
});
});
});
describe("detaching", () => {
it("lets go once, and says nothing the second time", () => {
// The setup screen erases the database, which detaches it; a restore started straight
// afterwards detaches again on its way to swapping the files in. That second one has
// nothing to do, and used to fail on a connection that was already gone.
let attached = true;
const provider = {
detach: () => { attached = false; },
isAttached: () => attached,
get inTransaction(): boolean {
if (!attached) throw new Error("DB not open.");
return false;
}
};
const sql = new SqlService({ provider } as unknown as SqlServiceParams, getLog());
sql.detachConnection();
expect(attached).toBe(false);
expect(() => sql.detachConnection()).not.toThrow();
});
});
describe("serialize", () => {
it("either throws (provider unsupported) or returns the serialized bytes", () => {
// Capability-aware: the server's better-sqlite3 provider has no
// serialize() and throws, while the WASM (sql.js) provider supports
// it and returns the database as a Uint8Array.
if (isBrowserRuntime) {
const bytes = getSql().serialize();
expect(bytes).toBeInstanceOf(Uint8Array);
} else {
expect(() => getSql().serialize()).toThrow("does not support serialization");
}
});
});
});