Skip to content

Database & migrations

runapi uses async SQLAlchemy and Alembic. Pick the extra for your database; the code is the same for all of them and only DATABASE_URL changes.

Extra Driver DATABASE_URL
runapi[sqlite] aiosqlite sqlite+aiosqlite:///./app.db
runapi[postgres] asyncpg postgresql+asyncpg://user:password@localhost:5432/mydb
runapi[mysql] aiomysql mysql+aiomysql://user:password@localhost:3306/mydb

(runapi[db] is kept as an alias for the SQLite stack.) runapi doctor tells you which extra to install if the driver for your DATABASE_URL is missing.

Quick start

pip install "runapi[postgres]"
runapi init my-api --db postgres      # or --db sqlite
cd my-api
docker compose up -d db               # PostgreSQL (skip for SQLite)
runapi db migrate -m "create items"
runapi db upgrade
runapi dev

--db generates models/, db.py, alembic.ini, migrations/, an example route and (for PostgreSQL) a docker-compose.yml. For an existing project run runapi db init.

Sessions in your routes

# db.py
from runapi import Database

database = Database()  # reads DATABASE_URL

# main.py
runapi_app.use_database(database)  # connect on startup, dispose on shutdown, /ready check

# routes/items.py
from fastapi import Depends
from sqlalchemy.ext.asyncio import AsyncSession
from db import database


async def post(body: ItemCreate, session: AsyncSession = Depends(database.get_session)):
    item = Item(name=body.name)
    session.add(item)
    await session.flush()  # assigns item.id
    return {"id": item.id}  # the session commits when the request finishes

The session commits when the request succeeds and rolls back if an exception escapes, so handlers do not manage transactions. Outside a request use async with database.session() as s:.

Define models on runapi.Base and import them in models/__init__.py so Alembic can see them.

Migrations

runapi db migrate -m "add due date"   # autogenerate from model changes; review the result
runapi db upgrade                     # apply (default: head)
runapi db downgrade                   # revert one step (or: runapi db downgrade base)

migrations/env.py reads DATABASE_URL and Base.metadata. Generate migrations against the database you deploy to, because SQLite renders some defaults differently from PostgreSQL.

Working example

examples/postgres-todo is a complete todo API with a committed migration. CI runs it against a real PostgreSQL service and against SQLite.

Repositories

If you prefer the repository pattern, SQLAlchemyRepository(session, Model) wraps a session with get, get_all, create, update, delete and count. See Schemas & storage.