Object-Relational Mapping

Code and databases hold data in different shapes. A relational database stores values in tables made of rows and columns, while a program treats one member as a single object with properties like name and signup date. Every place the two meet, something has to translate, and the two shapes rarely line up one to one — one object often spans several tables, and one table feeds several kinds of object.

That translation used to be written by hand. A developer wrote a SQL statement as a string, sent it off, then walked the returned rows one at a time — first column into name, second column into signup date — copying values into objects. A single screen that read member details carried thirty lines of that, and not one of those lines expressed a business rule.

The cost showed up on the day something changed. Add a phone number column to the member table and every scrap of translation code scattered across the list screen, the detail screen and the edit form had to be found and updated. Miss one and that screen showed a blank; miscount the column positions and the phone number landed in the signup date field. None of this stopped the code from compiling, so nobody found out until someone ran it.

Object-relational mapping (ORM) exists to write that translation once and hand the rest to a tool. Declare in one place how tables map to classes, and the reading and writing code is generated from that declaration instead of typed out per screen.

There are four jobs the tool takes over.

1. Mapping. One table pairs with one class, one row with one object, one column with one property. When a phone number column appears, you add one property to the member class and you are done — no hunting through screens. Relationships are declared the same way, so a member's documents arrive as a list on the member object.
2. Querying. Queries get written as code instead of strings. Describe "members who signed up last month and have never paid" as a condition object, hand it over, and the tool produces the matching statement. Conditions written in code catch typos before anything runs, and values typed by a user are kept separate from the statement itself, which closes a whole class of injection accidents.
3. Change tracking. When you modify an object you loaded, the tool remembers what changed and writes only those columns back. If several objects changed together they go out inside a transaction — a bundle that either commits entirely or is undone entirely — so a failure halfway through doesn't leave half the update saved.
4. Structural change. Edit a class definition and the tool drafts a migration describing how the table should change. Deploying code replaces the old code, but data stays, so moving the million rows you already have into a new shape is separate work. The tool writes the first draft of that and a person reviews it.

Put together, the thirty-line member lookup from earlier becomes two or three lines, and the lines that disappeared were mostly the ones mistakes used to hide in.

Three options keep the queries in your hands.

1. Write the queries yourself. You author the SQL and use a thin library only to turn returned rows into objects. What goes to the database is exactly what you typed, which makes performance predictable on the screens where it matters. In exchange, you find and fix every affected statement yourself when the schema changes.
2. A query builder. Statements are assembled in code, but the code maps almost line for line to the statement produced. It hides less than an ORM and is safer than gluing strings together — a middle position teams land on often.
3. Mix the two. This is the common choice in practice. Screens that read and write one table go through the ORM, and the reporting query that joins six tables is written by hand. Most tools deliberately leave a door open for passing raw statements through.

Two things usually decide it. The first is what the screens do: if most of the work is creating, reading, updating and deleting single records, the ORM saves a lot, while an aggregation-heavy product suffers from statements it can't see. The second is the team: an ORM prevents mistakes when few people are comfortable with databases, and it delays discovery of problems when nobody ever looks at the statements it emits.

"Don't pull this list through the ORM — write the query. It's firing over a hundred times right now."

The generated statements are inefficient, so this one screen should be handwritten.

"The member table has a new column, so update the model and generate the migration."

Change the class declaration, and prepare the procedure that reshapes the existing table too.

"Even with an ORM, someone has to look at the actual queries in the logs now and then."

What the tool hides needs checking on a schedule.

Pagedeck keeps documents, members and permissions in a relational database and works through an ORM. Most of Theo's new features end at adding a property to a class and writing two lines to save it. The exception is the usage statistics screen, which groups document counts and edit counts by team and month: the generated statement took eight seconds, so that one screen uses a handwritten query and now takes 0.4 seconds.

What Theo checks before shipping is the number of queries a page fires. Three when a list screen opens is fine; sixty means reading the code again. With an ORM, two lines of code turning into sixty queries leaves no trace on the surface.

The most common mistake is concluding that an ORM means you don't have to understand the database. You never type a query, so it feels that way. But the tool only writes the statements — which statements it writes depends entirely on how the code is written. Fetch 100 rows for a list and then read each author's name from the object, and 101 queries go out. That is the N+1 problem, and in code it looks like an ordinary loop. Left alone, ten times the data makes responses a hundred times slower rather than ten, and tracking down why takes days.

The belief that ORMs are slow misses in a similar way. What's slow is the statement, not the tool. The same statement written by a human is just as slow if the column being filtered on has no index — the prepared lookup that works like the one at the back of a book. Removing the tool doesn't fix it; reading the emitted statement and then adding an index or changing how rows are fetched does. Checking the statement before suspecting the tool settles most of these cases.