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 codegen/python/EmitPython.scala (dispatched from Emit.scala by profile language), which renders the Handlebars templates under codegen/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: 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 appear only with --with-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: codegen/resources/templates/python/fastapi/.

Naming conventions

Derived names are locked in 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 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 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 parser/Spec.g4.

HTTP contract

Status codes are chosen by resolveStatus in convention/Path.scala, with classification by 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 not yet emitted. List endpoints currently return a flat JSON array of <Entity>Read objects. Cursor- and offset-based pagination are designed in research/02, Convention Engine but tracked as a future milestone.

Live OpenAPI reference

curl -X POST "http://localhost:8000/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 "http://localhost:8000/string"

{
  "detail": "string"
}

{
  "detail": "string"
}

curl -X DELETE "http://localhost:8000/string"

{
  "detail": "string"
}

{
  "detail": "string"
}

curl -X GET "http://localhost:8000/urls"

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

curl -X GET "http://localhost:8000/health"

{
  "status": "ok"
}

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

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: codegen/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:

Template: codegen/resources/templates/python/fastapi/docker-compose.yml.hbs.

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 boots the app in the background with ENABLE_TEST_ADMIN=1 (required by the admin router the conformance suite uses), 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 --with-tests) rather than directly from the workflow; see Test Generation, Structural tests.

Template: codegen/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:

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

Developer ergonomics

A Makefile exposes install, run, test, lint, typecheck, migrate, docker-up, docker-down, and clean. Run make help in a generated project for the full list.

Extension points

Regeneration today overwrites every file in the emitted tree. Manual edits to app/*.py, alembic/*, the infrastructure files, or openapi.yaml will be lost on the next compile; the generated README.md warns consumers of this.

The supported, lossless ways to influence the output are:

• Convention overrides 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 parser/Spec.g4. See Convention Engine for the full property list.

• Profile selection. Eventually switching targets (python-fastapi-sqlite, go-chi-postgres) without editing emitted code.

Protected-region markers and sidecar extension files (so hand-written logic can coexist with regeneration) are planned in M7.8, Protected regions & extension files.

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:

• Authentication / authorization. DSL + runtime wiring tracked across M8.1 (#53), M8.2 (#54), and M8.3 (#55). Generated services are currently open.
• Diff migrations. Only the initial schema (001_initial_schema.py) is emitted. Spec changes require regenerating and hand-writing the migration delta. Tracked in M7.5 (#56).
• Triggers & partial indexes. Tracked in M7.6 (#57).
• Multi-environment compose overrides (staging/prod). M7.9 (#63); one docker-compose.yml targeting local development.
• 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 codegen/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) is the only remaining graduated-fallback level not yet shipped.