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.

Its exact shape depends on which backend you provisioned. psycopg reads the connection string with libpq, so it honors whichever applies automatically, no SSL flag to set:

• postgres: internal Postgres, the house default (no external account), a direct postgresql://user:pass@host/db connection.
• Neon and Supabase: pooled connections that require TLS (their URI carries sslmode=require).

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

SQLite (currently disabled as a selectable backend)

This playbook is for Postgres (postgres / neon / supabase), the selectable managed-database backends today. A file-on-volume sqlite engine is implemented behind the database seam but is currently disabled as a selectable backend, so you can't provision it. The notes below describe how it behaves for when it is re-enabled: DATABASE_URL would be a file: URL (e.g. file:/data/sqlite/<org>/<app>.db), not a Postgres connection string, so skip the psycopg steps below and use Python's built-in sqlite3 module (strip the file: scheme to get the path, or open it as a URI with sqlite3.connect(path, uri=True)). Open the connection once and reuse it (SQLite is in-process). Before you commit to SQLite, read these two non-negotiables:

• Runtime envelope: single-writer, in-process, file-on-volume. SQLite is one file opened by one process. There is no server and no network. It's a great fit for low-concurrency apps (a small internal tool, a worker, a low-traffic site), where the simplicity is a real win. It is the wrong choice for a multi-instance web app: you cannot run two app containers against one SQLite file safely (concurrent writers corrupt it), so SQLite caps you at a single replica. The .db file must live on a persistent volume (the server's SQLITE_DATA_DIR). On ephemeral container storage it's wiped on every redeploy. Heavy write concurrency will also serialize on SQLite's single write lock.
• SQLite → Postgres is a one-way door: a rewrite, not a data move. If you outgrow SQLite, switching to Postgres later is not a "change the provider and migrate the data" operation. The SQL dialect and type semantics differ (SQLite's dynamic typing, no native boolean/timestamptz/numeric the same way, different AUTOINCREMENT/sequence behavior, looser constraint enforcement). Tools like pgloader can move the bytes, but the schema semantics and your app's queries/migrations are on you to port. Choose SQLite only when you're confident the app stays within its envelope, or accept that growing out of it is real migration work. Previews still get an isolated copy of the parent .db (so destructive preview migrations stay off prod), exactly like the Postgres backends.

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.

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 Neon branch (copy-on-write) or an internal-Postgres dump/restore copy depending on the provider, 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, for Neon specifically, if NEON_API_KEY isn't configured), in which case a buggy migration hits prod directly. See deploymill://guides/previews for the matrix.
• Don't override the connection string's TLS mode. psycopg honors whatever sslmode is in DATABASE_URL: sslmode=require for pooled Neon/Supabase, none for the direct internal-Postgres house default. 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 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.