EF Core - Deployment: SQL Script Generation

1. Overview

The Idempotent SQL Script strategy involves generating a plain text SQL file that contains all database migration logic. Unlike standard SQL scripts, an idempotent script wraps every migration step in conditional logic (e.g., IF NOT EXISTS...). This ensures the script can be safely executed against any database version—whether it is an empty database, a partially migrated one, or fully up-to-date—without causing errors or duplication.

Key Benefits

• Transparency: The output is a standard SQL file that can be opened, read, and audited by DBAs before execution.

• Safety: The script self-checks the __EFMigrationsHistory table before applying any changes.

• Tooling Agnostic: Once generated, the script does not require .NET SDK or EF Core tools to run. It can be executed by any standard SQL runner (sqlcmd, psql, Invoke-Sqlcmd, etc.).

---

2. The Development & Build Lifecycle

Phase 1: Local Development

This phase remains identical to standard EF Core workflows.

1. Migration Creation: Developers use dotnet ef migrations add <Name> to create C# migration files.

2. Conflict Resolution: Merge conflicts in the snapshot file are resolved locally by developers.

Phase 2: Build Process (CI)

The Continuous Integration (CI) pipeline converts the C# migration files into a deployable SQL artifact.

The Command:

dotnet ef migrations script --idempotent --output deploy.sql

Parameters Explained:

• --idempotent: (Crucial) Instructs EF Core to wrap transactions in conditional logic checking the migration history table.

• --output <file>: Specifies the destination filename.

• --project <path>: (Optional) Points to the project containing the DbContext.

• --context <Name>: (Optional) Specifies which DbContext to use if multiple exist.

Artifacts Generated:

The build pipeline publishes two artifacts:

1. Application Binaries: The compiled web application.

2. Database Script: The deploy.sql file.

---

3. Review Process

One of the strongest arguments for this strategy is the ability to audit changes.

• Who Reviews: Tech Leads or Database Administrators (DBAs).

• What to Check:

  • Open deploy.sql and search for destructive keywords (DROP TABLE, DELETE).

  • Verify that INSERT INTO __EFMigrationsHistory commands exist at the end of each transaction block.

  • Ensure custom raw SQL (migrationBuilder.Sql) appears correctly within the transaction.

---

4. Deployment Process (CD)

The Continuous Deployment (CD) pipeline executes the script. Since the script is just text, a specific SQL CLI tool is required on the deployment agent.

For SQL Server (Example)

Using PowerShell or Bash in the release pipeline:

# Example using Invoke-Sqlcmd (Powershell)
Invoke-Sqlcmd -InputFile "./deploy.sql" -ServerInstance "ProdServer" -Database "ProdDB" -Username "..." -Password "..."

For PostgreSQL (Example)

Using the standard psql tool:

# Example using psql
psql -h prod-db-host -U username -d dbname -f deploy.sql

Execution Logic

1. The script starts a transaction.

2. It checks the __EFMigrationsHistory table.

3. If a migration ID (e.g., 20231025_AddUsers) is missing, the script executes the SQL for that migration and inserts the ID into the history table.

4. If the migration ID exists, the script skips that block entirely.

5. This process repeats for every migration in the file.

---

5. Rollback Strategy

Rollbacks are more manual with scripts than with bundles because the standard script command generates "Up" (Forward) logic by default.

How to Rollback

To revert the database to a previous state, a specific "Down" script must be generated.

1. Identify Targets: Determine the current migration (Source) and the target migration (Destination).

2. Generate Reverse Script:

    dotnet ef migrations script <From_Current_Migration_Name> <To_Target_Previous_Migration_Name> --output rollback.sql
   

   • Note: When the "From" migration is newer than the "To" migration, EF Core automatically generates a script using the Down() logic.

3. Execute: Run rollback.sql against the database.

4. Redeploy App: Deploy the previous version of the application binaries.

---

6. Best Practices & Considerations

1. The "Kitchen Sink" Script vs. Delta Scripts

• Recommended: Generate a single idempotent script containing all migrations (from migration 0 to Latest).

  • Why? It guarantees the database will end up in the correct state regardless of whether it was empty, half-updated, or current.

• Avoid: Generating "Delta" scripts (e.g., only from Migration A to B) unless strictly necessary. This increases the risk of pipeline errors if the target DB is not exactly at Migration A.

2. Transaction Handling

By default, the script command usually wraps operations in transactions. However, some database operations (like altering memory-optimized tables in SQL Server) cannot run inside a transaction.

• Adjustment: Use --no-transactions only if you have specific SQL limitations, but be aware that a failure might leave the DB in a "partial" state.

3. Data Motion Limitations

Complex data transformations (e.g., "Move data from Column A to Column B, then Drop Column A") are difficult to script purely in SQL if they rely on C# logic.

• Best Practice: Keep migrations focused on schema changes (DDL). Perform complex data migrations (DML) using separate background jobs or scripts, not within the EF Core deployment script.

4. CI/CD Security

• Credentials: The deployment agent needs a connection string with high privileges (DDL permissions to CREATE/ALTER tables).

• Separation: This connection string should be different from the one the application uses at runtime (which should ideally have lower privileges).

Summary of Responsibilities