skip to content

In EF Core 10, what does dotnet ef migrations script --idempotent generate, and why would a payroll team ship it instead of running database update?

level: middleimportance: should knowfreq 50%

answer

  1. FROM and TO arguments, default 0 to latest
  2. each statement guarded by a history check
  3. DO $EF$ blocks on PostgreSQL
  4. reviewable, but no lock and no seeding
  5. not supported on SQLite

basics

~20 s

It writes one SQL script covering all migrations, with every statement guarded by a check that its migration id is absent from __EFMigrationsHistory, so it can run against a database at any migration. A DBA can review it.

solid answer

~40 s

`dotnet ef migrations script` turns migrations into SQL for the configured provider: by default from `0` (an empty database) to the last migration, or between a `FROM` and a `TO` argument. A plain script assumes the database is exactly at `FROM`. With `--idempotent`, every statement is wrapped in a check that the migration's id is not yet in `__EFMigrationsHistory` (on PostgreSQL a `DO $EF$ ... IF NOT EXISTS ... END $EF$` block), so the same file can run against databases at different migrations. A payroll team ships it because the exact SQL can be reviewed, archived and run by a DBA or a deployment tool with a separate schema-owning login. The trade-offs: no EF migration lock, no `UseSeeding`, no idempotent scripts on SQLite, and SQL generated for one provider only.

code

bash · 5 lines
bash
# whole history, safe to run on any payroll database
dotnet ef migrations script --idempotent --output artifacts/payroll-migrate.sql

# rollback plan: from the newest migration back to AddPayslipTaxCode
dotnet ef migrations script AddPayRunAudit AddPayslipTaxCode --output artifacts/payroll-rollback.sql

go deeper

for a junior

Know that migrations can be turned into SQL with dotnet ef migrations script and that --idempotent makes the file safe to run on databases at different migrations.

for a middle

Explain FROM and TO, how the idempotent guard checks the history table per statement, and what a script gives up compared with EF applying migrations: the lock and seeding.

for a senior

Run review-gated deployments: generate once in CI, approve the exact file, deploy it with a schema-owning identity, and keep a tested rollback script beside it.

for a principal

Decide which environments need DBA-reviewed SQL versus automated EF execution, and how approvals, separate identities and rollback rehearsals fit the release process.

## What the command produces `dotnet ef migrations script` converts migrations into SQL for the provider the context is configured with, without connecting to apply anything. For a payroll service on Npgsql: - `dotnet ef migrations script` covers everything from an empty database (`0`) to the last migration; - `dotnet ef migrations script AddPayslipTaxCode` covers the migrations **after** `AddPayslipTaxCode` up to the last one; - `dotnet ef migrations script AddPayslipTaxCode AddPayRunAudit` covers the range between the two; - a `FROM` newer than `TO` produces a **rollback** script made of `Down` operations; - `-o` / `--output` writes the file; without it the SQL goes to standard output. ## Plain versus idempotent scripts A plain script is a straight list of statements plus the `INSERT` into the history table for each migration. It is correct only if the database is exactly at `FROM`; run it against a database one migration ahead and the first `CREATE TABLE` fails. An **idempotent** script (`--idempotent` or `-i`) removes that assumption: 1. When it starts from `0`, the default, it first creates `__EFMigrationsHistory` if it does not exist. 2. Every statement of every migration is wrapped in a condition that the migration's id is **absent** from the history table. On PostgreSQL that is a `DO $EF$ BEGIN IF NOT EXISTS(SELECT 1 FROM "__EFMigrationsHistory" WHERE "MigrationId" = '…') THEN … END IF; END $EF$;` block; SQL Server uses `IF NOT EXISTS (…) BEGIN … END`. 3. Statements of one migration are grouped in a transaction (on PostgreSQL `START TRANSACTION;` … `COMMIT;`) unless `--no-transactions` is passed or an operation suppresses it. The result can run against production, staging and a fresh test database alike; each skips what it already has. ## Why teams choose a script | Concern | Script | `dotnet ef database update` | |---|---|---| | Review the exact SQL before it runs | yes | no | | Run without the SDK and source | yes | no | | Hand to a DBA or a database deployment tool | yes | no | | EF Core's migration lock | no | yes | | `UseSeeding` delegates | no | yes | For payroll data the review argument usually wins: schema changes are signed off as SQL, the approved file is the artifact deployed, and the identity that runs it is a schema-owning login separate from the application's. ## Limits and traps - **Provider-specific.** The SQL is generated for the configured provider and its options; a script made for SQL Server will not run on PostgreSQL. - **No idempotent scripts on SQLite**: the SQLite provider throws when asked for one. - **No seeding delegates.** `UseSeeding` and `UseAsyncSeeding` run only when EF Core itself migrates; data that must reach a script-deployed database has to be in the migrations, for example as `InsertData` from `HasData`. - **No concurrency protection.** EF's migration lock applies to `Migrate`, `database update` and bundles, not to scripts run by another tool; the deployment process must avoid two concurrent runs. - **Generate once, deploy that file.** Regenerating at deploy time means the SQL that runs is not the SQL that was approved. - **Custom SQL is wrapped too.** Statements from `migrationBuilder.Sql` sit inside the guard; on SQL Server, statements that must start a batch may need `EXEC` to survive the wrapping. ## Rollback scripts `dotnet ef migrations script AddPayRunAudit AddPayslipTaxCode` emits the `Down` operations that take a database from `AddPayRunAudit` back to `AddPayslipTaxCode`. Store it beside the forward script, but treat it as a plan to test: `Down` cannot restore data a forward migration dropped. ## Generating it in a pipeline 1. Build the project from the commit being released. 2. Generate the script with the same provider and model configuration the application uses. The tools create the context at design time and assume the `Development` environment unless `ASPNETCORE_ENVIRONMENT` or `DOTNET_ENVIRONMENT` is set, so set it when environment-specific configuration shapes the model. 3. Publish the `.sql` file as a build artifact and send it for approval. 4. Apply the approved file with the database's own client, using a login that may change the schema, while the application keeps a login that can only read and write data. Because every statement re-checks the history table, re-running the same approved file after a failure skips the migrations that completed and retries the one that failed, provided its statements rolled back with their transaction.

  • Why does a plain script fail on a database that is one migration ahead of FROM?
    A plain script has no guards: it assumes the database is exactly at `FROM` and replays every statement after it. A migration that already ran tries to create objects that exist, and the script stops. `--idempotent` adds a history-table check around each statement so already-applied migrations are skipped.
  • Why generate the idempotent script once in CI instead of at deploy time?
    The script depends on the commit, the provider and the configuration the tools ran with. Generating it again at deploy time can produce different SQL from the file that was reviewed, for example after a new migration was merged. Publishing the reviewed file as an artifact guarantees that what runs is exactly what was approved.

  • An idempotent script checks whether each table or column already exists before creating it.
  • Idempotent scripts take EF Core's migration lock while they run.
  • A script generated for SQL Server can be run on PostgreSQL if the types match.
  • Regenerating the script at deploy time is equivalent to deploying the reviewed one.
  • Every EF Core provider can generate idempotent scripts, SQLite included.