What ORMs do
ORM libraries typically must take care of multiple things behind the scenes. They include but are not limited to:
- Representing the domain model in the application
- Parsing the SQL query results from raw objects into the domain objects
- Generating SQL statements for querying and modifying the data
- Managing transactions and isolation levels
Let’s cover these one by one and see what can go wrong.
Domain model in the application
Domain model in the Object Oriented Application can be either stateful or stateless.
Stateful objects typically have some behavior. They modify their state via domain methods that do business processing.
Stateless objects are just bags for data. They are created in a transaction script. Objects do not change their state on their own, but are rather handled in units of work and transactions.
Mapping from domain objects to the ORM logic is typically done via annotations or attributes. Each field is decorated with attributes describing how it should be handled on the database side - what column type to use, how to configure keys, relationships between tables, etc. We may put these annotations on the domain objects directly, or we may decide to have an in-between model for entities only. The latter also allows us to separate the model from the persistence infrastructure, and is thus highly recommended.
However, domain models cannot be mapped to the entity model easily. This is called Object-relational impedance mismatch. We may face issues like:
- Incompatible columns - we may not be able to represent database types in the OOP world and vice versa. For instance, text data may have different encoding, collation (order of characters in the alphabet), or comparison rules (whether lowercase and uppercase letters are to be considered the same or different)
- 1 to 1 and 1 to many relationships - they are always tricky when it comes to lazy loading and to eager loading
- Inheritance - there are multiple ways to represent the inheritance hierarchy (table per class, table per concrete class, table per hierarchy). Performance may heavily depend on the way we structure our data in the database because of joins and filtering we need to perform
One typical issue is n+1 queries. Let’s take the following schema:
We have Aircrafts. Each aircraft may have multiple Seats. We would like to get seats for every aircraft with the following code:
This may generate the following queries:
We get one query to get all the aircrafts, and then additional n queries to get the seats. This could be reduced to one query, though:
To get this faster query, we need to configure the ORM library to work in eager mode. This may not be a good idea to do it globally, and can’t be done blindly.
Parsing the results into the domain objects
Parsing the results by the ORM library is not as trivial as it may seem. This may especially be a problem when dealing with nested collections and related entities. Imagine that our entities have multiple unrelated details. For instance, the user entity may have details, questions, peers, etc. Let’s take the following code:
This may generate the following query:
This query may easily run for 30 seconds and return 300 thousand rows that are later flattened into one aggregation root by the ORM. However, if we split this into multiple queries like this:
And this will generate the following SQL queries:
While this generates many more queries, they are ultimately faster and can finish in one millisecond.
We can see that splitting one query into multiple may be very beneficial to the performance.
Generating SQL statements for querying and modifying the data
Generating an efficient query may be challenging. One example is the use of Common Table Expressions (CTEs). We have the boarding_passes table with nearly 8 million rows. Let’s say, that we run the following query:
It takes 13 seconds to execute. However, if we don’t use the CTE and encode the hashing directly, as in:
This query takes 8 seconds to complete. This is a nearly 40% improvement in the execution time.
However, some ORM operations can’t be translated into a single SQL query that easily. Let’s take Sequelize's findOrInsert. It first sends a SELECT query like this:
When this returns no rows, then the following function is created and executed:
What’s more, this may still fail because of race conditions.
Other operations may result in even more sophisticated queries. Generally, using simpler functions is recommended to avoid the complexity.
Managing transactions and isolation levels
ORM must handle transactions. It needs to begin and terminate them when the time comes.
However, transactions may have isolation levels that can change the result of our queries and affect the performance. MySQL uses REPEATABLE READ by default, PostgreSQL goes with READ COMMITTED, DB2 uses Cursor Stability. Most ORMs use the database default isolation level, so this depends on the database we use. This means that our code may behave differently when a different database is used.
However, ORM can reconfigure that and use different settings. This can be buried down in some configuration that we’re not very aware of. This may also change between ORM versions. Some libraries don’t use transactions at all. And keep in mind that we may get duplicates when using READ COMMITTED.
The rule of thumb is to manually set the isolation level and keep it consistent. Do not rely on defaults, do not leave this unattended.
Migrations must be understood by the ORM. Either we control migrations directly in the ORM (and the ORM makes the changes in the database), or we just modify the domain in the ORM and the library figures out how to handle that. If we have multiple applications with incompatible technical stacks, then managing migrations may be harder.
Some libraries store the list of migrations executed against the database. They typically create a new table with the history of migrations. This works great with one application only, and may break when multiple applications are in place.
Some other libraries assume our migrations are idempotent, so they can be executed many times. This makes managing the migrations much easier (we can always rerun them and nothing breaks), but writing such migrations is more challenging.
How to manage transactions efficiently? Some ideas to may consider:
- Keeping migrations separately from the ORM - to allow for multiple heterogeneous applications running against the same database
- Keeping migrations in plain SQL files makes it easy to analyze them and use with different tools
- Migrations should be numbered in a consistent way to be executed in an order
- Implementing migrations as idempotent operations. This simplifies the management
ORM libraries simplify multiple things. However, they also introduce additional layers of complexity that may result in performance drop and issues on their own. Developers need to understand the consequences of integrating these libraries into their codebase. Metis can help a lot with observability and troubleshooting.