---
name: postgres-fastapi
description: "Provisions a Postgres database (a Neon project of its own) for a FastAPI app with `npx extraorbital add postgres` (no signup, no card) and wires it into FastAPI with `psycopg[binary,pool]`. Use when a FastAPI project needs Postgres, PostgreSQL, a SQL database, Neon, POSTGRES_URL, SQLAlchemy or psycopg, or when POSTGRES_URL is missing or unset in .env."
---

# Postgres in FastAPI

<!-- Generated from ExtraOrbital's service registry by `pnpm skills:build` in platform/app. Edit the generator, not this file. -->

ExtraOrbital provisions a Postgres database (a Neon project of its own) in one command and writes the credentials into the project's env file. For anything this recipe does not cover, use the `extraorbital` skill or https://extraorbital.dev/llms.txt.

## 1. Provision

```bash
npx extraorbital add postgres
```

Run it from the project root. It is idempotent (a second run returns the same resource) and writes to `.env.local` if the project has one, otherwise `.env`, keeping that file out of git. Add `--json` to parse the result; credential values are redacted there, and you should never print them either.

| Variable | |
| --- | --- |
| `POSTGRES_URL` | Pooled: use it in the app |
| `POSTGRES_URL_NON_POOLING` | Direct: migrations, and anything that needs a session |
| `POSTGRES_HOST` |  |
| `POSTGRES_USER` |  |
| `POSTGRES_PASSWORD` |  |
| `POSTGRES_DATABASE` |  |

Free on the Prototype plan: 1 database, from the $5.00 account allowance.

## 2. Install the client

```bash
pip install pydantic-settings "psycopg[binary,pool]"
```

## 3. Connect

`pydantic-settings` reads `.env` and `.env.local` itself; if `app/config.py` already exists, add the fields to its `Settings`.

**`app/config.py`**

```python
from pydantic_settings import BaseSettings, SettingsConfigDict


class Settings(BaseSettings):
    # ExtraOrbital writes .env.local when the project has one, else .env; the later file wins.
    model_config = SettingsConfigDict(env_file=(".env", ".env.local"), extra="ignore")

    POSTGRES_URL: str


settings = Settings()
```

**`app/postgres.py`**

```python
from psycopg_pool import AsyncConnectionPool

from app.config import settings

# Opened in the app's lifespan rather than on import, so importing never waits on the network.
pool = AsyncConnectionPool(settings.POSTGRES_URL, open=False)
```

**`app/main.py`**

```python
from contextlib import asynccontextmanager

from fastapi import FastAPI

from app.postgres import pool


@asynccontextmanager
async def lifespan(app: FastAPI):
    await pool.open()
    yield
    await pool.close()


app = FastAPI(lifespan=lifespan)
```

## 4. Use it

**`app/api/postgres.py`**

```python
from fastapi import APIRouter

from app.postgres import pool

router = APIRouter()
# In app/main.py: from app.api import postgres; app.include_router(postgres.router)


@router.get("/api/postgres")
async def check_postgres():
    async with pool.connection() as conn:
        cursor = await conn.execute("select now()")
        (now,) = await cursor.fetchone()
    return {"now": str(now)}
```

## 5. Verify

```bash
npx extraorbital list        # the resource is listed as active
fastapi dev app/main.py
curl http://localhost:8000/api/postgres
```

A JSON answer means the credentials, the client and the route all work. Delete the route afterwards if the app does not need it.

## Pitfalls

- Point migration tools at `POSTGRES_URL_NON_POOLING` (Prisma's direct URL, `drizzle-kit`, Alembic, `manage.py migrate`); through the pooler they fail in confusing ways.
- Not `DATABASE_URL`: ExtraOrbital never claims that name. If the code already reads it, change the code or copy the value of `POSTGRES_URL` into it yourself.
- Scale-to-zero: the first query after five idle minutes takes a moment longer. Storage still bills while idle, from the account allowance on Prototype.
- A blocking client (`boto3`, `stripe`) belongs in a plain `def` route, which FastAPI runs in a threadpool; in `async def` it stalls every other request.
- With `uv`, `uv add` the same packages instead of `pip install`.
- Exit code 3 means the account is past its free allowance: show the human the link the command printed and wait. Do not retry or work around it.

Docs: https://extraorbital.dev/docs/resources/neon.md