A write lands in the database

201 Created is the response rendering. SELECT the row is the committed truth.

The situation

The API answers 201 Created, but did the project reach the store in the state the response promised? The response body is not the row. It is how the API describes the row.

The test creates the project over REST and then reads the committed row itself, through a connection the suite owns to the same database the application writes to. The sample suite runs this journey in DomainAccessJourney.cs.

The test

The test writes over REST and reads the row with the sample suite's own SQL:

DomainAccessJourney.cs3 notes

1[Application(NorthstarTargets.Api)]
2[NorthstarMember(PlanIds.Growth)]
3public sealed class DomainAccessJourney
4{
5[ProtoTest]
6[SignedInAs]
7[RequiresCapability(ProtoCapabilityKinds.Store, Reason = "The suite does not own the store, so it cannot inspect it.")]
8public async Task AProjectCreatedThroughRestIsCommittedToTheDatabase()
9{
10const string projectName = "rest-to-store";
11
12using var created = await Proto.Context.Rest()
13.Body(new CreateProjectRequest(projectName))
14.PostAsync("/api/v1/projects");
15var project = created
16.Should.HaveHttpStatus(HttpStatusCode.Created)1
17.ReadRequired<ProjectResponse>();
18
19await using var command = Proto.Context.SqlConnection().CreateCommand();
20command.CommandText = """
21SELECT "Id", "Name", "Status"
22FROM "Projects"
23WHERE "Id" = @id
24""";
25var id = command.CreateParameter();
26id.ParameterName = "@id";
27id.Value = project.Id;
28command.Parameters.Add(id);
29
30await using var stored = await command.ExecuteReaderAsync();
31Assert.That(await stored.ReadAsync(), Is.True, "The REST write did not create a project row.");2
32using (Assert.EnterMultipleScope())
33{
34Assert.That(stored.GetString(0), Is.EqualTo(project.Id));3
35Assert.That(stored.GetString(1), Is.EqualTo(projectName));
36Assert.That(stored.GetString(2), Is.EqualTo(ProjectStatuses.Active));
37}
38}
39}

1. 1
   The write landed

   HaveHttpStatus checks the response. ReadRequired reads the created project for its id.

2. 2
   The row exists

   ReadAsync fails with the message the test wrote when the REST write created no row.

3. 3
   The row matches

   One scope checks id, name and status together, so a mismatch lists every column that differs.

Proto.Context.SqlConnection() returns the test connection to the same store the application writes to. See SQL for the accessors and the isolation rules.

What the test proves	Where it lands
POST /api/v1/projects returns 201 Created	The response body names rest-to-store
SELECT finds the row	Id, Name = rest-to-store, Status = active
The row is committed, not staged	The read runs outside any transaction

Compose

The run owns the store. The sample suite uses a SQLite file that every run recreates, and switches to a PostgreSQL container when the environment asks for it:

[default] SQLite file, recreated per run | [ProtoTest__Database=postgres] container, skipped if configured

// Setup.cs: PostgreSQL when the run asks for it; a configured key skips the container.
if (run.OwnsPostgres)
{
    builder.AddInfrastructure(
        "NorthstarDatabase",
        chain => chain
            .UseConfigured()
            .UseContainer(PostgresDatabase.Container()),
        "ConnectionStrings:Northstar");
}

The test side is an ordinary SQL connection over the same address. The application commits on its own connection, so the test connection must not wrap its reads in a transaction:

// Setup.cs: the suite's connection to the store the application also uses.
builder
    .AddSql(
        provider => CreateDatabaseConnection(
            ResolveDatabase(provider, run.DatabaseConnection ?? string.Empty),
            run.UsesPostgres),
        sql => sql.Isolation = SqlIsolation.None)
    .ConfigureServices(services =>
        services.AddNorthstarDomain(
            (provider, options) =>
            {
                var connection = provider.GetRequiredService<DbConnection>();
                if (run.UsesPostgres)
                {
                    options.UseNpgsql(connection);
                }
                else
                {
                    options.UseSqlite(connection);
                }
            },
            ServiceLifetime.Scoped));

What the trace shows

REST write → committed rowPOST /api/v1/projects · SELECT the rowOpen in the viewer ↗

The trace reads in test order: the REST http.request for the write with assert.http.status, then the sql.connection.open operation from setup released at teardown. The SELECT itself is not traced. Individual commands are not traced; the connection lifecycle is, and the assertion proves the row. When the row is missing, the assertion fails with the message the test wrote. The long-form reading is on ProtoTrace.

Variations

When	Option	What changes
The test reads through Entity Framework Core	AddEntityFrameworkCore<TContext>	The context joins the test connection. The default Transaction isolation enlists the read and records sql.enlist. None skips the transaction. See Entity Framework Core.
The run should use a real PostgreSQL	Set ProtoTest__Database=postgres	The run starts the container. A configured connection string skips it.
The application should share the test transaction	Declare ShareConnectionWith(...)	The test's transaction can cover both sides, and a rollback at teardown undoes the application's write too. See Isolation.
The test should read with the application's mapping	AddEntityFrameworkCore<NorthstarDbContext>	The test reads with the same mapping the application wrote with, instead of hand-written SQL.

What it does not prove

• The test reads outside any transaction. The in-process application opens its own connection and commits; a rollback on the test's connection would not undo that write. With the default Transaction isolation the run fails at startup unless the application declares ShareConnectionWith(...). That declaration is a statement, not a check.
• Unique values, not cleanup. A container lives for one run and the SQLite file is recreated, so nothing survives to the next run. Within a run, a reference built from TestId keeps parallel tests out of each other's rows.
• No SQL tracing. The connection and transaction lifecycle is traced; individual commands are not.
• In a deployed environment the suite often cannot reach the database. Compose AddSql only where it can, and gate the test with [RequiresCapability(ProtoCapabilityKinds.Store)]: where no store is composed, it skips instead of failing.