6 ms·

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.

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 https://github.com/vitaly-t/pg-promise).

gmac 5y ago
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/ https://jawj.github.io/zapatos/
threeseed 5y ago
> 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.
- zoul 5y ago
  Meaning it’s vastly more universal than your ORM du jour.
- mozey 5y ago
  > 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.
  - lifthrasiir 5y ago
    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.
    - ttfkam 5y ago
      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.)
- phplovesong 5y ago
  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.
  - ttfkam 5y ago
    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.
    - phplovesong 5y ago
      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.
- dmitriid 5y ago
  > 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".
- osrec 5y ago
  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.
- nicoburns 5y ago
  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.
- f6v 5y ago
  It's always been the opposite for me. Whenever I had to use something PG-specific in Rails I had to resort to SQL.
- toomanybeersies 5y ago
  Regardless of whether ORMs or SQL are more effectively database-agnostic, how often are you changing databases that this is even a concern?
jeswin 5y ago
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".
- kristiandupont 5y ago
  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.
  - madeofpalk 5y ago
    How do you know you're saving and retrieving data that correctly matches your typescript definition? Or are you just saying "trust me"?
    - kristiandupont 5y ago
      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.
    - hebrox 5y ago
      This has been an issue for me as well. I created https://github.com/wwwouter/typed-knex 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.
deleted 5y ago
[deleted]
indeyets 5y ago
Give slonik a try. It's a very nice step up from raw drivers https://github.com/gajus/slonik https://github.com/gajus/slonik
golergka 5y ago
Have you tried pgtyped? I'm in love with it: it allows us check SQL queries at compile times and statically type them.
nine_k 5y ago
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.
nik736 5y ago
ActiveRecord is the only ORM that I feel comfortable with. It's great.
- phplovesong 5y ago
  It suffers from the same issues all others orms do.
f6v 5y ago
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.
deergomoo 5y ago
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 https://www.edgedb.com/docs/tutorial/queries
- eterps 5y ago
  Wow that looks great, thanks!
- phplovesong 5y ago
  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?
  - 1st1 5y ago
    > 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.
lyind 5y ago
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/ 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.
- KptMarchewa 5y ago
  JDBI is amazing.
elliebike 5y ago
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.
- furstenheim 5y ago
  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.
- santiagobasulto 5y ago
  I am with you. And it's always the top comment.
- deleted 5y ago
  [deleted]
- justsomeuser 5y ago
  I think it’s useful to debate both the general concepts and specific implementations
qeternity 5y ago
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.
hnbad 5y ago
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).
- ttfkam 5y ago
  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.
midrus 5y ago
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.
- christophilus 5y ago
  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.
  - midrus 5y ago
    How many people are working on it and how big is your project?
djupblue 5y ago
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.

gmac 5y ago

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/ https://jawj.github.io/zapatos/

threeseed 5y ago

> 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.

zoul 5y ago
Meaning it’s vastly more universal than your ORM du jour.
mozey 5y ago
> 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.
- lifthrasiir 5y ago
  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.
  - ttfkam 5y ago
    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.)
phplovesong 5y ago
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.
- ttfkam 5y ago
  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.
  - phplovesong 5y ago
    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.
dmitriid 5y ago
> 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".
osrec 5y ago
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.
nicoburns 5y ago
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.
f6v 5y ago
It's always been the opposite for me. Whenever I had to use something PG-specific in Rails I had to resort to SQL.
toomanybeersies 5y ago
Regardless of whether ORMs or SQL are more effectively database-agnostic, how often are you changing databases that this is even a concern?

jeswin 5y ago

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".

kristiandupont 5y ago
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.
- madeofpalk 5y ago
  How do you know you're saving and retrieving data that correctly matches your typescript definition? Or are you just saying "trust me"?
  - kristiandupont 5y ago
    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.
  - hebrox 5y ago
    This has been an issue for me as well. I created https://github.com/wwwouter/typed-knex 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.

deleted 5y ago

[deleted]

indeyets 5y ago

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

golergka 5y ago

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

nine_k 5y ago

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.

nik736 5y ago

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

phplovesong 5y ago
It suffers from the same issues all others orms do.

f6v 5y ago

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.

deergomoo 5y ago

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 https://www.edgedb.com/docs/tutorial/queries

eterps 5y ago
Wow that looks great, thanks!
phplovesong 5y ago
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?
- 1st1 5y ago
  > 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.

lyind 5y ago

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/ 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.

KptMarchewa 5y ago
JDBI is amazing.

elliebike 5y ago

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.

furstenheim 5y ago
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.
santiagobasulto 5y ago
I am with you. And it's always the top comment.
deleted 5y ago
[deleted]
justsomeuser 5y ago
I think it’s useful to debate both the general concepts and specific implementations

qeternity 5y ago

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.

hnbad 5y ago

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).

ttfkam 5y ago
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.

midrus 5y ago

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.

christophilus 5y ago
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.
- midrus 5y ago
  How many people are working on it and how big is your project?

djupblue 5y ago

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.