Objection to ORM hatred

Whenever someone asks which Node.js ORM they should use, one of the first answers is always some version of “don’t use an ORM, just write SQL”. This answer is then usually attacked with an opposite opinion along the lines of “you need to use an ORM to hide all that nasty SQL”. People always seem to ignore the third option: using an ORM that embraces SQL!

I completely understand where the hatred comes from though. There are real problems in many ORMs, that Thomas Hunter II summarizes well in his post Why you should avoid ORMs. The main points Thomas listed were

  1. You learn the ORM, not SQL and that knowledge is usually not transferable to other tools.

  2. Complex ORM calls are inefficient. ORMs have their own object-oriented query language which they try to convert to SQL, and it’s usually really difficult.

  3. ORMs can’t do everything. The object oriented approach of most ORMs doesn’t map well to SQL. For many SQL operations, there is no object oriented equivalent.

These problems arise from the fact that most ORMs are designed to abstract away SQL in favor of some object oriented interface. But not all ORMs are like that!

I created an ORM called objection.js for exactly the reasons Thomas listed. The design goal of objection.js is to allow you to use SQL whenever possible and only provide a DSL (Domain Specific Language) or a custom concept when something cannot be easily done using SQL. I’ll get back to objection.js soon. First, I’ll introduce you to the most common argument for avoiding ORMs:

It’s easier to write <something> in plain SQL than using an ORM

This argument is usually followed by a contrived example like this:

Yes, if you only need a flat list of items from one table at a time, by all means, don’t use an ORM! Of course you can use joins, subqueries and multiple queries to access related items in other tables, but that easily becomes tedious. For example, here’s a very basic query that joins a many-to-many relationship (children of a person) and a has-many relationship (pets of a person)

That’s already quite a bit of SQL. What if you want to join more relationships? How about nested relationships? You also need to repeat that every time you want to fetch the related objects. Not to mention the result of that query is a flat list of items and not a nested tree of objects.

The next thing any ORM hater would do is to use a library that converts that flat result list into a nicely nested object tree and then proceed to writing a bunch of helper functions for adding those joins to queries so that you don’t need to repeat yourself. You quickly realize that most of these helper functions look the same and only the table names and foreign keys are different. Then the obvious choice is to go ahead and define the relationships in a single place as objects and create a generic function that takes a relationship description object and outputs the needed joins.

Congratulations, you’ve just reinvented the ORM!

In my opinion, that’s all a good ORM is: a set of tools to make using SQL easier! It allows you to fully use all the features of SQL and the underlying database engine and only provides helpers and custom solutions to things that are difficult to achieve with just plain SQL.

 

Objection.js

Here’s how you would write the aforementioned many-to-many join query using objection.js:

With objection.js, you always work with a query builder. You can build any query and use as much SQL as you want but it also helps you to deal with the repetitive stuff like relations if you want it to. The leftJoinRelation method is given the names of the relations you want to join and objection.js uses the relation mappings to create the joins.

Now you may be thinking, “that’s not much shorter and I still need to define the relationships somewhere”. Well yeah, but you only need to do that once. If your project is so simple that defining the relationships and models takes a significant part of your development time, it doesn’t matter whether you use an ORM or not.

The joinRelation query above is 100% equivalent to the previous SQL query and would still produce a flat list of result rows. You can easily get a tree of objects using the joinEager method:

Loading nested relations is just as easy:

And in the next code block is the minimal SQL needed to carry out that query. The selects with aliases are needed to be able to build the object tree from the flat list.

For many ORMs, SQL concepts like subqueries are difficult or impossible to write. Since all objection.js operations return a query builder, writing subqueries is just as easy as using plain SQL. For example, fetching all people that have at least one pet called ‘Fluffy’

The SQL equivalent is:

But there’s also an easier way to write this in objection.js if you have defined the pets relationship for the Person model:

But that requires you to learn the ORM (which was one of the arguments against ORMs). You can still start by writing the SQL solution you are familiar with. Once you learn more about objection.js, you find helpers like this that improve your productivity.

Here’s another example of using the relatedQuery helper

 

If your application is simple and you rarely need to deal with nested data and relations, using plain SQL or a query builder like knex can be a good option. However, in most applications you want to work with nested data and a lot of relations and an ORM can be a helpful tool. With objection.js, you don’t need to make a compromise. You get the flexibility of a query builder and the relational power of an ORM in the same package.

If you found any of this interesting, take a closer look at objection.js.

Some links to get you started