Wiring a managed Postgres database into a Python app

You are an agent updating a DeployMill-managed Python app that has just had a managed Postgres database provisioned.

Precondition: the database is already provisioned, so DATABASE_URL is set in the app's environment and present in the container at boot. Done looks like: a /db health route returns { ok: true } after deploy, and migrations run automatically on each container start.

DATABASE_URL is a direct postgresql://user:pass@host/db connection to deploymill's managed Postgres — the only selectable managed-database backend. psycopg reads the connection string with libpq, so there is no SSL flag to set: whatever the URL says is what you get.

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: { engine: \"postgres\", provider: \"deploymill\" }` "
        "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.

Free Explore orgs can't edit the build recipe. A managed database is available on the free tier, but a push_files that includes a Dockerfile or .dockerignore is refused with build_recipe_locked (the whole push, before anything commits). If you hit that, run alembic upgrade head from app code at startup instead (e.g. a FastAPI lifespan/startup hook) and push only app source.

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, and 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 gives the preview its own isolated database (a dump/restore copy of the parent's), so a preview's migration won't touch prod rows. The preview shares the parent's DATABASE_URL only if previews.shareDatabase: true is set in the parent's .deploymill/project.json, or if the managed-database backend isn't configured on the server — in which case a buggy migration hits prod directly. create_preview reports which path it took via database.action and the top-level hitsProdData flag. See deploymill://guides/previews for the matrix.
• Don't override the connection string's TLS mode. psycopg honors whatever sslmode is in DATABASE_URL; the managed database is a direct in-cluster connection without TLS. Leave it to libpq.

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 the binding with delete_secret({ scope: "app", applicationId, name: "DATABASE_URL" }), then re-run reconcile_project to mint a fresh one. Use delete_secret, not delete_env_vars: the managed DATABASE_URL is stored as an app-scoped secret (masked in list_env_vars, injected at deploy time), so delete_env_vars reports it under missing and silently changes nothing. delete_secret also strips any legacy plaintext copy left in the env blob.
• 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.