SQLAlchemy: The ORM That Refused to Lie

The Library That Tells You the Truth

This is episode twenty-three of What Did I Just Install.

Almost every tool a developer installs makes the same promise. We will make the hard thing easy. We will hide the ugly part so you never have to look at it. Install us, and the complexity becomes our problem, not yours. That promise is the entire business of software libraries. You trade understanding for convenience, and most of the time it is a good trade.

Now imagine a library that builds its entire reputation on refusing to make that promise. A library for talking to databases, which is one of the most tedious, error-prone, repetitive chores in all of programming, and yet a library whose author looks you in the eye and says, I am not going to hide the database from you. You still need to understand it. You still need to know what a query costs. I will automate the boring repetition, but I will not pretend the database is something it is not. The leak in the abstraction is not a bug I failed to fix. It is the design.

That library is SQLAlchemy. The man who built it is named Michael Bayer, a former drummer from Long Island who dropped out of music school and stumbled into databases by accident. And the strange thing, the thing worth a whole episode, is that this contrarian, honest, slightly cranky piece of software has outlived nearly every competitor that promised to make the database disappear. Twenty years on, it is still the default way that serious Python applications talk to relational databases. Reddit uses it. Dropbox used it. Yelp uses it. The bet Bayer made in two thousand five, that developers should be allowed to understand their own database rather than be protected from it, turned out to be one of the most durable design decisions in the history of the language.

The Mismatch Nobody Could Solve

To understand why SQLAlchemy is contrarian, you have to understand the problem it refuses to solve cleanly. And the problem has a famous, slightly dramatic name. The object-relational impedance mismatch.

Here is the mismatch in plain terms. When you write a program in a modern language, you think in objects. A user is an object. It has a name, an email, a list of orders. Each order is its own object, with its own list of items. The data in your program is a web of objects pointing at other objects, nested, linked, alive in memory. That is how programmers think, and it feels natural.

But a relational database does not think in objects. It thinks in tables. Flat grids of rows and columns. There is no nesting. There is no pointer from one row to another, only foreign keys, numbers that reference other rows in other tables. To turn your beautiful web of objects into rows, and to turn rows back into objects, somebody has to do a tremendous amount of tedious translation. Pull the user row. Pull all the order rows that reference it. Pull all the item rows that reference those. Stitch them back together into objects. Then, when something changes, figure out exactly which rows need updating and in what order, so you do not violate a constraint halfway through.

Before tools existed to help, you did all of this by hand. You wrote the SQL, you read the results one column at a time, you assembled the objects yourself, and you wrote the updates yourself, and you got the order wrong, and the database rejected it, and you tried again. It was the kind of work that filled entire careers and entire codebases. The translation layer between objects and tables was where bugs went to breed.

The tools that automate this translation are called object-relational mappers. O R M for short, the ORM. And the dream of the ORM, the seductive, recurring dream, is that you should never have to think about the database at all. You just work with objects. You set user dot name. You call save. The ORM figures out the SQL. The database becomes invisible. A solved problem. A closed door.

That dream is so powerful that in two thousand four a software thinker named Ted Neward coined a phrase that stuck for two decades. He called object-relational mapping the Vietnam of computer science. Jeff Atwood, the cofounder of Stack Overflow, popularized it in a widely read essay in two thousand six.

Object relational mapping is a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy.

The argument was that ORMs work beautifully for the first eighty percent of what you need, and then the last twenty percent, the complicated queries, the performance tuning, the things real applications actually require, becomes a nightmare, because the abstraction that was supposed to protect you from SQL is now standing between you and the SQL you desperately need to write. You are fighting the tool that was supposed to help you. That is the quagmire.

The Drummer Who Read the Manual

Into this debate walked Michael Bayer, and his path there was nothing like the one you would expect.

Bayer grew up on Long Island in the nineteen seventies and eighties. He met his first computer around age twelve and taught himself to program on an Atari eight hundred, writing in BASIC and assembly, then Pascal, then Lisp. But code was not the plan. Music was. He went to Berklee College of Music, the famous conservatory in Boston, and then he dropped out to become a drummer in New York City. For a while, that was the identity. A drummer. The programming was a thing he could do, not a thing he was.

What pulled him back was the most ordinary door imaginable. He could type fast, fast from years of who knows what, and a fast typist could get office temp work doing word processing in nineteen nineties Manhattan. Sitting at those temp jobs, bored, he started writing little programs to automate the repetitive word processing tasks. And that habit, automating the boring parts, became the thread that ran through his entire career and eventually through SQLAlchemy itself. He coded his way out of temp work and into the late nineties internet boom, where there was suddenly endless demand for anyone who could build a website.

He wrote database code in Java. He wrote it in Perl. He wrote it, by his own cheerful admission, badly in C. And at every job, he found himself building the same thing over and over. A layer to move data between objects and the database. The same tedious translation, rewritten from scratch at every new company, because there was never a good enough reusable tool to bring with him.

The position that mattered most was at Major League Baseball in the early two thousands. It was a deeply SQL-intense job. They wrote complex queries constantly, and crucially, they cared enormously about something called eager loading, which is the art of fetching all the related data you are going to need in as few database trips as possible, instead of dribbling out hundreds of tiny queries. That obsession, getting the data efficiently, in bulk, with the developer in control of exactly how, would become a founding principle of SQLAlchemy.

SQLAlchemy was at the end of a string of various database abstraction layers I had written over the course of my career in various languages, including Java, Perl, and, badly, in C.

There was one more thing. Bayer was a Java and Perl man, skeptical of Python because of the whitespace. The indentation-as-syntax thing offended him. But a project forced him to use it, and the conversion was almost comically fast.

It forced me to work with Python long enough, about twenty minutes, to realize the whitespace thing was great, and the rest was history.

Twenty minutes. He had found a scripting language with the strong object orientation he wanted from Java, and no compile step. And he had a clear idea of what he wanted to build with it. Not just another website. The ultimate database layer. The one he would never have to rewrite again.

The Bet Against Rails

SQLAlchemy's first public release landed on February fourteenth, two thousand six. Valentine's Day. A database library, of all things, shipped on the most romantic day of the year, which tells you something about the kind of person who builds database libraries.

The timing matters, because two thousand six was the height of a revolution coming from a completely different direction. Ruby on Rails. Rails had exploded onto the web development scene, and at the heart of Rails was an ORM called Active Record. And Active Record made a very specific promise, the seductive one. You write a class called User. You do not tell it anything about the database. Active Record looks at your table called users, automatically figures out the columns, and gives your object methods to save itself, find itself, delete itself. The object and the database row were the same thing. You called user dot save and the SQL just happened. It was magic, and it was genuinely delightful, and it made Rails one of the most influential frameworks ever written.

The Active Record pattern, named and described by the software author Martin Fowler, puts the database logic directly inside the object. The object knows how to persist itself. It is simple, it is elegant for straightforward cases, and it is exactly what most people meant when they said the database should disappear.

Bayer looked at all of this and deliberately, knowingly, did the opposite.

He chose a different pattern from the same Fowler catalog, one called Data Mapper. In Data Mapper, the object does not know about the database at all. Your User object is just a User. It has no save method, no idea that a database exists anywhere in the universe. A separate layer, the mapper, handles all the translation between the object and the table. The persistence is somebody else's job, deliberately kept outside the object.

This sounds like a small architectural nuance. It was, in fact, a philosophical fork in the road, and the whole personality of SQLAlchemy flows from it. By keeping the database knowledge out of the object, Bayer kept the object pure and testable, and he kept the database honest and visible. The two worlds stayed two worlds, connected by a translator, instead of being smashed into one object that pretended the seam did not exist.

And he paired it with a second idea borrowed from the Java world, from a famous library called Hibernate. The Unit of Work. Instead of saving each object the instant you changed it, SQLAlchemy quietly watches everything you touch during a stretch of work. It keeps a running ledger of every new object, every modified object, every deleted one. Then, at the moment you choose, it works out the correct order to write all of those changes to the database in one coordinated burst, respecting every foreign key and constraint along the way. It is the difference between paying for each grocery item at a separate register and putting everything in one cart and checking out once.

The need for eager loading was also a core use case I learned to value. The parts of repetition in writing a database application, those aspects of querying and moving data in and out of object models which we always end up automating, became apparent.

So here was the bet, stated plainly. Rails said, let the object be the database, and you will rarely have to think about SQL. Bayer said, no. Keep them separate. Automate the tedium, but leave the SQL in plain sight, and trust the developer to understand it. In two thousand six, with Rails ascendant and the whole industry chasing the disappearing database, this looked stubborn. It looked like building a slower horse on purpose.

The Leak Is the Feature

To see why Bayer was right, you have to understand the thing he wrote into the project's own philosophy, the sentence that is still on the SQLAlchemy website today.

SQLAlchemy considers the database to be a relational algebra engine, not just a collection of tables.

Read that again. Most ORMs treat the database as a dumb bucket of tables, a place to dump objects and fish them back out. SQLAlchemy treats the database as what it actually is, a powerful engine for combining, filtering, and aggregating data, often far faster and smarter than your application code could ever be. The database is good at things. Hiding it does not just cost you control. It costs you the database's own intelligence.

And so Bayer built the project in two layers, and this is the part that confuses newcomers and delights veterans. There is SQLAlchemy Core, and there is the SQLAlchemy ORM, and Core comes first.

Core is a way to write SQL in Python without writing SQL as raw text strings. You build queries out of Python objects, select this, join that, filter by the other, and SQLAlchemy generates the actual SQL for whatever database you are pointed at. No objects, no mapping, no magic. Just SQL, expressed in Python, fully under your control. Core is for people who want SQL and want it composable and safe.

The ORM sits on top of Core. It adds the object mapping, the Data Mapper, the Unit of Work, the whole layer that turns rows into objects and back. But, and this is the crucial design decision, the ORM never seals Core away. You can drop down to Core, or to raw SQL, at any moment, in the middle of an ORM application, whenever the abstraction starts to cost you more than it saves. The escape hatch is always open, and using it is not considered a failure. It is considered Tuesday.

This is the answer to the Vietnam quagmire. Atwood's complaint was that ORMs trap you, that the last twenty percent has no exit strategy. SQLAlchemy's answer is to build the exit into the front door. There is no wall between you and SQL. The whole project is, in the words of its own philosophy, a series of composable, transparent tools, where instead of hiding away the details behind a wall of automation, all of the processes are fully exposed.

SQL databases behave less like object collections the more size and performance start to matter. Object collections behave less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.

That is the leak, stated as a design goal. An ORM is a leaky abstraction. Every ORM is. Bayer's competitors treated the leak as an embarrassment to be plastered over with more automation. Bayer treated the leak as the truth and built the entire architecture to let you reach through it whenever you needed to. He even put the real goal in capital letters at the top of the philosophy page.

The main goal of SQLAlchemy is to change the way you think about databases and SQL.

Not to free you from thinking about them. To change how you think about them. The opposite mission from Active Record.

The Name, and the Handle

The name itself carries the whole philosophy in two words. Alchemy. The medieval art of transformation, turning one substance into another, lead into gold. SQLAlchemy transforms one thing into another, objects into tables and tables into objects, the impedance mismatch turned into something workable. But notice what the name keeps and what it refuses to drop. SQL is right there in front. It is SQL-Alchemy. The transformation is honored, but the SQL is never hidden, not even in the name. A competing library might have called itself something that erased the database from view. Bayer led with it.

There is a second name worth knowing, his own. Online, in code, on GitHub, Bayer goes by zzzeek. Three z's, then e-e-k. It looks like a keyboard mash or the sound of someone falling asleep, and Bayer has the dry, self-deprecating humor to match it. On one professional profile, in the field where most engineers list their accomplishments, he reportedly wrote a note to recruiters describing himself as old, tired, extremely busy, not looking, and bad at tech interviews, adding that their clients would not hire him anyway. The man who built the database layer under Reddit and Dropbox flagging himself as unhireable. It is exactly the energy of someone who would ship a database library on Valentine's Day.

When the Crowd Wanted Magic Anyway

For all his conviction, Bayer did not win the argument by force. He won it by being patient while the world tried the other way first, even on top of his own creation.

In the early years, plenty of Python developers wanted exactly what Rails offered. They had seen Active Record. They wanted the magic, the object that saves itself, the database that disappears. And so in two thousand seven, barely a year after SQLAlchemy shipped, other developers built a layer on top of it called Elixir. Elixir was an Active Record style front end for SQLAlchemy, a domain-specific language deliberately modeled on Ruby on Rails, designed to let you write far less code and pretend the Data Mapper underneath was not there. It was popular. People liked it. It gave them the disappearing database they thought they wanted, while SQLAlchemy quietly did the real work below.

Bayer's response was telling. He did not declare war on Elixir. Instead he absorbed the lesson. People wanted less boilerplate. Fine. He could give them less boilerplate without abandoning the architecture. SQLAlchemy grew its own declarative layer, a way to define your mapped classes concisely, almost as compactly as Active Record, but with one essential difference. Underneath the convenient syntax, it was still Data Mapper. Still Unit of Work. Still Core sitting right there, the escape hatch still open. He gave people the ergonomics they wanted without selling them the lie they thought they needed. Elixir faded. SQLAlchemy's declarative approach became the standard way everyone writes it today.

By not overselling it before it was appropriate, and just waiting for it to mature very deeply and slowly, the project did not become as much of a target of derision.

That is the strategy in one sentence. Slow. Patient. Refuse to oversell. While flashier ORMs in flashier frameworks promised to abolish SQL and then collapsed under the weight of the last twenty percent, SQLAlchemy just kept being correct, kept being honest about the leak, and kept being there. It did not need a viral moment. It needed to still be standing in ten years, and it was.

The One-Man Decade

Here is the part of the story that connects to every other episode in this series. For a very long time, the database layer holding up some of the largest sites on the internet was essentially one person.

Bayer did not just create SQLAlchemy. He maintained it, more or less alone, for years. And he did not stop there. He built Mako, a fast template engine still used inside major projects. He built Alembic, the database migration tool that almost every SQLAlchemy user relies on to evolve their schema over time, which means the thing that safely changes the shape of your production database is also Bayer's. He built dogpile dot cache, a caching library. The pattern is familiar from this series. One person, an entire load-bearing ecosystem, the bus factor sitting at one for the part of the stack that, if it failed, would corrupt or lose the actual data, the thing companies fear losing most.

We have seen the lonely-maintainer story end in burnout. The requests creator burned out and handed his project away. The xz-utils maintainer, exhausted and unwell, was social-engineered into letting a stranger plant a backdoor. The pattern is brutal and it is structural. But SQLAlchemy reached a quieter, more stable resolution than most, and it is worth noticing why.

In two thousand fourteen, Bayer took a job at Red Hat, the open source company, as a senior software engineer. And the work he was assigned was, in part, OpenStack, the enormous open source cloud platform, which leaned heavily on SQLAlchemy for its database access. Red Hat had a direct corporate interest in SQLAlchemy being healthy. So Bayer found himself in an unusual and enviable position. He was being paid, by a serious company, to do work that overlapped with maintaining the open source project the world depended on. Not a grant. Not a tip jar. A salary, aligned with the thing he already cared about.

It is the same lesson the Jinja2 creator found by going to work at Sentry, and the same lesson the OpenSSL crisis taught the world far more painfully. The way critical infrastructure survives is rarely a heroic individual grinding for free forever. It is the maintainer finding an institution whose interests line up with the work, so that the unpaid passion project quietly becomes a paid responsibility, and the bus factor stops being a countdown. SQLAlchemy did not get a dramatic rescue fund after a catastrophe. It got something better and rarer. A maintainer who found a sustainable seat before the catastrophe came.

The Long, Careful Rewrite

By the late twenty tens, SQLAlchemy faced the danger that kills good old software. It was mature, correct, and beloved, and the world had moved underneath it.

Two things had changed. First, Python had grown type hints, a way to annotate your code so that tools and editors can catch mistakes before you run anything. The whole ecosystem was racing toward typed Python, and SQLAlchemy, designed in an era before type hints existed, did not fit the new tooling well. Second, Python had embraced asynchronous programming, the style that lets a single program juggle thousands of slow operations, like database calls, without grinding to a halt waiting for each one. Modern web frameworks were built around async. SQLAlchemy was built around the older synchronous model.

A lesser project would have bolted these on hastily and made a mess, or refused to change and slowly died. Bayer did neither. He began one of the most carefully staged rewrites in the Python world. The plan for what would become SQLAlchemy two point oh formed around November two thousand eighteen, and it had a genuinely ambitious goal, to finally unify the two halves of the library, Core and the ORM, into one consistent way of writing queries, while adding full type hints and proper async support, all without abandoning the millions of lines of existing code that depended on the old way.

He did it in two acts. First came version one point four, released in two thousand twenty-one. This was the bridge. It introduced the new unified query system and a beta of the async support, while keeping the old style working completely, so nobody was forced to jump. You could migrate gradually, on your own schedule, with both worlds running side by side. Then, on January twenty-sixth, two thousand twenty-three, SQLAlchemy two point oh arrived, with the unified architecture finished, async support out of beta, and deep integration with Python's type hints so your editor could finally understand your database models.

Think about the restraint in this. The whole effort spanned more than four years, from the first sketch in two thousand eighteen to the final release in two thousand twenty-three, with a transitional version in the middle whose entire job was to let people upgrade without pain. That is not how exciting software gets built. That is how durable software gets built. The same patience that let SQLAlchemy outlast Elixir let it cross from the old Python era into the new one without shattering its own foundations. The library that refused to lie about the database also refused to lie about migration. It told you the truth, gave you a bridge, and waited for you to cross.

What Holds the Data

So pull the thread. What does SQLAlchemy depend on, and what depends on it?

Downward, the tree is shallow and deliberate. SQLAlchemy is famously self-contained, leaning mainly on a small driver to actually talk to each specific database, the bit of glue that knows how to speak PostgreSQL, or MySQL, or the SQLite file we covered in an earlier episode, the one-man database that lives inside everything. SQLAlchemy is the layer above that driver, the one that turns your Python into the right dialect of SQL for whichever engine sits below. It does not pull in a sprawling forest of packages. Like the best infrastructure in this series, it tries to depend on as little as possible, because the thing guarding your data should have as few strangers in its supply chain as it can manage.

Upward is where it gets vertiginous. SQLAlchemy is the default ORM for an enormous slice of the Python web. Flask, the framework built on the Jinja2 templates from a previous episode, has Flask-SQLAlchemy as its near-universal database extension. The modern async frameworks, the FastAPI world built by the three developers we met in the validation episode, reach for SQLAlchemy two point oh's new async support to talk to their databases. OpenStack, the cloud platform running in data centers worldwide, sits on it. Reddit, Yelp, Dropbox, countless internal company tools nobody will ever hear about. If a Python application stores anything important in a relational database and was built by people who knew what they were doing, the odds are very good that Bayer's translation layer is the thing standing between their objects and their tables.

And consider what that means for risk. When the left-pad package vanished, builds failed and were fixed in minutes. When SQLAlchemy has a subtle bug, the failure mode is data. The most precious, least recoverable thing a company owns. Rows written in the wrong order. A relationship saved incorrectly. The kind of corruption that does not crash anything, that looks like everything is fine, right up until you discover that it is not. This is why the honesty matters so much. Bayer's refusal to hide the SQL is not aesthetic stubbornness. It is the recognition that when you are the layer guarding the data, pretending the database is simpler than it is becomes genuinely dangerous. The leak you can see is safer than the magic you cannot.

Where It Connects

Most of this series is about the disappearing act. Tools that make a hard thing vanish so you never have to think about it again. Requests made HTTP vanish. Docker made the environment vanish. The whole promise of a good dependency is that it takes a problem off your plate forever. And those episodes are, in a way, celebrations of that magic.

SQLAlchemy is the counterpoint. It is the tool that looked at the most seductive disappearing act in software, the dream of the database that vanishes behind objects, and said no. The database is too important and too smart to hide. I will carry the tedium for you. I will not carry the understanding. That is yours to keep.

And the remarkable thing, the thing the whole industry should sit with, is that this is the one that lasted. Active Record made Rails magical and Rails changed the world, but in the Python ecosystem the framework that promised to abolish SQL was not the one that ended up holding the data twenty years later. The honest one was. The one built by a drummer from Long Island who automated his way out of a temp job, who chose the harder pattern on purpose, who shipped on Valentine's Day, who described himself as old and tired and unhireable while quietly maintaining the layer beneath some of the largest sites online. He bet that respect for the database would outlast contempt for it, and he was right.

So the next time an ORM frustrates you, the next time you have to drop down to raw SQL because the abstraction could not carry the last twenty percent, do not curse the leak. The leak is where the truth gets in. Mike Bayer built an entire twenty-year-old institution on the radical idea that you, the developer, can handle the truth. The door to SQL is always open. He just refused to pretend it was a wall.

This one is a rabbit hole worth falling into. If you have any Python around, run pip install sqlalchemy, open a shell, and type from sqlalchemy import create_engine, select, then build a tiny query object. But here is the trick that reveals the whole philosophy. Take any query you construct, print it, and wrap it in str. You will see the actual SQL that SQLAlchemy is about to send. That print statement is the leak made visible. Most ORMs hide that string from you and dare you to find it. SQLAlchemy hands it over without flinching, because the entire point of the project is that you are allowed to see exactly what it is doing to your database. Watch the SQL appear, and you have understood Mike Bayer's whole argument in one line.

That was episode twenty-three.