libsql-client-ts
libsql-client-ts copied to clipboard
tursodatabase
Allow foreign key constraints to be disabled in batches
Our code currently implicitly wraps batches of statements into BEGIN + COMMIT or BEGIN + ROLLBACK. Problem is, in order to temporarily turn off foreign key contraints users might want to send a PRAGMA FOREIGN_KEYS=off statement as a first statement in the batch. Unfortunately, this pragma is not effective inside a transaction: https://www.sqlite.org/foreignkeys.html#fk_enable
A simple workaround would be to check if the first statement happens to be a pragma and execute it outside of the transaction scope. Another simple workaround would be to add a special interface that allows you to say "please run this batch without foreign keys", and then we'd send a PRAGMA FOREIGN_KEYS=off ourselves, right before the BEGIN+COMMIT block.
It also sounds like executeMultiple can be used to execute statements without the explicit BEGIN+COMMIT blocks: https://github.com/tursodatabase/libsql-client-ts/blob/9decdd54a6207e2b01c370af3dccf418ebbd4cc3/packages/libsql-core/src/api.ts#L259-L268
So a workaround for the above is to use executeMultiple with pragma foreign_keys=off; begin; your-stuff; commit rather than batch with your-stuff.
