Ask HN: What happened to the ORM?
After all those years, I sometimes feel like the whole ORM thing hasn't made progress or been settled. Which ORMs do you like and what do you like about them? What is the sweet spot? I'm asking this because database I/O has always been my biggest frustration when doing application programming. These days, I do more functional programming and tend to side with the ORM haters but I do occasionally wish I could just abstract out the database more. Anyways, I'd just like to hear HN's thoughts on this.
112 comments
[ 3.0 ms ] story [ 167 ms ] threadI find an ORM really excels when you've got complicated business logic, the sort of thing where there are dozens of business rules that may or may not be involved in any given installation. Something like this is extremely stateful and and ORM does a great job of tracking this state. In fact, this is what I'd define an ORM as, a state tracker for database modifications. If you don't have much state then you probably won't get much out of an ORM.
Even when using an ORM does make sense, it's important to remember that they don't make sense everywhere. For a performance critical or particularly complicated query then SQL should still be a fallback.
I never understood the ORM hate. Every place I worked we intermingled raw SQL when needed. Hibernate has a way to clear query caches so you can use raw SQL when you need to. You can just write raw SQL exclusively if you want within hibernate so I don't get how you could lose anything :) .
Still, my experience is mostly with Hibernate. It's extremely mature, meaning reliable, feature complete, and only 0-30% slower than raw queries in most cases. It makes adding support for things like multitenency and audited tables a breeze without losing database agnostic behavior. It makes database upgrades a no-brainer 95% of the time too. It has a built in memory caches that help enormously with common queries. Probably the biggest thing is it makes the database strongly typed so it's possible to refactor. Code refactoring in Java is easy but raw stringified SQL is nearly impossible to fix in any language.
I think the biggest counterpoint to ORM is shitty ORM. Things like SQLAlchemy generate downright horrific SQL slowing everything to a crawl and causing deadlocks. Another honest counterpoint to ORM is the learning curve. Everyone is taught SQL but the ORM is more abstract and harder to reason about, not a fun thing to learn.
TBH I think most ORM's are just poorly done. Putting an object abstraction on a relational database is hard. The only ones I've enjoyed for completeness and performance are Hibernate and to some extent Entity Framework. EF being the easiest to use but a bit slower with less features.
I have heard good things about Dapper but never used it. I like the idea of injecting a SQL DSL into the language itself, wish it was more prevalent.
Not my experience... Would you care to explain ? SQLA works pretty well for me, as long as I stay in rather simple queries. For example, data analysis queries are next to impossible to express with SQLA, but that doesn't matter much.
For the simple update queries, it just works fine for me. It also allows for very good control on relationships, etc.
One of the things I've recently prioritized is the ability to run arbitrary queries (generally read-only) at some sort of REPL prompt, for debugging and investigative purposes. ORMs and their offshoots offer a great deal of support in that area because you can converse with an ORM in a domain-specific way without dropping into raw SQL. That can be a big advantage.
And not just because Java is an odious, ugly language, but the SQL Hibernate generates I just didn't like.
Mind you, this is with complex queries. For 90% of them, I didn't notice any difference between them, as far as generated SQL goes anyway.
- Avoids mistakes when dealing with writing raw SQL queries (SQL is quite repetitive in practice)
- The declarative nature of classes maps well to types and relationships
- The declarative nature of classes maps out well to tables, even with polymorphism [1]
- Keeping "Models" in an ORM often maps out well to migration utility (Alembic, Django Migrations)
- Object-chaining map very well to queries
- ORM objects can be reused and composed
- They can abstract out intricacies across SQL dialects
- They can potentially make it easier to migrate to different SQL servers if no specialized features were used
- Can help avoid common security vulnerabilities like SQL injections
- When something can't be expressed via ORM relationships, they tend to allow the dev to drop down to raw SQL. In the case of SQLAlchemy, there is a core query language [2], too.
- In the case of Django, QuerySet is used as a standard throughout extensions that power a whole community. Plugins that don't even know each other (e.g. django-filter and django-tables2) can operate on the same django queryset to filter/search and sort/display data.
I mention QuerySet/Django ORM quite a bit in a recent blog post at https://www.git-pull.com/code_explorer/django-vs-flask.html.
[1] http://docs.sqlalchemy.org/en/latest/orm/inheritance.html [2] http://docs.sqlalchemy.org/en/latest/core/
This isn't remotely true; it turns what looks like an in-memory access into a network round trip. Navigating your database using an idiom of lists and member accesses is a fast path to n+1 queries all over the place, or ORM tuned to eagerly fetch the whole world when you only touch a tiny piece of it.
The closer an ORM's API is to a monad, the happier you'll be. Fundamentally, accessing the database is executing remote code; the more you can package up into the query before it goes off and does anything, the better performance you'll see.
IMO trying to shoehorn objects (in the OO sense, with polymorphism, data hiding and behaviour) into a database is wrong-headed. Data hiding in particular is a wrong mental model for thinking about facts in the database, and the more is hidden, the harder it will be to reason about performance and bulk remote operations generally.
Only if you use naive ways of doing this. In .NET at least your overcomplicated expression can be compiled down to the minimum query needed to pull the bits of data you use.
At least with EF it's possible to completely disable lazy loading. I always recommend doing so -- when you're passing entities between methods it's pretty easy to lose track of what's implicitly 'loaded' on the object. Innocent changes to a method that operates on an entity can cause a database round trip...horrible idea.
That isn't true for e.g. Django's ORM, which lazily evaluates the query and only actually accesses the db after that, with a single query, and filtering done in SQL.
Also, Django ORM documentation is very clear and you can use Django Debug Toolbar to analyse the raw sql generated.
The queries that most tend to be making just aren't that sophisticated. Relationships tend to be basic.
And I haven't even mentioned stuff that'd really, really hard to express/manage in pure SQL like tree/nested information that has to stay balanced [1]. Thanks django-treebeard/mptt and sqlalchemy-orm-tree.
> The closer an ORM's API is to a monad, the happier you'll be.
Developers using ORM's simply aren't caring about ORM's matching a certain construct. They care that models emit correct representations of their schemas and that the data is retrieved "fast enough".
Take it a different way: the best part about ORM's? They're effective 95% of the time, right out the box, so you end up avoiding time that'd be spent over and prematurely optimizing.
> IMO trying to shoehorn objects (in the OO sense, with polymorphism, data hiding and behaviour) into a database is wrong-headed.
Objects in things like SQLAlchemy declarative and Django Models map perfectly to generated SQL, so they also act as a way to generate migrations. It's that accurate. A lot of the relationships project's need expressed tend to be vanilla joins.
> Data hiding in particular is a wrong mental model for thinking about facts in the database, and the more is hidden, the harder it will be to reason about performance and bulk remote operations generally.
ORM's strive to hit a value sweet-spot in terms of code expressiveness, reducing duplication, handling the bread and butter relationships and types. That covers what most developers really need.
Perhaps there are projects out there not fitting to ORM's. Not all projects are sophisticated data mart projects, but even then, a good share of those still go back to simple joins at the end of the day.
And I've even gone as far as trusting heavy-duty stuff like django-mptt, along with plugins that filter and sort. I don't even look at the queries, all I see is they're running performantly. In all these years, SQL queries have never been a bottleneck. Maybe it's because I'm only storing simple stuff.
[1] https://en.wikipedia.org/wiki/Nested_set_model
If you define your schema in your database and derive your data layer from that, you get everything in your list (apart from that thing that totally always happens where you switch your underlying database technology every few months).
But then you don't have your database defined in two places. And if anybody ever does modify the db by hand, your build will break and it will quickly surface itself as an issue at compile time instead of via an obscure error message somewhere 40 levels deep in the call stack.
You can look at a table and its keys to determine what sort of thing it represents (entity, lookup, association, etc.) and build out helper stuff as needed. So in addition to basic CRUD anything that looks like an entity gets .Load(), .Save() and accessors for fields as well as .GetByWhateverID() methods for any foreign keys. I base my actual Entity classes off of those auto-generated base classes, so they can get blown away and recreated as often as necessary.
I also wrap any one-off stored procs that are lying around in calling code, so that they can be used in place of the standard-issue Frankenstein SQLBuilder thing that an ORM would have.
It's kinda all the upsides of an ORM, but without any dynamic garbage, schema-as-config-file, mystery auto-SQL, migrations, or (again) Almost-SQL-In-The-Magic-Query-Languague to Not-The-SQL-I-Meant conversion.
Compared to something like Hibernate or SQLAlchemy that tries to support everything under the sun and can result in a lot confusion when trying to understand what exactly it's doing.
http://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
http://modern-sql.com/use-case/literate-sql
"ORM is the Vietnam of Computer Science" (2006)
https://blog.codinghorror.com/object-relational-mapping-is-t...
To my opinion, it was a bad solution to the wrong problem.
For one, we're not that enamoured with objects anymore (what with functional programming, immutability, etc).
Second, SQL and DDL, being declarative, is both a higher abstraction that (at least) most ORMs, and offers more fine level control to the DB at the same time!
Third, people don't really switch databases that often, for the abstraction between different SQL syntaxes to matter.
But third-party libraries shouldn't assume any specific SQL database, so if you want libraries that can do database things, ORMs are very useful.
That said, people seem to still use ORMs in Ruby, Java, C#, etc. and frameworks look fairly mature.
SQL is the most concise and perfect fit for RDBMS.
However, at the application level there are benefits of using ORM.
- The application itself is usually imperative style as against the declarative nature of SQL.
- Chaining is sometimes more readable and concise. One can chain dynamic filters.
- Abstract the underlying data model with higher level names. SQL eq. of table views.
- Hides the underlying relational model. Which can sometimes be helpful in a large code base. And sometimes a curse.
I normally opt for ORM in Rails/Django web apps. But SQL in
- Performance critical - Report generation, where it might be complex and declarative nature of SQL shines.
It is still useful though, but hardly stands on its own term.
I think, the REPL makes a huge difference.
In a python project, getting a shell at any point - or experimenting in the shell Jupyter is really straightforward.
The brackety languages have options for this sort of thing now, but it is still just a lot more hassle - the easiest way to get something similar is to just write SQL.
That said, I never use an ORM myself unless it's one I implemented as stated above.
ORM haters would likely point out that complicated queries are hard to write with an ORM but these complicated queries are not a massive part of the work and can still be abstracted away in a method using SQL if needed.
Another good point of the ORM: validation of data. There are plenty of validation you cannot enforce in SQL (regex, postcode...).
Those particular examples are trivial to enforce in every DBMS I am aware of...
Speed of development greatly improved since I switched to EF. Speed of development is what matters most for me when building first version of an application.
A good ORM library needs to be well designed, battle tested and mature. Then it provides great benefits over writing just raw SQL. That takes years of development effort.
The problem is that with new languages (Node, Golang, Rust) there hasn't been enough time and effort put into ORM yet to produce something good enough to be usable.
When developing in Java or Python there are great, mature and battle tested ORMs I would use but with these new languages I had bad experience with ORMs as they still seem experimental and have issues therefor stick to raw SQL when working in newer languages.