PRAGMA foreign_keys = OFF not respected in migration files

[harshav17]

Hello @benbjohnson,

Thanks for this repo, it has been helpful in setting up my own backend application. I am facing an issue though. I have the following migration file...

PRAGMA foreign_keys = OFF;

/* add new role column */
CREATE TABLE tmp_parent (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    role  TEXT NOT NULL DEFAULT 'P',

    UNIQUE(id, parent)
);

INSERT INTO tmp_parent(id, name)
SELECT id, name
FROM parent;

DROP TABLE parent;
ALTER TABLE tmp_parent RENAME TO parent;

PRAGMA foreign_keys = ON;

I also have a Child table which has the following columns...

parent_id INTEGER NOT NULL REFERENCES parent (id) ON DELETE CASCADE,

When I manually execute the commands referenced in the migration file, sqlite seems to respect the statement PRAGMA foreign_keys = OFF;, i.e, the rows inside the child table are not deleted. But when the migration file runs, as part of the server startup, the rows inside the child table are getting deleted, unexpectedly. I am not sure where to start debugging such an issue, do you have any pointers?

Thanks in advance and Merry Christmas!

Update 1:
I found the following in the sqlite documentation, wonder if its because of that...

"This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT."

Update 2:
Verified that indeed was the issue by placing BEGIN and COMMIT directly into the migration files and moving the migration out of this method's BEGIN and COMMIT calls. While this works, it feels very kludgy, would still love your opinion on how to setup these migrations.

[benbjohnson]

Hmm, I don't have a great solution for turning off the foreign key checking in the transaction. You might be able to remove the transaction from here and make them individual db.Exec() statements instead. That's also kludgy but it would at least give you fine-grained control over each migration file's transaction.