skip to content

Why does an EF Core 10 migration that alters a column on SQLite rebuild the whole table, and what can still go wrong?

level: seniorimportance: should knowfreq 26%

answer

  1. SQLite's ALTER TABLE does little
  2. ef_temp_ table, copy, drop, rename
  3. PRAGMA foreign_keys = 0
  4. only tables in the model
  5. no sequences, no idempotent scripts

basics

~20 s

SQLite's ALTER TABLE cannot change a column, so EF Core's SQLite provider rebuilds the table: it creates ef_temp_<Table>, copies the rows, drops the original and renames the copy. Tables outside the model, sequences and idempotent scripts stay unsupported.

solid answer

~40 s

SQLite's `ALTER TABLE` can add and rename columns and rename tables, but it cannot alter a column or add and drop keys and constraints. For `AlterColumn`, `DropColumn`, foreign-key, primary-key, unique and check-constraint operations, `SqliteMigrationsSqlGenerator` replaces the operation with a **rebuild**: create `ef_temp_<Table>` with the new shape, `INSERT INTO ... SELECT` the rows, `PRAGMA foreign_keys = 0`, drop the old table, rename the new one, `PRAGMA foreign_keys = 1`, then recreate indexes. When a column becomes required and the operation carries a default value, the copy wraps it in `IFNULL`; without one, existing NULLs make the copy fail. A rebuild works only for tables in the EF model, otherwise `NotSupportedException` still follows. Sequences are unsupported, schemas are ignored, and idempotent scripts cannot be generated. Microsoft.Data.Sqlite 10 also reads offset-less timestamps as UTC.

code

csharp · 15 lines
csharp
public partial class RequirePatientEmail : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterColumn<string>(
            name: "Email",
            table: "Patients",
            type: "TEXT",
            nullable: false,
            defaultValue: "",          // lets the rebuild copy NULLs as IFNULL("Email", '')
            oldClrType: typeof(string),
            oldType: "TEXT",
            oldNullable: true);
    }
}

go deeper

for a junior

Recall that SQLite cannot alter most columns in place, so EF Core rebuilds the table by copying it into a new one.

for a middle

Explain which operations trigger a rebuild, the create-copy-drop-rename sequence, and why foreign keys are switched off around it.

for a senior

Show how you keep SQLite migrations safe: defaults for newly required columns, splitting migrations, and no reliance on sequences, schemas or idempotent scripts.

for a principal

Decide whether SQLite deserves a place in the clinic's test or deployment story, given rebuild costs and its divergence from the production engine.

## Why SQLite needs special treatment SQLite's `ALTER TABLE` supports only a few schema changes: renaming a table, renaming a column, adding a column and, in recent versions, dropping one under conditions. It cannot change a column's type or nullability, and it cannot add or remove primary keys, foreign keys, unique constraints or check constraints on an existing table. EF Core migrations, written in provider-neutral `MigrationBuilder` operations, routinely ask for exactly those changes. The clinic team, which runs its tests on SQLite, meets this the first time a migration makes `Patients.Email` required. ## Which operations rebuild | Operation | SQLite provider | |---|---| | `AddColumn`, `RenameColumn`, `RenameTable`, `CreateIndex`, `DropIndex`, `CreateTable`, `DropTable` | native SQL | | `AlterColumn`, `DropColumn` | table rebuild | | `AddForeignKey`, `DropForeignKey` on an existing table | table rebuild | | `AddPrimaryKey`, `DropPrimaryKey`, `AddUniqueConstraint`, `DropUniqueConstraint` | table rebuild | | `AddCheckConstraint`, `DropCheckConstraint` | table rebuild | | `EnsureSchema`, `DropSchema` | no-op | | sequence operations | `NotSupportedException` | ## What a rebuild does `SqliteMigrationsSqlGenerator` collects every rebuild-requiring operation for a table and replaces them with one sequence: 1. `CREATE TABLE "ef_temp_Patients"` with the table's target shape, taken from the EF model. 2. `INSERT INTO "ef_temp_Patients" (...) SELECT ... FROM "Patients"` to copy the rows. A column that changes from nullable to required, and whose `AlterColumn` carries a default value, is copied as `IFNULL(column, default)`. 3. `PRAGMA foreign_keys = 0`, run outside the migration transaction, so dropping the old table does not fire foreign-key actions on rows that reference it. 4. `DROP TABLE "Patients"` and `ALTER TABLE "ef_temp_Patients" RENAME TO "Patients"`. 5. `PRAGMA foreign_keys = 1`, then the table's indexes are recreated. ## What can still go wrong - **NULLs without a default.** If `Email` becomes required and the operation has no `defaultValue`, rows with NULL make the `INSERT ... SELECT` fail on the new `NOT NULL` column. Pass a default in `AlterColumn` or update the rows first. - **Tables EF does not know.** A rebuild needs the table's full shape from the EF model. A table created with `migrationBuilder.Sql` is not in the model, so the original operation stays and SQLite's provider throws `NotSupportedException`. - **Operations after a pending rebuild.** If later operations in the same migration touch a table whose rebuild is pending, EF logs `SqliteEventId.TableRebuildPendingWarning`, because the database may not be in the expected state. Move them to a following migration. - **Sequences and schemas.** SQLite has neither: a sequence configured in the model logs a warning, creating one in a migration throws "SQLite does not support sequences", and a configured schema is ignored with a warning. Models built for PostgreSQL or SQL Server can rely on both. - **Idempotent scripts.** `dotnet ef migrations script --idempotent` fails for SQLite, which has no procedural SQL for the existence checks; script from a known migration or apply with `dotnet ef database update`. ## Keeping SQLite migrations healthy 1. Read the generated SQL with `dotnet ef migrations script` before applying, so a rebuild never comes as a surprise. 2. Give every newly required column a default, or backfill it in an earlier migration. 3. Keep rebuild-heavy changes in their own migration, so no other operation runs while a rebuild is pending. 4. Keep objects created with raw SQL to a minimum, because the provider can only rebuild what the model describes. 5. On a large file, remember that a rebuild copies every row; test the migration against a copy of real data first. ## A version change that bites tests **Microsoft.Data.Sqlite 10**, which EF Core 10's SQLite provider uses, changed timestamp handling: `GetDateTimeOffset` on a text timestamp without an offset now assumes UTC instead of local time, `DateTimeOffset` values written to `REAL` columns are converted to UTC, and `GetDateTime` on a timestamp with an offset returns a UTC `DateTime`. Tests that compared local times can shift by the machine's offset after the upgrade; `Microsoft.Data.Sqlite.Pre10TimeZoneHandling` restores the old behaviour as a last resort.

  • What changed in Microsoft.Data.Sqlite 10 for timestamps, and how would you notice it?
    Text timestamps without an offset are now read as UTC by `GetDateTimeOffset`, `DateTimeOffset` values written to REAL columns are converted to UTC, and `GetDateTime` on a timestamp with an offset returns a UTC value. A test asserting local times can shift by the machine's offset; fix the assertion, or set `Microsoft.Data.Sqlite.Pre10TimeZoneHandling` as a temporary switch.
  • Why does a migration that only drops a column still rebuild the table on SQLite?
    EF Core's SQLite provider treats `DropColumn` as a rebuild operation rather than relying on SQLite's own DROP COLUMN, which has conditions such as the column not being part of a key, index or constraint. Rebuilding from the model works whatever the column takes part in.

  • SQLite supports ALTER COLUMN, so EF Core changes the column in place.
  • EF Core can rebuild a table that a migration created with migrationBuilder.Sql.
  • dotnet ef migrations script --idempotent works the same for SQLite as for SQL Server.
  • Making a nullable SQLite column required always succeeds because EF fills NULLs for you.
  • SQLite migrations create sequences for HiLo keys just as SQL Server's do.

Assumes EF Core 10 with Microsoft.Data.Sqlite 10, which reads offset-less text timestamps as UTC where earlier versions assumed local time.