I've recently been working on an dated side project which is built in node.js coupled with an ORM querying against Postgres.
Since picking this back up, I'm starting to reach the point where ORM is getting in the way and I'm now reaching back to plain SQL statements against my DB.
This got me thinking, do I really need an ORM anymore? What are the benefits I aimed to achieve at the outset of the project and do they still seem relevant now?
I took some time to see how the sentiment looked on google and found a few (some quite recent) blog posts suggesting ORMs are to be avoided, ditch the ORMs and get back to basics:
https://blog.logrocket.com/why-you-should-avoid-orms-with-examples-in-node-js-e0baab73fa5/
https://eli.thegreenplace.net/2019/to-orm-or-not-to-orm/
Since I build in Node.js I then ended up looking into the world of SQL query builders, namely Knex.js which a few of the ORMs out there also use. Hmm ok, now query builders do I really need this in my life?
End of the day, all of this has me thinking. I'm proficient and happy to write raw SQL. I find ORMs provide good structure whilst prototyping early on and adjusting the model as I go, but this may just all be an illusion, since I equally would have little issue doing this work directly in my DB and all things said I would probably push more of the work and design correctly into my DB rather than polluting my application with such decisions.
I'd like to get some views from the Dev community:
Are ORMs still relevant?
If deemed that SQL queries in your application are fine, I'd be interested to understand whether you bundle these into a service / db access layer in your application or you find yourself peppering them across files.
Have you found benefit in using a query builder such as the features offered by Knex?
Do you feel as though you are using an ORM knowing you are going to be breaking out into native SQL at some point? In which case, is that OK with you?
Top comments (8)
Ted Neward called O/RMs "the Vietnam [War] of computer science" nigh on fifteen years ago ("a quagmire which starts well, gets more complicated as time passes, and before long entraps its users in a commitment that has no clear demarcation point, no clear win conditions, and no clear exit strategy"). He was right then and he's still right now. People try, but it's impossible to cordon off the database entirely: it's a machine for organizing the very information your code manipulates, and you treat it as storage-and-nothing-more at your own peril.
I find query builders a little too barebones for my liking. I don't just want to generate SQL, I want to run it and operate on results; and I want to invoke and manipulate and organize around my data model through some kind of abstracting layer, the more convenient the better.
There's a happy medium, and it's treating your database like an API. You're going to write SQL at some point, but that doesn't mean you have to write all your SQL. You can generate the everyday stuff, you can make views and database functions accessible; if you've a mind to, you can even introspect the system catalogs and translate the schema into something application code can interact with. The first project I ran into that worked toward this was the Java data mapper MyBatis (then iBatis). In JavaScript, I develop my own for Postgres, MassiveJS.
The main reason people want to use ORMs is when using fully normalized parent-child tables more than a couple of levels deep. It is a legitimate pain to translate a decently nested object into relational tables and back. And ORMs can help you there.
However, I still feel the cost of being locked into an ORM's abstractions is too high in the long run. In those kinds of cases where I need to load and store a nested object graph as a single unit, I will just serialize it and store it in a single column. In Postgres you can even index and query on this kind of data when stored as JSON (with a column type
jsonb).I will use a so-called "micro ORM" in typed languages, which maps a row to an object. But I still write the SQL that returns the rows in the first place.
Take what I am saying with a grain of salt though. We make different trade-offs lately which avoids needing complex SQL queries in most cases. (We just replay facts into a new table to answer a different question.) But we pay for it with some architectural complications. Do what is best for your situation. :)
IMO if you like raw SQL (because like me you like to EXPLAIN ANALYSE then copy/paste), you'd better stick with Knex.
As for models, ObjectionJS looks cool, but you can write your own too. I may need it later on bigger side projects.
Right now, Knex solely is all I need to link my Node app to a Postgres database.
Personally I think that ORMs can be used in places where you just want to do a simple query( which is usually most of your app queries).
When we have features that require really complex queries, ORMs become slow thus we can fall back to good old SQL( in just those queries). I actually think it's quite painful and slow (development time wise) to use raw SQL everywhere in your application
Honestly I've seen this debate several times using ORM vs Raw SQL.
I totally prefer to use Raw SQL because it feels like I have total control over my data queries and it makes me a better developer IMO. Using ORM is also a good thing on the other hand, it can boost your productivity, if there are new versions of database, ORM maintainers will do the compatibility to work on new versions. The thing is, what if they stop maintaining the ORM? Just a possibility that it can happen. Anyways it's team's choice. ✌️
Does anyone have experience with having the most used queries as stored procedures on the database side?
Yes: it sucks, don't do it. Procedures/functions are great for manipulating large amounts of data in place, for implementing complex transactional processes, and for other sorts of heavy lifting or close-to-the-data work. They are not meant to encapsulate your
SELECT * FROM users WHERE ...queries and they're very bad at doing so.Many common queries are dynamic, which tends to be awkward in procs, but the principal reason this is a bad idea is that databases change much more slowly and deliberately than applications. If you need to filter users by another field from your application code, it's two seconds of typing and a deploy job away from production and all your teammates' dev environments. If you need to do it in a proc, every other copy of the schema is now out of date. Migration frameworks help, but only so much, and even minor migrations need to be handled with care.
Good question and definitely one that struck me whilst looking into this more.
If I move towards utilising more raw SQL, then really I've made some assumptions on the type of DB I'm going to be working against, therefore most of this could be moved to the DB as procedures or even views.
In some ways I can see that I'm dumbing down my application to be more concerned with working with defined interfaces, but I'm maybe losing some control / flexibility too.