Wiring a managed Postgres database into a Python app (deploymill)

You are an agent updating a deploymill-managed Python app that has just had a managed Postgres database provisioned. DATABASE_URL is already set in the app's environment (pooled connection string, SSL required) and will be present in the container at boot.

Follow these steps in order. Each step is a concrete file edit or command, not advice — apply them literally.

1. Add the Postgres client + migrations tool

Add to dependencies in pyproject.toml:

"psycopg[binary,pool]>=3.2",
"alembic>=1.13",

psycopg[binary] ships a pre-built libpq so the Docker image doesn't need build tooling. psycopg[pool] provides the connection pool used in step 2.

2. Create a single pool module

Create app/db.py:

import os
from psycopg_pool import ConnectionPool

if not os.environ.get("DATABASE_URL"):
    raise RuntimeError(
        "DATABASE_URL is not set. Add `database: { provider: \"neon\" }` "
        "to .deploymill/project.json and run reconcile_project."
    )

pool = ConnectionPool(
    conninfo=os.environ["DATABASE_URL"],
    min_size=1,
    max_size=10,
    open=True,
)


def query(sql: str, params: tuple = ()) -> list[dict]:
    with pool.connection() as conn, conn.cursor() as cur:
        cur.execute(sql, params)
        if cur.description is None:
            return []
        cols = [d.name for d in cur.description]
        return [dict(zip(cols, row)) for row in cur.fetchall()]

Important: create the pool once per process, at module scope. Do not create a new pool per request — that exhausts the database's connection limit and defeats the pooler. Import pool or query from this module everywhere.

3. Add a health route that proves the DB is reachable

In app/main.py, add:

from .db import query


@app.get("/db")
def db_health() -> dict:
    try:
        rows = query("SELECT 1 AS ok")
        return {"ok": True, "result": rows[0]}
    except Exception as e:
        return {"ok": False, "error": str(e)}

Hit /db after deploy to confirm the connection works.

4. Migrations: use Alembic

Initialize Alembic in the repo root:

alembic init migrations

This creates alembic.ini and a migrations/ directory. Edit alembic.ini and delete the sqlalchemy.url = … line — we read the URL from the environment instead.

Edit migrations/env.py. Near the top, add:

import os
config.set_main_option("sqlalchemy.url", os.environ["DATABASE_URL"])

Create your first migration:

alembic revision -m "init"

That writes migrations/versions/<id>_init.py. Edit it — example:

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.create_table(
        "users",
        sa.Column("id", sa.BigInteger, primary_key=True),
        sa.Column("email", sa.Text, nullable=False, unique=True),
        sa.Column("created_at", sa.TIMESTAMP(timezone=True), nullable=False, server_default=sa.func.now()),
    )


def downgrade():
    op.drop_table("users")

5. Run migrations on deploy

Migrations must run before the app starts handling traffic on each deploy. Update the Dockerfile's CMD (or add an entrypoint) so it runs alembic upgrade head before uvicorn:

CMD ["sh", "-c", "alembic upgrade head && uvicorn app.main:app --host 0.0.0.0 --port 8000"]

Alembic is idempotent — it skips revisions already in the alembic_version table — so this is safe to run on every container start.

If migrations fail, the container exits and the platform marks the deploy as failed. That's intentional: an app with a half-applied schema should not serve traffic.

6. Commit and redeploy

Use push_files to commit pyproject.toml, app/db.py, app/main.py, alembic.ini, the new migrations/ directory, and the Dockerfile. Then call deploy on the application.

What NOT to do

• Don't read DATABASE_URL inside a request handler — read it once at module load.
• Don't reach for SQLAlchemy ORM unless the user asks. Plain psycopg + Alembic is enough for most scaffolded apps and keeps the surface area small. (Alembic uses SQLAlchemy Core for migration ops, which is fine — that's different from using the ORM in app code.)
• Don't run alembic downgrade automatically. Rollbacks are destructive; leave them as a manual operator decision.
• Don't write migrations that depend on prod data shape. By default create_preview forks a Neon branch (copy-on-write from the parent's data) and points the preview at it, so a preview's migration won't touch prod rows — but if previews.shareDatabase: true is set in the parent's .deploymill/project.json, or NEON_API_KEY isn't configured, the preview shares the parent's DATABASE_URL and a buggy migration hits prod directly. See deploymill://guides/previews for the matrix.
• Don't disable TLS. The managed database requires it; psycopg honors the sslmode=require already present in DATABASE_URL.

If something breaks

• Connection refused / DNS errors → DATABASE_URL is missing or malformed. Check list_env_vars on the app.
• password authentication failed → the database role was rotated. Clear DATABASE_URL via delete_env_vars and re-run reconcile_project to mint a fresh one.
• too many connections → multiple pools were created. There must be exactly one ConnectionPool(...) call in the entire process.
• alembic.util.exc.CommandError: Can't locate revision → migrations/versions/ was not committed. Verify with list_files.