Hacker News Reader

Comment by phplovesong | Hacker News Reader

During my years as a dev i have really started to dislike ORMs. They always fail in the end. SQL is universal, and transfers between languages and tech fields. This is why im pro-sql, and always try to avoid unnecessary abstractions.

I have actually went back to writing pure SQL in files, and using those as params for whatever db engine i use, this makes it even possible to reuse the code in other projects (even its unlikely that you can use the exact same query, but just as a "it would work" in theory).

For node based projects i have used and would probably still choose pg-promise (https://github.com/vitaly-t/pg-promise).

View on HN

I always feel weird about comments like this. Instead of discussing the merits of the actual project shared, we're going on a rant about whether or not ORMs are a good idea?

It's like whenever someone shares something they made with Electron, and the comments are just Electron hate.

Not really, Electron would be the tool but not the end goal. Here an ORM is the end goal and that's what the parent complains about.

I am with you. And it's always the top comment.

I think it’s useful to debate both the general concepts and specific implementations

If the programming language can offer type safety for queries, it's a big win. For instance in the .Net framework, Entity Framework continues to be the default choice after a decade because it is well integrated with the language and also allows you to use SQL if needed. Meanwhile in say JS, an ORM has less to offer.

So really, just like most other programming decisions, the answer is "it depends".

I generate Typescript types directly from a Postgres database. That works great with Knex and a bit of custom code I've written on top.

How do you know you're saving and retrieving data that correctly matches your typescript definition? Or are you just saying "trust me"?

Knex has some built-in support for it, but I've made a wrapper that does it quite well. So I can write db.select('member', { displayName: 'John' }) -- and because I specify the table name in the first parameter, I get type checking on the second.

This has been an issue for me as well. I created https://github.com/wwwouter/typed-knex to mitigate this. At first I had to do a lot of weird things, but with the evolution of TypeScript, it is getting more and more like 'normal' Knex.js. The end goal is to be nothing more than a very small wrapper, if any.

> SQL is universal

No it's not. A small subset of it will work consistently across databases.

But if you want to get the most of your database then in almost cases you will be working with proprietary SQL. And ORMs have the advantage of abstracting this SQL away for you allowing that you to work across databases if you need to.

Meaning it’s vastly more universal than your ORM du jour.

> And ORMs have the advantage of abstracting this SQL away for you allowing that you to work across databases if you need to

In theory, in practise my experience is the oppose. It's easier to understand and tweak the SQL to work across DBs. You can easily diff and compare the SQL files. The ORM is another moving part, it adds convenience for simple queries, and complexity for anything advanced or non-standard.

SQL dialects vastly differ especially past SQL-92. And those differences tend to be very annoying (e.g. RETURNING). There is a reason that people do not generally work across different RDBMSes and instead pick and stick to a single RDBMS because it is really a PITA; I do dislike ORMs for usual reasons, but a bare SQL is not the alternative either.

And yet the strategy most ORMs take is the common denominator where you have no idea if it'll actually use RETURNING or fire off a completely separate query with a potential race condition because SELECTs go to the read-only replica.

(Ask me how I know about this kind of problem with ORMs.)

I mean universal in the sense that going from mysql to postgres is really not a big shift.

I have worked mainly with postgres/mysql but i would imagine i would be up and running at full speed in days/a few weeks with mssql if i ever chose it for a project.

sure there are syntax differences, but the "how do i do this" translates very well across databases.

How do you make event triggers in MySQL?

How do you make a system-versioned table/query in PostgreSQL?

How do you port regular expressions to MS SQL Server?

How do you set up an exclusion constraint on a range type (without race conditions or invalid data) in anything except Postgres?

Most relational databases fit niches that the other don't. No one RDBMS is best, but they are most certainly not interchangeable.

Even using the lowest common denominator SQL means you'll be missing out on major performance boosts because they each have their own hints/shortcuts.

Well if you are comparing non-standard database features you are out of luck. Im talking about the 95% CRUD stuff that you do. Basic selects/joins/aggregations etc.

Its super rare i see the need to actually change the underlying database from say postgres to mysql. And in this scenario you are still screwed if you did use a ORM, with database X only features.

My point was basic SQL knowledge transfers between databases for the bread and butter 95% of things you need to do.

> And ORMs have the advantage of abstracting this SQL away for you allowing that you to work across databases if you need to.

I've yet to see or hear of a single project (outside libs and frameworks) that needs to "work across databases with the same ORM".

I'd say for most standard use cases, the same SQL can be utilised across different DBs.

With ORMs, my big annoyance is inefficiency. When you start to scale or add complexity, ORM generated SQL queries can be rather expensive when compared to hand crafted ones.

True, but the only case where yoy really need to work across dbs is if you are creating a product that allows your customers to bring their own db. Otherwise you are much better off picking a db and sticking with it.

It's always been the opposite for me. Whenever I had to use something PG-specific in Rails I had to resort to SQL.

Regardless of whether ORMs or SQL are more effectively database-agnostic, how often are you changing databases that this is even a concern?

I've been keeping one eye on EdgeDB [0], which I believe could remove a lot of the desire for ORMs. In my experience a lot of the drive behind ORM use is that SQL kinda sucks at returning anything that looks vaguely like a tree.

For example, with SQL if you wanna load all a list of Users, and all those User's Posts, and the Thread that post was made in, you're either doing joins and some awkward transposition of the flat data into a tree, or you're doing three queries and looping through the data sets to join everything up by hand. When you have an ORM that lets you do `User::with('posts.thread')->get()`, it's easy to become reliant on it and never really dig into what's happening.

With EdgeDB, everything is a set. Retrieving a set of Users where each has a set of Posts where each has a set (of one) Threads becomes something where the database layer is pretty much a 1:1 mapping to your program's data structures, but with all the benefits of an RDBMS.

Considering insertions and updates also use sets, I could envision replacing an ORM with an ultra-thin layer that essentially just converts back and forth between trees of records and EdgeQL sets. As you might imagine this is very nice for GraphQL too.

Take this with a pinch of salt as I've only done the most basic playing around with it, but it certainly seems like an interesting idea.

[0] https://www.edgedb.com/docs/tutorial/queries

Wow that looks great, thanks!

Never seen edgedb before. Ill have a look, thanks for the tip.

I usually find the docs always giving a super trivial example. Like a join. Joins are the bread and butter of database work, and design. You model your data and write queries that operate on it.

When it comes to real-world things, i usually do complex reports that include joins from aggregated sets on some condition. Here is where the orms fail big time. Edgedb seems to be build on postgres, so does it come with the same power or is the new syntax limited in some way?

> Edgedb seems to be build on postgres, so does it come with the same power

Our goal with EdgeQL (the query language of EdgeDB) is to make it more powerful than SQL. There are a few things that we're still missing, but we're getting there.

What makes EdgeQL interesting is that it's functional in its nature, making it fully composable. So both simple queries and complex queries (deeply nested subqueries, aggregates, etc) work just fine.

I'd tend to agree with you in javascript-land, but the Django ORM is black magic...the (mostly) performant and complex queries you can build with it are simply amazing. Add in the migration system, and it's really tough to complain, especially since you can always drop into raw sql as needed.

Every single time I've seen somebody not using ORM on something more complicated than a todo list side project, the alternative becomes an imposible to understand and maintain mess of joins, custom made query builders, manual computation of computed fields, custom calls to fetch related data and hand made crappy shell scripts to do schema migrations.

Also, most of the times this happens to come always from ecosystems which don't have a good ORM... such as Go, Node (up until recently... Prisma is pretty good), etc.

At least in node, there are migration tools that let you write vanilla SQL. My current project is ORMless, and I think it’s turning out just fine. Time will tell.

How many people are working on it and how big is your project?

I agree that SQL is generally superior to using an ORM. However I'd argue that SQL is a lot harder to learn and significantly harder to master than any given ORM for most developers. It's very easy to write code for basic queries that performs terribly when writing SQL by hand, whereas ORMs will frequently produce code that is reasonably optimized.

In many situations the performance difference between SQL and an ORM is comparable to writing assembler code to optimize your C program: significant in theory but negligible in practice.

This gets even worse if you consider that most developers in a team will not touch the queries frequently enough to ensure their SQL skills don't get rusty eventually, even if you managed to bring them all up to speed in the first place. So in practice it'll be about deciding whether the team uses the ORM (that probably already comes with whatever framework you're using) or maintains badly written SQL with a few high performance sections nobody dares to touch because nobody remembers how it worked (e.g. pivot tables).

I wonder if you've ever worked on a Hibernate project that's more than a couple years old.

Once you exhaust the limits of what the ORM author deemed important (or within their ability to adequately deal with), you are left needing deep knowledge in both the ORM and SQL to debug/optimize.

Also, SQL knowledge transfer well between different engines. ORMs tend to be unique snowflakes with very few common patterns beyond the impedance mismatch that is object-relational mapping.

In my experience dealing with code written by people who only know ORM:s is about as fun as dealing with code written by people who refuse to use ORM:s when they really should have. The latter like to prove they don't need an ORM by creating their own "simple" libraries full of half broken functionality that pretty soon turns into to the ORM:s from hell.

Oh, be aware that SQL is not universal. Especially not if you do anything remotely advanced like triggers, non-trivial indexes or select queries that returns complex types.

Give slonik a try. It's a very nice step up from raw drivers https://github.com/gajus/slonik

I have reached the same conclusion regarding ORMs after strugling with Hibernate/JPA for some time.

At least for Java and Kotlin the awesome library jdbi ( https://jdbi.org/ ) implements a very useful hybrid approach.

One creates DAOs and Repositories to abstract away the DB and map results and arguments to/from objects on the fly. All while retaining full control over the SQL and all mapping aspects.

This way SQL and mapping can be optimized to leverage the features of each database (ie. PostgreSQL's array, UUID, hash and JSON types) or be handled generically.

The SQL loading can also be customized to read SQL from pure ".sql" files in resources/files or from inline specification via annotations.

The jdbi developers have in the past reacted very fast and competent to issue reports or PRs.

If find applications built using this library more easy to understand and also better performing than ones using a full-blown ORM.

JDBI is amazing.

I never shy away from complex SQL when it's warranted. But frameworks can make reading simple queries much easier, e.g. when using ActiveRecord in Rails. Readability is a big advantage in my book.

I agree that ORMs have a very narrow area of applicability, at best.

OTOH I do appreciate when a library allows to construct SQL from first-class, reusable parts, like SQLAlchemy allows in Python. Using the same where-clause object in both select and update operations, or combining descriptively named filter clauses to create or amend a where clause, or easily constructing CTEe from other queries, etc — this all is pretty empowering, and, most importantly, expresses the logic better.

Have you tried pgtyped? I'm in love with it: it allows us check SQL queries at compile times and statically type them.

Agreed on the appeal of sticking to SQL. I’ve written an alternative non-ORM for Node and TypeScript (which doesn’t separate out your queries): https://jawj.github.io/zapatos/

ActiveRecord is the only ORM that I feel comfortable with. It's great.

It suffers from the same issues all others orms do.

Reply on news.ycombinator.com