Testing APIs and Databases

Analogy categoryAnalogies

Testing an API at the HTTP Boundary

API tests treat your service as a black box reached over HTTP: they send a real request — method, path, headers, body — to a running instance of the application and assert on the response status code, headers, and body shape, without knowing or caring how the handler is implemented internally. This makes API tests resilient to internal refactors; as long as the contract (routes, status codes, payload shape) stays the same, the test keeps passing even if you rewrite the handler from scratch. Tools like Supertest (Node), RestAssured (Java), or httpx-based clients (Python) let you drive these requests programmatically and chain assertions on the JSON response.

🏏

Cricket analogy: An umpire judges a delivery purely by what crosses the crease and hits the stumps — not by the bowler's internal grip or run-up mechanics — the same way an API test judges only the HTTP response, not the handler's internal implementation.

python

# API test using httpx against a running FastAPI instance
import httpx
import pytest

BASE_URL = "http://localhost:8000"

def test_create_and_fetch_user():
    with httpx.Client(base_url=BASE_URL) as client:
        create_res = client.post(
            "/users",
            json={"email": "grace@example.com", "name": "Grace Hopper"},
        )
        assert create_res.status_code == 201
        user_id = create_res.json()["id"]

        get_res = client.get(f"/users/{user_id}")
        assert get_res.status_code == 200
        body = get_res.json()
        assert body["email"] == "grace@example.com"
        assert "createdAt" in body

        # negative path: duplicate email should be rejected
        dup_res = client.post(
            "/users",
            json={"email": "grace@example.com", "name": "Grace H."},
        )
        assert dup_res.status_code == 409

Validating Response Schemas, Not Just Values

Asserting on individual fields catches obvious regressions, but it's easy to miss a subtler bug: an extra field accidentally leaking sensitive data, or a field silently changing type from a number to a string. Schema validation — checking the response against a JSON Schema or OpenAPI definition — catches both. Many teams generate the schema from the same OpenAPI spec that documents the API, so the test suite and the documentation can never drift apart; if a developer adds a field to the response without updating the spec, the schema-validating test fails immediately rather than the mismatch surfacing weeks later in a client integration.

🏏

Cricket analogy: A team's kit inspector doesn't just check that players are wearing pads, but verifies the exact regulation size and material against the ICC equipment spec, the same way schema validation checks not just that a field exists but that its type and shape match the documented contract.

Generating your test schema from the same OpenAPI/Swagger spec used for documentation means the spec, the tests, and the real API can never silently drift apart — any breaking change fails a test immediately.

Testing the Database Layer Directly

While API tests exercise the whole HTTP stack, database-layer tests target the repository or data-access code directly against a real database engine, skipping the HTTP layer entirely. This is where you verify things an API test would be too indirect to catch cleanly: that a unique constraint actually rejects a duplicate, that a foreign key cascade deletes child rows correctly, that a complex query with joins and aggregates returns the right rows for edge cases like empty result sets or NULL values. Running these against the same database engine used in production (not an in-memory substitute with different SQL dialect quirks) is essential — SQLite's behavior around foreign keys and date handling, for instance, differs meaningfully from Postgres.

🏏

Cricket analogy: A groundsman tests pitch behavior on the actual stadium surface, not a generic practice net surface, because grip and bounce differ meaningfully between them, just as database tests should run against the real production database engine, not a different in-memory substitute.

Substituting SQLite for Postgres 'because it's faster in tests' is a common trap: foreign key enforcement, JSON column behavior, and date/timestamp handling differ enough between engines that tests can pass against SQLite and still fail in production against real Postgres constraints.

API tests treat the service as a black box over HTTP, asserting on status code, headers, and body without knowing internal implementation details.
This black-box approach makes API tests resilient to internal refactors as long as the HTTP contract stays stable.
Schema validation against an OpenAPI or JSON Schema definition catches type and structural regressions that value-only assertions miss.
Generating the test schema from the same spec used for documentation keeps tests and docs from silently drifting apart.
Database-layer tests skip HTTP and target the repository/data-access code directly against a real database engine.
Always test against the same database engine used in production; substitutes like SQLite have meaningfully different SQL dialect behavior.
Database tests are the right place to verify constraints, cascades, and complex query edge cases that API tests are too indirect to isolate cleanly.