J
M
R
M
user asks for a db -> create a new d1 db via api -> update worker script bindings via api workflow, but we'll be streamlining that as much as possible over the coming weeks/monthsR
env.DB.getByShardKey(id: string). Then all DB's under that shard key would share a schema, get the same migrations, and effectively be "grouped' together, if that makes sense.R
Rwrangler d1 migrations on our CI and would like to read the accountId and db Id from env vars rather than hard coding them in .toml files?
HMMHHHHHMHMHM
TTT
PRAGMA foreign_keys = OFF; at the beginning and PRAGMA foreign_keys = ON at the end of the migration but it still drops all of my records in table A which reference table B are deleted.MDROP TABLE commands: https://www.sqlite.org/foreignkeys.html#fk_schemacommandsT
I forgot to mention my scenario:
b_id which reference table B column idON DELETE CASCADE ON UPDATE CASCADEMIf foreign key constraints are enabled when it is prepared, the DROP TABLE command performs an implicit DELETE to remove all rows from the table before dropping it. The implicit DELETE does not cause any SQL triggers to fire, but may invoke foreign key actions or constraint violations. If an immediate foreign key constraint is violated, the DROP TABLE statement fails and the table is not dropped. If a deferred foreign key constraint is violated, then an error is reported when the user attempts to commit the transaction if the foreign key constraint violations still exist at that point. Any "foreign key mismatch" errors encountered as part of an implicit DELETE are ignored.
PRAGMA defer_foreign_keys = on (I have not tested this) - although I expect it will still fail as you still have violated fkey constraints when the transaction goes to complete.MTT
TTM
follow up: it seems like bracketing it with PRAGMA defer_foreign_keys = ON; and PRAGMA defer_foreign_keys = OFF; does make it work. I am not familiar enough with arcane SQLite stuff to give a super informed view on whether this should be automatically applied to every migration, but I suspect it might be a good idea - my understanding is that the foreign key constraints will still be checked, but just at the time that the transaction is committed.
TPRAGMA defer_foreign_keys = ON/PRAGMA defer_foreign_keys = OFF but it does not work either...TMTPRAGMA defer_foreign_keys, Table B was modified and my table A are cascaded without any errors like before
JJT
.wrangler/state/v3/d1. It's a sqlite file, you could explore or even modify it using your favorite toolTJTJJTTuser asks for a db -> create a new d1 db via api -> update worker script bindings via apienv.DB.getByShardKey(id: string)wrangler d1 migrationsPRAGMA foreign_keys = OFF;PRAGMA foreign_keys = ONDROP TABLEb_idON DELETE CASCADE ON UPDATE CASCADEPRAGMA defer_foreign_keys = onPRAGMA defer_foreign_keys = ONPRAGMA defer_foreign_keys = OFFPRAGMA defer_foreign_keys.wrangler/state/v3/d1PRAGMA foreign_keys = OFF;
create table b_temp ...;
drop table b...;
alter table b_tmp rename to b;
PRAGMA foreign_keys = ON;