6 ms·

The same can be said about ORMs... - You start of thinking, my problem is simple/unique so I don't need an ORM - You design your database schema, start buildi

The same can be said about ORMs... - You start of thinking, my problem is simple/unique so I don't need an ORM - You design your database schema, start building your application, inserting, updating, querying it... - First job is to write a few abstractions that represent a row in each the tables in your db. It handles deserialising/serialising from the database types to you languages types, plus a few of your custom types. - You find yourself duplicating code for simple select/update/insert/delete quires so you build another layer of abstraction so you have only one generic implementation of these that your table row abstractions use. - You start writing more complex queries based on user input, it starts to get a little complex with all the string concatenation. You write a query builder for one area of you application, but soon find yourself longing to use it elsewhere. So you generalise it to be usable on other tables. - Hand writing database migrations is simple to start with, but as your team grows, and you have more deployment environments, and more branches, it becomes a massive headache. Then one day someone runs the wrong migration and drops the main db... you write a database migration tool, reuse a bunch of your application table abstractions as the definitive source of truth for what your tables look like. Job done! Oops, you wrote an ORM... Obviously the world isn't that simple, and there are legitimate reasons not to use one. But the majority of the time it's better to start with an off the shelf ORM than not. The issue with ORMs really come down to restrictions on escape hatches, easy dropping down to SQL but with mapping back to your higher level abstractions.

ludston 4y ago
Certainly it depends on the use-case. ORMs are wonderful for CRUD style applications, but tend to be (in my experience) awful for reports. They also tend to discourage developers to think about query performance, because the ORMs query DSLs don't usually have first-class profiling.
- BiteCode_dev 4y ago
  You missed the last sentence of the comment. Even with custom query, using escape hatch bring a lot to the table because you benefit from the ORM core handling system for configuration, serialization, pooling, etc. Not to mention some core (like sqla core) in fact include a functional query language that is capable of representing low level sql in any form. In which case you benefit even more, and you can do your report perfectly.
  - doctor_eval 4y ago
    So now you need to know two languages to access the database. :-/
    - Foobar8568 4y ago
      But these are actually for different purposes and requirements. KISS and DRY are respected.
    - BiteCode_dev 4y ago
      Unless you access the database only and don't have the reports accessed through an end-user application, you usually have some language involved to call your SQL queries, normalize input, abstract them away, etc. You do have to learn an additional API, but as a programmer, that's something I have to do every day anyway.
    - ludston 4y ago
      At the very least you should know SQL. The purpose of having a query dsl in some other language isn't to avoid understanding sql, it's because sql isn't easily composable.
      - ttfkam 4y ago
        CTEs. Views. Set-returning functions. "SQL isn't easily composable" is a pernicious lie. Does it compose like your favorite functional language? No. SQL is not nor has ever been a functional or object-oriented or generic or procedural language. It's a fourth-generation declarative set-oriented language, so the composability is going to be based on sets. Period. Until you think of SQL as a set-oriented declarative language, you will be perpetually trying to push water uphill, ORM or no.
        ludston 4y ago
        It's not a pernicious lie at all. You can't just say, it's composable so long as you only compose with the special mentality that you consider to be blessed. Also the fact that SQL acts on sets is completely irrelevant to the syntax being composable or not. JSON is programmatically composable. XML is programmatically composable. S-expressions are programmatically composable. Most programming language syntax's are not. If you build a UI or reporting mechanism that wraps around an SQL engine and has multiple configuration options that combine to determine the SQL expression that should be used, the fact of the matter is that you will either abstract the combination of the configuration into a DSL that then gets converted to SQL, or you will have a mess of string concatenation that isn't type-safe and isn't verifiably correct until runtime. Show me the code you'd write to generate an SQL expression with ten optional filters, where each filter is comparing a different column to a set, and if the set is empty the filter should be ignored. I'll show you a DSL that does the same thing, is readable and statically verifiable, and ensures that if anybody else ever writes a similar query guarantee's that parameters will be the same so that the execution plan can be cached.
habibur 4y ago
For me, it never gets more complex beyond the first 3 points you listed. Write a few wrapper functions sql() rows() row() map() value() insert() update() delete() and that's it. Use hashmap to send parameters and receive the result set. Use sql() to write direct sql statements for complex tasks.
crabbone 4y ago
This is what happens the first time around. Maybe few more, if you don't learn the first time. It doesn't really lead to creation of ORM though. This is more similar to how a lot of people w/o appropriate tools would "naturally" think that Earth is flat. Another (better) way to deal with this stuff is to evolve the database instead of growing a defective translation layer and to keep the interface to the minimum.
marginalia_nu 4y ago
> - First job is to write a few abstractions that represent a row in each the tables in your db. It handles deserialising/serialising from the database types to you languages types, plus a few of your custom types. > - You find yourself duplicating code for simple select/update/insert/delete quires so you build another layer of abstraction so you have only one generic implementation of these that your table row abstractions use. > - You start writing more complex queries based on user input, it starts to get a little complex with all the string concatenation. You write a query builder for one area of you application, but soon find yourself longing to use it elsewhere. So you generalise it to be usable on other tables. I think most of your problems seem like they would be solvable within SQL. SQL goes far deeper than INSERT/UPDATE/SELECT/DELETE. Most DBMSes have functions and views that can provide shared functionality that removes the need for abstraction. They were designed to be able to deal with business logic, not just be dumb a model-store. Now obviously that means you have to write non-trivial amounts of SQL code, which can be a bit of a pain, but it will in most cases perform a lot better and end up a lot cleaner than wrangling the data as though it was a bunch of objects.
- deleted 4y ago
  [deleted]
Foobar8568 4y ago
You want simple CRUD and highest performance (cause 2ms vs 4ms is 2 times faster!), you go with a micro-ORM. You start adding associations two days later, 3 weeks later you finished coding an abstract layer to handle simple associations, you start wanting to have an aggregate root, 2 months later, you get a simple and basic ones... Now you want to handle cascading features because they are also nice to have for your use case. Congratulation, you have implemented something 100 worse and slower than a full-scale ORM without any benefits.
btschaegg 4y ago
I don't really share your opinion here (and, likely, the supporting ones about how to use ORMs). But simply disagreeing here would waste both of our time, so I thought I'd ask you about specifics instead. Can you give me some short links to examples demonstrating the upsides you see there? Which off-the-shelve ORMs are good ones in your opinion? Since my take is likely based on my biases: I really haven't made good experiences with a couple of off-the-shelf ORMs, especially Hibernate and Entity Framework, both in green-field and "entrenched before I joined the project" scenarios. Both models I've seen (generate a DB schema from code annotations and generating code from some magic tool you run against your DB schema) seem really ill-advised to me (from an architectural standpoint) if the respective code is supposed to form the core of an architectural model. And if it doesn't, I can't really see the benefit in avoiding code duplication unless supporting a wide range of database vendors is a requirement. That said, I also never worked on CRUD style applications and all projects I worked on didn't need to work with multiple database vendors. And I've certainly seen the "oops, you wrote an ORM!" effect, but the thing that irks me there was that it wasn't necessary (at least in my opinion). I'd also readily agree on schema migrations, but there are good non-ORM solutions for that problem, too. So, what I've seen there, as problems go, is mostly a lack of care, structure and proper process akin to every occurrence of "bad code". I'm not sure that this problem is easily solved by any solely technical solution. Do you think any of that is what made the difference in our experiences?
- imtringued 4y ago
  Try using GORM for Grails. You still have to specify your fetching strategy but it is so much more sane than using raw Hibernate. I don't know what people mean by code duplication. When using GORM I just use liquibase to create and update tables. The default data type mapping rules are easy enough to memorize.
  - ttfkam 4y ago
    Does GORM allow the use of window functions, pivot tables, recursive queries, filtered aggregates, unnesting arrays, splitting a string into rows, or lateral joins? I mean without an explicit escape valve to write raw SQL queries, which means you're bypassing the ORM anyway.
mrkeen 4y ago
It's the reverse for me. When I join a team, I get hit in the face with an existing ORM. It makes debugging performance issues completely opaque. Two issues in my current code base are on my mind: Firstly, our slowest query involved the ORM generating a "CROSS JOIN" clause in a query which returned 0 or 1 results. Of course there was no way of tying it back to the code which was emitting it without some investigation. Real SQL would have shown up in a text search. While investigating the above problem, I came up with a replacement query which would take 5 ms rather than the ~400ms it currently took. Unfortunately I couldn't use the query because my change (ordering by B.time instead of A.id) didn't return strictly the same results. But they should have matched. It turned out there was a slight race in writing out our rows via the ORM. We're currently not touching it because we're worried about breaking it. If it were SQL strings, I'd just wrap them in a transaction. SQL is already the do-everything high-level abstraction. Trying to put Java code on top of SQL is like trying to put a for-loop on top of a map-reduce cluster.
kgeist 4y ago
I have quite the opposite experience: you start with an ORM because your app is a simple CRUD, later when the app becomes more complex and the team/userbase grow, you start having problems: - N+1 query problem leading to subpar performance (down to unusable) - escape hatches in the form of raw SQL increase cognitive load (2 ways to do the same thing, having to always remember how ORM matches to SQL and back when writing business logic) and may introduce inconsistency/bugs when the ORM caches object state in memory while raw SQL doesn't and you accidentally mix the two in one request (we've had a few such bugs) - ORMs often encourage coupling business logic with DB access logic so it becomes harder to write tests and refactor code - with a large amount of data (millions/billions of rows), built-in ORM stuff stops working when you care about zero-downtime migrations (i.e. no table locking, support for custom sharding etc.) so you end up writing cutom tools tailored for your use cases anyway - it's harder to debug because there's a lot of magic Our product was initially written using ORM and we ended up rewriting it using layered architecture, DDD and CQRS, it's much easier to reason about now (because all DB access is encapsulated in one place and doesn't spill over the rest of the application), and we're in the process of moving away from ORM in our CRM as well. I don't find it that hard to write raw SQL when implementing repositories and query services in the infrastructure layer, it's mostly write-and-forget for me.
- imtringued 4y ago
  >- N+1 query problem leading to subpar performance (down to unusable) There are two problems in hibernate. The join fetch syntax is pointlessly verbose. This means doing the right thing can turn a beautiful 7 line query into a 21 line monster as you constantly have to thread the same IDs back into the query. I mean it doesn't take many brain cells to do this: select member from Member member left join fetch member.groups groups where member in (:members) order by member.fullname; except you have to repeat this for every single relation but hardly anyone is aware of it and it is highly inconvenient so it isn't done by default. Other ORMs usually provide some sort of .includes() or .with() call which makes it less painful but even those could probably do with implementing a batching select fetch mode. The 1+N problem is mostly the result of bad default behavior because it "always works". Honestly I would prefer it if lazy loading was disabled by default and unloaded relations just give you an uninitialized error the moment you access them so you know you should fix your query instead of blaming the tool. The other problem is absurdly janky handling of inheritance where it just goes straight back to 1+N. I would recommend to simply not use inheritance with discriminators. The feature basically doesn't work if you don't have a 1:1 mapping of discriminators to subclasses. You can now say ORMs are like the Vietnam war or you accept that the way you are using the tool is extremely counterproductive. I mean I have seen code bases where the discriminator is converted via a case statement and hibernate simply doesn't know what the heck to do with that. Another problem with ORMs is their absurdly bad support for arbitrarily complex computed properties. CTE/WITH are not some arcane construct but the @Formula support in hibernate doesn't use them which means computed properties have unusably bad performance. From my perspective 90% of the ORM performance problems could be solved and in theory the ORM could even provide enough batch operation features to make your code faster than not using an ORM.
Chris_Newton 4y ago
It’s funny how people’s experiences can differ. I find ORMs have enough downsides that I wouldn’t assume it was best to use one for a new project. ORMs encourage pulling knowledge and behaviour that belongs at the database level up into application code. ORMs by their nature end up duplicating the database schema in application code, resulting in multiple sources of truth that need to be synchronised. ORMs all have their own ideas about how to build queries. None is as powerful or explicit as just writing SQL in the first place, none is standardised and portable to other ORMs, and the queries they produce might or might not be efficient. SQL isn’t perfect, but it’s stable and universal. If you learn SQL to an intermediate level, you can probably already solve 95% of real world relational database problems, and you can do it using any database and any programming language with minimal adaptation. To be a good choice for any given project and team, I think an ORM needs to offer something more than what the database and SQL already provide. Quite often, I suspect that isn’t really the case, and the ORM is just being used for dogmatic reasons.
- samwillis 4y ago
  > ORMs by their nature end up duplicating the database schema in application code, resulting in multiple sources of truth that need to be synchronised. There is always a duplication of "schema", both your database and application language need a definition of this. It then makes sense to have a tool to, as you say, synchronise this one way or the other. I prefer the ORM approach of the application code being the source of truth, but for others it's completely fair and aloud to go the other way. It comes down the type of work you do, the size and number of teams and who has ownership of the database and data.
  - Chris_Newton 4y ago
    There is always a duplication of "schema", both your database and application language need a definition of this. Why can’t an application just run SQL queries directly against the database? I agree that there are many reasonable ways to manage a database, but not all of them involve reproducing the entire schema directly in client code the way an ORM does.
  - 4y ago

ludston 4y ago

Certainly it depends on the use-case. ORMs are wonderful for CRUD style applications, but tend to be (in my experience) awful for reports. They also tend to discourage developers to think about query performance, because the ORMs query DSLs don't usually have first-class profiling.

BiteCode_dev 4y ago
You missed the last sentence of the comment. Even with custom query, using escape hatch bring a lot to the table because you benefit from the ORM core handling system for configuration, serialization, pooling, etc. Not to mention some core (like sqla core) in fact include a functional query language that is capable of representing low level sql in any form. In which case you benefit even more, and you can do your report perfectly.
- doctor_eval 4y ago
  So now you need to know two languages to access the database. :-/
  - Foobar8568 4y ago
    But these are actually for different purposes and requirements. KISS and DRY are respected.
  - BiteCode_dev 4y ago
    Unless you access the database only and don't have the reports accessed through an end-user application, you usually have some language involved to call your SQL queries, normalize input, abstract them away, etc. You do have to learn an additional API, but as a programmer, that's something I have to do every day anyway.
  - ludston 4y ago
    At the very least you should know SQL. The purpose of having a query dsl in some other language isn't to avoid understanding sql, it's because sql isn't easily composable.
    - ttfkam 4y ago
      CTEs. Views. Set-returning functions. "SQL isn't easily composable" is a pernicious lie. Does it compose like your favorite functional language? No. SQL is not nor has ever been a functional or object-oriented or generic or procedural language. It's a fourth-generation declarative set-oriented language, so the composability is going to be based on sets. Period. Until you think of SQL as a set-oriented declarative language, you will be perpetually trying to push water uphill, ORM or no.
      - ludston 4y ago
        It's not a pernicious lie at all. You can't just say, it's composable so long as you only compose with the special mentality that you consider to be blessed. Also the fact that SQL acts on sets is completely irrelevant to the syntax being composable or not. JSON is programmatically composable. XML is programmatically composable. S-expressions are programmatically composable. Most programming language syntax's are not. If you build a UI or reporting mechanism that wraps around an SQL engine and has multiple configuration options that combine to determine the SQL expression that should be used, the fact of the matter is that you will either abstract the combination of the configuration into a DSL that then gets converted to SQL, or you will have a mess of string concatenation that isn't type-safe and isn't verifiably correct until runtime. Show me the code you'd write to generate an SQL expression with ten optional filters, where each filter is comparing a different column to a set, and if the set is empty the filter should be ignored. I'll show you a DSL that does the same thing, is readable and statically verifiable, and ensures that if anybody else ever writes a similar query guarantee's that parameters will be the same so that the execution plan can be cached.

habibur 4y ago

For me, it never gets more complex beyond the first 3 points you listed. Write a few wrapper functions sql() rows() row() map() value() insert() update() delete() and that's it. Use hashmap to send parameters and receive the result set. Use sql() to write direct sql statements for complex tasks.

crabbone 4y ago

This is what happens the first time around. Maybe few more, if you don't learn the first time. It doesn't really lead to creation of ORM though. This is more similar to how a lot of people w/o appropriate tools would "naturally" think that Earth is flat. Another (better) way to deal with this stuff is to evolve the database instead of growing a defective translation layer and to keep the interface to the minimum.

marginalia_nu 4y ago

> - First job is to write a few abstractions that represent a row in each the tables in your db. It handles deserialising/serialising from the database types to you languages types, plus a few of your custom types. > - You find yourself duplicating code for simple select/update/insert/delete quires so you build another layer of abstraction so you have only one generic implementation of these that your table row abstractions use. > - You start writing more complex queries based on user input, it starts to get a little complex with all the string concatenation. You write a query builder for one area of you application, but soon find yourself longing to use it elsewhere. So you generalise it to be usable on other tables. I think most of your problems seem like they would be solvable within SQL. SQL goes far deeper than INSERT/UPDATE/SELECT/DELETE. Most DBMSes have functions and views that can provide shared functionality that removes the need for abstraction. They were designed to be able to deal with business logic, not just be dumb a model-store. Now obviously that means you have to write non-trivial amounts of SQL code, which can be a bit of a pain, but it will in most cases perform a lot better and end up a lot cleaner than wrangling the data as though it was a bunch of objects.

deleted 4y ago
[deleted]

Foobar8568 4y ago

You want simple CRUD and highest performance (cause 2ms vs 4ms is 2 times faster!), you go with a micro-ORM. You start adding associations two days later, 3 weeks later you finished coding an abstract layer to handle simple associations, you start wanting to have an aggregate root, 2 months later, you get a simple and basic ones... Now you want to handle cascading features because they are also nice to have for your use case. Congratulation, you have implemented something 100 worse and slower than a full-scale ORM without any benefits.

btschaegg 4y ago

I don't really share your opinion here (and, likely, the supporting ones about how to use ORMs). But simply disagreeing here would waste both of our time, so I thought I'd ask you about specifics instead. Can you give me some short links to examples demonstrating the upsides you see there? Which off-the-shelve ORMs are good ones in your opinion? Since my take is likely based on my biases: I really haven't made good experiences with a couple of off-the-shelf ORMs, especially Hibernate and Entity Framework, both in green-field and "entrenched before I joined the project" scenarios. Both models I've seen (generate a DB schema from code annotations and generating code from some magic tool you run against your DB schema) seem really ill-advised to me (from an architectural standpoint) if the respective code is supposed to form the core of an architectural model. And if it doesn't, I can't really see the benefit in avoiding code duplication unless supporting a wide range of database vendors is a requirement. That said, I also never worked on CRUD style applications and all projects I worked on didn't need to work with multiple database vendors. And I've certainly seen the "oops, you wrote an ORM!" effect, but the thing that irks me there was that it wasn't necessary (at least in my opinion). I'd also readily agree on schema migrations, but there are good non-ORM solutions for that problem, too. So, what I've seen there, as problems go, is mostly a lack of care, structure and proper process akin to every occurrence of "bad code". I'm not sure that this problem is easily solved by any solely technical solution. Do you think any of that is what made the difference in our experiences?

imtringued 4y ago
Try using GORM for Grails. You still have to specify your fetching strategy but it is so much more sane than using raw Hibernate. I don't know what people mean by code duplication. When using GORM I just use liquibase to create and update tables. The default data type mapping rules are easy enough to memorize.
- ttfkam 4y ago
  Does GORM allow the use of window functions, pivot tables, recursive queries, filtered aggregates, unnesting arrays, splitting a string into rows, or lateral joins? I mean without an explicit escape valve to write raw SQL queries, which means you're bypassing the ORM anyway.

mrkeen 4y ago

It's the reverse for me. When I join a team, I get hit in the face with an existing ORM. It makes debugging performance issues completely opaque. Two issues in my current code base are on my mind: Firstly, our slowest query involved the ORM generating a "CROSS JOIN" clause in a query which returned 0 or 1 results. Of course there was no way of tying it back to the code which was emitting it without some investigation. Real SQL would have shown up in a text search. While investigating the above problem, I came up with a replacement query which would take 5 ms rather than the ~400ms it currently took. Unfortunately I couldn't use the query because my change (ordering by B.time instead of A.id) didn't return strictly the same results. But they should have matched. It turned out there was a slight race in writing out our rows via the ORM. We're currently not touching it because we're worried about breaking it. If it were SQL strings, I'd just wrap them in a transaction. SQL is already the do-everything high-level abstraction. Trying to put Java code on top of SQL is like trying to put a for-loop on top of a map-reduce cluster.

kgeist 4y ago

I have quite the opposite experience: you start with an ORM because your app is a simple CRUD, later when the app becomes more complex and the team/userbase grow, you start having problems: - N+1 query problem leading to subpar performance (down to unusable) - escape hatches in the form of raw SQL increase cognitive load (2 ways to do the same thing, having to always remember how ORM matches to SQL and back when writing business logic) and may introduce inconsistency/bugs when the ORM caches object state in memory while raw SQL doesn't and you accidentally mix the two in one request (we've had a few such bugs) - ORMs often encourage coupling business logic with DB access logic so it becomes harder to write tests and refactor code - with a large amount of data (millions/billions of rows), built-in ORM stuff stops working when you care about zero-downtime migrations (i.e. no table locking, support for custom sharding etc.) so you end up writing cutom tools tailored for your use cases anyway - it's harder to debug because there's a lot of magic Our product was initially written using ORM and we ended up rewriting it using layered architecture, DDD and CQRS, it's much easier to reason about now (because all DB access is encapsulated in one place and doesn't spill over the rest of the application), and we're in the process of moving away from ORM in our CRM as well. I don't find it that hard to write raw SQL when implementing repositories and query services in the infrastructure layer, it's mostly write-and-forget for me.

imtringued 4y ago
>- N+1 query problem leading to subpar performance (down to unusable) There are two problems in hibernate. The join fetch syntax is pointlessly verbose. This means doing the right thing can turn a beautiful 7 line query into a 21 line monster as you constantly have to thread the same IDs back into the query. I mean it doesn't take many brain cells to do this: select member from Member member left join fetch member.groups groups where member in (:members) order by member.fullname; except you have to repeat this for every single relation but hardly anyone is aware of it and it is highly inconvenient so it isn't done by default. Other ORMs usually provide some sort of .includes() or .with() call which makes it less painful but even those could probably do with implementing a batching select fetch mode. The 1+N problem is mostly the result of bad default behavior because it "always works". Honestly I would prefer it if lazy loading was disabled by default and unloaded relations just give you an uninitialized error the moment you access them so you know you should fix your query instead of blaming the tool. The other problem is absurdly janky handling of inheritance where it just goes straight back to 1+N. I would recommend to simply not use inheritance with discriminators. The feature basically doesn't work if you don't have a 1:1 mapping of discriminators to subclasses. You can now say ORMs are like the Vietnam war or you accept that the way you are using the tool is extremely counterproductive. I mean I have seen code bases where the discriminator is converted via a case statement and hibernate simply doesn't know what the heck to do with that. Another problem with ORMs is their absurdly bad support for arbitrarily complex computed properties. CTE/WITH are not some arcane construct but the @Formula support in hibernate doesn't use them which means computed properties have unusably bad performance. From my perspective 90% of the ORM performance problems could be solved and in theory the ORM could even provide enough batch operation features to make your code faster than not using an ORM.

Chris_Newton 4y ago

It’s funny how people’s experiences can differ. I find ORMs have enough downsides that I wouldn’t assume it was best to use one for a new project. ORMs encourage pulling knowledge and behaviour that belongs at the database level up into application code. ORMs by their nature end up duplicating the database schema in application code, resulting in multiple sources of truth that need to be synchronised. ORMs all have their own ideas about how to build queries. None is as powerful or explicit as just writing SQL in the first place, none is standardised and portable to other ORMs, and the queries they produce might or might not be efficient. SQL isn’t perfect, but it’s stable and universal. If you learn SQL to an intermediate level, you can probably already solve 95% of real world relational database problems, and you can do it using any database and any programming language with minimal adaptation. To be a good choice for any given project and team, I think an ORM needs to offer something more than what the database and SQL already provide. Quite often, I suspect that isn’t really the case, and the ORM is just being used for dogmatic reasons.

samwillis 4y ago
> ORMs by their nature end up duplicating the database schema in application code, resulting in multiple sources of truth that need to be synchronised. There is always a duplication of "schema", both your database and application language need a definition of this. It then makes sense to have a tool to, as you say, synchronise this one way or the other. I prefer the ORM approach of the application code being the source of truth, but for others it's completely fair and aloud to go the other way. It comes down the type of work you do, the size and number of teams and who has ownership of the database and data.
- Chris_Newton 4y ago
  There is always a duplication of "schema", both your database and application language need a definition of this. Why can’t an application just run SQL queries directly against the database? I agree that there are many reasonable ways to manage a database, but not all of them involve reproducing the entire schema directly in client code the way an ORM does.
- 4y ago