EF Core on AWS
Choose the right EF Core provider for RDS and Aurora, make units of work retry-safe across failovers, and ship migrations from a pipeline as a one-shot job without locking a busy ledger table.
Assumes: .NET 10 LTS · EF Core 10.0.15 · Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 · Amazon.CDK.Lib 2.277.0 · Terraform AWS provider v6 · RDS for PostgreSQL 17 · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Pick the right EF Core provider for RDS or Aurora (PostgreSQL, SQL Server, MySQL), and say when EF Core is the wrong tool (DynamoDB).
- Register a
DbContexton top of a singleNpgsqlDataSource, so pooling and rotating credentials are handled in one place. - Configure a retrying execution strategy, add the AWS-specific failover error, and write a unit of work that is safe to retry, including the commit ambiguity case.
- Run migrations from CI/CD as a one-shot job (migration bundle or idempotent script) under a separate identity, and know what EF Core 9+ locking does and does not protect.
- Write migrations that avoid long locks: expand and contract, concurrent index builds, lock timeouts.
- Compile and read the code in this lesson, and connect it to the module project in
labs/m06-banking-data-store.
Prerequisites
- Connection pooling and RDS Proxy: pool sizing, pinning, and why migrations bypass the proxy
- Encryption and database access: IAM tokens and rotated secrets
- Amazon Aurora and Multi-AZ, replicas, backups and PITR: what a failover looks like to a client
- Module 3: .NET on AWS: dependency injection, configuration, containers
- Assumed: you already use EF Core, migrations and
DbContextlifetime from your day job
Why this matters in banking
Two failures, both avoidable, both ordinary.
The double payment. A Multi-AZ failover happens at 14:02. A client posts a £2,400 supplier payment; the writer commits it and the connection drops before the acknowledgement arrives. EF Core's retry strategy dutifully re-runs the unit of work on the new primary. If the code inserts with a store-generated key and no uniqueness rule, the payment is booked twice. Nothing was misconfigured. The resilience feature did exactly what it was designed to do, and the application was not written for it.
The deploy that took the ledger down. A release adds an index to the 400 million row transactions table. The migration runs at application start, inside a transaction, so PostgreSQL takes a lock that blocks writes while the index builds. Twelve application instances start at once, each tries to migrate, and the payment API returns 500s for nine minutes. Auditors later ask who had permission to change the schema in production, and the answer is "the web application's database login".
This lesson prevents both: retry-safe units of work and migrations that are a deliberate, separate, least-privilege deployment step.
The idea in plain language
EF Core sits between your C# objects and the database driver (ADO.NET). On AWS, three things change compared with a data-centre SQL Server:
- Failover is a normal event. RDS Multi-AZ and Aurora replace the writer in tens of seconds, and sometimes the client still talks to the old one for a moment (DNS caching). Brief connection failures are routine, and the correct response is to retry the whole unit of work.
- Credentials are not static. The password may be a 15-minute IAM token or a rotated secret. The driver must fetch a fresh one for each new physical connection.
- Many instances share one database. Autoscaling means many copies of your app, so who runs migrations, and how connections are budgeted, becomes a design question.
Analogy: An execution strategy is a bank clerk who, when a call drops mid-conversation, phones back and repeats the request. That is helpful for "what is my balance?" and dangerous for "transfer £2,400". Idempotency keys are the reference number the clerk quotes on the second call so the back office can say "already done".
What problem the AWS service solves
EF Core is not an AWS service, so the problem here is fitting it to AWS databases:
| Database | EF Core provider (NuGet) | Notes |
|---|---|---|
| RDS for PostgreSQL / Aurora PostgreSQL | Npgsql.EntityFrameworkCore.PostgreSQL 10.0.3 (used by the module project) | First choice for new .NET workloads on AWS. Supports NpgsqlDataSource, retry strategy, timestamptz, jsonb |
| RDS for SQL Server | Microsoft.EntityFrameworkCore.SqlServer 10.0.x | Same provider as on-premises. Built-in EnableRetryOnFailure. Use Microsoft.Data.SqlClient Encrypt=True and install the RDS CA into the OS trust store |
| Aurora PostgreSQL with Babelfish | SQL Server provider over the TDS endpoint ⚠ VERIFY | Babelfish supports a subset of T-SQL. EF Core-generated SQL can hit unsupported features, so test the model against it. See the comparison lesson |
| RDS for MySQL / Aurora MySQL | Pomelo (Pomelo.EntityFrameworkCore.MySql) or Oracle's MySql.EntityFrameworkCore | ⚠ VERIFY which supports EF Core 10 at the time you start. Check the provider's compatibility table |
| RDS for MariaDB | The MySQL providers above | Same ⚠ VERIFY |
| DynamoDB | None (EF Core is for relational stores) | Use the AWS SDK v4 and its object persistence model. See DynamoDB fundamentals |
| Redshift | No supported EF Core provider ⚠ VERIFY | Use SQL (Npgsql/ODBC) for analytics, not an ORM |
The module project uses PostgreSQL, so the code in this lesson targets Npgsql. Where SQL Server differs, the lesson says so.
Limitations worth knowing before you start:
- Retries buffer result sets. Per the EF Core documentation, enabling retry causes EF to buffer query results internally, which can increase memory for queries returning large result sets. Page your queries.
- No cross-database ORM abstraction for HA. EF Core retries what the provider marks as transient. It does not know about RDS-specific states unless you teach it (the
25006example below). - Migrations are your responsibility on AWS. There is no managed migration service. A bundle or script in a pipeline is the standard answer.
- IAM database authentication is a driver feature, not an EF feature. You supply the token through Npgsql's password provider, as in the module project.
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
EnableRetryOnFailure() with Azure SQL, tuned for SQL error numbers | Same API; with Npgsql it uses PostgreSQL SQLSTATE codes | Add AWS-specific transient codes (such as 25006 after a failover) |
| Azure SQL failover and the retry "login ceiling" | RDS Multi-AZ / Aurora failover | Typically tens of seconds to a couple of minutes; DNS TTL and pool contents decide what clients see |
dotnet ef database update in a release pipeline | Migration bundle (efbundle) run as an ECS task or CodeBuild step | Same artefact; the AWS part is where it runs: inside the VPC, with the database identity, not from a build agent on the internet |
| Azure DevOps deploy task with a service connection to Azure SQL | ECS run-task / CodeBuild in a VPC with an IAM role and a Secrets Manager secret | The database is private, so the pipeline needs a runner inside the network |
Managed identity token in a connection string (Authentication=Active Directory Default) | IAM token as the Npgsql password provider | You write a few lines; the lab's RdsIamTokenPasswordSource does it |
AddDbContextPool | Same | Pools DbContext instances, not connections. Unrelated to RDS Proxy or the Npgsql pool |
Architecture
Security-boundary view: two identities, two network paths. The migration job has DDL rights and a short life; the application has data-manipulation rights only. They never share a credential. The migration job connects directly to the database (not through RDS Proxy), because migrations use session settings and long transactions that pin or block proxy connections.
The deployment order that keeps a bank safe
The rule is expand, deploy, contract. A migration must be compatible with both the old and the new application version, because during a rolling deployment both run at once and, with a rollback, the old one may return.
Step-by-step implementation
Register one data source and a context on top of it
Create a single NpgsqlDataSource (singleton) that owns the pool, TLS settings and the password provider (IAM token or secret). Pass it to UseNpgsql. Never build a new data source per request or per context. See the registration code below, and the module project's DatabaseRegistration.
Turn on a retry strategy, and teach it about AWS
Use the provider's retrying strategy. Add 25006 (read_only_sql_transaction), the error a client gets when DNS still points at a writer that has just been demoted. Cap the retries and the delay so a long outage fails fast enough for a health check to notice.
Wrap every explicit transaction in the strategy
If you call BeginTransactionAsync, run the whole block through CreateExecutionStrategy().ExecuteAsync(...). Clear the change tracker at the start of the block, check the idempotency key inside it, and give the strategy a verifySucceeded delegate for the commit-ambiguity case.
Move migrations out of the application
Remove Database.Migrate() from application startup. Generate a bundle (or an idempotent SQL script for DBA review) in CI, and run it as a one-shot job before the rolling deployment, under a migration identity, directly against the database.
Gate the pipeline on model drift
Run dotnet ef migrations has-pending-model-changes in CI. EF Core 9 and later throw when Migrate finds model changes that have no migration, so catching it in CI avoids failing in production.
Write migrations that do not block writers
Use expand and contract, build indexes concurrently with a lock_timeout, add nullable columns first and backfill in batches, and add constraints as NOT VALID then validate. Test the migration against a production-sized copy.
AWS Console instructions
EF Core runs in your code, so the console's role is observing what it does to the database:
- Console → RDS → Databases → your instance → Monitoring shows
DatabaseConnections, CPU and free memory, so you can see connection counts before and after changing pool sizes or adding the migration job. - Console → RDS → Databases → your instance → Logs & events lets you read the PostgreSQL log for slow statements (
log_min_duration_statement) and for long lock waits (log_lock_waitsif you enable it ⚠ VERIFY parameter). - Console → Performance Insights shows which SQL statements EF Core generated and which wait events dominate. A migration waiting on
Lock:relationshows up here (performance lesson). - Console → ECS → Clusters → Task definitions → hl-m06-migrate → Deploy → Run task starts the migration job by hand. Choose launch type Fargate, your private subnets and the migration security group, and assign no public IP.
- Console → CloudWatch → Log groups → /hl-m06/migrate shows the bundle's output (
Applying migration ...,Done.).
AWS CLI
PROFILE=learn-sandbox
REGION=us-east-1
# --- Build-time (CI): produce the migration artefacts. ---------------------------------------
# Fail the build if the model changed without a migration (EF Core 9+).
dotnet ef migrations has-pending-model-changes --project src/Harbourline.Ledger.Api
# A self-contained Linux bundle: no SDK or runtime needed on the machine that runs it.
ASPNETCORE_ENVIRONMENT=Production \
dotnet ef migrations bundle --project src/Harbourline.Ledger.Api \
--self-contained --target-runtime linux-arm64 --output artifacts/efbundle --force
# An idempotent script when a DBA must review the SQL first.
dotnet ef migrations script --idempotent --project src/Harbourline.Ledger.Api --output artifacts/migrations.sql
# --- Deploy-time: run the bundle as a one-shot ECS task inside the VPC. -----------------------
TASK=$(aws ecs run-task --cluster hl-m06 --launch-type FARGATE --task-definition hl-m06-migrate \
--network-configuration "awsvpcConfiguration={subnets=[<subnet-a>,<subnet-b>],securityGroups=[<migration-sg>],assignPublicIp=DISABLED}" \
--query 'tasks[0].taskArn' --output text --profile $PROFILE --region $REGION)
# Wait for it to stop, then read the exit code: anything other than 0 must fail the deployment.
aws ecs wait tasks-stopped --cluster hl-m06 --tasks "$TASK" --profile $PROFILE --region $REGION
aws ecs describe-tasks --cluster hl-m06 --tasks "$TASK" \
--query 'tasks[0].containers[0].[exitCode,reason]' --output text --profile $PROFILE --region $REGION
# --- Check what the app is doing to the database (from inside the VPC with psql). -------------
# psql ... -c "SELECT state, wait_event_type, wait_event, count(*) FROM pg_stat_activity
# WHERE application_name = 'harbourline-ledger' GROUP BY 1,2,3 ORDER BY 4 DESC;"Infrastructure as Code
The one-shot migration job is an ECS task definition with no service. The pipeline starts it with run-task. Both snippets compile or validate (CDK built with Amazon.CDK.Lib 2.277.0, Terraform with terraform validate on 1.14; neither was deployed). The image is expected to contain the efbundle executable and the RDS CA bundle at /certs/global-bundle.pem.
using Amazon.CDK;
using Amazon.CDK.AWS.ECR;
using Amazon.CDK.AWS.ECS;
using Amazon.CDK.AWS.Logs;
using Constructs;
namespace Harbourline.Infra;
public sealed class MigrationJobStackProps : StackProps
{
public required string RepositoryName { get; init; }
public required string ImageTag { get; init; }
public required string DbHost { get; init; }
/// <summary>The RDS-managed master secret: its "password" key is injected as PGPASSWORD.</summary>
public required string MasterSecretArn { get; init; }
}
/// <summary>
/// A task definition for the one-shot migration job: it runs the EF Core migration bundle with the
/// migration identity, then exits. There is deliberately no ECS service, so it never restarts.
/// Start it from the pipeline with `aws ecs run-task`.
/// </summary>
public sealed class MigrationJobStack : Stack
{
public MigrationJobStack(Construct scope, string id, MigrationJobStackProps props) : base(scope, id, props)
{
var repo = Repository.FromRepositoryName(this, "Repo", props.RepositoryName);
var master = Amazon.CDK.AWS.SecretsManager.Secret.FromSecretCompleteArn(this, "Master", props.MasterSecretArn);
var logs = new LogGroup(this, "Logs", new LogGroupProps
{
Retention = RetentionDays.ONE_MONTH,
RemovalPolicy = RemovalPolicy.DESTROY,
});
var task = new FargateTaskDefinition(this, "MigrateTask", new FargateTaskDefinitionProps
{
Cpu = 256,
MemoryLimitMiB = 512,
RuntimePlatform = new RuntimePlatform
{
CpuArchitecture = CpuArchitecture.ARM64,
OperatingSystemFamily = OperatingSystemFamily.LINUX,
},
});
task.AddContainer("migrate", new ContainerDefinitionOptions
{
Image = ContainerImage.FromEcrRepository(repo, props.ImageTag),
// The bundle is the only thing this image does. The password comes from the secret, not the command line.
EntryPoint = ["sh", "-c"],
Command = [$"./efbundle --connection \"Host={props.DbHost};Database=ledger;Username=ledger_admin;SSL Mode=VerifyFull;Root Certificate=/certs/global-bundle.pem\""],
Secrets = new Dictionary<string, Amazon.CDK.AWS.ECS.Secret>
{
["PGPASSWORD"] = Amazon.CDK.AWS.ECS.Secret.FromSecretsManager(master, "password"),
},
Logging = LogDrivers.AwsLogs(new AwsLogDriverProps { StreamPrefix = "migrate", LogGroup = logs }),
ReadonlyRootFilesystem = false,
});
// The construct creates the execution role and grants it read access to the secret. The task role has no
// policies on purpose: the job needs the database, not the AWS API.
Amazon.CDK.Tags.Of(this).Add("Project", "cea-lab");
Amazon.CDK.Tags.Of(this).Add("Lab", "m06-project");
_ = new CfnOutput(this, "TaskDefinitionArn", new CfnOutputProps { Value = task.TaskDefinitionArn });
}
}Two CDK C# details: Secret exists in both Amazon.CDK.AWS.ECS and Amazon.CDK.AWS.SecretsManager, so the types are fully qualified, and Tags.Of inside a Stack is written Amazon.CDK.Tags.Of(this) to avoid the CS0176 error. Npgsql reads PGPASSWORD when the connection string has no password, so the secret never appears on a command line or in the task definition.
Complete code
The code below is a small, self-contained console project that mirrors what the module project does inside the API. It compiles on .NET 10 with EF Core 10.0.15 and the Npgsql provider 10.0.3 (both the PostgreSQL and SQL Server registrations are included, so you can compare them). It needs no AWS account to build.
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="10.0.3" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="10.0.15" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="10.0.15">
<PrivateAssets>all</PrivateAssets>
</PackageReference>
</ItemGroup>
</Project>using Microsoft.EntityFrameworkCore;
namespace Harbourline.EfAws;
public sealed class Account
{
public required string AccountId { get; init; }
public decimal Balance { get; set; }
public long Version { get; set; }
}
public sealed class LedgerTransaction
{
public required string TransactionId { get; init; }
public required string AccountId { get; init; }
public decimal Amount { get; init; }
public required string IdempotencyKey { get; init; }
public DateOnly BookingDate { get; init; }
}
public sealed class LedgerDb(DbContextOptions<LedgerDb> options) : DbContext(options)
{
public DbSet<Account> Accounts => Set<Account>();
public DbSet<LedgerTransaction> Transactions => Set<LedgerTransaction>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Account>(e =>
{
e.ToTable("accounts");
e.HasKey(a => a.AccountId);
e.Property(a => a.Balance).HasPrecision(18, 2);
e.Property(a => a.Version).IsConcurrencyToken();
});
modelBuilder.Entity<LedgerTransaction>(e =>
{
e.ToTable("transactions");
e.HasKey(t => t.TransactionId);
e.Property(t => t.Amount).HasPrecision(18, 2);
e.HasIndex(t => new { t.AccountId, t.IdempotencyKey }).IsUnique();
});
}
}using Microsoft.EntityFrameworkCore.Storage;
using Npgsql.EntityFrameworkCore.PostgreSQL;
namespace Harbourline.EfAws;
/// <summary>
/// Npgsql's retrying strategy plus one SQLSTATE that matters on AWS: 25006 (read_only_sql_transaction).
/// After a Multi-AZ cluster or Aurora failover, a client whose DNS cache still points at the old writer
/// reaches a server that is now read-only. The error disappears as soon as the client reconnects to
/// the new writer, so it is safe to retry the whole unit of work.
/// </summary>
public sealed class LedgerExecutionStrategy(ExecutionStrategyDependencies dependencies)
: NpgsqlRetryingExecutionStrategy(
dependencies,
maxRetryCount: 5,
maxRetryDelay: TimeSpan.FromSeconds(10),
errorCodesToAdd: ["25006"]);using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Npgsql;
namespace Harbourline.EfAws;
public static class Registration
{
/// <summary>PostgreSQL / Aurora PostgreSQL: one NpgsqlDataSource owns the pool and the credentials.</summary>
public static IServiceCollection AddLedgerPostgres(this IServiceCollection services, NpgsqlDataSource dataSource)
{
services.AddSingleton(dataSource);
// A pool of DbContext instances (not connections) trims allocation cost on hot endpoints.
// Do not keep per-request state on the context when you pool it.
services.AddDbContextPool<LedgerDb>((sp, options) => options.UseNpgsql(
sp.GetRequiredService<NpgsqlDataSource>(),
npgsql =>
{
npgsql.ExecutionStrategy(deps => new LedgerExecutionStrategy(deps));
npgsql.CommandTimeout(15); // seconds; the default is 30
}));
return services;
}
/// <summary>RDS for SQL Server: the built-in strategy knows SQL Server's transient error numbers.</summary>
public static IServiceCollection AddLedgerSqlServer(this IServiceCollection services, string connectionString)
{
services.AddDbContext<LedgerDb>(options => options.UseSqlServer(
connectionString,
sql => sql.EnableRetryOnFailure(
maxRetryCount: 5,
maxRetryDelay: TimeSpan.FromSeconds(10),
errorNumbersToAdd: null)));
return services;
}
}using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
namespace Harbourline.EfAws;
/// <summary>
/// The commit-ambiguity problem: the connection drops after the COMMIT is sent but before the reply
/// arrives. The retry cannot know whether the first attempt committed. Two defences are shown:
/// (1) check inside the unit of work (idempotency key), and (2) ask the strategy to verify explicitly.
/// </summary>
public sealed class PostingUnitOfWork(LedgerDb db)
{
public async Task<bool> PostAsync(string accountId, string idempotencyKey, decimal amount, CancellationToken ct)
{
var strategy = db.Database.CreateExecutionStrategy();
return await strategy.ExecuteAsync(
(accountId, idempotencyKey, amount),
async (_, state, token) =>
{
db.ChangeTracker.Clear(); // a retry must not see entities tracked by the failed attempt
await using var tx = await db.Database.BeginTransactionAsync(token);
// Defence 1: the unique key says "already booked", so the retry does nothing.
if (await db.Transactions.AnyAsync(t => t.AccountId == state.accountId && t.IdempotencyKey == state.idempotencyKey, token))
{
return false;
}
var account = await db.Accounts.SingleAsync(a => a.AccountId == state.accountId, token);
account.Balance += state.amount;
account.Version++;
db.Transactions.Add(new LedgerTransaction
{
TransactionId = Guid.CreateVersion7().ToString(),
AccountId = state.accountId,
Amount = state.amount,
IdempotencyKey = state.idempotencyKey,
BookingDate = DateOnly.FromDateTime(DateTime.UtcNow),
});
await db.SaveChangesAsync(token);
await tx.CommitAsync(token);
return true;
},
// Defence 2: after an ambiguous failure the strategy calls this to learn whether the work committed.
async (_, state, token) =>
{
var committed = await db.Transactions.AsNoTracking()
.AnyAsync(t => t.AccountId == state.accountId && t.IdempotencyKey == state.idempotencyKey, token);
return new ExecutionResult<bool>(committed, true);
},
ct);
}
/// <summary>Set-based update: one statement, no entities loaded, no tracking.</summary>
public Task<int> FreezeDormantAsync(decimal maxBalance, CancellationToken ct) =>
db.Accounts.Where(a => a.Balance <= maxBalance).ExecuteUpdateAsync(s => s.SetProperty(a => a.Version, a => a.Version + 1), ct);
}using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace Harbourline.EfAws.Migrations;
/// <summary>
/// Adds an index on a busy table without blocking writes (PostgreSQL).
/// CREATE INDEX CONCURRENTLY cannot run inside a transaction, so the statement opts out of the
/// migration's transaction. lock_timeout makes DDL give up quickly instead of queueing behind a
/// long query and blocking every query that arrives after it.
/// </summary>
[DbContext(typeof(LedgerDb))]
[Migration("20261118100000_AddBookingDateIndex")]
public partial class AddBookingDateIndex : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql("SET lock_timeout = '5s';", suppressTransaction: true);
migrationBuilder.Sql(
"CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_transactions_account_id_booking_date ON transactions (account_id, booking_date);",
suppressTransaction: true);
migrationBuilder.Sql("RESET lock_timeout;", suppressTransaction: true);
}
protected override void Down(MigrationBuilder migrationBuilder) =>
migrationBuilder.Sql("DROP INDEX CONCURRENTLY IF EXISTS ix_transactions_account_id_booking_date;", suppressTransaction: true);
}using Harbourline.EfAws;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using Npgsql;
// One-shot migration runner for pipelines: `dotnet run -- migrate`.
// Runs as its OWN deployment step, with its OWN database identity, never from every web instance.
var connectionString = Environment.GetEnvironmentVariable("HL_PG_ADMIN")
?? throw new InvalidOperationException("Set HL_PG_ADMIN (the migration identity's connection string).");
var services = new ServiceCollection();
services.AddLedgerPostgres(NpgsqlDataSource.Create(connectionString));
await using var provider = services.BuildServiceProvider();
await using var scope = provider.CreateAsyncScope();
var db = scope.ServiceProvider.GetRequiredService<LedgerDb>();
var pending = (await db.Database.GetPendingMigrationsAsync()).ToList();
if (pending.Count == 0)
{
Console.WriteLine("No pending migrations.");
return 0;
}
Console.WriteLine($"Applying {pending.Count} migration(s): {string.Join(", ", pending)}");
await db.Database.MigrateAsync();
Console.WriteLine("Schema is up to date.");
return 0;How this relates to the module project: labs/m06-banking-data-store/src/Harbourline.Ledger.Api/Posting/PostingService.cs is the full version of PostingUnitOfWork (it adds validation, hashing of the request payload, and a retry loop for optimistic-concurrency conflicts). Connectivity/DatabaseRegistration.cs builds the NpgsqlDataSource with the IAM or Secrets Manager password provider. Program.cs exposes migrate, seed and fingerprint commands. Its README shows the PostgreSQL run-through.
Code explained line by line
Account.Versionis markedIsConcurrencyToken(). EF Core addsWHERE version = @originalto theUPDATE, so two writers cannot both apply a change from the same starting balance. The loser gets aDbUpdateConcurrencyExceptionand must re-read.- The unique index on
(AccountId, IdempotencyKey)makes "have I seen this key?" a database guarantee, not an application check. LedgerExecutionStrategyderives fromNpgsqlRetryingExecutionStrategyand passeserrorCodesToAdd: ["25006"]. That adds the SQLSTATE for "cannot execute in a read-only transaction", which a client sees when it reaches a just-demoted writer. Retries are capped at 5 and the delay at 10 seconds so a long outage becomes a visible failure.AddLedgerPostgresregisters theNpgsqlDataSourceas a singleton and gives it toUseNpgsql, so EF Core borrows connections from the pool that already handles credential refresh.AddDbContextPoolpoolsDbContextobjects: a micro-optimisation unrelated to connection pooling.CommandTimeout(15)sets the EF command timeout in seconds. Choose it below your API gateway or load balancer timeout so the database stops work before the client has given up.AddLedgerSqlServershows the SQL Server equivalent.EnableRetryOnFailureuses the SQL Server provider's own transient-error list, anderrorNumbersToAdd: nullmeans "no extras".- In
PostAsync,CreateExecutionStrategy().ExecuteAsync(state, operation, verifySucceeded, ct)runs the whole delegate again on a transient failure. That is why the first line isdb.ChangeTracker.Clear(): a retry must start from a clean tracker, because the failed attempt may have left entities half-modified. BeginTransactionAsyncis called inside the delegate. EF Core throws if you call it outside while a retrying strategy is active, because it cannot replay a transaction it did not start.- The first
AnyAsyncis defence one: if the idempotency key already exists, the earlier attempt committed, so return without changing anything. verifySucceededis defence two. After a transient failure the strategy calls it to ask "did the work actually commit?", which resolves the commit-ambiguity case described in the EF Core documentation: the connection can drop while the commit is in flight.ExecuteUpdateAsyncinFreezeDormantAsyncruns a single set-basedUPDATEwith no entities loaded. Use it for bulk changes instead of loading thousands of rows into the change tracker.- The migration uses
suppressTransaction: trueon each statement, so EF does not wrap them in a transaction.CREATE INDEX CONCURRENTLYis not allowed inside one.SET lock_timeoutmakes a statement fail after 5 seconds rather than queueing behind a long-running query and blocking everything behind it. Program.csis the migration runner. It lists pending migrations, applies them, and exits with 0 or an exception (non-zero), which is what a pipeline gate needs.
Migrations that do not take the ledger down
EF Core 9 and later lock migrations. Migrate and MigrateAsync, dotnet ef database update and bundles acquire a database-wide lock before applying, so twelve instances starting at once no longer corrupt each other. The EF documentation warns the mechanism varies by provider, and SQL scripts bypass it. The lock stops concurrent migrators. It does not make a migration non-blocking for your queries.
Other facts from the EF Core documentation worth keeping (checked 2026-11-18):
- Prefer a bundle for automated deployment and a SQL script when the SQL must be reviewed. Use a separate identity for deployment with schema rights; the application's identity should not have them.
- Do not run every replica's entrypoint through
Migrate. Run the bundle as a one-shot job, and configure the platform not to restart it after a successful exit. Migratethrows when the model has pending changes. Detect that in CI withdotnet ef migrations has-pending-model-changes.- Wrapping
MigrateAsyncin your own explicit transaction is not supported. - Do not call
EnsureCreatedAsync()beforeMigrateAsync(). - Pass the connection string to the bundle with
--connection, and setASPNETCORE_ENVIRONMENTexplicitly so a bundle does not load development user secrets. Do not embed production connection strings.
Expand and contract is how you change a schema while old and new application versions run together:
| Goal | Unsafe single step | Safe sequence |
|---|---|---|
Rename reference to narrative | RenameColumn (old app breaks immediately) | Release 1: add narrative, write both, backfill in batches. Release 2: read narrative. Release 3: stop writing reference. Release 4: drop it |
| Add a required column | ALTER TABLE ... ADD COLUMN x NOT NULL without a default on a big table | Add nullable, backfill in batches, add a CHECK (x IS NOT NULL) NOT VALID, validate, then set NOT NULL |
| Add an index | CREATE INDEX in the migration transaction (blocks writes) | CREATE INDEX CONCURRENTLY outside a transaction, with lock_timeout |
| Add a foreign key | ADD CONSTRAINT ... FOREIGN KEY (scans and locks) | ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT |
| Change a column type | ALTER COLUMN TYPE (may rewrite the table) | New column, dual write, backfill, switch reads, drop old |
PostgreSQL specifics ⚠ VERIFY against your version: adding a column with a constant default is a metadata-only change on modern versions, while a volatile default rewrites the table. CREATE INDEX CONCURRENTLY takes longer, can fail and leave an invalid index you must drop, and needs suppressTransaction. lock_timeout protects the other queries from waiting behind your DDL.
SQL Server specifics: online index operations (WITH (ONLINE = ON)) are available on Enterprise edition, which is also the expensive RDS licence, so Standard edition teams rely on off-peak windows, smaller batches and WAIT_AT_LOW_PRIORITY style options ⚠ VERIFY per edition. This is a place where the RDS SQL Server licence choice changes your migration strategy.
Rollback. EF Down methods are often untested. In a bank, assume forward-only: write the next migration that fixes things, and rely on the expand and contract rule so the old application still works.
Security considerations
⚠ Learning shortcut — not for production
The lab runs Migrate at startup for the local SQLite file and runs the migration job with the RDS master secret. Production uses a dedicated ledger_migrator identity with only the DDL rights it needs on the application schema, not the master user, rotated credentials, and a manual approval before the job runs against the ledger.
- Separate identities. The application's role has no DDL and no
DELETE. The migration identity has DDL but no standing access: the job gets its secret at start, then the task ends. - Never put a connection string with a password in the image or the task definition. Use
PGPASSWORDfrom a secret, as above, or an IAM token generated for the migration role. - Review generated SQL. A rename is sometimes generated as drop and add, which loses data. Inspect migrations and the idempotent script, and test on a copy of production-shaped data.
- Log carefully. EF Core's
EnableSensitiveDataLoggingwrites parameter values, including account numbers, to logs. Keep it off outside local development.EnableDetailedErrorsis safer but still verbose. - Parameterise, always.
FromSqlInterpolatedand LINQ parameterise.FromSqlRawwith string concatenation does not. Treat raw SQL as a review trigger. - The retry strategy is a risk if the work is not idempotent. Any non-idempotent side effect inside the delegate (sending an email, calling a payment partner) will be repeated. Keep side effects outside the retried block or behind an outbox.
- Compliance. Audit trails for schema changes (who ran which migration, when) come from the pipeline and CloudTrail for the
RunTaskcall. Whether that satisfies a regulation is for your compliance team to decide.
Banking use case
Harbourline's release process for the ledger service:
- A pull request changes the model and adds a migration. CI runs
dotnet test(including the PostgreSQL integration test with 20 concurrent postings),has-pending-model-changes, and generatesmigrations.sqlas an artefact. - A DBA reviews the idempotent script for any change that touches
transactionsoraccounts. Index and constraint changes must use the non-blocking patterns above. - The pipeline builds the image (application plus
efbundle), pushes to ECR and, in staging, runs the migration task, then deploys the service. - For production, a human approves the pipeline stage. The migration task runs first and must exit 0. Only then does the rolling deployment begin. If the task fails, the deployment stops and the old version keeps serving.
- The application's posting endpoint uses the unit of work from this lesson: idempotency key, optimistic concurrency, retry strategy, and a
409withRetry-Afterwhen conflicts exhaust the retries. - CloudWatch alarms watch
DatabaseConnections, replica lag, and the count of retried commands, derived from theRetrying commandlog entries ⚠ VERIFY exact log wording.
Common mistakes
- Calling
Migrate()inProgram.csof every service. Even with EF 9 locking, it gives the web identity DDL rights and ties deploy time to migration time. Use a one-shot job. - Beginning a transaction outside the execution strategy. You get "does not support user-initiated transactions" at runtime. Wrap the block.
- Not clearing the change tracker on retry. The retry sees stale tracked entities and writes duplicates or throws.
- Relying on store-generated keys with retries. A retried insert can create a second row. Use client-generated IDs (the module project uses UUID v7) and a unique idempotency key.
- Putting a
SETor temp table in migrations that run through RDS Proxy. They pin or block. Run migrations directly. - Creating an index in a migration transaction on a big table. Writers wait for the whole build. Use
CONCURRENTLYwithsuppressTransaction. - One
DbContextshared across requests or threads.DbContextis not thread-safe. Use scoped instances, orIDbContextFactory<T>for background work. - Large unbounded queries with retries on. Retry buffers results. Page with
SkipandTakeor keyset pagination (WHERE transaction_id < @last ORDER BY transaction_id DESC). - Forgetting
DateTimeOffsetrules with Npgsql.timestamptzcolumns accept UTC only. Convert to UTC before saving.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
The configured execution strategy ... does not support user-initiated transactions | BeginTransactionAsync outside strategy.ExecuteAsync | Wrap the whole unit of work in the strategy |
The model for context ... has pending changes when migrating | Model changed with no migration (EF 9+) | dotnet ef migrations add, and add has-pending-model-changes to CI |
| Duplicate rows after a failover | Non-idempotent insert retried after an ambiguous commit | Unique idempotency key, client-generated IDs, verifySucceeded |
25006: cannot execute ... in a read-only transaction | Client reached a demoted writer (stale DNS) | Add 25006 to the strategy, shorten Connection Lifetime, use RDS Proxy or the cluster endpoint |
53300: too many clients after deploying | Pool size times instance count exceeds max_connections | Budget the pool (pooling lesson) |
Migration task exits non-zero, log says password authentication failed | Wrong secret key, rotated secret, or PGPASSWORD missing | Check the secret's password key, run the task again |
| Migration hangs, writes stall | DDL waiting for a lock, or index build in a transaction | Cancel, rerun with lock_timeout and CONCURRENTLY; see Performance Insights Lock:relation |
CREATE INDEX CONCURRENTLY cannot run inside a transaction block | Statement not marked suppressTransaction: true | Add the argument, and keep it as its own migration |
| Bundle runs but changes the wrong database | Environment or config picked the default connection | Pass --connection explicitly and set ASPNETCORE_ENVIRONMENT=Production |
| Memory spikes on large queries after enabling retries | EF buffers results when retrying is on | Page the query, project to DTOs, use AsNoTracking |
Cost and cleanup
Cost label: free. Everything compiles and runs locally. The module project's local PostgreSQL container (docker compose up -d) is enough to try the migration runner and the unit of work.
If you also run the AWS pieces (the migration task against the lab RDS instance), the cost is the Fargate task for a few seconds plus the CloudWatch log group, a few cents. The database cost belongs to the module project (labs/m06-banking-data-store).
PROFILE=learn-sandbox
REGION=us-east-1
# Remove the migration task definition and log group (CDK / Terraform equivalents below).
aws logs delete-log-group --log-group-name /hl-m06/migrate --profile $PROFILE --region $REGION
# Deregister task definition revisions (they are free, but keep the account tidy):
aws ecs list-task-definitions --family-prefix hl-m06-migrate --query 'taskDefinitionArns[]' --output text \
--profile $PROFILE --region $REGION | tr '\t' '\n' | while read -r arn; do
aws ecs deregister-task-definition --task-definition "$arn" --profile $PROFILE --region $REGION >/dev/null
done
# cdk destroy hl-m06-migration-job (from the CDK app folder)
# terraform destroy (from infra/terraform)
# Local: docker compose down -vDelete the build output too: bin/ and obj/ folders.
Exercises
- 🟢 Guided: start the module project's PostgreSQL container, set
HL_PG_ADMIN, and run the migration runner twice. The first run applies the migration, the second prints "No pending migrations". Then rundotnet ef migrations script --idempotentand read the SQL forCREATE INDEX CONCURRENTLY. - 🟡 Challenge: write a second migration that renames
transactions.referencetonarrativesafely over three releases. List which release contains which migration and which application code change. Then add the foreign-key variant usingNOT VALIDandVALIDATE CONSTRAINTas raw SQL. - 🔴 Live problem: after the nightly deployment, support reports that 3 customers each have the same £2,400 supplier payment booked twice at 14:02, the minute of an RDS failover. Logs show
Retrying commandentries and the posting endpoint usesGuid.NewGuid()forTransactionIdwith no idempotency key column. Write the incident analysis, the immediate remediation (data repair approach, communication) and the code and schema change that prevents recurrence.
Assignment
Add docs/runbook-migrations.md to the module project. It must contain: the pipeline stages with the gate for has-pending-model-changes, the identities and their rights, the exact run-task command with network settings, how to read the exit code, the expand and contract rules your team will follow with one worked example (the rename), the rollback policy ("forward only" or otherwise, with reasons), and a checklist the reviewer uses for any migration touching transactions.
Solutions
The first run prints Applying 1 migration(s): 20261118100000_AddBookingDateIndex, then Schema is up to date.. The second prints No pending migrations.. The idempotent script wraps each migration in a check against __EFMigrationsHistory, so it is safe to run repeatedly. For the index migration the script contains the CREATE INDEX CONCURRENTLY statement outside any BEGIN block, because of suppressTransaction: true. If you removed that argument, PostgreSQL would reject the statement inside a transaction.
Release 1: migration adds nullable narrative; code writes both columns and reads reference; a backfill job copies reference into narrative in batches of a few thousand rows with a pause between batches. Release 2: code reads narrative (falling back to reference for rows not yet backfilled); still writes both. Release 3: code stops writing reference; the migration sets narrative NOT NULL through a NOT VALID check constraint then VALIDATE. A later cleanup release drops reference. The foreign key: ALTER TABLE transactions ADD CONSTRAINT fk_transactions_accounts FOREIGN KEY (account_id) REFERENCES accounts (account_id) NOT VALID; then, in a separate migration, ALTER TABLE transactions VALIDATE CONSTRAINT fk_transactions_accounts;. The first step takes a brief lock only; validation scans the table with a weaker lock that does not block normal writes ⚠ VERIFY for your PostgreSQL version.
Analysis: the failover dropped the connection around the commit. The retry strategy re-ran the unit of work. Because the insert had a client-generated random ID and no uniqueness rule, nothing stopped a second row. The first attempt had committed; the second duplicated it. Remediation: identify duplicates by (account_id, amount, counterparty, booking_date) within the failover window, do not delete (a ledger is append-only): post compensating reversal transactions with a reference to the duplicates, approved under your operational procedure, and notify affected customers and your incident manager. Prevention: add idempotency_key with a unique index on (account_id, idempotency_key), require the Idempotency-Key header, check it inside the retried delegate, and add verifySucceeded. Add a test that simulates a transient failure after commit (for example with an interceptor that throws once after CommitAsync) and asserts one row.
Knowledge check
Answer: The strategy can only replay work it controls. A transaction you start outside defines a group of operations it cannot re-run, so EF Core throws. Inside the delegate, a transient failure re-runs the whole block, including the transaction.
Answer: Commit ambiguity. If the connection drops during COMMIT, the outcome is unknown. By default the strategy retries as if it rolled back, which could duplicate the work. verifySucceeded lets you check whether the work is already in the database.
Answer: It prevents several processes from applying migrations at the same time. It does not make a migration non-blocking for other queries, and it does not apply to SQL scripts run outside EF Core.
Answer: None. EF Core targets relational stores. Use the AWS SDK for .NET (v4), including its object persistence model, for DynamoDB.
Answer: Migrations commonly use session settings (SET lock_timeout), long transactions and sometimes advisory locks, which pin proxy connections or behave unpredictably. They are also rare, so the proxy's benefits do not apply.
Answer: Add the column as nullable, backfill in batches, add a CHECK (col IS NOT NULL) NOT VALID constraint, validate it, then set NOT NULL (PostgreSQL can use the validated constraint to avoid a full scan on modern versions ⚠ VERIFY).
Interview questions
- How do you make a payment-posting endpoint safe against retries? A client-supplied idempotency key with a unique index, a client-generated primary key, the whole unit of work inside the execution strategy, a clean change tracker per attempt, optimistic concurrency on balances, and a
verifySucceededcheck for the ambiguous commit. - How do you run EF Core migrations in production on AWS? Generate a bundle or idempotent script in CI, run it as a one-shot ECS or CodeBuild job in the VPC under a dedicated migration identity before the rolling deployment, fail the pipeline on a non-zero exit, and design the migration for expand and contract.
- A migration needs an index on a table with hundreds of millions of rows. What do you do? Build it concurrently outside a transaction with a
lock_timeout, schedule it off-peak, monitorpg_stat_progress_create_index, and have a plan for an invalid index if it fails. - Why not
Migrate()on application startup? Gives the app DDL rights, couples startup to migration time, runs on every replica (EF 9 locks, but still), complicates rollbacks and auditing.
Certification practice
An application using an ORM connects to Amazon RDS for PostgreSQL in a Multi-AZ configuration. During a failover, some requests fail for about a minute and a few payments are created twice after retries. Which change MOST improves correctness?
A. Increase the retry count to 20 B. Use an idempotency key enforced by a unique constraint, and run each unit of work inside a retry-safe transaction block C. Enable read replicas and retry reads D. Disable retries
Answer: B. Retries are needed during failover; duplicates arise because the work is not idempotent. A makes duplicates more likely. C does not address writes. D turns brief failures into outages.
A container service runs 20 tasks. Each task runs database schema migrations at startup using the application's database login. Deployments sometimes cause errors and the security team objects to the login's permissions. What should the architect recommend?
A. Increase the database instance size B. Run migrations as a separate one-shot task with a dedicated, higher-privileged identity before the rolling deployment, and give the application login only data permissions C. Give each task its own database D. Disable automated backups during deployment
Answer: B. It separates duties and avoids concurrent migrators. A does not fix permissions or concurrency. C breaks the data model. D reduces resilience.
A team must add an index to a very large, busy PostgreSQL table on RDS without blocking writes. Which approach is MOST appropriate?
A. Run a normal CREATE INDEX inside the migration transaction at 09:00
B. Use CREATE INDEX CONCURRENTLY outside a transaction, with a lock timeout, ideally off-peak
C. Stop the application during the index build
D. Drop the table and recreate it with the index
Answer: B. Concurrent builds avoid blocking writers (at the cost of time and a possible invalid index on failure). A blocks writes. C is unnecessary downtime. D loses data.
Completion checklist
- I can name the right EF Core provider for each RDS or Aurora engine, and when EF Core does not apply
- I can explain commit ambiguity and the two defences (idempotency key and
verifySucceeded) - I ran the migration runner twice and read the idempotent script
- I can design an expand and contract sequence for a rename
- I removed
Migrate()from startup in my own code or wrote down why not - I deleted any task definitions, log groups and build output I created
Key takeaways
- One
NpgsqlDataSource, one execution strategy, and units of work that are safe to retry. Teach the strategy about AWS failover (25006). - Idempotency is a database property: a unique key plus a client-generated ID beats any application-level check.
- Migrations are a deployment step, not a startup side effect: bundle or script, one-shot job, separate identity, direct connection.
- EF Core 9 locking prevents concurrent migrators, not blocked queries. Use expand and contract, concurrent indexes and lock timeouts.
- EF Core is for relational stores. For DynamoDB, use the SDK.
Further reading
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Connection pooling and RDS Proxy
Work out how many database connections a .NET fleet can open, size ADO.NET and Npgsql pools to stay inside the limit, and decide when to put Amazon RDS Proxy in front of RDS or Aurora, including what makes it stop helping.
Transactions, isolation levels and consistency
Compare SQL Server locking and snapshot isolation with PostgreSQL MVCC and DynamoDB read consistency, then write .NET code that moves money correctly under concurrency on RDS, Aurora and DynamoDB.