bun-sqlite/tests/crud-returning.test.ts

import { describe, expect, beforeAll, test } from "bun:test";
import { Database } from "bun:sqlite";
import * as sqliteVec from "sqlite-vec";
import DbInsert from "../src/lib/sqlite/db-insert";
import DbUpdate from "../src/lib/sqlite/db-update";
import type { BUN_SQLITE_DatabaseSchemaType } from "../src/types";

const db = new Database(":memory:");
sqliteVec.load(db);

const dbSchema: BUN_SQLITE_DatabaseSchemaType = {
    dbName: "test",
    tables: [
        {
            tableName: "users",
            fields: [
                {
                    fieldName: "id",
                    dataType: "INTEGER",
                    primaryKey: true,
                    autoIncrement: true,
                },
                { fieldName: "first_name", dataType: "TEXT" },
                { fieldName: "email", dataType: "TEXT", unique: true },
                { fieldName: "created_at", dataType: "INTEGER" },
                { fieldName: "updated_at", dataType: "INTEGER" },
            ],
        },
        {
            tableName: "face_data",
            isVector: true,
            fields: [
                { fieldName: "user_id", dataType: "INTEGER" },
                { fieldName: "embedding", dataType: "TEXT", isVector: true },
                { fieldName: "note", dataType: "TEXT", sideCar: true },
                { fieldName: "updated_at", dataType: "INTEGER" },
            ],
        },
    ],
};

global.CONFIG = { db_name: "test" };
global.DB_SCHEMA = dbSchema;
global.SQLITE_CLIENT = db;

beforeAll(() => {
    db.run(`CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        first_name TEXT,
        email TEXT UNIQUE,
        created_at INTEGER,
        updated_at INTEGER
    )`);

    db.run(`CREATE VIRTUAL TABLE face_data USING vec0(
        user_id INTEGER,
        embedding float[3],
        +note TEXT,
        updated_at INTEGER
    )`);
});

describe("DbInsert RETURNING", () => {
    test("returns the inserted record", async () => {
        const res = await DbInsert({
            table: "users",
            data: [{ first_name: "Alice", email: "[email protected]" }],
        });

        expect(res.success).toBe(true);
        expect(res.singleRes?.first_name).toBe("Alice");
        expect(res.singleRes?.id).toBe(1);
        expect(res.singleRes?.created_at).toBeGreaterThan(0);
        expect(res.postInsertReturn?.affectedRows).toBe(1);
        expect(res.postInsertReturn?.insertId).toBe(1);
        expect(res.debug.sqlObj.query).toContain("RETURNING *");
    });

    test("returns every row for a multi-row insert", async () => {
        const res = await DbInsert({
            table: "users",
            data: [
                { first_name: "Bob", email: "[email protected]" },
                { first_name: "Cara", email: "[email protected]" },
            ],
        });

        expect(res.success).toBe(true);
        expect(res.payload?.length).toBe(2);
        expect(res.payload?.map((r) => r.first_name)).toEqual(["Bob", "Cara"]);
        expect(res.postInsertReturn?.insertId).toBe(res.payload?.at(-1)?.id);
    });

    test("returns the row on an upsert", async () => {
        const res = await DbInsert({
            table: "users",
            update_on_duplicate: true,
            data: [
                {
                    first_name: "Alicia",
                    email: "[email protected]",
                    created_at: 123,
                },
            ],
        });

        expect(res.success).toBe(true);
        expect(res.singleRes?.first_name).toBe("Alicia");
        expect(res.singleRes?.id).toBe(1);
        expect(res.singleRes?.created_at).toBe(123);
    });

    test("returning: false keeps the old postInsertReturn shape", async () => {
        const res = await DbInsert({
            table: "users",
            returning: false,
            data: [{ first_name: "Dan", email: "[email protected]" }],
        });

        expect(res.success).toBe(true);
        expect(res.payload).toBeUndefined();
        expect(res.singleRes).toBeUndefined();
        expect(res.postInsertReturn?.insertId).toBeGreaterThan(0);
    });

    test("empty data fails without running SQL", async () => {
        const res = await DbInsert({ table: "users", data: [] });

        expect(res.success).toBe(false);
        expect(res.msg).toBe("No insert data provided");
    });
});

describe("DbUpdate RETURNING", () => {
    test("returns the updated record", async () => {
        const res = await DbUpdate({
            table: "users",
            targetId: 1,
            data: { first_name: "Alicia" },
        });

        expect(res.success).toBe(true);
        expect(res.singleRes?.first_name).toBe("Alicia");
        expect(res.singleRes?.updated_at).toBeGreaterThan(0);
        expect(res.postInsertReturn?.affectedRows).toBe(1);
        expect(res.debug.sqlObj.string).toContain("RETURNING *");
    });

    test("no matching row resolves to success: false", async () => {
        const res = await DbUpdate({
            table: "users",
            targetId: 99999,
            data: { first_name: "Nobody" },
        });

        expect(res.success).toBe(false);
        expect(res.payload).toEqual([]);
    });

    test("vector tables skip RETURNING but still report affectedRows", async () => {
        db.run(
            "INSERT INTO face_data (user_id, embedding, note, updated_at) VALUES (?, ?, ?, ?)",
            4,
            new Float32Array([1, 2, 3]),
            "hi",
            0,
        );

        const res = await DbUpdate({
            table: "face_data",
            query: { query: { user_id: { value: 4 } } },
            data: { note: "bye" },
        });

        expect(res.success).toBe(true);
        expect(res.payload).toBeUndefined();
        expect(res.debug.sqlObj.string).not.toContain("RETURNING");
        expect(res.postInsertReturn?.affectedRows).toBeGreaterThan(0);

        const row = db
            .query("SELECT note FROM face_data WHERE user_id = 4")
            .get() as { note: string };
        expect(row.note).toBe("bye");
    });

    test("returning: false keeps the old postInsertReturn shape", async () => {
        const res = await DbUpdate({
            table: "users",
            targetId: 1,
            returning: false,
            data: { first_name: "Ally" },
        });

        expect(res.success).toBe(true);
        expect(res.payload).toBeUndefined();
        expect(res.postInsertReturn?.affectedRows).toBe(1);
    });
});