migration:fresh & db:wipe fail with "ERROR: table sqlite_master may not be modified" about lucid HOT 3 CLOSED

Verified, it does not work with better-sqlite3 package. So we must look into it

from lucid.

Im not really into this stuff but i found something out that could be interesting:

I played around with better-sqlite3 and found out that PRAGMA writable_schema = 1 has no effect when better-sqlite3 isn't switched to unsafeMode. That would explain why the delete on sqlite_master fails.

But still, i dont understand how the tests didn't catch that (though i did not read through them).

Edit: I did go one layer up to knex for my testing:

import knex from "knex";

const db = knex({
    client: "better-sqlite3",
    connection: {
        filename: "test.sqlite3",
    },
    useNullAsDefault: true,
});

/* This fails, however when you uncomment the unsafeMode calls, it works */
db.client.acquireRawConnection().then((conn) => {
    // conn.unsafeMode(true);
    conn.pragma("writable_schema = 1");

    conn.prepare(
        "DELETE FROM sqlite_master WHERE type IN ('table', 'index', 'trigger')"
    ).run();

    conn.pragma("writable_schema = 0");
    // conn.unsafeMode(false);
});

db.destroy();

I couldn't find any other method to call the better-sqlite3 method from knex. Even acquireRawConnection isn't even documented (thanks copilot 😂)

from lucid.

Closing in favor of the PR.

from lucid.