Background and Reflections on the Vietnam Problem

While at DjangoCon around 2010 someone told me that using an ORM is like the U.S. going to war in Vietnam. That comment was in reference to Ted Neward coining the term and formalizing his comparison in his 2006 blog post titled The Vietnam of Computer Science.

The comparison resonated with me but I never considered abandoning the ORM. Drawing the completely wrong lessons from the analogy I spent several years looking for the perfect ORM and either through my own decision making or someone else’s I’ve been exposed to a veritable tour de France: Django and SQLAlchemy in Python, Active Record, Linq, Hibernate, Entity Framework, and most recently, Gorm in our Golang-powered backend at Bridge Financial Technology.

When we find pain in software development it’s important to lean into it. Make it hurt until you can’t stand it anymore. The more acute the pain the better you’ll be able to describe and identify where the pain comes from. Our pain caused me to reevaluate the so-called Vietnam Problem and question whether ORMs make sense at all. I won’t go until a full analysis of the mismatch between the object-oriented and relational world. For that I recommend reading Ted Neward’s timeless post. But I will summarize the important takeaways.

Vietnam: The Object-Relational Mismatch

I begin by oversimplifying the Vietnam war the way most historians see it today. The Vietnamese were fighting a civil war to unify their country. The United States was engaged in a proxy war with the Soviet Union and communism itself to prevent it from spreading. That is, the Vietnamese saw it as a north/south issue while the U.S. saw it as an east/west issue.

And the whole thing wasn’t worth it. Vietnam is a united communist country today (the Vietcong got what it wanted) without witnessing the spread of communist doctrine throughout Asia beyond China, which became a communist country in 1949 (the U.S. got what it wanted). And yet it spanned nearly 15 years, 3 presidential administrations and over a million casualties (across the U.S., North and South Vietnamese, allies on both sides and military and civilian personnel). The better strategy, ostensibly at any point, would have been to disengage entirely.

Shifting to software development: objects and relations are likewise fundamentally different things. They come from different places with different goals, and neither is wrong or bad. Of course technologies can be stack-ranked within these worlds and you’ll get more mileage using a stack that your team knows, respects and trusts. For us, we believe Postgres is the best-in-class implementation of a relational database (better than say, MySQL or SQL Server). Likewise we like and admire Go, particularly that it doesn’t implement object oriented design goals through inheritance. If you struggle with either your database or programming language I recommend fixing that problem first before solving problems at the intersection, or at least was our trajectory and we’re happy with the results. To flesh out the analogy below is an overview of these “sides”: objects, relations, the differences and problems encountered when they interact with each other.

Object Orientation Principles

An object oriented system aims to provide:

• Identity management: making a separation between the equivalence of state and the objects themselves. Object equivalence occurs when the values of two objects are the same, while identity equivalence occurs when two objects are the same, which is to say, they both point to the same location in memory.
• State management: the ability to correlate several primitives into a larger bundle that represents something about the world or problem.
• Behaviors: a collection of operations to manipulate said state.
• Encapsulation: the ability to define a simplified, exported surface area of the object to the rest of the system thereby hiding unnecessary details.
• Polymorphism: the ability to treat different objects homogeneously that can react in similar ways, despite being different things.

Most programming languages achieve these principles with an inheritance model. But it’s worth noting that many highly successful languages are indeed object oriented without inheritance: notably, Go, Erlang and Rust.

Relational Principles

A relational storage engine seeks to normalize data and document facts of the world. SQL provides operations to interact with data built up around set theory to ensure mathematical correctness and achieve properties during the mutability of the data; namely, ACID compliance. SQL operations executed within a transaction are atomic, consistent, isolated and durable. Normalization is typically achieved through proper design, the gold standard being third-normal, or Boyce-Codd, form.

(Some) Differences

These are completely different systems. Some of their differences include:

• Objects make sense with aggregated state, while relations seek to fragment it into multiple tables.
• Data mutability concerns in the object world are around concerns of accidental overwrite in a concurrent system and protected through encapsulation. Mutability in the relational world requires a transaction to guarantee correctness when state has been altered. There’s less concern to obtain correctness in mutating object state until the data is persisted.
• A collection of objects exists to enable behaviors across those objects, while a collection of relations exists to establish facts about the world.

Conceptual Problems

Below is a list of problems that stem from these differences.

The mapping problem. It’s difficult to map tables to objects using any mapper because of the following problems:

• An object relationship will be represented by composing one object with another. However, related objects rely on an implicit JOIN operation in SQL, otherwise related objects won’t be initialized with data.
• A many to many table in a relational engine involves 3 tables: the two sources of data and a third join table. However such a relationship only requires two objects with a list of the other.
• If your programming language supports inheritance, it may be tempting to model objects in that way, but there’s no IS-A type of relation within databases.

Who / what owns the schema definition? The programming language, and as such the the application developers, or the DDL of the database, and as such the DBAs? Even if you don’t have a distinction between these roles in your organization you’ll still have this problem, known as slaving the “O” to the “R” or vice-versa.

Where does metadata go? Most fields will naturally have a 1:1 correspondence. For example, integers, booleans, etc. will have well known types in the database and your programming language. But what about enumerables? Likely these are strings or ints in either with a limited number of options, and both the database and programming language can enforce constraints. So where do you place such metadata? The application? The database? Both?

All of these problems need a resolution by declaring one side (objects or relations) as the definitive authority. And that leads to a classification of ORMs- what is it’s opinion on that authority? Objects or relations?

Understanding and Classifying ORMs

In reality you can’t simply “exit” and disengage from the problem. The title of this post is somewhat marketing-motivated. After all, you’re not going to persist objects to flat files and call it a “database” and you’re not going to build applications in SQL. These things must meet at some point. But I argue that the common approach many ORMs have taken is limited and largely motivated by convenience, usually at the expense of one side.

Broadly, there are two types of ORMs: code-first and database-first.

• In the code-first approach you define object model definitions as classes or types that will map to database entities (one or more tables). This approach generates SQL on the fly and makes heavy use of reflection. An example in the Go community is gorm.
• The database-first approach typically relies on generating object definition code from your database definition language (DDL). A Golang example is SQLBoiler.

You’ll probably have an intuitive and gut reaction to one of these. And that view can be stubbornly-held. Personally, I always prioritized objects over relations and went with code-first ORMs. But ultimately I switched my thinking to prioritizing the database. That decision precipitated dropping the ORM all together by generating the code used to interact with the database.

Our Journey to Drop the ORM

The above points are all theoretical. Our journey begins with the practical pain and problems we started feeling from our ORM, Gorm.

Problem 1: Wrapped API

An early facepalm moment came from updating some records in the database. The SQL for doing this is:

UPDATE <table>
SET <values>
WHERE <conditions>

But the Gorm API changes the order in which values and conditions are accepted.

// intended
db.Model(<table>).Where(<conditions>).Updates(<values>)// updates the whole table, ignoring conditions
db.Model(<table>).Updates(<values>).Where(<conditions>)

We learned the hard way that getting this wrong has disastrous consequences. The ORM’s API is chainable but it’s not entirely lazy. Certain statements are finalizing, including the Update. If you reverse the order the updates will apply without the Where conditions taking effect, meaning you’ve updated everything in the model.

Now you can argue that SQL has this backwards and the ORM is simply making a correction to what the API should really be. And you can argue our team should have known better. Or that the API could have been better documented. Whatever the argument the result is the same: we had a bad day. And the broader point is this: an ORM is effectively a wrapper on SQL.

Early in my career an engineering manager taught me to be skeptical of wrappers, as they only add layers of cognitive load to the workload. I pushed back, arguing that, taken to its logical conclusion, he’d have all code written in assembly. Neither extreme is correct: abstraction is about achieving a balancing act. But as my career progresses the more I place the ORM in the unnecessary wrapper camp. Why are we dealing with a middleware that reverses the super stable and well known ANSI SQL API that all virtually all developers are trained on? Every developer knows how to update records in a table using SQL (or could easily Google it) and can expect an error when they get it wrong. Not every developer, in fact very few developers, know Gorm’s abstraction. And the same is true (among new hires) for whatever ORM you’re using; you’ll constantly be training people on a mission-critical section of the stack.

Problem 2: Performance and Excessive Memory Consumption

Our backend runs on a serverless stack (AWS Fargate) with limited memory capacity. Over time we kept having to increase the memory capacity of our instances, eventually reaching the maximum and then watching our containers die. As the data volume grew we saw the number of containers grow in somewhat linear fashion. One would hope the backend would instead scale sublinearly.

The ORM was a natural culprit: it’s easy to see that many ORMs will make use of object introspection to build SQL statements, hydrate results, or both. Gorm’s memory footprint is extreme although sadly not uncommon. Bridge started with Python on the backend and we used Django’s ORM to interact with the database which had similar problems.

We didn’t realize the extent of the problem until we ultimately removed it from our stack to give us a comparison point. The details are included in the next section, but as a preview: we increased execution performance by about 2x and reduced our memory footprint by nearly 10x.

Problem 3: Understanding our I/O Profile

Over time we noticed ourselves consuming database logging tools to understand our own use cases and recognized it as a mismatch. We had setup RDS Performance Insights on AWS and pganalyze to identify bottlenecks in the database. These tools proved their value early on and we ended up using them to learn about the ways in which we interacted with the database. Do we over-fetch columns? Are we running unindexed queries?

Of course these questions have known, definitive answers. The fact that we need an outside tool to shed light on the matter is an obvious structural flaw in the code. To me the underlying problem is that the ORM made it too easy to interact with the database. The code wasn’t centralized or modularized to a middleware layer in the codebase. Instead it was spaghettified throughout. Understanding our database interactivity required extensive code audits and reviews of stuff that had more to do with business logic than reads and writes.

Alternatives

The alternative to having an ORM seem fairly limited: use a low-level database driver, build SQL queries at runtime and map the results back into objects yourself. Of course an ORM does all these things in automated fashion, so going this route would be a huge sacrifice to maintainability. Our team concluded (fairly easily and without much decision making) that whatever benefits here, the costs are simply too high to consider it.

There is a third route, however: employing code generators to automate these steps. We bucketed projects in the Go community along two lines:

• Code-generating SQL at runtime (example: squirrel)
• Generating application code at compile time(examples: jet, sqlc)

Generating SQL code is an interesting idea, and requires less tooling and commitment than generating application code. However we felt it would be a lateral move in the maintainability of our code. SQL generation will require string interpolation, which means auditing the code as database migrations are applied, a labor-intensive and exhausting process we wanted to end.

Baby in the Bathwater?

We thought long and hard about whether we were throwing out the baby with the bathwater. Perhaps the problem isn’t ORMs per se, but only the code-first subset. In the Go community sqlboiler is an interesting project that generates model definitions from your DDL.

We decided against using this project, for the following reasons:

• There is such a thing as too much code generation. Generated code requires flexible configuration to control the output, which is a fine line to walk. On the one hand you don’t want to swap code for configuration and drop huge yaml or toml files in your codebase that require its own set of maintenance issues. At the other extreme if there’s something that you want to control or customize about the generated code that isn’t exposed in configuration, you’re out of luck.
• Sqlboiler is largely inspired by Active Record which we feel overly abstracts the database. We sought to embrace the database because, culturally, we’re a data-centric organization and wanted our database to be more transparent within our application and API.

Selecting a Code Generator

We looked closely at two code generators: jet and sqlc, ultimately selecting sqlc. With jet you write SQL within your application as a DSL. But because it generates code it goes a step beyond what a runtime SQL generator like squirrel offers. Models and fields and are first-class referenceable types, rather than requiring string interpolation, which avoids the need to grep through code in an audit process when you want to make changes.

Even more appealing, it offers a way to aggregate, or de-normalize data in a database. Whereas an ORM’s goal is to make relationship traversal easy, Jet’s goal is to provide a package of data in a complete and well-typed struct that clearly advertises what’s available within it. Here’s an example:

stmt := SELECT(
    Actor.ActorID, Actor.FirstName, Actor.LastName, Actor.LastUpdate,
    Film.AllColumns,
    Language.AllColumns,
    Category.AllColumns,
).FROM(
    Actor.
        INNER_JOIN(FilmActor, Actor.ActorID.EQ(FilmActor.ActorID)).
        INNER_JOIN(Film, Film.FilmID.EQ(FilmActor.FilmID)).
        INNER_JOIN(Language, Language.LanguageID.EQ(Film.LanguageID)).
        INNER_JOIN(FilmCategory, FilmCategory.FilmID.EQ(Film.FilmID)).
        INNER_JOIN(Category, Category.CategoryID.EQ(FilmCategory.CategoryID)),
).WHERE(
    Language.Name.EQ(String("English")).
        AND(Category.Name.NOT_EQ(String("Action"))).
        AND(Film.Length.GT(Int(180))),
).ORDER_BY(
    Actor.ActorID.ASC(),
    Film.FilmID.ASC(),
)var dest []struct {
    model.Actor    Films []struct {
        model.Film        Language   model.Language
        Categories []model.Category
    }
}// Execute query and store result
err = stmt.Query(db, &dest)

There’s a lot of data aggregation going on here. The application-side model being built up is an actor with all the films they’ve been in, the language the film is featured in and the categories its classified under.

We were initially attracted to this design, but after experimenting a bit it didn’t quite feel right. In this example the query is driving the data model within the application, rather than the other way around and we feared this approach would lead to a lot of throw-away aggregate models. Our objective was to promote reusable models with lots of business logic and mutability captured in methods on their types.

Moreover our preference was to move SQL out of code entirely. The problem here is that any developer can simply query the database however they wish. While this is an initial productivity win, it’s at the cost of long-term maintainability of both the code and runtime performance. What if a developer queries the database sub-optimally, without using an index? That risk is high as the data model becomes larger and complex since it’s a step removed from the SQL. And while the DSL is appreciated, we still felt it was wrapper-like in the end.

The Answer: sqlc

We decided to use sqlc, a configurable opt-in sql compiler. The approach resonated with us; we like that it doesn’t generate things you don’t ask for, and the resulting generated code can be tailored to the types and tags we’ve defined. It makes the code feel like ours while providing an obvious path to migrate our current implementation. I’ll elaborate on the details of how we made sqlc work for us in a future post.