Breaking Free From the ORM: Replacing Database Queries
The final step to remove the ORM from our programs
TL;DR, Make your life easier by configuring data types transformations and data formatting from data going in and out of the database.
In previous articles, we looked at why object relational mapping is a detrimental construct, how to decouple database migrations from the ORM and how to handle database connections in our program.
An ORM usually combines three competencies in one. So, when we remove the ORM, we have to replace:
- Database migrations
- Database connection handling
- Database queries
In this fourth and final part of the series, we will explore how to manage database queries without relying on an ORM.
Database read, write and other operations
In this article, we’ll use python and PostgreSQL to illustrate the some of the concepts.
We now have the necessary components in place to write the database queries required to implement our application’s business logic. We also have access to use all database functionality without a mediocre adapter(ORM).
Once we start using raw SQL queries, we realize that we have shifted from the complexity of dealing with the ORM to the complexity involved in formatting the data going back and forth between our program and the database. The latter complexity is now much easier to understand, solve and debug.
When formatting data back and forth from the database these are the two main requirements:
1. Appropriate data types transformations between application and database
Every project has its own requirements. Here, some of the most common requirements that we usually have when working with Python and PostgreSQL:
UUIDs as unique identifiers in the database should be properly mapped to aUUIDPython data type.- PostgerSQL datetimes should be mapped to the Python datetimes using the
arrowlibrary. - The
jsonbPostgreSQL data type should be mapped to a Python dictionary.
To achieve those transformations we need to configure the database connector to handle those data types correctly. When using psycopg2 as the Python library to connect to PostgreSQL:
import psycopg2.extensions
from psycopg2.extras import Jsondef cast_date(value, conn):
return arrow.get(value)# Cast datetime to Arrow objects
NewDate = psycopg2.extensions.new_type(
psycopg2.extensions.PYDATETIMETZ.values,
'DATE',
cast_date
)# Handles datetimes transfo
psycopg2.extensions.register_type(NewDate)# Handles UUID transformations
psycopg2.extras.register_uuid()# Handles jsonb to/from Python dictionary transformations
psycopg2.extensions.register_adapter(dict, Json)
2. Manageable data format coming back from the database
The data usually comes back from the database as a collection of tuples. Each element of the tuple refers to a different field in the database. A collection of dictionaries where each key refers to the database column name and each value to the respective column’s value is a more manageable data format.
When using psycopg2 this can be achieved by passing a specific cursor option:
from psycopg2.extras import RealDictCursorwith conn.cursor(cursor_factory=RealDictCursor) as cursor:
cursor.execute("""SELECT id FROM users where id=1""")
result = cursor.fetchall()assert result == [{‘id’: 1}]
Plain Data vs. Objects
We now understand how to do database migrations, connect to the database and query the database without using an ORM. At this point, we receive plain data from the database and must decide how to treat that data. Communities around languages such as Java, Ruby or Python favor placing the data in objects. Other communities such as the Clojure community favor plain data structures.
By removing the ORM from our system we are now able to have these architecture conversations within the team, which was not possible before because the decision was already made by the ORM.
Building the system
With this final post, we conclude the series on “Breaking free from the ORM”. Over the course of these four articles, we have discussed reasons regarding why to remove the ORM dependency from our programs. We have also explored what tools and programming techniques we need to use to eliminate the ORM from our programs.
Warren Buffet and Charlie Munger run Berkshire Hathaway. Over the years, they have achieved slightly below average results on the ‘good’ financial years; and, done great on the ‘bad’ financial years. This has led them to realize tremendous financial success.
Breaking free from the ORM should yield similar results. In very straightforward cases, removing the ORM undoubtedly creates more work. In obscure cases, where “shit hits the fan” (sooner or later, it always does), not using an ORM will yield high dividends. This can largely be attributed to reducing the complexity in the system that comes from using an ORM and having code that is better understood and easier to debug.
By removing the ORM from our systems we are improving the system by increasing understanding and enhancing control of how the data tier is managed.
