How we spent thirty years trying not to write SQL

I re-read the things I write an unreasonable number of times. Not editing exactly, just re-reading — same paragraphs, same order, looking for whatever is off. I did it to the last post probably a dozen times after it was already published, which is a fantastic use of an evening. In fairness to me it's the first thing I've managed to actually publish in about ten years of starting this site roughly twice a year and never finishing it, so I've earned some of the fussing.

And every single pass, the last line snagged on something. I'd ended it with a throwaway joke about going off to write some SQL and feel like myself again. The bit being that after a week of poking at a frontend framework I'd retreat to my own side of the stack, where things make sense and nothing has a build step.

Except the more times I read it, the less it held up. When did I last actually write SQL? Not "work on a database." Not "think about an index." Sit down and type a query, because a query was the thing that needed to exist.

The honest answer is 2022, and a bit of 2023.

I spent that stretch pulling several states' worth of student information systems into one place. Every dialect of SQL you can name and one or two you'd rather not, schemas that had grown up independently for years and agreed on almost nothing.

What we built on top of it was an early warning system. Grades, behavior, proficiency across different tests, how a student had trended in the years before, how their teachers compared to other teachers — enough signal to estimate, early enough in the year to still matter, whether a kid was quietly coming off the rails. The point was for a teacher to find out in October rather than in May, while there was still a whole year in which to do something about it. It worked. It ended up part of the daily routine of thousands of teachers and administrators.

Which is a long way of saying that the disagreements between those schemas mattered, because they were disagreements about children. That was real SQL. That was months of nothing else.

And then it ended, and I have not needed to write anything like it since. Django writes my queries now. I read the migration it generated, decide it looks reasonable, and approve my own pull request — sometimes literally, and once on a team of seventeen engineers where I was the only one qualified to review the database half of my own work. They knew it. It was a running joke. It was also just true.

So I went back and changed one word. It says "pretend to write some SQL" now. Go look, it's still up there.

And then I couldn't leave it alone, because that's a genuinely odd thing to be true about a backend developer. It isn't laziness and it isn't a skill I lost somewhere. It's that thirty years of very deliberate work, by developers older and more talented than myself, went into making sure I don't have to — and it seemed worth understanding how that actually happened.

Every web application is a database with a website stapled to the front. That's not a criticism, it's just what we do. Somebody types something into a box, we put it somewhere durable, and later we get it back out and show it to a different person. Everything else — the framework, the CSS, the acronyms I complained about in the last post — is decoration on top of that one transaction.

So the part of this industry I actually find interesting isn't the databases. Postgres and MySQL have been recognizably themselves for a long time now. What's changed, wildly, over thirty years is how we talk to them. And most of that story is people trying very hard not to write SQL, for reasons that were extremely good at the time.

One caveat before I start narrating a stretch of history I was largely not present for.

I'm going to tell this twice at once. Once as what was actually happening, and once as what I thought was happening, which for most of these years was something completely different and usually much dumber.

When the pipe was the problem

I started writing code in 2003 because of RuneScape.

I was eight and I could not get over the fact that it existed. A whole world running in a browser window on the family computer with other people already in it. I wanted to know how it was made, and then almost immediately I wanted to know what I could do to it from my end.

It did not occur to me that the second question had a different name. I wasn't trying to cheat in any sense I'd have recognized as cheating. I was trying to find the seams. When I eventually worked out that the thing I was doing was called botting, I stopped — but by then the interesting part had moved anyway. It wasn't the game. It was that the game was made of something, and that the something appeared to be available to me.

So I started building browser games instead, which turned into building websites, which is roughly where I've been ever since.

The part that decided what kind of developer I'd be came a year later. I got Sedgewick's Algorithms out of my local library just after I turned nine and understood almost none of it. A third would be a generous estimate and I'm not going to make it. But the fraction I did follow rearranged something. Up to then, code was a way to make a thing appear on a screen. After it, code was a way to make a machine do work on your behalf, and the work was the interesting part. I've been pointed at the back of the stack ever since, and it traces directly to a library book I was too young for.

What I did not have was a computer. We had a computer, one, in a room off the kitchen, shared between all of us. So I wrote code in notebooks. Pages of it, by hand, unrunnable, while somebody else was using the machine — and then typed it in when my turn came around and found out how wrong it was. I got my own laptop at fourteen, a beat-up old Dell, and used it constantly and indiscriminately from that day on.

I bring that up because this whole essay is about constraints outliving themselves. Mine was access to the machine, and it turned me into someone who thinks for a long time before running anything. I still write in notebooks constantly. I've tried every digital system anyone has recommended to me and I'm back on paper inside a month every time, which makes it a constraint from 2004 I've apparently decided to keep. The industry had a different constraint, and it's the one everything else in this story follows from.

You have to start with the modem, because everything else follows from the modem.

A 56k connection in 1999 gave you, realistically, something like five kilobytes a second on a good day with the wind behind you. That is your entire budget. Not for your database query — for the whole page. HTML, images, everything. If your page was 100KB, your user was staring at a half-drawn table for twenty seconds, and they were paying for the phone call.

That one constraint shaped everything about how we handled data, and it shaped it in a direction that feels completely alien now: the network was slow, but the database was fast. Your MySQL instance was on the same box as your web server, or one hop away in the same rack. The expensive thing wasn't the query. The expensive thing was the pipe to the human.

So the architecture wrote itself. One request in. Do all the work server-side. Assemble one small HTML document. Ship it. Never make the user wait for a second round trip, because a second round trip cost them another two seconds of modem time. Pagination existed not because 10,000 rows were hard to query, but because 10,000 rows were impossible to send.

The database was working with one hand behind its back too. MySQL 3.x, which is what most of the web ran on, used MyISAM tables. No transactions. No foreign keys. No subqueries until 4.1. If you wanted referential integrity you enforced it in application code, by hand, and hoped everyone on the team remembered to.

So a lot of the schemas from that era that get sneered at now — the denormalized tables, the missing constraints, the joins done in a for loop in PHP — weren't people being bad at their jobs. They were people working with a database that physically could not enforce the better version, talking to users through a straw.

Here's my problem with all of that: I missed it.

Not by much, but by enough to matter. We never had dial-up at home. Friends did, and I knew the noise, but by the time I was the one at the keyboard we had something slow rather than something crawling. Images arrived band by band over a couple of seconds instead of a couple of minutes. The only thing that genuinely made me wait was pulling an SDK or a set of docs off some university server, which could eat an afternoon.

So I never felt the constraint. What I inherited was everything the constraint had built, with the reasons already worn off.

Because the tutorials didn't update. The blogs didn't update. The code I was reading in view-source had been written by people solving a 1999 problem and I was reading it in 2005, and nowhere in any of it did anyone stop to say we do it this way because the pipe is the expensive part. It was just how you did it. It didn't occur to me that anybody had decided anything, the same way it didn't occur to me that somebody decides how long a red light lasts.

Took me another decade to find out that half the decisions I'd copied were answers to a question nobody had needed to ask in years.

The mysql_query() years

Here is roughly every data access layer on the web in 1999:

$sql = "SELECT * FROM users WHERE email = '" . $_GET['email'] . "'";
$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {
    echo "<li>" . $row['name'] . "</li>";
}

That is a SQL injection. It is also, verbatim in spirit, what the tutorials taught. It's what the books taught. And I wrote versions of it myself — not in 1999, I was three — but years later, in codebases that hadn't moved on and off tutorials nobody had updated since. This pattern outlived its era by about a decade.

There was no layer here at all. The database interface was a function that took a string, and the string got built by gluing user input onto SQL with a period. Escaping was something you were supposed to remember, which is another way of saying it was something that didn't happen. Half the security history of the early web is this one pattern repeated a few million times.

And the schema lived in somebody's head, plus maybe a .sql file that got emailed around when it got badly out of date. Changing a column in production meant SSHing into the box, opening the MySQL client, and pasting an ALTER TABLE in by hand while everyone held very still.

This is where I come in, and it's not a flattering entrance.

I was maybe eleven, working off tutorials that were already stale, building the sort of thing an eleven year old builds. A fan site for Muse, because I loved the music and loved the aesthetic more than I could have explained to you at the time. Then one for Linkin Park. Then, once it occurred to me that the skill transferred, sites for local bands and for a couple of friends who took photographs — which, without anyone ever framing it that way, was the first time I built something for somebody other than me.

All of them had a guestbook. All of them had a login form guarding absolutely nothing of value. All of them were wide open, though it would be another couple of years before anybody told me so.

The warning, when it came, came from an adult on Reddit somewhere around 2008 or 2009.

That was the whole lesson. Watch out for SQL injection. Not what it was, not how it worked, not why my string concatenation was the part causing it. Just: this is bad, use mysql_real_escape_string, you'll be fine.

I never knew his username and I'm certain he never knew I existed. He turned up in the comments of half the threads I read, sarcastic but never once rude about a stupid question, which at that age is the whole difference between a place you keep going back to and a place you quietly stop opening. He was the closest thing I had to a mentor and the relationship ran entirely in one direction.

So I did. On everything. On integers. On values that came out of my own code and had never been near a user in their lives. I had no idea what the function did. I knew that not calling it was how you got Hacked, which in my head was a thing that happened to you, like lightning, rather than something a person does on purpose with a mistake you left lying around.

Nobody ever sat me down and explained it, and there was no moment where it clicked. It came apart slowly, over years, a bit more of the mechanism each time I had a reason to look at it, until at some point I noticed I understood it: the quote character was closing my string early, and everything after it wasn't input anymore. It was just SQL. The user wasn't attacking the database. The user was helping me write the query.

And lightning did eventually strike, just not from the direction I'd spent years watching.

I lost a site completely. It was a graphing calculator app I'd been building, and it was mine — nobody else paid for my mistake, which is the only part of this I'd call lucky. And it wasn't injection. I was thoroughly covered against the one attack anybody had warned me about, integers included. I had left an SSH key in a comment in a text file, in a directory the web server was perfectly happy to hand to anyone who asked for it. Somebody asked for it. There was nothing sensitive on the site and nothing worth taking, so the real-world damage was approximately zero. But it was a server I was leasing with my own money and I could not get back into it, and that part landed in a way no tutorial had managed to.

Which is the lesson underneath the lesson, and it cost me a box: I had been given a rule, I had followed the rule perfectly, and a rule only ever covers the door somebody thought to mention.

I think about that more than you'd expect now that I'm the one reviewing other people's code. There's a real gap between someone who follows the rule and someone who knows what the rule is made of, and it usually isn't about how smart they are. It's whether anybody bothered explaining the mechanism instead of just issuing the warning.

Anyway. That's the baseline. That's what everything after this was trying to get away from, and it's worth remembering when ORMs look like bloat from where we're standing now. From where we were standing then they looked like somebody turning the lights on.

Somebody finally gave it a name

The thing that unlocked the next decade wasn't a library, it was vocabulary.

Martin Fowler's Patterns of Enterprise Application Architecture came out in 2002 and did the unglamorous work of naming patterns people were already fumbling toward. Active Record: an object that knows how to save itself, where the class maps to a table and the instance maps to a row. Data Mapper: a separate layer that shuttles data between objects and tables so neither has to know about the other. Unit of Work. Identity Map.

None of it was new in 2002. What was new is that you could say "we're using Active Record here" and have the other person know what you meant, which sounds like a small thing and is not one.

Although the place that vocabulary has actually paid off for me is not the one I expected. Between engineers, the pattern names are a mild convenience. Between an engineer and somebody on the business side, they turn out to be about the only technical language that survives the trip intact. Say "connection pool" or "denormalization" and you get a nod that means nothing happened. Say "this object knows how to save itself, which is why that change is cheap and this other one isn't" and you watch it land. Patterns are descriptions of shape and responsibility, and shape and responsibility are things everybody in the building already thinks about all day.

Java got there first in practice. Hibernate showed up in 2001 and proved you really could map a rich object model onto a relational schema and have it hold up at scale. It also proved you could bury yourself in XML configuration deep enough that future archaeologists would need funding to reach you. But the core idea was sound, and it settled the question of whether the object relational gap was bridgeable or just permanently annoying.

I got my hands on Hibernate exactly once, in a coding bootcamp in 2013, and I hated every minute of it. Java struck me as verbose and confusing in a way I could not square with what I was doing for fun at the time, which was messing about with embedded systems and IoT — and finding C easier. A language where I had to manage my own memory cost me less thought than a language whose entire pitch was that it would manage it for me. I've never really recovered from that, and I'm aware it says more about me than about Java.

I got to Fowler in 2012, and only because somebody was making fun of him.

I was doing contract work while I was in school, and a friend I wrote code with had arrived at the opinion that ORMs were lazy coding for people who couldn't be bothered to learn how a database actually worked. He wasn't being cruel. He thought it was self-evident. And he said it to me — a person who had checked algorithms books out of the library for fun as a child, and who had, that month, an extremely specific and still quite raw reason for wanting an ORM. I'll come back to that.

I didn't have an answer for him. I felt like a walking contradiction.

That's the question, by the way. That's this whole essay, asked of me at sixteen by someone who had no idea he was doing it. It took me about fourteen years to work out that "I want to know how it works" and "I'm glad I don't have to do it by hand" were never in tension, and that the second one is only on the table because a great many people did the first one extremely thoroughly on my behalf.

But it sent me looking, and what I found was that the thing I'd been doing on instinct since I was a teenager had a name, an author, and a documented alternative. I'd been writing Active Record code for years without knowing any of that, because it was simply how my framework worked.

Which is also the answer to why I was so late. The Django documentation was my bible. It was so good, and so complete, that I never once had a reason to look underneath it — and when I did want to go deeper I read the source, which is a different kind of worship pointed in the same direction. Documentation that good will keep you out of the literature for years. You don't go looking for a map of somewhere you were never lost.

For what it's worth, I did eventually find the edge of the pattern. Active Record is genuinely lovely for what most applications are: business objects with a reasonably stable shape, one to a table. It comes apart the moment you're pulling data with real structural disagreements into a unified form — not because the ORM is bad at it, but because it will cheerfully let you defer the hard part, and the hard part is normalizing the thing before it ever touches your primary system. Every hour I saved skipping that step got repaid later with interest. Ask me about 2022.

Then Rails and Django showed up

The mid-2000s is when these ideas got out of enterprise Java and into the part of the web where people shipped things quickly.

Rails landed in 2004 with ActiveRecord, named straight after Fowler's pattern, and made it look easy in a way Hibernate never managed. Django open-sourced in 2005 with a similar bargain and a bit more explicitness about it — you describe your models, the ORM writes your SQL:

class Post(models.Model):
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    published = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now_add=True)

recent = Post.objects.filter(author=author, published=True).order_by("-created_at")[:10]

Django is where I ended up living. I found it around 2010 and liked it right away. I did Rails too, on contract, the way you do work that pays — I liked it fine and I was glad of the money, but I never loved it, and I'm not going to pretend otherwise for the sake of a balanced paragraph.

I want to be precise about that word, though, because in 2010 I only liked Django. Loving it came two years later and it came for a deeply unromantic reason: it would not let me do the thing that had just cost me everything.

Which means I have to talk about migrations, and specifically about not having them.

Django in 2011 gave you syncdb. It would look at your models and create the tables, which felt like magic the first time. What it would not do is change a table that already existed. Add a field to a model that was already in your database and syncdb would look at it, decide everything was fine, and do nothing at all.

The correct answer was South, the third-party migration tool that everyone bolted on and that eventually got absorbed into Django itself. So even the framework I was moving toward had a hole in it, and the patch was something you had to already know to go and get.

I knew. I skipped it anyway.

The site in question was for a photographer friend who wanted his contact form to land somewhere he could sit and browse through, rather than dumping into an inbox where it would be lost by Thursday. That is four tables if you are being generous about it. South was obviously overkill for four tables, and I made that call in about four seconds.

Nothing dramatic came of it, and that's worth saying, because dramatic is not usually how this decision punishes you. It was a hundred small frictions instead. The column I added on the server and forgot to add locally. The evening where the two copies disagreed and I couldn't remember which one was correct. Every future change to that site slightly harder than it needed to be, forever, for no reason at all.

I want to flag that call, because I was about to make it again — on a much older project, for the same reason, where it cost me considerably more than four seconds.

Here is what it cost me.

The project was a game recommender, and it was a LAMP stack, because of course it was. PHP and MySQL and every habit from the previous section of this essay, carried forward intact. I'd started it around 2009, when I was thirteen or fourteen, got bored of it, abandoned it, and come back to it — so by the time it mattered it had been quietly alive for three years and had never once had anything you could reasonably call a schema management strategy.

Which raises the obvious question of why it was still a LAMP stack in 2012, given that I'd had Django for two years by then and have just spent several paragraphs telling you how much I liked it. The answer is laziness, and underneath the laziness something I find harder to admit: I'd been writing PHP for longer, so I trusted myself more in it. Familiarity felt like competence. Those are not the same thing, and the gap between them is the entire reason the rest of this section exists.

You told it what you'd played and enjoyed and it suggested what to play next. The datasets I'd scraped together from wherever I could find them, and those were replaceable. The part that was mine was the tagging — how you categorize a game, which tags imply which other tags, and how strongly. I spent months on those weights. Tuning them by hand, playing things specifically to argue with my own classifications, changing a number and seeing what it did to the recommendations. Somewhere between a hundred and a hundred and fifty hours, and almost none of it was code.

Which is exactly why none of it was protected. The weights weren't code, they were rows. And the rows lived in a database on a thumb drive, because that's where the whole project lived.

I was in the middle of fixing precisely that. January 2012. I had finally got a server, and the entire point of the server was that it would run backups — this was me being responsible at last, moving the thing off a piece of plastic in my bag and onto infrastructure that would look after it properly.

I wiped the weights during the move.

All of them. That schema had grown for three years, one hand-typed ALTER TABLE at a time, and it existed nowhere except inside the database itself and inside a version of my memory that turned out to be wrong. There was no tooling to disagree with me, because I hadn't chosen a stack that had any. Reconciling those two things destroyed the only copy of the only part I actually cared about. The backups whose existence was the entire reason I was doing this were, of course, not running yet. That's the joke. I lost it in the exact window between deciding to be careful and being careful.

I didn't rebuild it. I'd like to say I did. But a hundred and fifty hours of judgment calls is not a thing you retype, and whatever had been driving the project went out of it completely and never came back.

I've had worse outages since. Professional ones, with real money attached and other people's names on the incident report. Not one of them has come close. That was the most devastating couple of months I have had in this field, and it stopped me writing code at all for a while — which, given that writing code was the only thing I had wanted to do since I was eight years old and staring at a browser game, should tell you roughly how far down it went.

What I did do was decide that if I ever built it again, I was building it in Django.

Not for the templating. Not for the admin. For the protections — because Django had opinions about columns and tables, and three years of editing a schema by hand at two in the morning had left me badly wanting something with opinions. I wanted a system that would notice. I wanted to be disagreed with by software.

I told my friend that was the plan, about a month after I lost it.

That's the conversation. That's the one I've already told you about — the ORMs-are-lazy one, the one that sent me to Fowler. He said it to somebody who had just been shown, at a cost of a hundred and fifty hours, precisely what a database does when there is nobody minding it.

And I still couldn't answer him. That's the part I find hardest to explain about being sixteen. I was holding the entire argument. It was a month old and still warm. And I could not assemble it into a sentence, so I just felt caught, and went away thinking maybe he had a point.

Django shipped real migrations built in with 1.7 in 2014, written by the same person who'd built South, and by then I did not need convincing. That's the actual revolution in this whole story, more than the ORM part. Your schema stops being a fact that is simply true about the server and becomes a decision with a history — one you can read, review, run the same way in three places, and undo. If you've never worked the other way it reads as boring infrastructure. It replaced an entire genre of outage, and I would have given a great deal for it in January 2012.

And the reason I've never really left is the shape of the bargain, which I don't think Django gets enough credit for. The ORM covers ninety-nine percent of what an application needs and asks me to think about approximately none of it. Then for the other one percent — the backfill with a condition that has to be applied in a particular order, the index that needs creating concurrently, the change that has to run in three careful steps because the table is too large to lock — it hands me an empty migration file and gets out of my way. I can put whatever I want in there. Including, when it's the right answer, raw SQL.

That's the part my friend's argument missed, and it took me years to be able to say it properly. A good abstraction isn't one that hides the machine from you. It's one that handles the ordinary case so completely that you have your full attention available for the one that isn't, and then trusts you with it.

What we actually got out of it

It's fashionable to be a bit tired of ORMs and I understand the impulse. But it's worth being specific about what the detour bought, because it isn't nothing:

• SQL injection went from the default to an anomaly. Not because developers got more careful, but because parameterized queries became the easiest path. You have to go out of your way to write the 1999 bug now.
• Schema changes became reviewable code. Migrations in version control killed a whole class of deployment failure.
• Complex domain models became tractable. You can hold an application with three hundred tables in your head well enough to work on it, because you're thinking about relationships between objects instead of hand-writing four hundred lines of join.
• Types got involved. Prisma, Drizzle, sqlc — the schema generates types now, and a bad column reference is a build error instead of a page.

The applications we build routinely today wouldn't have been hard in 2003 so much as unreasonable. Not because the databases couldn't hold the data, but because the hand-written, hand-validated, hand-escaped glue would have eaten the entire team.

I mostly understand what that first one is worth by watching people who never had to earn it. There are developers I've worked with these past few years who have never written an injectable query and never will, because the tools will not let them hold it wrong. When I reminisce about it they look at me politely and have absolutely no idea what I am talking about. They didn't lose years to the lesson I lost years to, and that's not them missing out on something. That's the job working.

Which is, about fourteen years late, my answer to the friend who told me that ORMs were lazy coding for people who couldn't be bothered to learn how a database worked.

He was right that I didn't have to learn. He was wrong about what that meant. Somebody learned — several thousand somebodies, over three decades, in enormous detail — and then did the genuinely difficult work of packaging it up so that the rest of us could not easily get it wrong. Refusing that on principle wouldn't have made me a better engineer. It would have made me a slower one with more interesting scars.

I did eventually go and learn how it worked underneath. Two decades after the guestbooks I spent the better part of a year hand-writing SQL across school district databases that agreed on nothing, so that a teacher could find out in October which kids were in trouble — and every hour of that was easier for knowing what the ORM had been quietly doing on my behalf all the years I wasn't looking.

So it was worth learning. It just was never the price of admission, and I am glad nobody made me pay it up front, before they'd let me build anything at all.