SQLAlchemy ORM 2.x: modern Python + real relational data

The ORM lets you work with classes and instances — SQLAlchemy translates them to SQL under the hood.

The modern (2.x) declarative style

Uses type hints for mapping — the class is the schema.

Relationships

posts: Mapped[list["Post"]] = relationship(back_populates="author")

• One-to-many: a user has many posts.
• back_populates — bidirectional link, so post.author also works.
• Foreign key + relationship together = navigable graph in Python.

Session — your unit of work

• s.add(obj) — mark for insert.
• s.commit() — flush + commit transaction.
• s.rollback() — undo pending changes.
• s.query(User).filter_by(...) — build a query.
• Objects returned are tracked — mutating them + commit() issues an UPDATE.

Loading strategies (the one thing to know)

• Lazy (default): each user.posts access triggers a fresh SELECT. Beware N+1 queries in loops.
• Eager: .options(selectinload(User.posts)) — one extra query for all posts.
• Rule of thumb: if you're iterating a list of objects and touching a relationship, eager-load.

Try it

1. Add a Post.body column and a second post.
2. Query all posts whose title contains "SQL".