Anil's Blog

ORMs and SQL

Anil Vaitla's photo
Anil Vaitla
·

2 min read

Some benefits of ORMs:

  1. They avoid string mangling when building dynamic queries. Some complex string concatenations are actually more easily expressed in an ORM than fragile string builders:

    def f(q, user=None, date=None, reverse=False):

     if user:
     q = q.filter(user__email=user)
 if date:
     q = q.filter(date__lt=date)
 if reverse:
     q = q.reverse()
 return q

  2. They can help with the mapping of data objects. In certain cases it's easier to use domain specific language objects over dictionaries. In strongly typed languages this may be a very important value add. This also makes it much easier to develop safely in IDES that support object inspection like intellij or vscode.

  3. Helps avoid SQL Injection and can bolt on useful middleware (like ledgering and auditing in one central place).

  4. They can help auto generate code like graphql endpoints due to you having to define the model classes and annotate metadata up front for them on what fields are useful for what and how they are joined together.

I have heard very good things about Ecto in Elixir, and there are even useful string builder libraries that don't require pulling in full model mapping classes: pypika.readthedocs.io/en/latest/index.html

Some reasons to prefer raw SQL:

  1. Raw SQL can make it easier to find the culprit of a bad query. 'SHOW PROCESSLIST' output can be directly searched on github. When there are poor performing or deadlocked queries, it's helpful to be able to search parts the query across the code base and find the source quickly. With an ORM this is no longer possibility as the query is code generated.

  2. Complex multi level joins / transactional sometimes necessitate raw sql generation (have you had to force a full join instead of an inner one?) - If you're already having to write significant sql or half way sql, why not just use SQL throughout. One language is easier than 2 to grok between and being consistent is useful.

  3. ORMS change language by language / framework by framework - you will need to understand the intricacies of the SQL generated case by case which takes time, whereas SQL is roughly the same wherever it's written.

Ultimately try both for your team and see what you prefer. ORMS still require you to understand SQL since you won't have them when you're on a raw shell or raw sql runner, there's no getting away from that but ORM's can add benefits around type safety and correctness checking (so it's not a one or another but can be complementary).

SQLMySQLorm