bun-mariadb/tests/db.test.ts

/**
 * Integration tests against a real MariaDB.
 *
 * Skipped unless BUN_MARIADB_TEST_DB is set. Uses the usual
 * BUN_MARIADB_SERVER_* env vars to connect. Point BUN_MARIADB_TEST_DB at a
 * throwaway database: the tests create and drop tables in it.
 */
import { afterAll, beforeAll, describe, expect, test } from "bun:test";
import type { BUN_MARIADB_DatabaseSchemaType } from "../src/types";

const TEST_DB = process.env.BUN_MARIADB_TEST_DB;

const schema: BUN_MARIADB_DatabaseSchemaType = {
    tables: [
        {
            tableName: "bmt_items",
            fields: [
                { fieldName: "name", dataType: "VARCHAR" },
                { fieldName: "qty", dataType: "INT" },
                { fieldName: "active", dataType: "TINYINT" },
                { fieldName: "body", dataType: "TEXT", html: true },
            ],
        },
    ],
} as BUN_MARIADB_DatabaseSchemaType;

describe.skipIf(!TEST_DB)("MariaDB integration", () => {
    let DB: typeof import("../src/index").default;
    let schemaLib: typeof import("../src/lib/schema/run-schema-query");
    let createDBSchema: typeof import("../src/lib/schema/create-db-schema").default;
    let appendDefaults: typeof import("../src/utils/append-default-fields-to-db-schema").default;

    beforeAll(async () => {
        const setMariaDBClient = (await import("../src/functions/set-mariadb-client")).default;
        createDBSchema = (await import("../src/lib/schema/create-db-schema")).default;
        appendDefaults = (await import("../src/utils/append-default-fields-to-db-schema")).default;
        schemaLib = await import("../src/lib/schema/run-schema-query");

        // The test container uses a self-signed certificate
        global.CONFIG = { db_name: TEST_DB!, ssl_reject_unauthorized: false };
        global.DB_SCHEMA = schema;
        setMariaDBClient({ config: global.CONFIG });

        DB = (await import("../src/index")).default;

        await createDBSchema({
            db_schema: appendDefaults({ dbSchema: schema }),
            config: global.CONFIG,
        });
        await DB.sql({ sql: "DELETE FROM bmt_items" });

        const res = await DB.insert({
            table: "bmt_items",
            data: [
                { name: "a", qty: 0, active: false, body: '<p style="position:fixed" id="x">hi</p><button>b</button>' },
                { name: "b", qty: 5, active: true, body: "" },
                { name: "c", qty: 0, active: true },
            ],
        });
        expect(res.success).toBe(true);
    });

    afterAll(async () => {
        await DB?.sql({ sql: "DROP TABLE IF EXISTS bmt_items" });
        await DB?.sql({
            sql: "DELETE FROM __db_schema_manager__ WHERE table_name = 'bmt_items'",
        });
        await global.MARIADB_CLIENT?.close();
    });

    test("insert stores false as 0, keeps '' for text, sanitizes html fields", async () => {
        const { payload } = await DB.select<any>({ table: "bmt_items", query: { order: { field: "id", strategy: "ASC" } } });
        expect(payload?.map((r) => r.active)).toEqual([0, 1, 1]);
        expect(payload?.[0].body).toBe("<p>hi</p>b");
        expect(payload?.[1].body).toBe("");
        expect(typeof payload?.[0].id).toBe("number");
    });

    test("select matches 0", async () => {
        const res = await DB.select<any>({ table: "bmt_items", query: { query: { qty: { value: 0 } } } });
        expect(res.payload?.map((r) => r.name).sort()).toEqual(["a", "c"]);
    });

    test("select with IN and paged count", async () => {
        const res = await DB.select<any>({
            table: "bmt_items",
            count: true,
            query: { query: { name: { value: ["a", "b"], equality: "IN" } }, limit: 1, offset: 1 },
        });
        expect(res.payload?.length).toBe(1);
        expect(res.count).toBe(2);
    });

    test("sql() reports success for empty selects", async () => {
        const res = await DB.sql({ sql: "SELECT * FROM bmt_items WHERE name = ?", values: ["nope"] });
        expect(res.success).toBe(true);
        expect(res.payload).toEqual([]);
    });

    test("update returns rows even when the WHERE column changes", async () => {
        const res = await DB.update<any>({ table: "bmt_items", data: { qty: 9 }, query: { query: { qty: { value: 0 } } } });
        expect(res.success).toBe(true);
        expect(res.payload?.map((r: any) => r.qty)).toEqual([9, 9]);
    });

    test("update with no matches fails", async () => {
        const res = await DB.update<any>({ table: "bmt_items", data: { qty: 1 }, query: { query: { name: { value: "zzz" } } } });
        expect(res.success).toBe(false);
    });

    test("delete with targetId is ANDed with an OR query", async () => {
        const { payload } = await DB.select<any>({ table: "bmt_items", query: { query: { name: { value: "b" } } } });
        const idB = payload![0].id;

        const res = await DB.delete<any>({
            table: "bmt_items",
            targetId: idB,
            query: { query: { name: { value: "a" }, qty: { value: 9 } }, searchOperator: "OR" },
        });
        // Row "b" (qty 5) matches neither condition, so nothing is deleted
        expect(res.success).toBe(false);

        const remaining = await DB.select<any>({ table: "bmt_items" });
        expect(remaining.payload?.length).toBe(3);
    });

    test("delete refuses an empty WHERE clause", async () => {
        const res = await DB.delete({ table: "bmt_items", query: {} });
        expect(res.success).toBe(false);
        expect(res.msg).toBe("No WHERE clause");
    });

    test("schema plan flags dropped columns, guarded run refuses them", async () => {
        const reduced = {
            tables: [{ ...schema.tables[0]!, fields: schema.tables[0]!.fields.filter((f) => f.fieldName !== "body") }],
        } as BUN_MARIADB_DatabaseSchemaType;
        const params = { db_schema: appendDefaults({ dbSchema: reduced }), config: global.CONFIG };

        const plan = await schemaLib.planSchemaQueries(() => createDBSchema(params));
        const destructive = plan.filter(schemaLib.isDestructiveSchemaStatement);
        expect(destructive).toEqual(["ALTER TABLE `bmt_items` DROP COLUMN `body`"]);

        await expect(
            schemaLib.runSchemaQueriesGuarded(() => createDBSchema(params), new Set()),
        ).rejects.toThrow(/unapproved destructive/);

        const { payload } = await DB.select<any>({ table: "bmt_items", query: { limit: 1 } });
        expect(payload?.[0]).toHaveProperty("body");
    });
});