A mere `add_foreign_key` can wipe out your whole Rails+SQLite production table. In fact, this is exactly what happened with @TelebugsHQ.
I added a new foreign key to the error groups table like I normally would, then deployed my test instance and saw that all individual reports (those that belong to error groups) were gone. Completely. My face was like WTF (if that could be a face).
It was a larger migration (a few, actually), but I distilled the culprit down to `add_foreign_key`.
So what's going on?
Well, this is because of how SQLite and the Rails driver work. I had `add_foreign_key "reports", "groups", on_delete: :cascade` in my schema. I needed to add a new foreign key to my `groups` table.
So far so good.
But to achieve that with SQLite, you need to create a new temporary table with your new foreign key, then copy data from the old table into the new one, then drop the old table, and then copy data from the temp table over to the new one.
Do you see the problem?
It's `on_delete: :cascade`. When my `groups` table dropped, it killed off all dependent reports 💀
So what did I do?
I avoided `add_foreign_key` altogether and used `add_column` + `add_index` instead and enforced constraints at the app level. `add_column` doesn't nuke tables and it is safe.
Another option: Change to `on_delete: :nullify` for the reports FK to prevent cascades, but then you'll need to handle orphaned reports (with `group_id: nil`) in your code.
Do I still love SQLite? But of course! However it comes with gotchas that you must know to use it efficiently and avoid landmines.
I added a new foreign key to the error groups table like I normally would, then deployed my test instance and saw that all individual reports (those that belong to error groups) were gone. Completely. My face was like WTF (if that could be a face).
It was a larger migration (a few, actually), but I distilled the culprit down to `add_foreign_key`.
So what's going on?
Well, this is because of how SQLite and the Rails driver work. I had `add_foreign_key "reports", "groups", on_delete: :cascade` in my schema. I needed to add a new foreign key to my `groups` table.
So far so good.
But to achieve that with SQLite, you need to create a new temporary table with your new foreign key, then copy data from the old table into the new one, then drop the old table, and then copy data from the temp table over to the new one.
Do you see the problem?
It's `on_delete: :cascade`. When my `groups` table dropped, it killed off all dependent reports 💀
So what did I do?
I avoided `add_foreign_key` altogether and used `add_column` + `add_index` instead and enforced constraints at the app level. `add_column` doesn't nuke tables and it is safe.
Another option: Change to `on_delete: :nullify` for the reports FK to prevent cascades, but then you'll need to handle orphaned reports (with `group_id: nil`) in your code.
Do I still love SQLite? But of course! However it comes with gotchas that you must know to use it efficiently and avoid landmines.
• • •
Missing some Tweet in this thread? You can try to
force a refresh
