import { describe, it, expect, beforeEach, afterEach } from "vitest"; import { Kysely, ParseJSONResultsPlugin, sql } from "kysely"; import { BunSqliteDialect } from "kysely-bun-sqlite"; import { Database } from "bun:sqlite"; import { tmpdir } from "node:os"; import { join } from "node:path"; import { unlinkSync, existsSync } from "node:fs"; import { randomUUID } from "node:crypto"; import { ParseBooleanResultsPlugin, introspectBooleanColumns } from "../kysely-parse-boolean.js"; import { createKyselyDatasourceClient } from "../kysely.bun.js"; interface TestDatabase { test_items: { id: string; name: string; is_active: number | null; is_verified: number | null; metadata: string | null; tags: string | null; }; other_table: { id: string; count: number; }; } describe("createKyselyDatasourceClient", () => { let dbPath: string; let db: Kysely; let booleanPlugin: ParseBooleanResultsPlugin; beforeEach(async () => { dbPath = join(tmpdir(), `test-kysely-${randomUUID()}.db`); // Create the plugin with an initially empty cache (will be updated after table creation) booleanPlugin = new ParseBooleanResultsPlugin(Promise.resolve(new Map())); db = new Kysely({ dialect: new BunSqliteDialect({ database: new Database(dbPath, { create: true }), }), plugins: [new ParseJSONResultsPlugin(), booleanPlugin], log: process.env.FLIPPABLE_ENV !== "production" ? ["query", "error"] : ["error"], }); // Create test table with various column types await sql` CREATE TABLE test_items ( id TEXT PRIMARY KEY, name TEXT NOT NULL, is_active BOOLEAN, is_verified BOOLEAN, metadata JSON, tags JSON ) `.execute(db); // Create a table without boolean columns for comparison await sql` CREATE TABLE other_table ( id TEXT PRIMARY KEY, count INTEGER ) `.execute(db); // Now introspect and update the plugin cache with the actual boolean columns const booleanColumns = await introspectBooleanColumns(db); booleanPlugin.updateCache(booleanColumns); }); afterEach(async () => { await db.destroy(); if (existsSync(dbPath)) { unlinkSync(dbPath); } }); describe("client creation", () => { it("should create a database client with file path", () => { expect(db).toBeDefined(); expect(existsSync(dbPath)).toBe(true); }); it("should create a database client with in-memory database", async () => { const memDb = new Kysely({ dialect: new BunSqliteDialect({ database: new Database(":memory:", { create: true }), }), plugins: [new ParseJSONResultsPlugin()], }); expect(memDb).toBeDefined(); await memDb.destroy(); }); }); describe("basic CRUD operations", () => { it("should insert and select a row", async () => { await db .insertInto("test_items") .values({ id: "1", name: "Test Item", is_active: 1, is_verified: 0, metadata: JSON.stringify({ foo: "bar" }), tags: JSON.stringify(["a", "b"]), }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(result).toBeDefined(); expect(result?.name).toBe("Test Item"); }); it("should update a row", async () => { await db .insertInto("test_items") .values({ id: "1", name: "Original", is_active: 1, is_verified: 0, metadata: null, tags: null, }) .execute(); await db.updateTable("test_items").set({ name: "Updated" }).where("id", "=", "1").execute(); const result = await db .selectFrom("test_items") .select("name") .where("id", "=", "1") .executeTakeFirst(); expect(result?.name).toBe("Updated"); }); it("should delete a row", async () => { await db .insertInto("test_items") .values({ id: "1", name: "To Delete", is_active: null, is_verified: null, metadata: null, tags: null, }) .execute(); await db.deleteFrom("test_items").where("id", "=", "1").execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(result).toBeUndefined(); }); it("should select with WHERE clause", async () => { await db .insertInto("test_items") .values([ { id: "1", name: "Active", is_active: 1, is_verified: 1, metadata: null, tags: null }, { id: "2", name: "Inactive", is_active: 0, is_verified: 0, metadata: null, tags: null }, { id: "3", name: "Also Active", is_active: 1, is_verified: 0, metadata: null, tags: null }, ]) .execute(); const results = await db.selectFrom("test_items").selectAll().where("is_active", "=", 1).execute(); expect(results).toHaveLength(2); expect(results.map((r) => r.name)).toContain("Active"); expect(results.map((r) => r.name)).toContain("Also Active"); }); }); describe("ParseBooleanResultsPlugin", () => { it("should convert 1 to true for BOOLEAN columns", async () => { await db .insertInto("test_items") .values({ id: "1", name: "Test", is_active: 1, is_verified: 1, metadata: null, tags: null, }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(result?.is_active).toBe(true); expect(result?.is_verified).toBe(true); }); it("should convert 0 to false for BOOLEAN columns", async () => { await db .insertInto("test_items") .values({ id: "1", name: "Test", is_active: 0, is_verified: 0, metadata: null, tags: null, }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(result?.is_active).toBe(false); expect(result?.is_verified).toBe(false); }); it("should preserve null for BOOLEAN columns", async () => { await db .insertInto("test_items") .values({ id: "1", name: "Test", is_active: null, is_verified: null, metadata: null, tags: null, }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(result?.is_active).toBeNull(); expect(result?.is_verified).toBeNull(); }); it("should NOT convert INTEGER columns that are not BOOLEAN", async () => { await db.insertInto("other_table").values({ id: "1", count: 1 }).execute(); const result = await db.selectFrom("other_table").selectAll().where("id", "=", "1").executeTakeFirst(); // count should remain as number 1, not converted to true expect(result?.count).toBe(1); expect(typeof result?.count).toBe("number"); }); it("should handle boolean insert and round-trip correctly", async () => { // Insert using JavaScript booleans (which Kysely will convert to 1/0) await sql` INSERT INTO test_items (id, name, is_active, is_verified, metadata, tags) VALUES ('1', 'Test', ${true}, ${false}, NULL, NULL) `.execute(db); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); // Should come back as JavaScript booleans expect(result?.is_active).toBe(true); expect(result?.is_verified).toBe(false); }); }); describe("ParseJSONResultsPlugin", () => { it("should parse JSON object columns", async () => { const metadata = { foo: "bar", nested: { baz: 123 } }; await db .insertInto("test_items") .values({ id: "1", name: "Test", is_active: null, is_verified: null, metadata: JSON.stringify(metadata), tags: null, }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(result?.metadata).toEqual(metadata); expect(typeof result?.metadata).toBe("object"); }); it("should parse JSON array columns", async () => { const tags = ["typescript", "vitest", "kysely"]; await db .insertInto("test_items") .values({ id: "1", name: "Test", is_active: null, is_verified: null, metadata: null, tags: JSON.stringify(tags), }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(result?.tags).toEqual(tags); expect(Array.isArray(result?.tags)).toBe(true); }); it("should preserve null for JSON columns", async () => { await db .insertInto("test_items") .values({ id: "1", name: "Test", is_active: null, is_verified: null, metadata: null, tags: null, }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(result?.metadata).toBeNull(); expect(result?.tags).toBeNull(); }); it("should handle complex nested JSON", async () => { const metadata = { users: [ { id: 1, name: "Alice", active: true }, { id: 2, name: "Bob", active: false }, ], config: { enabled: true, options: ["a", "b", "c"], }, }; await db .insertInto("test_items") .values({ id: "1", name: "Test", is_active: null, is_verified: null, metadata: JSON.stringify(metadata), tags: null, }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(result?.metadata).toEqual(metadata); }); }); describe("combined plugin behavior", () => { it("should correctly handle rows with both boolean and JSON columns", async () => { const metadata = { status: "active", count: 5 }; const tags = ["featured", "new"]; await db .insertInto("test_items") .values({ id: "1", name: "Combined Test", is_active: 1, is_verified: 0, metadata: JSON.stringify(metadata), tags: JSON.stringify(tags), }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); // Boolean conversions expect(result?.is_active).toBe(true); expect(result?.is_verified).toBe(false); // JSON parsing expect(result?.metadata).toEqual(metadata); expect(result?.tags).toEqual(tags); // Other fields unchanged expect(result?.id).toBe("1"); expect(result?.name).toBe("Combined Test"); }); it("should handle multiple rows correctly", async () => { await db .insertInto("test_items") .values([ { id: "1", name: "First", is_active: 1, is_verified: 1, metadata: JSON.stringify({ order: 1 }), tags: JSON.stringify(["first"]), }, { id: "2", name: "Second", is_active: 0, is_verified: 1, metadata: JSON.stringify({ order: 2 }), tags: JSON.stringify(["second"]), }, { id: "3", name: "Third", is_active: 1, is_verified: 0, metadata: null, tags: null, }, ]) .execute(); const results = await db.selectFrom("test_items").selectAll().execute(); expect(results).toHaveLength(3); const first = results.find((r) => r.id === "1"); expect(first?.is_active).toBe(true); expect(first?.is_verified).toBe(true); expect(first?.metadata).toEqual({ order: 1 }); const second = results.find((r) => r.id === "2"); expect(second?.is_active).toBe(false); expect(second?.is_verified).toBe(true); expect(second?.metadata).toEqual({ order: 2 }); const third = results.find((r) => r.id === "3"); expect(third?.is_active).toBe(true); expect(third?.is_verified).toBe(false); expect(third?.metadata).toBeNull(); }); }); }); describe("createKyselyDatasourceClient integration", () => { let dbPath: string; let db: Kysely; beforeEach(() => { dbPath = join(tmpdir(), `test-kysely-integration-${randomUUID()}.db`); }); afterEach(async () => { if (db) { await db.destroy(); } if (existsSync(dbPath)) { unlinkSync(dbPath); } }); it("should not deadlock on creation", async () => { // This test verifies that createKyselyDatasourceClient doesn't hang. // Previously, there was a deadlock where transformResult awaited a promise // that couldn't resolve until introspection completed, but introspection // couldn't complete because its query was blocked on transformResult. db = createKyselyDatasourceClient(dbPath); // Create test table - this should work without hanging await sql` CREATE TABLE test_items ( id TEXT PRIMARY KEY, name TEXT NOT NULL, is_active BOOLEAN, is_verified BOOLEAN, metadata JSON, tags JSON ) `.execute(db); // Insert and query - verifies basic functionality works await db .insertInto("test_items") .values({ id: "1", name: "Test", is_active: 1, is_verified: 0, metadata: null, tags: null, }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); // Basic verification - no deadlock occurred expect(result).toBeDefined(); expect(result?.name).toBe("Test"); }); it("should convert booleans when database has existing tables", async () => { // First, create a database with tables using a raw connection const rawDb = new Database(dbPath, { create: true }); rawDb.run(` CREATE TABLE test_items ( id TEXT PRIMARY KEY, name TEXT NOT NULL, is_active BOOLEAN, is_verified BOOLEAN, metadata JSON, tags JSON ) `); rawDb.close(); // Now create the client - introspection will find the existing table db = createKyselyDatasourceClient(dbPath); // Small delay to allow introspection to complete await new Promise((resolve) => setTimeout(resolve, 50)); // Insert and query with boolean columns await db .insertInto("test_items") .values({ id: "1", name: "Test", is_active: 1, is_verified: 0, metadata: null, tags: null, }) .execute(); const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); // Boolean values should be converted because introspection found the table expect(result?.is_active).toBe(true); expect(result?.is_verified).toBe(false); }); it("converts booleans on the very first query, even when fired before introspection settles", async () => { // Reproduces the original race: pricing_tiers loader fires SELECT immediately // after the client is created, before background introspection has resolved. // Pre-create the schema so introspection has something to find. const rawDb = new Database(dbPath, { create: true }); rawDb.run(` CREATE TABLE test_items ( id TEXT PRIMARY KEY, name TEXT NOT NULL, is_active BOOLEAN, is_verified BOOLEAN, metadata JSON, tags JSON ) `); rawDb.run(`INSERT INTO test_items (id, name, is_active, is_verified) VALUES ('1', 'Test', 1, 0)`); rawDb.close(); db = createKyselyDatasourceClient(dbPath); // No delay: query immediately, racing the background introspection. const result = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(result?.is_active).toBe(true); expect(result?.is_verified).toBe(false); }); it("does not deadlock during introspection of a populated schema", async () => { // Introspection must run via withoutPlugins() so the plugin doesn't await // its own unresolved promise during PRAGMA queries. const rawDb = new Database(dbPath, { create: true }); rawDb.run(` CREATE TABLE test_items ( id TEXT PRIMARY KEY, name TEXT NOT NULL, is_active BOOLEAN, is_verified BOOLEAN, metadata JSON, tags JSON ) `); rawDb.run(` CREATE TABLE other_table ( id TEXT PRIMARY KEY, count INTEGER ) `); rawDb.close(); db = createKyselyDatasourceClient(dbPath); // Issue a couple of concurrent queries straight away. If the deferred-promise // fix were wrong, these would hang. const [items, others] = await Promise.all([ db.selectFrom("test_items").selectAll().execute(), db.selectFrom("other_table").selectAll().execute(), ]); expect(items).toEqual([]); expect(others).toEqual([]); }); it("converts booleans across many concurrent racing queries", async () => { const rawDb = new Database(dbPath, { create: true }); rawDb.run(` CREATE TABLE test_items ( id TEXT PRIMARY KEY, name TEXT NOT NULL, is_active BOOLEAN, is_verified BOOLEAN, metadata JSON, tags JSON ) `); for (let i = 0; i < 10; i++) { rawDb.run( `INSERT INTO test_items (id, name, is_active, is_verified) VALUES ('${i}', 'r${i}', ${i % 2}, ${(i + 1) % 2})`, ); } rawDb.close(); db = createKyselyDatasourceClient(dbPath); const results = await Promise.all( Array.from({ length: 10 }, (_, i) => db.selectFrom("test_items").selectAll().where("id", "=", String(i)).executeTakeFirst(), ), ); for (const [i, row] of results.entries()) { expect(row?.is_active).toBe(i % 2 === 1); expect(row?.is_verified).toBe((i + 1) % 2 === 1); } }); }); describe("ParseBooleanResultsPlugin (race + edge cases)", () => { let dbPath: string; let db: Kysely; beforeEach(() => { dbPath = join(tmpdir(), `test-kysely-race-${randomUUID()}.db`); }); afterEach(async () => { if (db) await db.destroy(); if (existsSync(dbPath)) unlinkSync(dbPath); }); it("user queries wait for an unresolved boolean-columns promise (no silent skip)", async () => { // Build a Kysely directly with an unresolved promise to simulate slow introspection. let resolveCols!: (m: Map>) => void; const colsPromise = new Promise>>((r) => { resolveCols = r; }); const plugin = new ParseBooleanResultsPlugin(colsPromise); db = new Kysely({ dialect: new BunSqliteDialect({ database: new Database(dbPath, { create: true }) }), plugins: [new ParseJSONResultsPlugin(), plugin], }); await sql` CREATE TABLE test_items ( id TEXT PRIMARY KEY, name TEXT NOT NULL, is_active BOOLEAN, is_verified BOOLEAN, metadata JSON, tags JSON ) `.execute(db.withoutPlugins()); await db .withoutPlugins() .insertInto("test_items") .values({ id: "1", name: "x", is_active: 1, is_verified: 0, metadata: null, tags: null }) .execute(); // Fire the query before the columns promise resolves. const queryPromise = db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); // Race a short timer against the query — the query should still be pending. const winner = await Promise.race([ queryPromise.then(() => "query"), new Promise((r) => setTimeout(() => r("timer"), 50)), ]); expect(winner).toBe("timer"); // Resolve introspection now and assert the awaited query produces booleans. resolveCols(new Map([["test_items", new Set(["is_active", "is_verified"])]])); const result = await queryPromise; expect(result?.is_active).toBe(true); expect(result?.is_verified).toBe(false); }); it("introspection succeeds on a plugin-less view even when the plugin promise is unresolved", async () => { // This is the deadlock scenario: if introspection went through the plugin, // it would await colsPromise that itself depends on introspection. let resolveCols!: (m: Map>) => void; const colsPromise = new Promise>>((r) => { resolveCols = r; }); const plugin = new ParseBooleanResultsPlugin(colsPromise); db = new Kysely({ dialect: new BunSqliteDialect({ database: new Database(dbPath, { create: true }) }), plugins: [new ParseJSONResultsPlugin(), plugin], }); await sql` CREATE TABLE test_items ( id TEXT PRIMARY KEY, name TEXT NOT NULL, is_active BOOLEAN, is_verified BOOLEAN, metadata JSON, tags JSON ) `.execute(db.withoutPlugins()); // Should resolve quickly — withoutPlugins() bypasses the plugin entirely. const intro = introspectBooleanColumns(db.withoutPlugins()); const winner = await Promise.race([ intro.then(() => "intro"), new Promise((r) => setTimeout(() => r("timeout"), 1000)), ]); expect(winner).toBe("intro"); const cols = await intro; expect(cols.get("test_items")).toEqual(new Set(["is_active", "is_verified"])); // Resolve so destroy doesn't leave a dangling promise. resolveCols(cols); }); it("updateCache lets late-created tables get conversion without re-introspection", async () => { const plugin = new ParseBooleanResultsPlugin(Promise.resolve(new Map())); db = new Kysely({ dialect: new BunSqliteDialect({ database: new Database(dbPath, { create: true }) }), plugins: [new ParseJSONResultsPlugin(), plugin], }); await sql` CREATE TABLE test_items ( id TEXT PRIMARY KEY, name TEXT NOT NULL, is_active BOOLEAN, is_verified BOOLEAN, metadata JSON, tags JSON ) `.execute(db); // Without an updated cache, conversion is skipped. await db .insertInto("test_items") .values({ id: "1", name: "x", is_active: 1, is_verified: 0, metadata: null, tags: null }) .execute(); const before = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(before?.is_active).toBe(1); // After updateCache, conversion kicks in. plugin.updateCache(await introspectBooleanColumns(db)); const after = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); expect(after?.is_active).toBe(true); expect(after?.is_verified).toBe(false); }); it("non-1/0 numeric values in BOOLEAN columns are coerced via truthiness", async () => { const plugin = new ParseBooleanResultsPlugin(Promise.resolve(new Map())); db = new Kysely({ dialect: new BunSqliteDialect({ database: new Database(dbPath, { create: true }) }), plugins: [new ParseJSONResultsPlugin(), plugin], }); await sql` CREATE TABLE test_items ( id TEXT PRIMARY KEY, name TEXT NOT NULL, is_active BOOLEAN, is_verified BOOLEAN, metadata JSON, tags JSON ) `.execute(db); // SQLite is permissive about types — write a 2 directly. await sql`INSERT INTO test_items (id, name, is_active, is_verified) VALUES ('1', 'x', 2, 0)`.execute(db); plugin.updateCache(await introspectBooleanColumns(db)); const row = await db.selectFrom("test_items").selectAll().where("id", "=", "1").executeTakeFirst(); // Anything non-zero coerces to true; 0 to false. expect(row?.is_active).toBe(true); expect(row?.is_verified).toBe(false); }); });