PostgreSQL

This page documents exactly what the compiler emits when a spec is compiled against the python-fastapi-postgres target. It is the concrete companion to the target-agnostic Code Generation Pipeline research doc and is intended for readers choosing between compiler targets rather than for consumers of an already-generated service (that audience is served by the emitted project's own README.md).

The canonical source of truth is the emitter at modules/codegen/src/main/scala/specrest/codegen/python/EmitPython.scala (dispatched from Emit.scala by profile language), which renders the Handlebars templates under modules/codegen/src/main/resources/templates/python/fastapi/.

Database dialects. FastAPI also targets SQLite and MySQL through a pluggable Dialect strategy; this page covers the default PostgreSQL dialect. The per-dialect deltas (driver, SQLAlchemy column types, compose wiring) are on the sibling pages: SQLite and MySQL.

Running sbt "cli/run compile --framework fastapi --db postgres --ignore-verify --out /tmp/out fixtures/spec/url_shortener.spec" produces the tree this page describes (--ignore-verify is needed because url_shortener.spec exercises translator-coverage-gap checks that the verify-as-gate treats as a block, see below). If this page and the emitted output disagree, the emitter wins, file an issue or PR to correct the doc.

compile runs the verification engine as a pre-codegen gate: a spec with any failing or skipped check causes compile to exit non-zero and write no files. Pass --ignore-verify to opt out (with a warning). See Verify-as-gate in compile for the full contract.

At a glance

Profile definition: modules/profile/src/main/scala/specrest/profile/Targets.scala.

File tree

The compiler emits a complete, runnable project. For a single-entity spec like fixtures/spec/url_shortener.spec the output is:

tests/test_admin.py (admin router), tests/test_behavioral_*.py, tests/test_stateful_*.py, tests/test_structural_*.py, plus tests/conftest.py, tests/predicates.py, tests/strategies.py, tests/strategies_user.py, tests/redaction.py, tests/run_conformance.py, and tests/_testgen_skips.json are emitted by default (suppress with --no-tests). See Test Generation.

Multi-entity specs (e.g. fixtures/spec/ecommerce.spec) produce one parallel app/models/<entity>.py, app/schemas/<entity>.py, app/routers/<entities>.py, and app/services/<entity>.py per entity. The infrastructure layer (Dockerfile, docker-compose.yml, alembic/, CI workflow) is identical regardless of entity count.

Template sources: modules/codegen/src/main/resources/templates/python/fastapi/.

Code layering

The four app/ layers keep a strict one-way dependency that mirrors FastAPI's idiomatic split between the API boundary and persistence: routers depend on services, and services depend on models and schemas. Nothing flows the other way.

• schemas/ (Pydantic v2) is the API boundary. The read DTO sets model_config = ConfigDict(from_attributes=True), so an ORM row becomes its response shape via ReadSchema.model_validate(row) rather than a hand-written mapper.
• models/ (SQLAlchemy 2.0) is pure persistence: Mapped[...] columns and __tablename__, and nothing more. A model never imports a schema and defines no custom __init__; it is built through SQLAlchemy's keyword constructor.
• services/ is the only layer aware of both shapes. A create handler constructs the model with explicit keyword arguments (Model(field=body.field, ...), unwrapping SecretStr inputs via get_secret_value() where present) and reads it back with model_validate.

The result is deliberately free of *_to_* converter functions, **model_dump() splats into the ORM constructor, and dynamic imports. The API-to-DB mapping lives only in the service, where cross-layer knowledge belongs.

Naming conventions

Derived names are locked in modules/profile/src/main/scala/specrest/profile/Targets.scala and the convention engine under modules/convention/. A concrete worked example for the UrlShortener service with its UrlMapping entity and Shorten operation:

Foreign keys are emitted in Alembic DDL only: the generated SQLAlchemy models do not declare ForeignKey(...) on mapped_column today. For example, compile fixtures/spec/ecommerce.spec and inspect the generated app/models/payment.py: order_id: Mapped[int] = mapped_column(Integer), no FK. Relationship constraints live exclusively in alembic/versions/001_initial_schema.py as sa.ForeignKeyConstraint([...], [...]). Two inference paths feed that DDL, both in modules/convention/src/main/scala/specrest/convention/Schema.scala:

• Name-based: a field called <entity_snake>_id (e.g. user_id, order_id) is matched against the entity set; the DDL column type is taken from the referenced entity's id type (INTEGER if the target's id is Int, BIGINT only when it's the synthetic BIGSERIAL).
• Type-based: a field whose declared type is an entity (e.g. owner: User) produces a <field>_id BIGINT column with a FK constraint. This path is currently hardcoded to BIGINT regardless of the target entity's id type (mapTypeToColumn in modules/convention/src/main/scala/specrest/convention/Schema.scala).

Any of these mappings can be overridden per-entity or per-operation via a service-level conventions { Target.property = value } block; the conventions system is documented in Convention Engine and the grammar rule is conventionBlock in modules/parser/src/main/antlr4/Spec.g4.

HTTP contract

Status codes are chosen by resolveStatus in modules/convention/src/main/scala/specrest/convention/Path.scala, with classification by modules/codegen/src/main/scala/specrest/codegen/RouteKind.scala:

The error response shape is uniform across endpoints. From a real generated openapi.yaml:

ErrorResponse:
  type: object
  description: Standard error response body
  required:
    - detail
  properties:
    detail:
      type: string
      description: Human-readable error description

Pagination is emitted for list endpoints (since #464): they accept limit (default 50, clamped to 1..100) and offset (default 0) query parameters and return a flat, bounded JSON array of <Entity>Read objects (no envelope, backward-compatible). Clamping is centralized in an app/pagination.py helper. Cursor-based pagination remains a future milestone.

Live OpenAPI reference

curl -X POST "https://example.com/shorten" \  -H "Content-Type: application/json" \  -d '{    "code": "string",    "url": "string",    "created_at": "2019-08-24T14:15:22Z",    "click_count": 0  }'

{  "url": "string",  "code": "string",  "created_at": "2019-08-24T14:15:22Z",  "click_count": 0,  "id": 0}

{  "detail": "string"}

curl -X GET "https://example.com/string"

{  "detail": "string"}

{  "detail": "string"}

curl -X DELETE "https://example.com/string"

{  "detail": "string"}

{  "detail": "string"}

curl -X GET "https://example.com/urls"

[  {    "url": "string",    "code": "string",    "created_at": "2019-08-24T14:15:22Z",    "click_count": 0,    "id": 0  }]

curl -X GET "https://example.com/health"

{  "status": "ok"}

curl -X GET "https://example.com/ready"

{  "status": "ready"}

{  "status": "unavailable"}

curl -X GET "https://example.com/metrics"

"string"

sbt "cli/run compile --framework fastapi --db postgres --ignore-verify --out /tmp/openapi-gen fixtures/spec/url_shortener.spec"
cp /tmp/openapi-gen/openapi.yaml docs/public/openapi/url_shortener.yaml

Infrastructure

Observability routes

Three routes ship outside the spec surface, all tagged infrastructure in the OpenAPI document. GET /health returns 200 unconditionally (liveness; the Dockerfile healthcheck probes it). GET /ready round-trips SELECT 1 through the SQLAlchemy engine under a 5-second timeout and answers 200 {"status": "ready"} or 503 {"status": "unavailable"}, logging the failure reason. GET /metrics serves Prometheus text format via prometheus-client: http_requests_total and http_request_duration_seconds, labelled by method, route template (never the raw URL), and status code, counted in a middleware finally so requests that raise are observed as 500s. The generated tests/test_health.py covers all three.

Tracing is opt-in. With OTEL_EXPORTER_OTLP_ENDPOINT set, the app instruments itself with FastAPIInstrumentor and exports OTLP/HTTP spans named by route template; unset, nothing is wired. OTEL_SERVICE_NAME overrides the default service name.

Docker image

Two-stage build. uv is copied from the upstream image; app runs as non-root appuser; HEALTHCHECK probes /health every 10s.

FROM python:3.13-slim-bookworm AS builder
COPY --from=ghcr.io/astral-sh/uv:0.5.11 /uv /uvx /bin/
WORKDIR /app
ENV UV_COMPILE_BYTECODE=1 UV_LINK_MODE=copy
COPY pyproject.toml ./
RUN --mount=type=cache,target=/root/.cache/uv uv sync --no-dev --no-install-project
COPY . .
RUN --mount=type=cache,target=/root/.cache/uv uv sync --no-dev

FROM python:3.13-slim-bookworm AS runtime
# ... useradd, curl, healthcheck, CMD uvicorn app.main:app

Template: modules/codegen/src/main/resources/templates/python/fastapi/Dockerfile.hbs.

Compose topology

Three services wired through healthchecks so the app only starts after migrations succeed:

services:
  db:         # postgres:17-alpine, healthcheck: pg_isready
  migrations: # build: ., command: alembic upgrade head, depends_on db healthy
  app:        # build: ., ports 8000:8000, depends_on db healthy + migrations completed
volumes:
  db_data:

Builder: modules/codegen/src/main/scala/specrest/codegen/Compose.scala (typed Compose model

• recipes); rendered by ComposeYaml.scala. The .hbs template was removed in M7.9: limits, restart policies, and secret-env keys are now Scala constants in one place.

Multi-environment overlays

Alongside the base docker-compose.yml, three additional files ship with every compile:

• docker-compose.override.yml.example: local-dev overrides. Copy to docker-compose.override.yml (Compose auto-loads it) and edit freely. The .example itself is regenerated on every compile; your copy is never touched.
• docker-compose.staging.yml: staging overlay. restart: on-failure, memory: 256M/cpus: 0.25 on the app, mandatory ${KEY:?…} substitution for every database secret. Scaffolded once; preserved across regeneration.
• docker-compose.prod.yml: production overlay. restart: unless-stopped, memory: 512M/cpus: 0.5 on the app, memory: 1G/cpus: 1.0 on the db, no exposed database port, mandatory ${KEY:?…} for every secret. Scaffolded once; preserved across regeneration.

Apply an overlay with the standard Compose -f layering:

docker compose -f docker-compose.yml -f docker-compose.prod.yml up -d

Every secret listed under # Required for staging / production (no defaults) in .env.example must be exported in the shell or .env before up; Compose fails fast with a clear error if any is missing.

CI workflow

GitHub Actions workflow triggered on push/PR to main plus a nightly schedule: '0 2 * * *'. Two jobs (the second needs: test):

• test: installs uv, sets up Python 3.13, syncs deps, runs ruff check app/, mypy app/, alembic upgrade head, then pytest tests/test_health.py. After unit tests pass it mints a per-run ADMIN_TOKEN (the conformance suite authenticates against the /admin router with it), boots the app in the background, waits up to 30 s for /health, and runs make test-conformance PROFILE=$SPEC_TEST_PROFILE. The profile is exhaustive on scheduled runs and thorough otherwise, selected via ${{ github.event_name == 'schedule' && 'exhaustive' || 'thorough' }}. JUnit XML results from the conformance phases are uploaded as an artifact on every run (if: always()). Postgres is provided by the workflow's services: block (postgres:17-alpine).
• docker: cp .env.example .env, docker compose up -d --build, smoke /health with a 60 s timeout, then docker compose down -v (in if: always()).

Schemathesis is invoked inside the conformance suite (the tests/test_structural_*.py files emitted by default, opt out with --no-tests) rather than directly from the workflow; see Test Generation, Structural tests.

Template: modules/codegen/src/main/resources/templates/python/fastapi/github/workflows/ci.yml.hbs (the leading dot is removed from the resource path since sbt's default HiddenFileFilter excludes dotfiles from copyResources; the emitted file is still .github/workflows/ci.yml).

Environment variables

.env.example is emitted with sensible defaults; app/config.py loads it via pydantic-settings. The file has two sections: the always-required vars, then a commented-out "required for staging / production" block listing every secret the prod overlay's ${KEY:?…} substitutions expect:

DATABASE_URL=postgresql+asyncpg://url_shortener:url_shortener@db:5432/url_shortener
BASE_URL=http://localhost:8000
LOG_LEVEL=info

# ─── Required for staging / production (no defaults) ────────────────────────
# docker-compose.prod.yml fails fast (`${KEY:?…}`) if any of these is unset.
# POSTGRES_USER=
# POSTGRES_PASSWORD=
# POSTGRES_DB=

The prod-required block is omitted for SQLite (no DB service, no secrets to externalise). Builder: modules/codegen/src/main/scala/specrest/codegen/EnvExample.scala.

Developer ergonomics

A Makefile exposes install, run, test, lint, typecheck, migrate-up (= alembic upgrade head), migrate-down (= alembic downgrade -1), migrate (alias for migrate-up), docker-up, docker-down, and clean. Run make help in a generated project for the full list.

Extension points

Regeneration overwrites every file in the emitted tree except a small set of user-owned files that the compiler scaffolds once and never touches again. The supported, lossless ways to influence the output are:

• app/extensions/__init__.py: scaffolded on first compile, preserved on every subsequent compile. The generated app/main.py calls register(app) from this module once, before mounting any spec-derived router, so middleware added here wraps every generated endpoint and routes declared here take precedence on path collisions. Use it for custom endpoints, lifecycle hooks, or middleware that should live next to the generated code but survive regeneration:

  from fastapi import APIRouter, FastAPI
  
  custom = APIRouter(prefix="/custom", tags=["custom"])
  
  @custom.get("/ping")
  async def ping() -> dict[str, str]:
      return {"status": "ok"}
  
  def register(app: FastAPI) -> None:
      app.include_router(custom)

  spec-to-rest compile --dry-run reports this file as preserve on every run after the first, distinct from create / update / unchanged.

• Convention overrides are declared inside a service-level conventions { ... } block. Each rule is Target.property = value, with an optional quoted qualifier between the property and = for properties like http_header that address a named slot: Target.http_header "Name" = expr. Example from fixtures/spec/url_shortener.spec:

  conventions {
    Shorten.http_method = "POST"
    Shorten.http_path = "/shorten"
    Shorten.http_status_success = 201
    Resolve.http_status_success = 302
    Resolve.http_header "Location" = output.url
    Delete.http_method = "DELETE"
  }

  These live inside the spec and survive regeneration. Grammar rule: conventionRule: UPPER_IDENT DOT lowerIdent STRING_LIT? EQ expr in modules/parser/src/main/antlr4/Spec.g4. See Convention Engine for the full property list.

• Profile selection switches targets (python-fastapi-sqlite, go-chi-postgres, ts-express-mysql, …) without editing emitted code.

Manual edits to any file other than app/extensions/__init__.py will be lost on the next compile; the generated README.md warns consumers of this. In-file protected-region markers (fences that splice user code into otherwise-generated bodies) are intentionally not provided. Sidecar extension files are the supported mechanism.

CI gate

.github/workflows/python-build.yml re-runs compile --framework fastapi --db <dialect> on every PR that touches the Python templates, profile, emitter, or the shared migration renderer, then runs uv sync and an Alembic round-trip (upgrade head → downgrade base → upgrade head) against a real database, a matrix over postgres, sqlite, and mysql (postgres:17 / mysql:8.4 service containers; SQLite file-backed), so the emitted alembic/versions/*.py is proven to apply and reverse on every supported dialect, not just that it imports.

Limitations

What this target does not generate today, with tracking issues:

• Synthesised operation bodies for non-CRUD operations: transitions, side-effects, and redirects whose body shape doesn't match the entity create schema emit raise NotImplementedError(...) stubs by default (see modules/codegen/src/main/resources/templates/python/fastapi/services/entity.py.hbs). Run synth verify for each LLM_SYNTHESIS-classified operation to populate .spec-to-rest/synth-cache/verified/, then re-run with compile --with-synthesis: the verified Dafny bodies are translated to Python via dafny translate py, laid under app/dafny_kernel/, and the matching handlers call into the kernel via the boundary helpers in app/services/_dafny_adapter.py. The full chain: M6.1 (#31) classification, M6.2 (#32) Dafny signature generation, M6.3 (#28) LLM integration, M6.4 (#29) the CEGIS feedback loop, and M6.5 (#27) Dafny $\to$ Python compilation, and M6.6 (#30) the graduated fallback (prompt-strategy ladder + model escalation + skeleton emit + synthesis report) all shipped. Today, when no verified/ cache entry exists for an op, compile --with-synthesis exits 1 and points at synth verify; with the new opt-in --allow-skeletons flag, the compiler instead consults synth-cache/skeletons/ (populated by synth verify --fallback or synth verify-all), emits a warning per op, and ships a project that compiles but halts at runtime inside the unverified handler with a _dafny.HaltException carrying the operation, strategy, model, and reason. L2 (operation decomposition) was closed wontfix-with-pivot (#227), replaced by the M6.7 hint-augmentation path.