6 ms·

I never get the ORM hate for CRUD apps. It gives a centralized place to understand what data is in play in the application and adding a new column to the datab

I never get the ORM hate for CRUD apps. It gives a centralized place to understand what data is in play in the application and adding a new column to the database means updating a class / struct / type in one location and checking the use of that within the code base. Without an ORM and with poor discipline I end up having to go find every query in the app referencing that table and check if I need to update the query which usually means changing the columns, the ? value placeholder and putting the correct value in the associated values container. It all seems like a lot of tedium and ripe for simple typos in the queries that is easily avoided by using beneficial tooling.

amelius 6y ago
The hate for CRUD apps stems from it being too simple to be called a "high-tech" solution, that's all.
- arcturus17 6y ago
  Many global apps start as CRUD apps, and only become high-tech with scale. And even at hyperscale companies a lot of software engineering work is probably "basic CRUD" - reading from one system and writing into another, without applying a radical transformation to the data in the process. And even doing that well is quite challenging, which is why the average software developer gets paid well. It's great if your talent allows you to do much more than that, but I wouldn't deride it, and much less hate it, when your own success is likely going to have to rely on competent people around you doing that job extremely well.
nobody0 6y ago
Yeah, as long as it's well designed, like LINQ. ORM is necessary, otherwise we will find ourselves in the old days again, a large part of string concatenations, security problems, etc,.
- jonnypotty 6y ago
  There is so much code out there to help you do typed params and to stop you having to concat strings to make queries. You don't need an orm for this.
bendiksolheim 6y ago
There is a tradeoff here, as usual. The hate for ORMs comes from more advanced applications, not simple CRUD apps. When you have 5 tables with no relations, or maybe a simple one-to-many relation at most, an ORM is the perfect solution. The problem comes when you have 25 tables, complex relations, complex updates and need to optimize your queries for better performance. You now need to know the inner workings of you ORM, and pray that there are sane ways of configuring these things. Knowing the inner workings of these enormous beasts are not always easy – at least not easier than writing the SQL yourself.
- pmontra 6y ago
  This and the infamous question "how do I write this complex SQL query in ${ORM}?" I ended up using raw queries in many of the projects I worked on for more than 2 or 3 years. It's more cost effective than trying to find the right combination of ORM statements.
  - arcturus17 6y ago
    Yes, but as I say in the sibling comment, could this not be the best of both worlds?
    - pmontra 6y ago
      Almost. The best of both worlds would be an ORM that can understand the SQL query and populate its objects/structs/whatever with the result. I have to manually process the results now. If I had an ORM like that maybe I would write all SELECTs directly in SQL. What makes that impractical in projects with multiple developers is that about half of them don't know SQL nowadays. They know how to use the ORM and the language to write CRUD queries and that's all. If they see SELECT * FROM invoices WHERE customer_id = ? they probably understand it. Nested queries, HAVING, GROUP BY... oops! CTEs... OMG! This means that sometimes they write three or four queries when one would be enough.
- arcturus17 6y ago
  For complex data models, I find the ORM provides me with a lot of help in the form of IDE hints. I find the ORM schemas also provide documenting/referential value with less code than the raw SQL equivalent. For the scenario you're describing, you could still use a mix of an ORM and raw SQL as needed.
blacktriangle 6y ago
Here's the root of my problem: ORMs always force their world view onto the database, which is completely backwards from how things should work. The database is the most important part of the application, it will probably outlive your current code base. Yet your data model ends up getting warped to support a single ORMs worldview. New table columns should never cause problems for existing code because you should never use `SELECT *` and you should never use positional semantics in your SQL, put a linter on it. As for typos, that's what SQL builder libraries are for, they allow you to work with your own language constructs instead of mashing together strings manually. ORMs are this thing that developers seem to think is a life preserver but as time goes on morphs into an anchor.
- fiedzia 6y ago
  > ORMs always force their world view onto the database, which is completely backwards from how things should work. I have different experience. There are situations where you work with database given of course, but in many projects ORM enforces good and unified practices (rows have unique id, constraints and indexes are properly named, relationships are done in always the same way), and generally database is just a servant to developer idea of data model, not a god to worship and bend to.
- leetrout 6y ago
  > you should never use positional semantics in your SQL If I'm understanding you, that along with your mention of a SQL builder means, for example, in Python, you'd expect CreateUser(name="blah") As opposed to something like db.Exec("INSERT INTO users (name) values (?)", "blah") Is that correct? I think we're agreeing on reducing the amount of raw SQL being tossed around within the code.
  - MereInterest 6y ago
    SQL columns can be referred to either by name or by index. Avoiding positional semantics means using "ORDER BY transaction_date ASC" rather than "ORDER BY 3 ASC". On the calling code, your python library may return either a tuple or a dictionary. The dictionary is the right way to do it, because then you access parameters by name, and reordering or adding parameters to the query output doesn't break your script.
soco 6y ago
If all you do is CRUD then fine. However databases are meant to do so much more than that. SQL means structured query language, it supports and enables complex queries. You could argue all those queries can be done in code and you'd be right, while paying the price of inefficient big data transfers and duplicating logic in possibly quite slower ways.
falcolas 6y ago
My distaste is simple: The abstraction that is an ORM is leaky AF. Optimizing an automatic query is a PITA. Doing more than simple joins across tables is hard. Falling back to pure SQL quickly becomes untenable across an application (because each instance quickly becomes an ad-hoc workaround). And to be frank, CRUD apps created with an ORM that remain as simple CRUD apps are the vast minority of the work that I’ve done in my career. Everything else evolves into something where we inevitably end up working around the ORM more than using it as-is. The solution in my opinion: SQL generators (like linq and such). ORMs often include these as an after thought, but pulling in a full ORM for the sql generation is like pulling in a semi truck for its generator; it’s too easy to start experimenting with the toys stored in the trailer.
- xtracto 6y ago
  > The abstraction that is an ORM is leaky AF Add to that, as someone mentioned above ORMs tend to be "common least set of features". And if you for some reason want to use a specific feature of your RDBMS you have to jump through hoops and the ORM "illusion" ends.
NDizzle 6y ago
Have you ever seen what EntityFramework produces for queries? I wish I could share some of the beautiful disasters that it has produced for my company.
- leetrout 6y ago
  I have not. I assumed it was analogous to Django / SQLAlchemy where you could always pull the escape handle use raw SQL when it was truly necessary.
  - csharptwdec19 6y ago
    You can do raw SQL with EF, but you lose a lot in the process. EF Core -can- be better than the older, much more reviled EF as far as generated SQL, but in EF core 3 they did a pretty big breaking change on joins, bringing behavior closer to EF6's tendency to do a cartesian asplosion
- nightski 6y ago
  It produces what you tell it. The thing is Linq is a more general language than SQL. If you tell EF to generate something that would be really hard to translate to SQL then of course it's going to do a bad job. Just by taking a few seconds to think how the query you are writing will translate into SQL goes a long way towards helping EF produce good, clean queries. EF still provides many benefits on top of this so imo it's worth it but at the end of the day whether you are writing SQL or EF you have to understand the basics of how databases work.
- MaKey 6y ago
  I have. Recently I was looking at an Azure SQL DB and a long running query caught my eye. "Beautiful disaster" is an apt description for the 123 line query I saw.
scottious 6y ago
ORMs are great for the simple CRUD case. That's how people get lured in. It's when things get complicated and performance is important that they start to feel like a hindrance. I've had to wrestle with ORMs only to figure out that it's generating bad queries or lots of queries when it could do it with one query. I've usually had to end up subverting the ORM or throwing it away entirely in almost every case.
cmckn 6y ago
> I end up having to go find every query in the app referencing that table This sounds like bad design, why would you have SQL queries for the same table littered across a project, rather than in context with each other in the same file/class/interface/etc.? Most of the CRUD experience I have is in Java, and I've preferred using JDBI over ORM's like Hibernate. You write a CRUD interface for the table and annotate the methods with (something very close to) plain old SQL. I can define how a SQL row gets mapped to a POJO, though in many cases the default mapping logic is fine. I know exactly what SQL will be executed, at compile time. I use Liquibase to manage the database schema. It's about as painless as it gets; I've had many obtuse errors to resolve attempting to use an ORM. I think most importantly, I'm familiar enough with the schema that if and when I need to do an ad-hoc query to give some data to an analyst, or debug an issue, I know what I'm working with.
- mdpopescu 6y ago
  Yes, it is bad design, because you have one Sql.RunQuery method returning a DataTable and it's used every-freaking-where. Then you have to convert the data in every place back to the proper objects, paying special attention to the difference between DBNull and regular null. I've used both ORMs and direct ADO queries (in C#), and while ORMs can trip you in special cases like the N+1 SELECT problem, the raw queries are a pain everywhere. It's just to easy to say "ah, I need a quick SELECT here, I'll just return this bunch of records and I know that record[5] is a string with the value I need". Hello, versioning hell.

amelius 6y ago

The hate for CRUD apps stems from it being too simple to be called a "high-tech" solution, that's all.

arcturus17 6y ago
Many global apps start as CRUD apps, and only become high-tech with scale. And even at hyperscale companies a lot of software engineering work is probably "basic CRUD" - reading from one system and writing into another, without applying a radical transformation to the data in the process. And even doing that well is quite challenging, which is why the average software developer gets paid well. It's great if your talent allows you to do much more than that, but I wouldn't deride it, and much less hate it, when your own success is likely going to have to rely on competent people around you doing that job extremely well.

nobody0 6y ago

Yeah, as long as it's well designed, like LINQ. ORM is necessary, otherwise we will find ourselves in the old days again, a large part of string concatenations, security problems, etc,.

jonnypotty 6y ago
There is so much code out there to help you do typed params and to stop you having to concat strings to make queries. You don't need an orm for this.

bendiksolheim 6y ago

There is a tradeoff here, as usual. The hate for ORMs comes from more advanced applications, not simple CRUD apps. When you have 5 tables with no relations, or maybe a simple one-to-many relation at most, an ORM is the perfect solution. The problem comes when you have 25 tables, complex relations, complex updates and need to optimize your queries for better performance. You now need to know the inner workings of you ORM, and pray that there are sane ways of configuring these things. Knowing the inner workings of these enormous beasts are not always easy – at least not easier than writing the SQL yourself.

pmontra 6y ago
This and the infamous question "how do I write this complex SQL query in ${ORM}?" I ended up using raw queries in many of the projects I worked on for more than 2 or 3 years. It's more cost effective than trying to find the right combination of ORM statements.
- arcturus17 6y ago
  Yes, but as I say in the sibling comment, could this not be the best of both worlds?
  - pmontra 6y ago
    Almost. The best of both worlds would be an ORM that can understand the SQL query and populate its objects/structs/whatever with the result. I have to manually process the results now. If I had an ORM like that maybe I would write all SELECTs directly in SQL. What makes that impractical in projects with multiple developers is that about half of them don't know SQL nowadays. They know how to use the ORM and the language to write CRUD queries and that's all. If they see SELECT * FROM invoices WHERE customer_id = ? they probably understand it. Nested queries, HAVING, GROUP BY... oops! CTEs... OMG! This means that sometimes they write three or four queries when one would be enough.
arcturus17 6y ago
For complex data models, I find the ORM provides me with a lot of help in the form of IDE hints. I find the ORM schemas also provide documenting/referential value with less code than the raw SQL equivalent. For the scenario you're describing, you could still use a mix of an ORM and raw SQL as needed.

blacktriangle 6y ago

Here's the root of my problem: ORMs always force their world view onto the database, which is completely backwards from how things should work. The database is the most important part of the application, it will probably outlive your current code base. Yet your data model ends up getting warped to support a single ORMs worldview. New table columns should never cause problems for existing code because you should never use `SELECT *` and you should never use positional semantics in your SQL, put a linter on it. As for typos, that's what SQL builder libraries are for, they allow you to work with your own language constructs instead of mashing together strings manually. ORMs are this thing that developers seem to think is a life preserver but as time goes on morphs into an anchor.

fiedzia 6y ago
> ORMs always force their world view onto the database, which is completely backwards from how things should work. I have different experience. There are situations where you work with database given of course, but in many projects ORM enforces good and unified practices (rows have unique id, constraints and indexes are properly named, relationships are done in always the same way), and generally database is just a servant to developer idea of data model, not a god to worship and bend to.
leetrout 6y ago
> you should never use positional semantics in your SQL If I'm understanding you, that along with your mention of a SQL builder means, for example, in Python, you'd expect CreateUser(name="blah") As opposed to something like db.Exec("INSERT INTO users (name) values (?)", "blah") Is that correct? I think we're agreeing on reducing the amount of raw SQL being tossed around within the code.
- MereInterest 6y ago
  SQL columns can be referred to either by name or by index. Avoiding positional semantics means using "ORDER BY transaction_date ASC" rather than "ORDER BY 3 ASC". On the calling code, your python library may return either a tuple or a dictionary. The dictionary is the right way to do it, because then you access parameters by name, and reordering or adding parameters to the query output doesn't break your script.

soco 6y ago

If all you do is CRUD then fine. However databases are meant to do so much more than that. SQL means structured query language, it supports and enables complex queries. You could argue all those queries can be done in code and you'd be right, while paying the price of inefficient big data transfers and duplicating logic in possibly quite slower ways.

falcolas 6y ago

My distaste is simple: The abstraction that is an ORM is leaky AF. Optimizing an automatic query is a PITA. Doing more than simple joins across tables is hard. Falling back to pure SQL quickly becomes untenable across an application (because each instance quickly becomes an ad-hoc workaround). And to be frank, CRUD apps created with an ORM that remain as simple CRUD apps are the vast minority of the work that I’ve done in my career. Everything else evolves into something where we inevitably end up working around the ORM more than using it as-is. The solution in my opinion: SQL generators (like linq and such). ORMs often include these as an after thought, but pulling in a full ORM for the sql generation is like pulling in a semi truck for its generator; it’s too easy to start experimenting with the toys stored in the trailer.

xtracto 6y ago
> The abstraction that is an ORM is leaky AF Add to that, as someone mentioned above ORMs tend to be "common least set of features". And if you for some reason want to use a specific feature of your RDBMS you have to jump through hoops and the ORM "illusion" ends.

NDizzle 6y ago

Have you ever seen what EntityFramework produces for queries? I wish I could share some of the beautiful disasters that it has produced for my company.

leetrout 6y ago
I have not. I assumed it was analogous to Django / SQLAlchemy where you could always pull the escape handle use raw SQL when it was truly necessary.
- csharptwdec19 6y ago
  You can do raw SQL with EF, but you lose a lot in the process. EF Core -can- be better than the older, much more reviled EF as far as generated SQL, but in EF core 3 they did a pretty big breaking change on joins, bringing behavior closer to EF6's tendency to do a cartesian asplosion
nightski 6y ago
It produces what you tell it. The thing is Linq is a more general language than SQL. If you tell EF to generate something that would be really hard to translate to SQL then of course it's going to do a bad job. Just by taking a few seconds to think how the query you are writing will translate into SQL goes a long way towards helping EF produce good, clean queries. EF still provides many benefits on top of this so imo it's worth it but at the end of the day whether you are writing SQL or EF you have to understand the basics of how databases work.
MaKey 6y ago
I have. Recently I was looking at an Azure SQL DB and a long running query caught my eye. "Beautiful disaster" is an apt description for the 123 line query I saw.

scottious 6y ago

ORMs are great for the simple CRUD case. That's how people get lured in. It's when things get complicated and performance is important that they start to feel like a hindrance. I've had to wrestle with ORMs only to figure out that it's generating bad queries or lots of queries when it could do it with one query. I've usually had to end up subverting the ORM or throwing it away entirely in almost every case.

cmckn 6y ago

> I end up having to go find every query in the app referencing that table This sounds like bad design, why would you have SQL queries for the same table littered across a project, rather than in context with each other in the same file/class/interface/etc.? Most of the CRUD experience I have is in Java, and I've preferred using JDBI over ORM's like Hibernate. You write a CRUD interface for the table and annotate the methods with (something very close to) plain old SQL. I can define how a SQL row gets mapped to a POJO, though in many cases the default mapping logic is fine. I know exactly what SQL will be executed, at compile time. I use Liquibase to manage the database schema. It's about as painless as it gets; I've had many obtuse errors to resolve attempting to use an ORM. I think most importantly, I'm familiar enough with the schema that if and when I need to do an ad-hoc query to give some data to an analyst, or debug an issue, I know what I'm working with.

mdpopescu 6y ago
Yes, it is bad design, because you have one Sql.RunQuery method returning a DataTable and it's used every-freaking-where. Then you have to convert the data in every place back to the proper objects, paying special attention to the difference between DBNull and regular null. I've used both ORMs and direct ADO queries (in C#), and while ORMs can trip you in special cases like the N+1 SELECT problem, the raw queries are a pain everywhere. It's just to easy to say "ah, I need a quick SELECT here, I'll just return this bunch of records and I know that record[5] is a string with the value I need". Hello, versioning hell.