Hacker News Reader

> Advocating for the use of SQL over an ORM in every case is like advocating for the use of Assembly over C in every case.

Comparing SQL to Assembly is probably one of the worst comparisons you can make. SQL is a very high-level and powerful declarative language that I would compare, if at all, with functional languages and not with assembly. A few lines of SQL are counting, sorting, grouping, aggregating, and merging data that would take dozens of lines if written row by row in a procedural language.

Yeah, basically an ORM is like a leaky abstraction over an already high abstraction and that's where the problem lies.

A better example would be having someone write C macros for you to implement your logic in. Like you can't call `open` to a get a file handle, you need to use the `WRITE_TO_ABSOLUTE_FILEPATH(path, content)` macro every time you wish to write something to a file because someone thought "you know, C is complicated and what are we gonna do, rewrite all uses of open every time we might want to use a different flag".

Comes with its own limitations while being sufficient for _some_ people's use cases.

an analogy is not about comparing, it's about communicating an idea.

pointing out that the comparison is not perfect does not invalidate or refute anything. Every analogy, by definition, is not perfect. If the analogy were to ever become perfect, it would stop being an analogy and would instead be a tautology.

"an ORM is like an ORM".

IOW, the differences are WHY analogies are useful.

The point was not that the comparison was not perfect, but that it was utterly wrong. SQL is not at a lower level of abstraction than procedural code, but at a higher level. Abstraction should make complex things easier, not harder.

you're still missing the point.

An analogy cannot be wrong because it is not comparing anything. it's communicating an idea.

okay, you win... let’s call it an illogical or even obtuse analogy.

What a weird statement. In many ways, SQL is a higher-level abstraction than an ORM. A better analogy might be functional versus imperative styles, but even that breaks down pretty quickly.

ORMs generally serve some subset of three purposes: 1) constrain the dynamic nature and expressiveness of SQL in such a way that it can work well in less expressive languages 2) serve as a bridge between a typed language and an untyped language and 3) a way to avoid your team needing to learn and work in two languages for backend dev (similar to arguments of using Node so that your frontend and backend can be the same language).

To claim one is superior to the other without context is just a very inexperienced statement to make. I'd highly encourage any team that is comfortable being a polyglot to consider avoiding ORMs and embracing the DB as much as possible.

> 3) a way to avoid your team needing to learn and work in two languages for backend dev (similar to arguments of using Node so that your frontend and backend can be the same language)

It's not about learning or working in multiple languages, it's about duplicating the specification of important details.

The holy grail would allow me to write critical business rules in one place, then make use of them everywhere. I want to only have to specify once that `email` is required on `contact`, and have that manifest as `NOT NULL` on the database, an API validation, and a little star next to the field on the UI. Unfortunately, there doesn't yet seem to be a solution to that problem.

Well, there is the option of writing all your CRUD methods as stored procedures which are the things that actually create/update records (while still allowing complex reporting queries), but that means writing all that logic in the provided SQL language.

That was one of the most interesting things to me about the Drizzle MySQL fork project. One of the goals was to make the SQL language that was used a plugin, so you could use just about whatever you want, JavaScript, Perl, Python, Ruby, Haskell, whatever, as the SQL server level. Being able to share client and server level code opens up some interesting possibilities. Alas, I think it ended up being abandoned.

I live in a world where tables have 10^12 rows and all the queries need to be manually optimized. In my world, an ORM is the most useless thing in the world. Different people, different needs. However, in all my projects SQL was more useful than ORM, except where the data models were so simple my mom could write the code for it.

If you have one table that you’re doing a query on, what types of optimizations are you doing to the query as opposed to the database - or even if you’re doing joins?

In general for one table an index helps, but we have almost no queries on a single table. The Query Profiler is telling what part of the query takes the most (CPU, disk reads) and that is the starting point. Tuning a single query can take hours, depending how slow it is and how often it will run.

There is some information on Internet on query optimizations, look it up. There are also server side optimizations, specific to the RDBMS you use and the flexibility it has.

Are you really doing OLTP transactions on a table with millions of rows that require complex searches or are you doing OLAP style reporting?

I don’t think anyone would suggest using an ORM for reporting, aggregating, etc. I would even go so far as suggesting using a different type of database/table structure - a columnar store and send data to a reporting database. A reporting query over millions of rows wouldn’t be real time anyway.

Just keeping the lights on in manufacturing plants with thousands of sensors and counters monitoring the production. Just data collection is useless if you don't put it on good use and that requires some aggregation and correlation to tell what is going on on the floor.

My only infrastructure/data analytics experience outside of standard RDMS is with AWS so anytime I mention an AWS service feel free to substitute a non AWS/open source alternative...

For aggregation and reporting my usual go to would be Redshift - a custom AWS version of Postgres that uses columnar storage instead of rows and/or something like Kinesis (aka similar to Kafka) for streaming data and real-time aggregation while the data is being captured.

Its really not though. C is just sugary Assembly that abstracts some things but the design patterns are aligned. ORMs promote a fundamental mistake of coupling your object model to your normalized data model.

Great comment. Whereas typical object models have many relationships and out of these, nested data, the optimization for SQL lies essentially in the other direction. Your DB should be optimized for speed, your code for use and readability. These are concepts, IMO, that are a classic trade-off against one another and it doesn't make sense to combine them into a single abstraction.

The basic idea of the relational model was actually data independence, i.e. optimization for flexibility. If you are interested you can have a look at the original paper from 1970: https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

Your assembly/c analogy is more akin to comparing a shovel to a mini excavator. 95% of the time you’re better off using a query builder library for the convenience rather than locking your architecture into using an ORM that will, inevitably, cause long term headaches.

For the other 5% of the time, you’re just building a todo app, use whatever fancy general purpose libraries are hot at the moment.

Why isn't your ORM using that same query builder library?

Why use a full ORM with everything it imposes when all you need is the query builder?

It probably does. But the query builder doesn’t require architectural constraints, the ORM does.

So, SQL is the shovel, correct?

The typical language people use is imperative language where you state how to do something, while sql is a declarative language where you say what you want.

Since we are in the world of analogies, using an ORM is like taking a shovel and using it as a prop (without speaking) to explain excavator operator where to dig, how deep, how wide, what things to avoid etc. Except querying a database can be much more complicated. You might be successful with some simple tasks, but it will fall flat on more advanced things.

Anyway perhaps if databases would expose an interface and allowed you to directly write a query plan then that wouldn't be so bad? But then many people would complain that you need to know a lot ot use it, and also based on data your plan for the same data might need to be different to be fast.

Used to do this by replacing slow queries with a dozen joins with stored procedures,insert/update/delete triggers, materialized views.

The mindset was correctness and validity of a detailed and interrelated collection of known facts, at rest on disk.

Merging 2 of these, say when an insurance Corp buys a competitor, took detailed and painstaking effort by people with both the domain and systems knowledge.

It's very different now,with Json doc stores, where in 5 years time noone will know what timezone that happened in, or if this person is that person with different name because of lossy utf8->ASCII .

OK, I was thinking the shovel gets you closer to the dirt and you can ‘manually’ choose where to specifically dig, or not dig.

Whereas, the mini-ex is a complex hydraulic machine requiring regular maintenance and an operator that knows how to use the controls, and a fuel source. Lots of dependencies.

There was a youtube video of a group of men using shovels cooperatively to ‘throw’ cement:

https://m.youtube.com/watch?v=Xz31W2Uci0c

How is this standard C# code to work over a list:

  var males = from p in context.Person where p.Sex = “M” select c;

Any more imperative whether context represents a C# object or a database?

Well, LINQ is special, it is a language that is similar to SQL that's inside of another language. Such code then is translated to SQL. The language is still not SQL but SQL-like and is incomplete, for example you can't do CTE in it.

No, both can dig but one gives you excruciating finesse (assembly/shovel). I’ve updated the ordering.

There is a middle ground.

Everyone should know sql, but choose an ORM (micro-ORM) that doesn't have any abstractions, and gets data in and out. There are many solutions that offer you this without needing to write any SQL.

https://github.com/CollaboratingPlatypus/PetaPoco

https://github.com/ServiceStack/ServiceStack.OrmLite

Micro ORMs is where it's at. You're asking for trouble if you choose something that does WAY more than it should, like Entity Framework.

Ok, what kind of models are you working on? For me your comparison is completely unconceivable. I don’t like SQL so much, but I’m fairly certain at this point that the various ORMs are supposed to be an higher level abstraction but are in reality only an imperative facade over the mostly functional SQL language.