Database migration anti-patterns to avoid with SQLite. Use when creating or reviewing database migrations to prevent locks, data corruption, or runtime errors. Covers unsafe column operations, type changes, constraint modifications, and index management.
Causes runtime errors when app restarts - app tries to read cached column that no longer exists.
See sqlite.md - Remove Column Directly for the safe 3-step pattern:
safety_assuredEntire table rewritten and locked during change - risks downtime and data corruption.
See sqlite.md - Change Column Type for the safe 6-step pattern:
One massive update_all within a transaction locks the entire table for the duration - blocks all reads and writes.
See sqlite.md - Backfilling Data for safe pattern:
in_batches(of: 10000) with disable_ddl_transaction!sleep(0.01) between batches to prevent lock starvationchange_column_null checks ALL rows and locks entire table - data consistency issue if violations exist.
See sqlite.md - Set NOT NULL for safe pattern:
validate: falsechange_column_null + remove constraintLocks both tables while validating all existing rows - prevents all operations during validation.
See sqlite.md - Add Foreign Key for safe pattern:
validate: falsevalidate_foreign_keyApp code conflicts with renamed column - causes runtime errors on active code reading old name.
See sqlite.md - Rename Column for safe pattern (same as type change):
database_consistency CheckDeploy without auditing after migrations - data inconsistencies slip through to production.
Safe process:
bundle exec rails db:migrate
bundle exec database_consistency # Required step!
git push
Catches:
safety_assuredsafety_assured { remove_column ... } without proper context - this disables all safety checks.
Only use when BOTH conditions are met:
ignored_columns += ["field_name"]See sqlite.md - safety_assured Pattern for details.
Index exists but no constraint - index can be dropped accidentally, uniqueness no longer enforced.
Safe pattern:
class AddUniqueSlug < ActiveRecord::Migration[8.1]
def change
add_index :articles, :slug, unique: true
add_unique_constraint :articles, :slug # Enforced at DB level
end
end
Validation only at app level - direct SQL inserts or app bugs can bypass validation.
Safe pattern:
class AddPrice < ActiveRecord::Migration[8.1]
def change
add_column :products, :price, :decimal, null: false
add_check_constraint :products, "price > 0", name: "products_price_positive"
end
end
Protects against: