Transactions and tests

Commit several writes atomically with ExecuteTransactionAsync, then unit-test the data layer with no database by mocking the generated context interfaces.

updated 5 Jun 20262 min readv0.3.2View as Markdown

The last endpoint creates a project and its first tasks together. Either all of it lands or none of it does, which is exactly what ExecuteTransactionAsync provides.

One atomic unit of work

ExecuteTransactionAsync opens a transaction, runs the callback, commits when it returns, and rolls back if it throws. Move the multi-write logic into a service:

public sealed class ProjectService(ISocigyDatabaseFactory<IAppDb> db)
{
    public Task<Guid> CreateWithTasksAsync(string name, IEnumerable<string> titles) =>
        db.ExecuteTransactionAsync(async d =>
        {
            var project = new Project { Id = Guid.NewGuid(), Name = name };
            await d.Projects.InsertAsync(project, includeAutoFields: true);

            foreach (var title in titles)
                await d.Tasks.InsertAsync(new TaskItem { ProjectId = project.Id, Title = title });

            return project.Id;   // committed here; any throw above rolls the whole thing back
        });
}

Generating the Id client-side and passing includeAutoFields: true writes that value, so the foreign key on each task is known before the rows are inserted. Register the service and wire the endpoint:

builder.Services.AddScoped<ProjectService>();

app.MapPost("/projects/seeded", (SeedRequest body, ProjectService svc) =>
    svc.CreateWithTasksAsync(body.Name, body.Tasks));

record SeedRequest(string Name, string[] Tasks);

NOTE

Nested ExecuteAsync / ExecuteTransactionAsync calls join the outermost scope, so a service that calls another service still commits once. See Transactions.

Test it without a database

Because the context is a set of generated interfaces (ISocigyDatabaseFactory<IAppDb>, IAppDb, IProjectSet, ITaskItemSet), the service is testable with a mocking library and no PostgreSQL. Wire the mock factory to invoke the callback against a mock context:

[Fact]
public async Task CreateWithTasksAsync_inserts_project_and_each_task()
{
    var projects = new Mock<IProjectSet>();
    var tasks = new Mock<ITaskItemSet>();

    var ctx = new Mock<IAppDb>();
    ctx.SetupGet(c => c.Projects).Returns(projects.Object);
    ctx.SetupGet(c => c.Tasks).Returns(tasks.Object);

    var db = new Mock<ISocigyDatabaseFactory<IAppDb>>();
    db.Setup(f => f.ExecuteTransactionAsync(It.IsAny<Func<IAppDb, Task<Guid>>>(), It.IsAny<CancellationToken>()))
      .Returns((Func<IAppDb, Task<Guid>> work, CancellationToken _) => work(ctx.Object));

    var svc = new ProjectService(db.Object);
    await svc.CreateWithTasksAsync("Launch", new[] { "Design", "Build", "Ship" });

    projects.Verify(p => p.InsertAsync(It.IsAny<Project>(), true), Times.Once);
    tasks.Verify(t => t.InsertAsync(It.IsAny<TaskItem>(), false), Times.Exactly(3));
}

The mocked factory runs your real CreateWithTasksAsync logic, so the test verifies the unit of work itself: one project insert, one task insert per title. For tests against a real database, point a connection string at a disposable PostgreSQL instance and run migrations in a fixture. See Unit testing.

Where to go next

You have modelled a schema, migrated it, and read, written, and tested a typed data layer. From here:

Querying for the full standalone query builder, joins, and set operations.
Dynamic tables for per-tenant and time-partitioned tables.
Recipes for production patterns like pagination, soft deletes, and multi-tenancy.