I’ve been working on a project that uses sqlalchemy for database abstraction and as I was finding out how to express queries with sqlalchemy, a thought struck me:

All these query DSLs look really cool: sqlalchemy, Django ORM, Prisma, Phoenix/Ecto, Rails ActiveRecord. Some allow you to express relational entities as objects and classes and to issue queries by composing functions. Others are lower-level and have a more direct mapping to SQL statements (e.g. sqlalchemy or Ecto) but still use features native to your language that are then mapped to SQL operations.

Over the years I’ve seen two main issues with these database access libraries and I’m finally ranting and crystallizing them in a couple of thoughts.

First, ORMs abstract developers from the intricaces of the underlying data storage engine. This is a good thing because it allows them to just use the data store with no learning curve, right? The problem comes when coders don’t think about what’s underneath. It’s easy to assume you’re just creating objects and calling methods on them, but each of these operations has a cost. Countless stories about developers assuming method calls for related collections are free, searches are just RAM dictionary lookups and you can query by any of an object’s attributes at no extra cost, and then getting bitten by the fact that you have a relational DB underneath, are very common. My usual saying is that a developer must absolutely know the features, properties, antipatterns and best practice of the database anyway.

Second, unless you’re literally only ever going to code in a single language, those db abstraction libraries are more of a hindrance. If I’m jumping between Python, Ruby and Elixir projects I have to learn and keep current with sqlalchemy or Django ORM, ActiveRecord, and Ecto, each with their distinct syntax to express queries. If only there was a single common language with which to express RDBMS operations… guess what? it’s called SQL!

I think it’s likely beneficial to just use raw SQL queries for all but the most trivial of operations (and even then: I can more easily remember how to write a SELECT statement than how to express that in three different ways via native libraries). Most of those libraries have an easy way to take the result of a query and map that back to domain objects if you prefer to use those.

Another benefit is that it’s then very easy to switch database libraries; I was using raw aiosqlite in a project and it was fairly easy to switch to sqlalchemy while still using the queries I’d written for aiosqlite.