PRAGMA foreign_keys assumed to be ON in Electric

[davidmartos96]

The Electric Satellite logic assumes that foreign_keys are ON but it never actually sets the PRAGMA. For example, when defer_foreign_keys is set to ON and there is error handling during transactions.

The tests work correctly by default when using the better-sqlite3 adapter because that dependency changes the SQLite foreign keys default to ON, but an "untouched" SQLite driver has foreign keys off by default. For instance, when querying PRAGMA foreign_keys in the example app from the monorepo, which uses wa-sqlite, it returns OFF.

Does it make sense to enable the PRAGMA internally in Electric, or is it something that the user has to set?

[icehaunter]

@thruflo, any opinions? One one hand, we indeed assume that it's ON, on the other it maybe good that it's off for the upcoming shape stuff. But it's weird to work around FKs without them being enforced

[thruflo]

Hmm. I think this is more a question for @balegas or @paulharter.

[balegas]

Will have a look at what would be a safe approach with the limitations we have right now.

[thruflo]

Fwiw, I've just run up against the same issue, working on a data model with a foreign key with ON DELETE SET NULL and wondering why the child doesn't update when the parent is deleted.

This obviously doesn't mean that it's an Electric responsibility to set it but strikes me that it's a much saner default, coming from Postgres land. We obviously do quite a lot of setup in the generated migrations. Adding PRAGMA foreign_keys = 1 to the other setup would make sense to me.

[thruflo]

@balegas just bumping thread — we do def want to move this into the client. Currently apps are having to set the right Pragma in their init code.

[balegas]

@kevin-dp can we close this?

[balegas]

Closed in #397