An Object-Relational Mapper, or ORM, is a library for reading and writing database rows through Python objects. A Product row in the products table becomes a Product instance in Python; a query becomes a method call; an update becomes an attribute assignment followed by a commit. The ORM writes the SQL. This lesson covers what ORMs do, why teams adopt one, the trade-offs that come with using one, and a tour of the major Python ORMs.

A raw database driver returns tuples. A query for a product returns something like (1, "Ceramic Mug", 14.99, 80), and the calling code indexes into the tuple to get each column. If the columns change order, every caller breaks. If the wrong column is read as price instead of stock, the value is wrong without an error. An ORM solves both problems by returning an object with named attributes.

The mapping has two directions. On reads, the ORM takes a row from the database and constructs a Python object whose attributes match the columns. On writes, it takes a Python object and translates attribute changes into SQL INSERT or UPDATE statements. The class itself, often called a "model," is the schema definition: its attributes are the columns, its name is the table.

The flow above shows the round trip. A class is defined once, the ORM uses it as both the schema and the type of the loaded objects. Code that touches the database stops talking in SQL strings and starts talking in Python objects and method calls.

A minimal SQLAlchemy example:

Base.metadata.create_all(engine) walks every subclass of Base and issues CREATE TABLE statements. session.add(...) plus session.commit() becomes an INSERT. session.query(...).filter_by(...).first() becomes a SELECT ... WHERE name = ? LIMIT 1. No SQL is written by hand; the ORM generates it from the class definition and the method calls. The same Product class drives schema creation, inserts, and queries.

The strongest argument for an ORM is less boilerplate. Compare the raw-driver version of "find a customer by email and update their address" to the ORM version:

The same operation through an ORM:

The second version is shorter because the ORM is doing the work. It generated the SELECT, ran it, mapped the row to a Customer object, tracked the changed attribute, generated the UPDATE, ran that, and committed. The application code is one assignment.

The second reason teams adopt ORMs is portability. The same model code runs against SQLite for tests, PostgreSQL in production, and MySQL on a developer's laptop, because the ORM speaks a dialect-aware SQL on the application's behalf. Raw SQL commits to one database's quirks: SQLite's INTEGER PRIMARY KEY AUTOINCREMENT, Postgres's SERIAL, MySQL's AUTO_INCREMENT. The ORM smooths over those differences.

The third reason is composability. Filters chain naturally on Python objects, so a complex query reads as a sequence of small steps:

Building that same query with raw strings means either one long SQL literal or a fragile string-concatenation pipeline. The ORM version composes; a single filter can be refactored without rewriting the whole query.

The fourth reason is safety by default. Every value that flows into an ORM filter is bound as a parameter, automatically. Whatever the user types as their email goes through the placeholder mechanism without explicit handling. SQL injection becomes structurally impossible for the queries that go through the ORM. The same is true of escaping, quoting, and type conversion.

The fifth reason is relationships. An order has many items, a customer has many orders, a product belongs to a category. The ORM expresses those connections as Python attributes (order.items, customer.orders, product.category), and a relationship traversal becomes an attribute access. Done well, this collapses ten lines of join logic into one attribute reference.

The price of all that convenience is a layer of abstraction between the application and the database. When the abstraction leaks, the engineer has to know both the ORM and the SQL it generated, which is one more thing than the raw driver demanded. Three specific problems come up often enough to name.

The first is the N+1 query problem. Loading 100 customers and then accessing each customer's orders starts with one query for the customers. Each access to customer.orders triggers another query (one hundred queries). One hundred and one queries to display a page. The ORM did the literal thing on each attribute access. The fix is to load the orders eagerly with a JOIN in the first query, which is one more line of code, but the call has to be added explicitly.

The diagram captures the shape: one query, then a hundred more, all hidden behind innocent-looking attribute accesses. The ORM did exactly what was asked, attribute by attribute, but the total cost only shows up under load.

The second problem is performance surprises in less obvious ways. The ORM might issue a SELECT * when only two columns were needed. It might serialize a 50,000-row update into 50,000 individual UPDATE statements instead of a single bulk update. It might fetch every related row of a relationship when only the count was needed. The same convenience that makes simple queries short makes complex queries opaque.

The third is the learning curve. SQLAlchemy's full feature set is large enough to be a separate skill from "I know SQL." Sessions, identity maps, flush versus commit, lazy versus eager loading, scoped sessions, declarative bases, and migrations all have their own docs and edge cases. The investment pays off, but the first weeks are slower than writing SQL.

A common fourth issue is that ORMs are well-suited to row-at-a-time CRUD work and clumsy for the set-at-a-time analytical queries that reporting systems do. A query that joins six tables, groups by three columns, and computes window functions is easier to write in SQL than in any ORM's query builder. The pragmatic answer is to use both: ORMs for the CRUD layer, raw SQL (still parameterized) for the heavy analytical queries.

An N+1 problem sounds abstract until measured. A list page that issues 200 queries instead of 2 can turn a 50ms response into a 5-second response. Every ORM has tooling to detect this (SQLAlchemy's echo=True and the sqlalchemy.engine logger, Django's django-debug-toolbar); turn it on during development.

Three situations are worth flagging where raw SQL is the better call.

The first is bulk operations. Updating 100,000 rows one object at a time is much slower than running a single UPDATE products SET stock = stock - 1 WHERE category = 'mugs'. SQLAlchemy supports bulk operations (session.execute(update(Product).where(...).values(...)), session.bulk_save_objects(...)), but they bypass much of the ORM machinery (no per-row events, no relationship cascades). At that point, the ORM is a thin wrapper around the raw query, and the wrapper adds little.

The second is complex analytical queries. Window functions, recursive CTEs, multi-CTE pipelines, and full-text search syntax are all easier in SQL than in any ORM. SQLAlchemy Core (the lower layer below the ORM) can express most of this, but the readability advantage of the ORM is gone. If the query is going into a reporting tool or a one-off data pipeline, write it as a SQL string with parameters and keep the SQL string in source control next to the rest of the migrations.

The third is database-specific features. PostgreSQL's JSONB operators, MySQL's MATCH ... AGAINST, and SQLite's RTREE indices are not equally well supported across ORMs. When a feature is unique to one database, the cross-database abstraction gets in the way. Use the feature directly through the driver, or through the ORM's raw-SQL escape hatch.

The rule of thumb teams settle on is: ORM by default, raw SQL for the cases above. The choice isn't all-or-nothing for the whole codebase. The same Session can run an ORM query on one line and a raw SQL query on the next.

Python has several mature ORMs, and the choice between them depends on the project, the framework, and the team's preferences. A tour of the main options:

A short example from each shows the surface differences.

SQLAlchemy 2.0 style:

Django ORM, used inside Django views:

Peewee, lightweight and quick to pick up:

Tortoise ORM, designed for async code:

SQLModel, popular in the FastAPI ecosystem because it doubles as a Pydantic model:

The common shape across all of them is "a Python class defines the table, queries chain through method calls, the ORM emits SQL." Once one is learned, the others come quickly because the same patterns recur with minor syntax differences. SQLAlchemy is the most widely used of the bunch and the one most production codebases run on.

Most ORMs share a small set of operations that map cleanly to CRUD: create, read, update, delete. The vocabulary varies, but the shape is the same.

The cycle in the diagram is the common case. Build an object, hand it to the ORM, and commit. To change a row, load it, set an attribute, and commit. To delete, call delete on the object and commit. The ORM tracks which objects are new, which are changed, and which are marked for deletion, and at commit time it issues the SQL for all of them in one transaction.

A concrete walkthrough using SQLAlchemy:

The same four operations would take three times the code with the raw driver, with a separate query for each. The ORM expresses each operation in the natural Python way, then translates the whole batch into the right sequence of SQL statements at commit time.

Schemas change over time: a column gets added, a table gets renamed, an index gets dropped. Every serious ORM has a companion migration tool that turns a model diff into a sequence of ALTER TABLE statements that can be applied, versioned, and rolled back.

The basic workflow is the same across tools. Change the model in Python; the migration tool inspects the diff between the current model state and the database state; it generates a Python file that captures the migration; review the file (because auto-generated migrations sometimes miss things, especially renames); apply the migration with one command; the migration tool records the version in a dedicated table so the same migration won't run twice.

Migrations are how schemas evolve in production. Manually editing the database with ALTER TABLE statements works for the very first deploy and breaks down within months as the team grows, deployments multiply, and rollbacks become necessary. Treating migrations as code, in version control, alongside the application is the only sustainable approach.

A short decision guide for everyday code:

The same codebase routinely uses both. SQLAlchemy in particular makes this easy: session.execute(text("SELECT ...")) runs raw queries in the same session that's building ORM queries elsewhere.