SQLite Migrations and Table Rebuilds: DDL Limitations, Idempotency Boundaries, and Testing

Learning outcomes

1. The problem: SQLite schema evolution is often copy-and-replace

Server databases expose broad ALTER TABLE capabilities. SQLite intentionally supports a smaller direct DDL surface. The EF Core SQLite provider compensates for many operations by rebuilding the table: create a replacement table, copy rows, drop/rename tables, and recreate modeled artifacts. That is a real data movement with lock, disk-space and recovery implications.

2. Use a realistic ServiceHub change that requires a rebuild

Suppose the business decides CustomerName must be non-empty and at most 160 characters at the database boundary. Adding a check constraint to an existing SQLite table requires a rebuild. The constraint makes the earlier Lesson 1 point concrete: a database invariant is different from a model facet.

builder.ToTable("work_orders", table =>{    table.HasCheckConstraint(        "CK_work_orders_customer_name_length",        "length(customer_name) BETWEEN 1 AND 160");});

dotnet ef migrations add SqliteCustomerNameConstraint   --project src/ServiceHub.EfLab   --startup-project src/ServiceHub.EfLabdotnet ef migrations script PreviousMigration SqliteCustomerNameConstraint   --project src/ServiceHub.EfLab   --startup-project src/ServiceHub.EfLab   --output artifacts/sqlite-customer-name-constraint.sql

3. Read the rebuild as a data-copy algorithm, not opaque provider noise

The exact temporary table names and statements are provider-version-specific, but the mechanism is stable. A representative rebuild looks like this:

CREATE TABLE "ef_temp_work_orders" (    "work_order_id" INTEGER NOT NULL CONSTRAINT "PK_work_orders" PRIMARY KEY AUTOINCREMENT,    "work_order_number" TEXT NOT NULL,    "customer_name" TEXT NOT NULL,    ...,    CONSTRAINT "CK_work_orders_customer_name_length"        CHECK (length(customer_name) BETWEEN 1 AND 160));INSERT INTO "ef_temp_work_orders" (...)SELECT ... FROM "work_orders";DROP TABLE "work_orders";ALTER TABLE "ef_temp_work_orders" RENAME TO "work_orders";CREATE UNIQUE INDEX ...;CREATE INDEX ...;

The copy step can fail if existing data violates the new constraint or cannot be converted. The rebuilt table must also restore modeled foreign keys, unique constraints and indexes. Therefore, validate data before applying the migration and compare schema metadata afterward.

4. Preflight the data before introducing a stricter constraint

A migration is the wrong moment to discover that thousands of rows violate a new invariant. Make the data profile observable first.

SELECT work_order_id, customer_name, length(customer_name) AS lengthFROM work_ordersWHERE customer_name IS NULL   OR length(customer_name) NOT BETWEEN 1 AND 160;SELECT COUNT(*) AS work_orders_before FROM work_orders;PRAGMA foreign_key_check;PRAGMA integrity_check;

If invalid data exists, design an explicit data-cleanup/backfill migration or stop the deployment for human review. Do not silently truncate customer names merely to make DDL succeed.

5. Rebuilds can only safely recreate artifacts EF knows about

The SQLite provider documentation warns that rebuilds are possible only for database artifacts represented in the EF model. A trigger, custom index, virtual table, or other object created manually inside a migration may not be automatically reconstructed during a later provider rebuild.

SELECT type, name, tbl_name, sqlFROM sqlite_masterWHERE tbl_name = 'work_orders'  AND type IN ('index', 'trigger', 'view')ORDER BY type, name;

6. Foreign keys and indexes must be verified after the rebuild

The model may say the right thing while the live database diverges after an interrupted/manual deployment. Verify both relationship constraints and indexes after the migration.

PRAGMA table_info('work_orders');PRAGMA foreign_key_list('work_orders');PRAGMA index_list('work_orders');PRAGMA foreign_key_check;PRAGMA integrity_check;SELECT COUNT(*) AS work_orders_after FROM work_orders;

Compare deterministic row counts and representative values from before/after the rebuild. integrity_check is useful engine evidence, but it does not prove business data transformations were semantically correct.

7. SQLite idempotent migration scripts remain unsupported

SQLite does not have the procedural if/then machinery EF uses to conditionally apply each missing migration in an idempotent script. Therefore dotnet ef migrations script --idempotent is not the SQLite deployment path. If the current migration is known, generate a bounded script from that migration to the target. Otherwise use a coordinated EF migration application path such as dotnet ef database update against the chosen file.

dotnet ef migrations script 202608270900_PreviousMigration 202608271000_SqliteCustomerNameConstraint   --output artifacts/sqlite-bounded.sql# Or, for a coordinated local deployment:dotnet ef database update   --connection "Data Source=servicehub-sqlite-deepdive.db;Foreign Keys=True"

8. Concurrent migration protection uses __EFMigrationsLock

EF Core 9 introduced provider migration locking. The SQLite provider implements this with an __EFMigrationsLock table. A crash can leave that table behind and block later EF-driven migrations. Manual cleanup is appropriate only after proving that no migration process is active and after understanding the deployment state.

SELECT name, sqlFROM sqlite_masterWHERE name IN ('__EFMigrationsHistory', '__EFMigrationsLock');SELECT * FROM __EFMigrationsHistory ORDER BY MigrationId;

Externally executing a generated SQL script does not inherit EF’s migration lock. The deployment pipeline itself must serialize such script execution.

9. Failure case: a rebuild collides with an unmanaged trigger

An earlier migration created a trigger with raw SQL but no later migration test inventories it. A new AlterColumn causes a rebuild. Depending on the operation, EF may throw NotSupportedException because it cannot safely rebuild the unmanaged artifact, or the hand-maintained lifecycle can drift from the model.

Repair: decide ownership. Either represent the required behavior using EF-supported model constructs, or own the trigger explicitly with create/drop/recreate migration SQL plus schema verification. Never respond by deleting production triggers until the migration happens to run.

10. Mandatory lab: prove row and schema preservation through a rebuild

1. Copy servicehub-sqlite-deepdive.db to a backup filename while the lab application is stopped.
2. Seed deterministic work orders and record row count, selected values, foreign-key check, integrity check, indexes and a file hash.
3. Add the customer-name check constraint and scaffold SqliteCustomerNameConstraint.
4. Inspect the generated migration operation and bounded SQL script; identify the replacement table, copy, drop/rename and index recreation.
5. Apply the migration once through a single coordinated owner.
6. Verify row counts/values, PRAGMA foreign_key_check, PRAGMA integrity_check, PRAGMA index_list, and __EFMigrationsHistory.
7. Attempt an invalid insert and confirm the database constraint—not only C# validation—rejects it.
8. Restore the backup or delete the disposable database after recording evidence.

11. Production judgment and bridge

SQLite migration safety is strongly tied to data size, file locking, disk space and artifact ownership because many schema changes are rebuilds. Review generated SQL, preflight data, back up correctly, serialize migration ownership and verify postconditions. Do not use unsupported idempotent scripts or EnsureCreated as substitutes for a migrations lifecycle. Lesson 3 now moves from schema-time locking to runtime concurrency, where SQLite’s single-writer design must be separated from EF optimistic conflicts.

Authoritative references

SQLite migrations mix EF model operations with engine DDL constraints; inspect both provider and SQLite documentation.

• SQLite provider limitations - EF Core — rebuild operation matrix, idempotent-script and migration-lock limitations
• Applying migrations - EF Core — script/database-update/bundle deployment choices
• Migrations overview - EF Core — migration/model snapshot workflow
• ALTER TABLE - SQLite — native SQLite ALTER capabilities
• Making other kinds of table schema changes - SQLite — official rebuild-style procedure
• PRAGMA statements - SQLite — integrity, foreign-key and schema verification commands