ORM

SQLAlchemy builds its ORM on top of Core, its SQL toolkit, which you can also use alone. The ORM follows the data mapper pattern, so your classes and your database schema can change independently of each other. Declare models with typed Mapped[] attributes, as in the ORM Quick Start. Keep the session outside the functions that query data. Open it with with Session(engine) as session: around one unit of work, like a web request, and keep transactions short. For schema changes over time, its docs point you to Alembic. In asyncio code, the asyncio extension covers both Core and the ORM.

Django comes with an ORM in which you describe your database layout in Python code. It follows the Active Record pattern, so a model holds its domain logic along with its fields. Change a model, then run makemigrations and migrate, and migrations carry the change into your schema. In async code, every QuerySet method that runs a query has an a-prefixed variant, like afirst().

Peewee is a simple and small ORM with few concepts, in a single module with no required dependencies. It works with SQLite, MySQL, MariaDB, and PostgreSQL. As in its README, set the database once in a BaseModel's Meta.database and subclass that for every model. A with db: block opens a connection and a transaction, commits when the block exits normally, rolls back on an exception, and closes the connection.

SQLModel is a thin layer on top of Pydantic and SQLAlchemy: each model is both a Pydantic model and a SQLAlchemy model. FastAPI's author built it for FastAPI apps, but it works in any other kind of app too. Mark the classes that are tables with table=True, and keep what your API reads and returns in data models, which are only Pydantic models.

PynamoDB wraps DynamoDB's verbose API in a simple, elegant one. Write a model per table, with the table's name in Meta.table_name and its hash key marked hash_key=True, since every DynamoDB table has one.

MongoEngine is an ORM-like layer on top of PyMongo, and synchronous: it's based on PyMongo alone, not on async drivers. Your document classes define a schema that MongoDB never sees, since it's enforced at the application level. Store data that belongs to one document, like a post's comments, as embedded documents on it.

Beanie is an asynchronous ODM whose data models are based on Pydantic. Setting it up takes three steps: subclass beanie.Document instead of pydantic.BaseModel, create PyMongo's async client, and call init_beanie() with your database and models inside your event loop. That call also creates the collections and sets up the indexes your models define.

Django MongoDB Backend is a Django database backend that uses PyMongo. MongoDB's docs say it translates Django ORM methods into MongoDB queries, so your project keeps Django's models and ORM. Start from its project template, which sets ObjectIdAutoField as the default primary key.

With SQLAlchemy, the Django ORM, or Peewee, load related rows up front instead of running one query per row in a loop. SQLAlchemy calls selectinload() the best strategy for collections and joinedload() the most general one for many-to-one. Django has select_related() and prefetch_related(), and Peewee joins or eager-loads depending on which way the relationship goes.

For more SQLAlchemy extensions and resources, see awesome-sqlalchemy.