This article is part of a series on ORMs in general and SQLAlchemy in particular.

TL;DR

• Use the ORM query generator, but not ORM mapped objects for read transactions.
• Use ORM mapped objects and sacrifice some efficiency for write transactions.

ORM or Object Relational Mappers are libraries that handle the mapping of relational database rows to object in object oriented programming languages. And they suck. Ted Neward says it more eloquently, more accurately, and with more grounding in hard-won experience, in this excellent ticket:

The Vietnam of computer science. http://blogs.tedneward.com/post/the-vietnam-of-computer-science/

Have you read it? If you have not, go read it. It's better than what you are reading now. Then, if you have the time, come back here.

To summarize it shortly: you cannot really use relational database to store objects. And the more you try, the deeper into trouble you will get. The relation gap is there, and no amount of code will be able to fill it. But you can build bridges.

ORM are a good example of Leaky Abstraction, like Remote Procedure Calls. If you are born after 1990, you have probably not heard of RPC much. They used to be all the rage until around 2000. Then engineers got to actually experience the practical internet. And RPC turned out to be a pretty bad idea. If you are talking over the network, you need a protocol. But I digress.

As a software craftsman, I try to always cut into my problems along the wood. When I encounter unexpected resistance, I look for ways to cut the problem into different pieces. Clearly, trying to cut out transparent persistence of objects in a relational store does not work. So what were the problems we were trying to solve when we started using ORMs? Here's my perspective:

• Building SQL queries in some way that is easier, more flexible, and more expressive than string interpolation, or even generic template languages.
• Reducing the boilerplate code involved in processing database output.
• Using the attribute assignment and traversal syntax of our object oriented language to create, process, and update complex structures that span many tables.

Now if you squint really hard, you might be able to see the outline of the idea.

Databases have many different uses. But here I am focused on one use case, which I think is the most common by far today: web applications that use a relational database to store their domain data. User accounts, blog posts, customers, candidates, readers, comments, workflows, ratings, uploaded files metadata.

So in our web application, there is usually two very different access patterns.

• Read access. Listing, search, presentation of domain objects. That can involve large collections of objects, but relatively few queries. These operations are the most common. Even a write-heavy system will have around 90% of those read only operations. A read-heavy system can have a million times more reads than writes. Read transactions need to be fast.
• Write access. Creating, updating, deleting domain objects. Here there are usually only a few rows affected on each table, but you sometime end up using objects of a dozen different tables. Writes do not need to be as fast as reads.

Writes need to be fast too, bot not as much as reads. Even the user is not in the same frame of mind in each case. Read access occur when the user is browsing, searching, clicking around for the data. The faster, the better. After all, if the data is there, why should there be any delay in accessing it? But write access occurs when the user is performing an action, so a fraction second more latency is acceptable, and is even expected. Have you ever heard or said: "Hey! That was so fast I am not sure that worked!"

A good ORM can help you a lot for both use cases. But each use case will be best served by different abstractions, from different parts of the ORM.

However, there is one thing where an ORM will not help you, and might even actively hinder you: good relational database design. A lot of the badness caused by ORMs come from encouraging programmers to produce bad relational design in an attempt at reusing familiar object oriented design patterns.

So first, look at your database, your SQL schema. This is your bible. The written record of all truth in the universe, the revelation to your unworthy pile of pasta code (spaghetti, ravioli, lasagna, pick your flavor), sent down by godly users and customers. Your database is the crown jewels, the single point of truth, and SQL is your direct conduit to this most holy place. Complete with obscure arcana, mysterious rituals, and all-consuming madness. The database rules.

Once you have figured a relational model that will solve your problem, code it, as SQL. I do not believe in having your ORM generate your DDL or your migration scripts. Sure, maybe your specific framework can handle it fine, for all 90% of the real word situations that your framework can handle. But at the end of the day, this extra layer does not carry its own weight. Unless, maybe, if you have many database instances, which could be using different past schema versions, but then you are not really in the use case I am talking about: you are producing packaged software, no a web service.

Now, write your table classes. I call a table class, a class that directly models a relational table. Here's the trick: use object oriented programming to model your relational database. Do not try to use the database as a backend for your object design. And please, for the sake of all that is good, do not use database introspection to build your table classes at run time. Being able to compile and load your code without database access is a Good Thing. It helps with code analysis. It helps with unit testing. So translate the DDL operations as table definitions.

That will produce duplication. Your database schema will have its idea of what tables exists, with which column names and types. And your application code will have its own idea, in the form of the table class definitions. But this is useful duplication, it is documentation, and the best kind: executable documentation. The rest of the application code needs to have its own idea of the schema, and one that is often a lot more implicit, encoded in assumptions that underly the code. Your table classes document those assumptions.

Then write your read-only transaction code using the SELECT query generator of your ORM. Sometimes query generation will become very complex. Search engines with configurable output columns and sort orders will do that. You will need something that can abstract over queries and add joins, filters, and subqueries. If your ORM does not provide it, you will have to code something half assed that fills in the missing parts. Here, start from the actual SELECT query you would write if you did not have a ORM: this should be the most efficient way to get the data you need: after all, SQL is mostly about this. If you do not have a lot of experience with both SQL and our ORM query generator, start by actually coding the SQL query. Then translate it to the query API of your ORM. Do not load columns you do not need. Do not produce objects that will implicitly execute database queries to load missing columns or traverse associations.

There, a tension will appear. By working at the SQL level, your read-only application code will need to know about the database schema. Does that look like poor separation of concerns? Yes, it does. But do you need to perform this particular, highly efficient query somewhere else? If you don't, then do not try to abstract it out. You are writing data presentation code, if that query is not useful somewhere else, then this is the single point of truth on how to present this data. No need to add any abstraction here.

Now, if this particular query is useful somewhere else, then by all means, factor it out. But keep it separate from your table class code. Keep your table classes focused on modeling your SQL table as classes. And use another object model to represent the particular query of interest. It's probably a SomethingCollection, or a SomeOtherThingSearch, or a ThingyQueryBuilder, and it's going to use several table classes, and usually only parts of each.

Then the application code creates, updates and deletes data. The execution of a write transaction will usually involve three phases:

1. Access control. Checking whether the authenticated user can perform this specific change on those specific objects.
2. Figuring out what accually needs to be changed. For example, if the transaction actually makes a change, then external systems might need to be notified: external search engine, pubsub buses, anything.
3. Updating the SQL database, with INSERT, UPDATE and DELETE statements. But those statements needs to be executed in a specific order, that depends on the object structure and the foreign keys.

If you go full relational, you can collapse all three phases in a single phase. But except for simple cases, this will cause you a lot of pain. You will need to figure out the specific order in which to performe your INSERT, UPDATE and DELETE queries, and ORMs are pretty good at saving you this specific kind of hurt. So, do not.

Writing complex object structures to the database is best performed by the ORM. But for that, you will need full mapped objects: objects that model database rows. Phase 2 is easier to write and test by manipulating mapped objects. And Phase 1 is a good opportunity to actually load those mapped objects, because you need anyway to query the database to control access.

So, for write transactions, use the actual ORM. Load full objects from the database. Modify them, create complex object graphs as required by your relational design, and let your ORM find out the right way to flush them out to the relational database. But keep in mind that those object represent database rows, not domain objects.

Once again, if different parts of your application need to perform similar write operations, do not hesitate to factor them out. But outside of your table classes. Here you probably need a SomethingWriter, with a class structure that models the different ways you need to do the writing.

By following this approach, you can get both highly efficient operation, and good object oriented code. And you will produce code with high quality decoupling. Not decoupling based on an idealized notion of the objects you are operating on, but decoupling based on what data processing your actually need your application to perform. Decoupling based on code behaviour, not on diagrams.