flat-test-persistence
Use when testing a flat-layered service's data-access package against the real datastore — its tables and the repository class that owns its transactions — pinning the generated constraint name where
SKILL.mdplugins/pyhouse-flat/skills/flat-test-persistence/SKILL.md
GitHubFlat Test — Data-Access Contract
Consult test-principles for the testing constitution. Where this skill contradicts test-principles,
the constitution wins.
One integration-test file per repository class, under the distribution's own tests/integration/,
driven against the real datastore from flat-test-integration-setup. This is the only level that
can catch what the data-access package exists to guarantee: that a conflict updates the columns it
claims to, that a read returns what the store holds, and that a driver error arrives as the service's own
exception.
Where several distributions share one data-access library, the files sit with that library's own tests
instead — myschema/tests/integration/ — and nothing else changes.
The isolation fixture follows the declared transaction owner (test-principles reliability
rule 2): connection for every assertion query and for a callable that accepts a connection; the engine
fixture handed to the repository class's constructor, with truncate_all cleaning up, for one that
opens its own — asserting through a fresh connection only where its own rollback is under test
(rule 10).
When to use vs. neighbours
- A pure function in the data-access package — a connection-string builder, the row mapper → not this skill; it is a unit test with no fixtures at all.
- The container, migration and isolation fixtures themselves →
flat-test-integration-setup; this skill consumesconnection,engineandtruncate_alland lays none of its own. - Writing the tables, repository class and migrations under test →
flat-persistence. - The work a trigger calls reaching this write path as part of a run →
flat-test-entrypoint; this skill tests the write path, that one tests the wiring above it. - The client feeding these rows →
flat-test-service-client. - A static "no package outside the data-access package constructs a statement" rule →
test-architecture-rule. - The shared groundwork — fixture placement, assertion strength, reliability →
test-principles. - The adapter sits behind a domain repository protocol →
hex-test-repository-contract, in thepyhouse-hexplugin.
Template — the repository class's contract (pytest, SQLAlchemy async over Postgres)
tests/integration/test_foo_repository.py:
from uuid import UUID
import pytest
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncConnection, AsyncEngine
from myapp.exceptions import StorageWriteRejectedError
from myapp.postgres import FooRepository
from myapp.postgres.foo_table import foo_table
from myapp.schemas import Foo, FooExternalId
def _a_foo(external_id: str = "alpha", name: str = "first") -> Foo:
return Foo(external_id=FooExternalId(external_id), name=name)
# only where a write updates on conflict
async def test_a_second_write_of_one_external_id_updates_the_set_and_keeps_the_rest(
engine: AsyncEngine,
connection: AsyncConnection,
) -> None:
repository = FooRepository(engine)
await repository.upsert(_a_foo(name="first"))
first_id: UUID = (await connection.execute(select(foo_table.c.id))).scalar_one()
await repository.upsert(_a_foo(name="second"))
rows = (await connection.execute(select(foo_table.c.id, foo_table.c.name))).all()
assert [tuple(row) for row in rows] == [(first_id, "second")]
async def test_a_value_the_store_refuses_arrives_as_the_catalogue_error(engine: AsyncEngine) -> None:
with pytest.raises(StorageWriteRejectedError) as exc_info:
await FooRepository(engine).upsert(_a_foo(name="nul\x00"))
assert exc_info.value.context == {"sqlstate": "22021", "constraint": None}
The tests drive the class a caller uses and assert through connection, a query against the store.
The first test pins the declared update set from both sides in one comparison: one row, not two; the name the set covers changed; the id it does not cover kept the value the first write minted.
The translation test forces the write through the public method a caller uses, refused by the store
itself — a NUL character, which a Postgres text value cannot hold, is SQLSTATE 22021 in the
data-exception class — so no constraint had to be invented for the test.
Where a write resolves a conflict by doing nothing, the file gains the no-op test of rule 5; where it spans statements, the atomicity test of rule 10; where a method takes a batch, the two tests of rule 6; where writes for one key can arrive out of order, the test of rule 13; and a test of what each read returns, rule 14, wherever the class reads.
Other bindings
- The ORM in place of Core. Mapped classes replace the table objects and the session replaces the connection; every rule below is unchanged, because each pins a property of the stored data rather than of the statement that wrote it. Assertions still read the store back (rule 1) — through a fresh query, not through the session's identity map, which can hand back the object just written without it ever having reached the datastore.
- Another engine or dialect. The upsert spelling, the generated constraint names and the driver errors a violation raises change together; the contract questions — idempotence, the update set from both sides, the translated exception — do not. Where the dialect has no single-statement upsert, the repository's contract is still the subject and the test pins whatever its write does instead.
- How the datastore is provisioned — a disposable container, a pre-provisioned throwaway database, an
in-process engine. That choice belongs to
flat-test-integration-setup, which names its trade-offs; nothing in these files changes with it except that an in-process engine of a different dialect invalidates rules 2 and 3.
Rules
- This level runs against the real store, substitutes nothing below the repository class, and
asserts through a query against the store —
test-principles, Datastore contract rule 1. - Where the translator branches on a constraint's name, pin that name —
test-principles, Datastore contract rule 2. - A unique constraint the translator branches on is tested on the plain insert and, where the
class has one, on the conflict-resolution path —
test-principles, Datastore contract rule 3. - Where a write updates on conflict, test its declared update set from both sides. The second write must change what the set names and leave every column outside it alone — that is the whole contract of an upsert, and both are pinned, whether in one comparison or two.
- An empty update set means "do nothing on conflict". Where a write declares one, test it as a no-op that raises nothing and changes nothing; a write that meets a key already recorded and must leave it alone relies on exactly that.
- Where a method takes a batch, it carries the two tests
test-principles, Datastore contract rule 7, states — a chunk edge crossed and two inputs sharing one key in one call. - A forced driver error asserts the catalogue exception the data-access package produces, on each
write and read the class has —
test-principles, Datastore contract rule 4. A read with no input the store can refuse takes reliability rule 6's one exception. - Where the store assigns a timestamp, assert it as
test-principlesreliability rule 4 states. - Ordering is asserted only where the query guarantees it. Add an explicit order clause to any query whose result is compared to a list; a relational store promises no insertion order, and a test that passes on two rows fails on two hundred.
- Where one write spans statements, it has an atomicity test that forces its last statement to fail
through a constraint the schema declares, expects the narrowest catalogue exception that constraint
produces, and asserts through a fresh connection that the earlier statements left nothing behind —
never through
connection, whose own transaction cannot observe another connection's rollback. Without it a refactor splitting the statements into two transactions passes every test. - Where a run walks a table in pages, a test crosses a page edge where the ordering column ties.
- A test arranges every row it asserts on as
test-principlesDatastore contract rule 5 states — the service's declared type written through the repository class, or through the schema's own table object where the class has no write for it — and, where the subject opens its own connection, committed before it runs (flat-test-integration-setup). - Where writes for one key can arrive out of order, the test
test-principles, Datastore contract rule 6, states; the update-set test's second write then carries the newer stamp, so it holds with the guard. - Each read the class has is tested on what it returns —
test-principles, Datastore contract rule 9 — its rows arranged as rule 12 states.
Hard stops
- The constraint under test does not exist in a migration yet → stop, add the revision first
(
flat-persistence); a test asserting a constraint the schema never had passes for the wrong reason. - The adapter sits behind a domain repository protocol → stop, use
hex-test-repository-contract, in thepyhouse-hexplugin.