Hacker News Reader

Comment by monero-xmr | Hacker News Reader

My problems with ORMs can be reduced to:

- SQL is so easy and so useful that being a professional engineer that can code but can't learn SQL is absurd

- You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd

- If you ever get enough scale to require optimization the ORM will be your enemy

In summation, anything related to ORMs and SQL is immediately invalid, other than questions like "Help! I joined a company where they mandate ORMs and I hate my life!"

View on HN

(probably) not many are using ORMs because "SQL is hard". You're attacking a strawman. I use ORMs because they make two things incredibly easy:

- saving object graphs: it's trivial to write SQL for a simple CRUD form. It's far more difficult to work with real models where you have to deconstruct the form the user posted into a dozen tables with joins between them, doing insert/update/delete as needed. With a decent ORM you simply construct an object graph by assigning fields (the result can be very complicated), shove that into the database, and the ORM takes care of the rest. It may not be web scale™, but many of us don't need that.

- constructing queries on the fly: if you ever get any exposure to LINQ, I don't think you'll want to go back to SQL after that. It allows you to easily construct queries based on user input (in a type-safe way, unlike some other solutions), which saved me countless hours when building complicated tables with search and filtering on many (sometimes hundreds) of fields.

Here is a trivial example: https://stackoverflow.com/a/10884757

And with good ORMs like Entity Framework the underlying queries they generate are predictable.

So much this. I hate micro-ORM, they lost the relational part of RDBMS.

> And with good ORMs like Entity Framework the underlying queries they generate are predictable.

Right up until they are not and generate crap. I went from "predictable" EF generated queries to handcrafted ones reduced the load on the DB by about 70% while having more queries per second...

from personal experience this was a much larger problem during .net framework days. Now with EFcore, most generated queries are much more sane and performant

A very popular alternative is to inject json in the DB.

I only end up doing that when using an ORM to work around the limitations. I have never needed to do it when just using SQL.

I find that ORMs makes it significantly harder to construct queries for anything but the most basic of queries. I routinely end up having to fight the ORM.

Linq is not really an orm, though. I think it's a "query builder"

ORM is never about "not learning SQL". I am great at writing SQL, but i hate converting my resultset to object graph, converting lazy loading vs eager loading at times, dealing with types and changing types etc.

On "great" side of things, Linq brought massive type safety to .net land, and made queries also compile time checked.

IS it leaky? yes. Every once in a while you'll need to write SQL. Does it solve a lot of problems? Also yes.

Disclaimer: I was once a contributor to NHibernate.

Especially writing 80% of queries that are simple crud to update or read single table that needs to be written most of the time.

Such a waste of time to maintain that conversion code by hand.

>LINQ... made queries also compile time checked

Alas, it's far from a complete solution. It's too easy to type an expression that compiles but blows up at runtime because the underlying provider doesn't support it or gets confused.

This is very true, when NHibernate was getting LINQ it was easy to trip its provider to throw an exception.

But it was still great for majority of cases.

All your arguments are correct, but are unrelated to the main motivations for ORMs. ORMs are about:

- Convenience of using SQL from $LANGUAGE where mapping the result, (re)naming, etc. is handled for you. Do this manually, and eventually you will either make silly mistakes or create your own mini-framework.

- Connection pooling / retrying if desired. Can be done without, but again mini-framework.

- Migrations. Generating a schema from code is neat.

- Making our lives miserable when the ORM inevitably generates horrible SQL somewhere and we need to override it manually. That is, ORMs are NOT a replacement for learning SQL.

Realistically the only ORM part of your list is the first item. Everything else is frequently done without having an ORM.

I actively dislike merging ORMs with schema migrations. They really seem like they are two totally separate use cases and shouldn't be conflated like they were.

I only have experience with Java ORMs, so I'd say you forgot to add that ORMs require entity objects which basically duplicate everything - table structures, relationships, the whole lot is redefined.

In my experience Java domain code written with an ORM is about 4x the LOC compared to using stored procedures to do the same job.

Oh - and that 4x LOC gets you about 1/4 the performance, loosely speaking.

I am a substantially more productive developer since I kicked all those abstractions to the kerb.

It is the database and its structure that is the important part of an application. Building an additional layer of abstraction on top of an already existing abstraction no longer makes sense to me.

> You will never switch databases

We used SQLAlchemy on a Python project which allowed us to use an in-memory SQLite DB in our integration tests. This gave us blazingly fast database tests during development where a database was torn down and recreated, with data, for each test in milliseconds. When the commits were pushed, CI would run the tests against SQLite and MySql.

It worked extremely well and would be a tremendous amount of effort to handwrite SQL for both DBs. I'd do it on ever project if I could.

I know that a lot of people do this but I would have very little faith in my tests if they were run against a different type of database than my production one.

Likewise, if there weren't also integration tests that ran against a DB engine more similar to that used in production.

But no unit test environment is ever going to emulate everything in production, nor should it.

> I would have very little faith in my tests if they were run against a different type of database than my production one.

Hence why they were run against MySql on CI.

Ah, I missed that part. Makes sense.

I spin up a new database per test suite.

I use pg and test suites run in parallel against their own db. It’s pretty cheap with CREATE DATABASE helpers plus using container means little to no headache.

Why would you also run the tests against SQLite during CI if prod database was MySQL?

Switching DB's does happen, but it is generally rare in my experience, and usually a strategic mistake. Those who do it usually underestimate the migration time. It's not economical to spend a lot of time up front to prepare for a scenario that happens once in a blue moon.

The main reason to write mostly generic SQL is so new hire-ees can get up to speed.

I was lead on migrating several systems from traditional deployment to a cloud provider for a major company. Part of the migration was switching a microsoft DB to MySQL. The java ecosystem and ORMs makes this so easy it's barely worth talking about. And that wasn't the only time db migrations have some up.

Not sure if that's enough to justify ORMs, but DB migration is a real use case and there are things you can do ahead of time to greatly simplify the process.

> You will never switch databases

No, you may never have had to switch databases, but I have worked on codebases where we've had to switch databases, and I'm sure others have to. Most likely when dealing with monoliths where you can't do a full re-write and you can't break out a separate service for various reasons. The repository pattern is your friend in these situations. An ORM should ideally be able to read/write to multiple database drivers which makes this easy by dual-writing to both databases from the repository layer.

I've even worked on a number of projects where a) the unit tests used an in-memory database b) dev environments used mySQL/postgres/SQLite or similar c) production used Oracle or SQL server or similar

Good luck doing that with hand-coded SQL.

But for me 99% of the benefit of using an ORM is compile-time checking of queries. I don't see why in principle that couldn't be possible using raw SQL, but I don't know of any good examples of it.

> You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd

One of the main reasons that Django has been so popular for so long is the idea of reusable "apps". Eg, Django Admin, which comes bundled with Django but there are also thousands of 3rd party ones that you can use to quickly put together very powerful sets of features out of polished, tested pieces. That whole ecosystem really only works because of the ORM, which means that apps mostly don't have to know/care what database might be used. (I assume some other frameworks have similar stories, but Django's what I'm most familiar with).

As far as scaling, I've developed and supported Django apps that have hundreds of thousands of users and serve thousands of requests per second. I would probably not even put the ORM in the top ten list of problems with achieving scale. I've occasionally found it generating slow queries and had to tweak or bypass it (both extremely easy to do) but honestly, those have been low hanging fruit when it comes to optimizing (and very easy to find thanks to reusable apps like `django-debug-toolbar`, which exists at least in part because of the ORM). Are ORMs unteneble once you're in the millions of users or tens of thousands of requests per second range? Maybe. But that's a very small part of the software landscape and for the vast, vast majority of the rest of us, a good ORM isn't going to be the problem.

The scaling problem is as future based and hypothetical as the switching issue. Which is it? Care about future hypotheticals or no?

I use Django a lot and have found the ORM invaluable for all use cases and can be optimised when needed, with some friction.

One way to have the best of both worlds for situations where you have complicated read queries is to create database views for the queries. You can then define them as an unmanaged table in Django (requires some boilerplate) and query them in a simple way as if they were a normal ORM model.

> You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd.

You actually might switch databases at some point, but in my experience this always included overriding some of the ORM-queries with custom ones that the particular RDBMS needed for some reason.

So you have to write SQL after all when switching, might as well do it from the beginning.

Most data I have is not tabular, but tree-like in nature. A course has instances of a course, which have students and assignments. Students have submissions per assignment. You can press this into tables, and then join it back together, but it is a detour. The question for me is not: why use an ORM, but why use an SQL database as the underlying storage.

My reason is that there are no good object databases yet. I would want something that allows you to take your C++ vector or Python list of plain old objects, and stuff it into a data structure:

- with a choice of rowwise (array-of-struct) or columnwise (struct-of-array) storage

- with custom indexes (e.g. not just map<string, MyStruct>, but Database<MyStruct> and tell it to index of propertyA, propertyB, ...)

basically taking performance oriented aspects of relational databases and applying them to an in-language data structure, without leaving the world of trees and objects. Back to the question, an ORM is for me not an alternative to SQL - it is an alternative to dicts and list comprehensions and manual storage.

- SQL is so easy and so useful

SQL is not hard, it just sucks. Massively. "So useful" in the same way BASIC was useful for 80's computers.

- You will never switch databases

You might not want, but you might need to

- If you ever get enough scale to require optimization the ORM will be your enemy

BS. Most ORMs do let you drop into SQL, no problem. This is usually one or two queries on a whole project that merit this treatment

If things are slow you can probably optimize stuff at the ORM level itself (or your modeling was done by interns - which would be a problem regardless of ORM or no ORM)

> You will never switch databases

So this is an interesting one. We didn’t switch databases, but deployed the same application in some different infrastructure due to how different companies in our “group” have different setup. So we went from having an API running in Azure connecting to a DB running in MS SQL Server, and had to also deploy that (with some UI changes) to Google Cloud with postgres DB.

It went surprisingly well, but there were a few fiddly bits and I was quite glad I didn’t have to worry about the DB interaction, EF Core handled it for us

First point is really absurd and I don’t understand why people make it.

I have never seen argument “I use ORM so I don’t need to know SQL”.

It is always people who boast they know SQL that they don’t need to know ORM.

Exactly. I'm happy with tsql [0] - template based, with safe sanitation, helper renderers/combinators, used in production for several years, would recommend this approach. It's basically pure sql with safe helpers when you want to use them. You can't use orm if you want to use underlying database beyond common denominator for basics.

[0] https://github.com/appliedblockchain/tsql

>template based

There's a reason why the modern approach is parameterized: The old PHP-style templating led to galore SQL injections, and worse performance. Don't see any reason to ever go back.

This is common misunderstanding.

Tagged templates in js allow you to write safe, injection free sql templates, tsql does exactly that.

Performance is better if anything, but irrelevant - you're talking about sql rendering performance, which is several orders of magnitude lower than invoking query?

You should double check it. See examples of usage. We use it for mssql but same principles apply to other vendors. Mssql has many interesting things in its dialect that can't be expressed in orm. With templates you simply write sql and aid yourself with functional combinators for complex statements. It works really well.

I'm referring to possible performance loss due to lack of parameterization. Yeah, one could enable forced parameterization and let parameter sniffing do its job, but that can be a double edged sword.

> You will never switch databases, so abstracting away the DB for some hypothetical "I might want to switch DBs one day!" scenario is absurd

I have a number of hobby projects that work with both SQLite and Postgres by virtue of a ORM. SQLite is great for testing and lightweight installs, while Postgres scaled much better.

So yes, there are absolutely use-cases where the ability to switch DBs is very valuable.

I also dislike ORM, mostly because it takes what I like (beautifully normalized relational data) and squishes it into ugly object graphs. No need for ORM if you don't do OOP.

But if I have to do OOP on data from database I much prefer ORM over doing it manually.

I'm not sure what you'd call them as they're not ORMs, but I really like libraries like Jdbi that make it easy to map results to objects.

I’ve worked on multiple database migration from something commercial to Postgres. ORM was a huge help.

Am I wrong to remind that Part of the reason to not generally write raw SQL is to prevent SQL injections?

Parameterized queries do that just as well.

Yes, then your junior PRs code that doesn't use it and it gets missed in the scuffle

Reply on news.ycombinator.com