Repository Contract at the SQL Line
Integration tests lock the repository contract at the SQL line: mapping error 1062 to a Go sentinel, soft-delete visibility, and pagination clamps on MySQL.
TL;DR
Binding a time function as a string parameter silently failed in MySQL because parameter markers only accept data values, not SQL expressions, so the fix splits inserts into two branches calling the function inline. Integration tests now lock the repository contract, mapping driver error 1062 to ErrSlugTaken via errors.Is. repository_lifecycle_test.go covers visibility, soft deletes, sorting, and pagination clamps.
While writing a seed helper for the announcement repository in KotaPortal, inserting an already-deleted row failed silently. The helper tried to bind the current-time value as a string parameter on the deleted_at column. The query ran without a database error, but the row was not deleted as expected. The column held the raw string instead of a timestamp.
The initial assumption: the database engine would evaluate that string as a time function at execution. The approach relied on parameter binding to carry a SQL expression into a prepared statement.
The official MySQL prepared statement documentation states the opposite. Parameter markers only mark positions of data values, "not for SQL keywords, identifiers, and so forth" [3]. Binding a SQL expression as a string was the root of the failure. The fix required two separate SQL branches: one for inserting an active row, one for a deleted row that calls the time function directly inside the query rather than as a bound parameter.
This fix changed how the project views its data access layer. The repository contract no longer lives as an assumption in a developer's head; it is locked inside the integration test suites. The test file becomes a specification the machine can execute automatically.
Mapping Errors at the SQL Boundary
The integration tests run through testutil.SetupTestDB on a real MySQL instance, not a mock. When a slug uniqueness violation happens, the MySQL driver returns ER_DUP_ENTRY, error code 1062, SQLSTATE 23000, with the message "Duplicate entry '%s' for key %d" [4]. The repository layer translates this raw driver error into the ErrSlugTaken sentinel that the application layer above can read.
On the Go side, error comparison uses errors.Is, not the == operator. The official Go 1.13 guide describes this pattern for checking errors that have been wrapped with context [1]. Repository layers often wrap driver errors with extra information, and errors.Is still recognizes the sentinel behind those wrappers.
Lifecycles Locked by Tests
The data visibility specification is locked in a file named repository_lifecycle_test.go. The default list hides rows whose deletion column is set. A dedicated trash view keeps them visible for administration. A second delete on the same entry returns ErrNotFound. Admin list sorting has a column whitelist with a newest-first fallback when the requested column is unknown. Pagination applies a clamp: pages below one and out-of-range limits are corrected to safe defaults, preventing excessive query load from invalid requests.
The Go testing package sets the contract: T.Error and related methods signal failure [2]. One assertion in this suite originally depended on fragile execution order. A follow-up fix commit added t.Fatalf before dereferencing slice elements, preventing a panic on an empty slice. The scaffolding function also got t.Helper so failure reports point at the caller line instead of the inside of the helper.
One last observation from the fix commit: the contract most often violated is the simplest one. Duplicate slugs have been handled by a UNIQUE constraint since day one, yet without a test mapping error 1062 to the right sentinel, every caller was free to guess what the raw error meant. The integration test ends that guessing.