A mere add_foreign_key can wipe out your whole Rails+SQLite production table | Kyrylo Silin

A single add_foreign_key in a Rails migration can obliterate a dependent table<br>in your SQLite database. This is exactly what happened with my self-hosted error<br>tracker, Telebugs.

I added a new foreign key to the error groups table like I normally would,<br>then deployed my test instance and saw that all individual reports (those that<br>belong to error groups) were gone. Completely. My face was like WTF (if that<br>could be a face).

The migration was part of a larger set, but I narrowed it down to this:

class AddOwnerToGroups ActiveRecord::Migration[8.0]<br>def change<br>add_foreign_key :groups, :users, column: :owner_id, on_delete: :nullify<br>end<br>end

So what’s going on? It’s all about how Rails and SQLite handle schema changes. My schema had a foreign key from reports to groups with on_delete: :cascade:

add_foreign_key "reports", "groups", on_delete: :cascade

When I added the new foreign key to groups, SQLite (via Rails) needed to<br>recreate the groups table because SQLite doesn’t support ALTER TABLE for<br>adding foreign keys. The process goes like this:

Create a temporary table (agroups) with the new schema, including the<br>foreign key.

Copy data from groups to agroups.

Drop the original groups table.

Recreate groups with the new schema.

Copy data back from agroups to groups.

Do you see the problem? When the original groups table is dropped, the reports<br>table’s on_delete: :cascade foreign key kicks in. Since reports.group_id<br>temporarily points to non-existent groups.id values, SQLite deletes all reports<br>records 💀

How I fixed it

I rewrote the migration to avoid add_foreign_key:

class AddOwnerToGroups ActiveRecord::Migration[8.0]<br>def change<br>add_column :groups, :owner_id, :integer<br>add_index :groups, :owner_id<br>end<br>end

This adds the owner_id column and index without touching the table structure,<br>so no data is lost. To enforce the foreign key constraint, I added a validation<br>in my Group model:

class Group ApplicationRecord<br>belongs_to :owner, class_name: 'User', optional: true<br>validates :owner_id, inclusion: { in: ->(record) { User.pluck(:id) + [nil] } }, allow_nil: true<br>end

This ensures owner_id is either nil or a valid User.id, mimicking the<br>database foreign key without SQLite’s pitfalls.

Lessons learned

SQLite gotchas: SQLite’s table recreation for schema changes can wreak<br>havoc with cascading foreign keys. Be cautious with on_delete: :cascade in<br>migrations.

Safer migrations: Use add_column and add_index for simple column<br>additions, and enforce constraints at the application level when using SQLite.

Avoid cascading deletes: Consider using on_delete: :nullify for foreign<br>keys like reports to groups to prevent data loss, though you’ll need to<br>handle orphaned reports (with group_id: nil) in your application logic.

Test with data: Always test migrations with realistic data, including<br>invalid foreign key values, to catch issues early.

Backup first: Before running migrations, back up your database (sqlite3<br>db/production.sqlite3 ".backup backup.sqlite3") to avoid data loss.

Do I still love SQLite? But of course! However it comes with gotchas that you<br>must know to use it efficiently and avoid landmines.

Next: AI amplifies programmers, not replaces them

Prev: Why do software developers love complexity?