Hacker News Reader

Comment by icxa | Hacker News Reader

The argument against ORMs aren't that they are a leaky abstraction (although they most definitely are).

The argument is although they appear to offer you value up front, they cost you much more down the road. The moment you have a hot path query that needs optimizing, you are dropping down to your ORM's "raw sql" mode. Then you do it again. Then again. Then you are ripping out the ORM and spending cycles refactoring it out of your code and replacing it with simpler abstractions.

I always find the people who don't believe writing raw SQL is preferable to using an ORM is usually down to a lack of experience or because they have been coerced to use ORMs by their "enterprise grade" language (usually your C# developers of the world, no offense to you all but if every time you look up examples of data operations it's dealing with EntityFramework you are probably going to wind up with a lot of devs using EntityFramework). The ORMers, as I call them, don't have confidence in their own SQL ability, and they haven't experienced the aforementioned situation enough times to realize you are better off starting with SQL to begin with.

The realization is, if I could summarize it: yes all abstractions are at least somewhat leaky, so you are better to use simpler ones than complex ones (and if you need something more complex, compose it from simpler ones)

View on HN

> The argument against ORMs aren't that they are a leaky abstraction (although they most definitely are).

I'm not using an "OO" language these days, so I don't really have a horse in this race, but: isn't the problem you mention here completely explained by the leakiness?

Replace "ORM" with "compiler" and "SQL" with "assembly language", and this is exactly the argument I heard back in the 1980's about why not to use a compiler, or in the 1990's about why not to use a GC. Nobody I know is writing assembly language at all any more. It sucks at composition, so even though it's better at many tactical micro-optimizations, we've improved compilers to the point that we can use them all the time, because it helps productivity massively.

> and if you need something more complex, compose it from simpler ones

Composition is the killer feature in all of software building, and the fatal flaw of assembly, and SQL. We use higher-level languages for their composition abilities, even when they are less efficient. When their inefficiencies make them unusable, we make them smarter, even when it means designing new languages with 100+ keywords (ugh).

If the major complaint against ORMs is that they aren't good enough to make the value worth it in all cases, well, yeah, I've seen that phase of literally every other technology I'm using today. This, too, will pass.

Comparing SQL - a 4GL - to assembly language seems disingenuous at best, to the point where the Blub paradox applies. (And lo and behold, just four days ago you compared SQL to Lisp on the basis of incompatible implementation!)

Unlike assembly, which is famously redundant, SQL doesn't have much to abstract away, and the majority of counterexamples are covered by macros. The things that it really can't abstract are the implementation details that you want to care about when you optimize. Some parts could be a little less clunky, in the same way that the C language could be a little less clunky, but the successful track record of SQL over decades speaks volumes: it's not the stuff going on inside the database causing people the most grief, it's the programming goop that gets attached to it.

He does have a point about composability though. Wonder how, things would have turned out had prolog become the default query language.

The difference is that in my whole programming career I've never had a problem with a compiler.

I have spent so many hours battling Entity Framework when writing the exact query I need is absolutely trivial. It's a rabbit hole, and it can blow up deadlines unpredictably.

Next time I start a new project I'm just using straight Dapper.

This is a pretty condescending take, and doesn't really seem rooted in anything substantial.

The core of your argument revolves around the inevitability of needing to drop down to raw sql for performance -- care to elaborate on that? It seems overblown, I've worked on some apps at decent scale and optimized quite a few queries and I haven't encountered this pattern of needing to throw out the ORM altogether in more and more places.

For any simple to moderately complex queries, the ORM is generating the exact SQL you would write by hand... There are a few pitfalls -- by default you may be fetching more fields than you absolutely need, but this is fairly cheap as long as your rows aren't super wide, and any good ORM offers an easy way to pluck only specific fields that you can use in hot paths. It's often easy to end up with N+1 queries in an ORM, but that's also a well-known access pattern that you learn to avoid with a little bit of experience and doesn't require raw sql. This latter is one area that I think a lot of ORMs could really improve upon, in terms of making it harder to do the wrong thing for inexperienced folks. But it's hardly a reason to throw out the whole tool, you can write n+1 queries in raw sql too.

I would agree that very complex queries can be more tedious to express in an ORM syntax. A good rule of thumb for when it's worth dropping down to raw SQL from the ORM, is if the shape of the query results don't match an object shape anyway -- i.e. for complex analytics type queries that are doing aggregations. But again I don't see much of an argument for throwing out the whole ORM just because you want to write a small percentage of very complex queries in raw sql. Use the best tool for the job. I don't think there's really al performance argument for these types of queries either, a really complex query can still be just as slow written in raw SQL. The solution to scaling these kinds of queries in a hot path tends to be denormalizing or precomputing or caching parts of the data.

Agreed - For example in SQLAlchemy, you have:

1) Super high level - my_record_object.filter(whatever)

2) Common query api: my_table_object.select.where(whatever).limit(whatever) etc etc

3) Straight up SQL execution: db_engine_object.connection.execute(Sql query as string)

There are tools to safely interpolate arguments into SQL query templates for use in #3 too. What else could you want in the vast majority of applications?

I prefer ORMs to writing raw SQL in application logic - and that is not due to lack of experience with either. (I notice your criticism is mostly ad-hominems directed against people using ORM's rather than actual criticism of the technology.)

Note that SQL is also a leaky abstraction over the relational operations - it is just a question of which abstraction is most useful for a particular purpose. Linq express the relational operations just as directly as SQL does, and with a less clunky and more composable syntax.

If you are optimizing raw SQL for performance then you are breaking the SQL abstraction anyway. SQL is supposed to work on a logical level, and the query optimizer do the rest. I know it is sometimes necessary anyway, but that does not show a weakness in the ORM abstraction, it shows a weakness in the query optimizer.

I'd not mind writing raw SQL if it was anything other than a big, non-typed blob of crap as far as my language is concerned. I want compile time safety in my SQL. Tbh, I'm surprised that raw SQL folks don't promote some type of non-ORM but also compile-time type safe SQL implementation. The runtime-ness of SQL strings have always blown me away.

I imagine it would be pretty easy too. Move SQL out of the program language (ie, into files). Verify the syntax. Compare the SQL to the db to ensure validity. Bam, compile time verified SQL. Though, I've never used a setup like that, as that is what my ORM does, just in-language.

Your compiler doesn’t — and can’t — know what’s in your database.

To the extent you create a system that requires this constraint, you’ve created a brittle (and soon to be broken) system.

(I suppose there may be some case where all the data is all known up-front and will always be updated in lock-step with the consuming code/services but that’s rare in my experience.)

> Your compiler doesn’t — and can’t — know what’s in your database.

At compile time, yes it can. Not inherently the compiler itself, but at compile time the process can fail to build if your models do not match the schema in the DB. Diesel, for example, keeps the schema in code and (optionally) compares it to that of the DB. Ensuring that at compile to your code matches the schema of the DB.

That of course doesn't handle changes to the schema post-build, but I hope no one is ad-hoc modifying their DB :)

edit: And this becomes even more of a local, isolated solution if your ORM is also managing your migrations. Which, in the case of Diesel, it also (optionally) does.

Maybe I should have added: it doesn't know what's in your database at runtime.

It's not you'll be ad hoc modifying your DB. It's that your schema and your application-side entities will change over time (continuously, during development, and somewhere between continuously and periodically in production, depending on how you release). Generally you won't be able to guarantee that they are updated in lock-step, or you won't want to pay the price to ensure that they always are.

> Your compiler doesn’t — and can’t — know what’s in your database.

There's no reason you couldn't compile code against a schema, at least the portions that would be exposed to the application anyway (independent of the data content and non-exposed backing parts of the schema) just as you do against header files (independent of the implementation). It is a form of coupling, but its coupling that exists anyway in DB-consuming code, its just not typically statically verified and so is prone to unnecessary run-time breakage.

Unfortunately, you need tooling to statically analyze SQL schemas in whatever flavor of SQL you are using (and vendor differences will matter here), including inferring types through view definitions, etc., and then you need to tooling to map that to the type system of your implementation language, and potentially you need extension points so that you can do custom mapping for custom types from the database side.

Unless your DB and application platform share tight common control or are near ubiquitous, getting someone to make the investment to do this and keep it current is hard. Its not too hard to imagine Microsoft doing if for the SQL Server / .NET combo or Oracle doing it for Oracle DB / Java, but a general and maintained enough to be usable solution is harder to see getting the kind of support it would need.

Sure, you can validate your databases schema against types in your code. Nothing wrong with that either, as far as it goes.

But that isn't a guarantee of anything at runtime.

You could make a runtime requirement that the schema and code match, but you're going to pay a price for that tight coupling. E.g. you'll need to put in place a mechanism to be able to update all your database clients and all your database schemas atomically. Once you get a decent amount of data in your database or scale horizontally, this could become infeasible (e.g., due to the downtime to update the schema) or impossible (e.g., because you don't have complete control of all databases, data, and database clients).

If you are going to go in this direction, you're usually going to be better off targeting a mapping layer/API, not the base tables. The coupling is not so rigid (e.g. there's room for simultaneous support for multiple versions of the data access API/schema. This all exists, of course. Of course, ORMs and other higher level data access libraries generally do this kind of thing to a greater or lessor degree. Importantly, the mapping layer/data access API should live with the database. That is, be developed and deployed with the database (directly or in controlled parallel).

In Java I like to use JOOQ for this. It generates classes and constants from your database. Then you can use the library to build your queries using those generated constants and classes.

This gives you type safety and the ability to use IDE refactoring tools.

Nice. Yea in Rust I use Diesel for this as well. I imagine any ORM worth its weight basically handles this same thing. Even code generation from DB is basically a verification of the schema.

Nim has a library (ormin) that does this, although it is still far from production quality:

It parses your sql (in compile time), including the "create table" definition, and does type checking, rebuilds it with parameters (so that it is SQL injection safe and the DB optimizer can make a plan once and reuse it).

Obviously, it needs to be aware of specific SQL dialects if you need to use them .... so, it's unlikely to be very popular.

When ORM's work as expected, they are wonderful, but when they don't, you can waste a lot of time figuring out why because they are complex contraptions with lots of parts and rules. They are black boxes, or at least dark gray boxes such that if they don't produce the expected answer, your hair will turn light-gray trying to figure them out.

I would suggest using "sub-helpers", utilities that make using SQL and/or stored procedures easier, but don't hide all of the SQL when you don't want to. They automate the common grunt-work, but you are not forced to use them if they don't apply to a situation.

Come up with shop conventions for how and where to join, how to handle reference tables, etc. Then automate/abstract around these conventions. Tune based on lessons from the last application and they will improve over time. It's difficult to get abstractions right the first time.

In other words, wrap and automate the repetitious parts primarily. Use abstraction where it works best and don't use it where it doesn't.

> They are black boxes, or at least dark gray boxes such that if they don't produce the expected answer, your hair will turn light-gray trying to figure them out.

I'd love to hear specifics about some of your personal experiences like this if you're willing to share.

> The argument is although they appear to offer you value up front, they cost you much more down the road.

I always enjoyed this[0][1] take on that sentiment.

“Although it may seem trite to say it, Object/Relational Mapping is the Vietnam of Computer Science. It represents 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.”

[0] https://blog.codinghorror.com/object-relational-mapping-is-t...

[1] https://web.archive.org/web/20180118171352/http://blogs.tedn...

This argument against ORM is like just because I need to drop down to assembly to get maximum speed in a few cases I should throw out all the C code and redo the whole application in assembly.

No, it’s actually not like that at all...

It's more like writing a Python framework, which dynamically produces Ruby code which is executed, then the results are returned back to Python. Sub out Python and Ruby for any other pair of languages. At some point, your generator framework must be at least as complex as the underlying language in order to produce the same functionality.

ORMs seems fine for SQL, because on the surface, SQL appears to be a simple language. But that image falls apart the minute you try and use some (relatively) esoteric, but important language feature.

Yes, just because I need to drop down to SQL to use the few esoteric feature in the 1% case doesn't mean I cannot use ORM to make my life easier for 99% of the case.

I just don't see the point of the argument against ORM to have 100% SQL coverage or else. Most ORM's allow dropping down to SQL when needed. It's easy to have the best of both worlds.

> But that image falls apart the minute you try and use some (relatively) esoteric, but important language feature.

No, it doesn't. The only people I hear claim this are people that don't have decent experience with ORMs. Mature ORMs also act as data mappers when you need more fine-tuned control.

SQL itself is a leaky abstraction. You're essentially hacking a sql expression and permuting it in different ways so it executes a specific imperative search algorithm.

AN ORM is like a backwards abstraction. SQL abstracts imperative instructions into an expression. An ORM abstracts a SQL expression into imperative object oriented instructions. I get the reasoning though. It's to keep the developer from having to write foreign code within code.

Sometimes, the ORM abstraction just isn't expressive enough. So I have this idea for an ERM. Expression relational mapper. Basically it compiles an expression based language into ORM methods. So like "SELECT * FROM TABLE" becomes table.all().

This is how Linq in .net works.

By now, we should be able to transition between ORM and raw SQL code without anyone batting an eye. It's not hard at all. You can't convince me people are actually worried about an extra ORM dependency these days when we have node_modules.

I would much, much rather junior devs use an ORM to start simply to prevent SQL injection while they skill up on SQL itself. But there's not many companies who will hire you and give you a month to learn SQL from the ground-up and do nothing else (learning SQL is NOT like learning another compiled/interpreted language -- there's new concepts plus the DBMS's own language idiosyncrasies which you're definitely using in prod).

This is reflective of my experience with the ORM zealots as well... SQL is already an abstraction, and it happens to be one of the most well thought out, battle tested abstractions we have in the software world... it drives me nuts when junior devs and devs with poor SQL abilities want to pollute a codebase with EF, Hibernate, etc. without understanding if it’s necessary or even helpful beyond simply allowing the developer to continue onward without spending time becoming proficient in SQL. That said, there are scenarios where an ORM is helpful, but I personally find those occasions to be the exception.

> SQL is already an abstraction

Still a leaky one, though. Sure, the syntax likes to pretend that you "just describe what you want", but in practice getting queries on large data sets to perform well involves layers of optimization, all the way down to deciding on how data is partitioned between storage backends and what the on-disk format of it and its indexes needs to be.

In fact I view SQL as a somewhat thin abstraction, with a bunch of unavoidable (or at least unavoided) holes. Maybe that's part of the reason it's been so successful.

Another way of saying it: database abstractions should be specific to your codebase, rather than use generalized patterns.

My bones to pick with ORMs are slathering them all over the place like too much mayo on a sandwich. They are very useful tools for some situations and more trouble than they are worth in others.

Where they really shine is writing/editing data, managing transactions, and migrations. Even for situations where I know I'm going to use the ORM very little, I'll model the database in SQLAlchemy, for example, so I can version the database with Alembic.

Reading data, I generally want more flexibility and don't want to have to push a code change to include another column here or there. For datagrid stuff, you develop a dynamic grid once for your app that just displays whatever the SQL spits out and then you can push changes by updating a stored proc. Reading data is also where a lot of complexity adds up quickly if it's directly from a normalized transactional database. And if you need to manipulate data that's already in your database, obviously, the best tool for that job is SQL, not an ORM.

Where a lot of the friction comes in is where people start off using an ORM and don't make any allowance for what's going to happen when you need to use SQL. Then using a little requires a ton of effort. Conversely, you have the same problem with projects that are designed to only use raw SQL, and then it's a big overhaul to use the ORM where it's genuinely useful, adds clarity and safety.

At this point, I just assume that every project is going to be a hybrid of the two, set up my boilerplate wiring and don't worry about it after that.

The argument against ORMs aren't that they are a leaky abstraction

From what you say, they are indeed a leaky abstraction. It's just that the conditions in which they leak are not easily accessible from the "Hello World" level demo. This is why the leaky analogy is good. It's not hard to make a boat that floats, period. It's hard to make a boat that will remain reliably watertight on the open ocean on rough seas.

yes all abstractions are at least somewhat leaky, so you are better to use simpler ones than complex ones

This is like the Russian philosophy towards military equipment. It's better that it's simple, robust, and can take a lot of dirt and abuse, because this will better in actual combat (or "production") conditions.

Dropping down to a lower level of abstraction from the get go because you might need that level of control for performance reasons in the future sounds like a typical case of premature optimization to me. Of course any sufficiently complex system is eventually going to need specifically optimized code at a lower level of abstraction in some places (usually then wrapped in an abstraction of your own)but that should always be done on a case by case basis as necessary.

> Dropping down to a lower level of abstraction from the get go because you might need that level of control for performance reasons in the future sounds like a typical case of premature optimization to me.

On the flip side, I can make use of the code generation tools i have compiled (some myself, some others) and write code just as fast, if not faster, using raw sql and simple data structures than I did when I used ORMs.

There are infinitely many possibilities in between:

- A developer who is experienced in modeling and sql will deliver working software faster than a developer who might be experienced, but not with modeling and not familiar with a particular ORM or a particular version of that ORM. We could continue ad nauseam here.

The main point: if all things are equal, meaning you have a two developers, one comfortable and experienced in their tools of choice, the "raw sql and simple data modeler" developer and the developer very comfortable in their ORM of choice, the raw sql developer will deliver working software at the same time as the ORM developer, but will have a smaller surface area for buggy software due to the increased inadvertent complexity of using an ORM, and poorer database designs, since naturally, developers that rely on ORMs tend to have poorer or suboptimal database design (see this classic talk https://www.youtube.com/watch?v=uFLRc6y_O3s)

Again if the rebuttal to the database design point is "well you should still know SQL" then what point does the ORM serve?! To write less code? Anyone can write a code generator. If in order to be an effective ORM user I have to know the ORM and understand the SQL it generates to tweak the ORM flags or features to make it generate better SQL, then not only is it a leaky abstraction, it s a bad abstraction. I rely on my compiler to write assembly for me because I can't write assembly better than my compiler and I don't know x86_64 better than compiler developers. If I can't rely on my ORM to write good enough SQL, and I have to know the underlying things going on with the ORM, then why use the ORM? Again, "less code" by itself is not a sufficient answer.

As your database fills, the performance penalty for using the unoptimized mapped queries and the unoptimized data schema will start to increase by whole factors.

In this case, I'm not sure I believe SQL is lower level than an ORM. SQL actually seems higher level than what most OO languages give you, and I wonder if that's a big part of the problem with ORMs.

Maybetthat's why the abstractions that ORMs give you generally seem more complex than SQL.

Lack of experience in this case being, "Haven't blamed problems on the same things as I have."

Stances like yours sit in defiance of so many thousands of people who've been able to extract value from an ORM. Maybe the issue you're having is specific to you, not the problem.

Are they perfect? No. Are your incredibly limited set of experiences universal? Also no.

so your generalization is, "people who understand SQL really well would never use an ORM unless forced by their job".

That is, you are claiming those who choose to use ORMs must lack experience. This is what you have observed, in your experience.

Can I therefore claim, since I know tons of SQL experts who use ORMs and even create new ones, that you "lack experience" as well? because your claim is wildly untrue.

edit: i apologize for the snark but you are literally claiming a whole segment of the programming userbase is less experienced than you are, based on their choice of tools, while you aren't considering that perhaps you haven't worked with many of these kinds of tools to know what's really out there and how different ORMs approach the problem.

Unless ORMs are 1:1 compatible with SQL features, some developers will inevitably be required to write SQL by hand.

Lots of developers are happy to leverage stored procedures and let the database do heavy lifting to reduce network traffic (or whatever reason). Others prefer the database to simply be persistent storage for data structures and leave the logic to their Java/C++/PHP code. One of those camps will never be able to use ORMs, and the other may live by them.

It stands to reason that more advanced SQL developers will leverage more advanced SQL features, some of which aren't available in ORM implementations.

But that doesn't matter, you still can use tools to help compose the 90% of SQL that is quite boring

Truth be told I think it is the "i write 100% of my SQL by hand" crowd that is still quite junior in their SQL experience. How many times would they like to write a rote INSERT statement over and over against their data records before realizing an abstraction would save them lots of typing and redundancy?

Edit: also note that ORMs most certainly can be used with stored procedure architectures in theory. I'm not sure if such tools exist but if I had to use SPs, I'd be writing tools to both generate the SPs from a given set of models as well as to do the runtime work of marshaling my objects to and from these SPs from cursor.callproc() in the same way an ORM does it to plain SELECT / INSERT / UPDATE / DELETE statements from cursor.execute(). Additionally, systems like PostgreSQL's pluggable SP languages, which includes Python, might even allow SQLAlchemy models to work inside a stored procedure, something I've always wanted to try. Both concepts are things I'd gladly pursue if someone wanted to pay me a few years' salary.

> It stands to reason that more advanced SQL developers will leverage more advanced SQL features,

I'm one of those advanced SQL developers, and an ORM has never gotten in my way. Don't blame the tool for the users lack of experience.

I'm not blaming anyone, I'm just pointing out that ORMs aren't going to have every SQL feature. Django didn't add windowing functions until rather recently. So while they are great for most use cases, but you're going to have to drop down to SQL if you want to use advanced features which are not available in your ORM of choice.

I admit the comment was meant to be a bit tongue in cheek but it doesn't appear to have had that impact. My intention wasn't to be condescending or anything like that as others have taken it.

A better argument would have been to explain why ORMs are bad.

In my view: - they encourage bad data modeling practices. Eventually there will come a time where the way your ORM maps your data model to physical tables will be inefficient. Maybe you really have event based time series. The impedance mismatch becomes exacerbated.

- they are an extremely leaky abstraction. It is hard to think of a more widely used abstraction that leaks more. You have to be familiar with your ORM's SQL generation. Every single comment here has admitted that yes you eventually do have to drop down into raw sql, so this part isn't even subjective at this point. The article is about leaky abstractions, ORMs are the poster child for them. - On the other side of things, there are simpler abstractions between SQL and ORM that provide more value comparatively for the leakage, and my argument was you would be better off having simple leaky abstractions than one massive behemoth of an abstraction that leaks more than a waterfall.

- Really hammering the point home here on the leakiness, for being such a leaky abstraction, after it is all said and done, in my view they really don't provide you with all that much value, given that you have extensive experience working without them (the source of my seemingly condescending point about experience and some languages guide you to never working without them).

I can just as easily use my code generation tools to have "less code to write", and map some functions to SQL statements using my language's favorite DB drivers, use a battle tested and proven SQL migration and schema tool, and I can do this just as fast as any experienced ORM developer.

In this view, just like we have had the movement away from big massive monolithic frameworks to smaller opinionated sets of libraries stitched together with simple glue code, your ORM is the massive monolith in this case, and your simpler tools like code gen and migration tools are the smaller libraries.

> I always find the people who don't believe writing raw SQL is preferable to using an ORM is usually down to a lack of experience

> The ORMers, as I call them, don't have confidence in their own SQL ability

SQL is leaky abstraction too. Every time you look at query plans and try to tweak the right amount and kind of indices, you are having to deal with the drawbacks of the SQL abstraction. That does not make people who do that "don't have confidence in their own file-system ability".

Reply on news.ycombinator.com