Executing Pragma in SQLite

Question

Executing Pragma in SQLite

Closed this issue 5 years ago · 3 comments

I thought I'd finally found a use for init.sql -- in SQLite you actually need to enable foreign keys with PRAGMA foreign_keys = ON; so I stuck this in my init.sql. However, even this seems to have no effect (foreign keys aren't enforced until I log into the CLI and enter this command manually). Am I missing something?

Answer 1 · 2019-09-09T13:27:16.000Z

It looks to me like migratus is running the init script in a separate database connection, and AFAIR the PRAGMA foreign_keys applies only to the connection in which it was executed.

If that's not it, then it is probably that migratus is executing the init in a transaction. You can disable that with :init-in-transaction? and as per the sqlite docs

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.

Answer 2 · 2019-09-10T00:53:04.000Z

Hi, looks like there was a bug around running init without transaction as well. It's fixed in the latest version via this PR #174

Answer 3 · 2019-09-11T01:20:32.000Z

I'll close this one since it should be working at this point, but we can reopen it if it's still an issue.