SQLAlchemy and Alembic 2 cheatsheet

SQLAlchemy and Alembic 2: Engine, models and tables

5 common SQLAlchemy and Alembic 2 tasks.

Install SQLAlchemy

Install SQLAlchemy and a database driver, for example psycopg for PostgreSQL.

pip install sqlalchemy psycopg[binary]

Create an engine

An engine manages connections. SQLite is handy for tests; use your real URL in production.

from sqlalchemy import create_engine

engine = create_engine("sqlite:///app.db", echo=False)

Define models

Declare tables with typed Mapped attributes.

from sqlalchemy import ForeignKey, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    email: Mapped[str | None]
    posts: Mapped[list["Post"]] = relationship(back_populates="author")

class Post(Base):
    __tablename__ = "posts"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    author: Mapped[User] = relationship(back_populates="posts")

Create the tables

Create tables for all models. Use migrations for real schema changes.

Base.metadata.create_all(engine)

Open a session

A session is a unit of work. Use it as a context manager so it always closes.

from sqlalchemy.orm import Session

with Session(engine) as session:
    ...