Can’t agree with this enough. The worst argument I see is against embedding one langauge (SQL) inside another, as if the average project isn’t already a mix of shell, program, web, and/or Makefile code.
Stored procs become more critical when you have many different services smashing the same database and possibly not respecting divisions of responsibility, among other things.
The first thing I do when I read an ORM article is control-f for ‘Hibernate.’ This article has one, though the author has apparently worked with other ORMs as well.
At my first job, our DBA forbid the usage of any kind of ORM. They did, however, provide a sample class which, after find/replacing the names, did provide very simple features, with SQL calls built into each method. Later, after using ActiveRecord, and seeing that most of the basic queries were identical….. yeah.
Basically, what ORMs have taught me is that I don’t need to care about SQL, because by the time that it matters, someone else on my team will have that expertise, and they can simply replace the methods I’m calling with a specialized version that does the efficient query.
Give me
Client.find(10)
over
SELECT * FROM clients WHERE (clients.id = 10) LIMIT 1
any day. Give me
Category.joins(:posts)
over
SELECT categories.* FROM categories INNER JOIN posts ON posts.category_id = categories.id
SELECT "posts"."id" AS t0_r0, ... "comments"."updated_at" AS t1_r5 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE (comments.visible = 1)
Yes, functions over pure SQL strings. However, you don’t really address what is common problem with ORMs at any kind of scale: fetching useless information. Often you only need a small portion of the data in an object. In that sense I think ORMs are quite wrong because you really want to think of these things in terms of table of data rather than objects.
What does that actually evaluate to, though? My point is not that one should use function calls over SQL, it’s what do these things look like in the end? A bunch of half-filled objects? A bunch of dictionaries? Tuples? The problem with ORMs, IMO, is that it doesn’t make sense to map query to objects (unless you’re ok with half-full objects).
Basically, what ORMs have taught me is that I don’t need to care about SQL, because by the time that it matters, someone else on my team will have that expertise…
That sounds like a good use case for the continued use of the ORM as the database interface in the application. And that’s how my work started too, until it didn’t work any more due to efficiency constraints and logic complexity.
And due to the DevOps movement (as well as the fact that my company doesn’t have enough people), I am both the DBA and the developer. Hence, I’m in the process of changing the mindset of the app: the objects in the app do not map to objects in the database, but rather the results of the queries. The queries have turned into templated SQL using the data descriptions of the tables in the app. At this point, the ORM does little to nothing in the app anymore.
I’ve traded off the flexibility of combining the objects at the app level for the efficiency of doing more work in the database. What apy says is the key: the database is not objects, but data.
As I mentioned in my other response (it just makes more sense here, I should have read more before responding): this is actually the important distinction! Results vs objects is very far apart when you usually do not need all of an object.
And I think this is an interesting thing about the ORMs I’ve worked with (primarily ActiveRecord though). They do two things, one is the actual mapping, and the other is exposing a powerful DSL to allow for composable queries. There’s a lot of value to a DSL, and the only thing that isn’t clear to me is the path toward optimization. I suppose if we combine a nice DSL with well written functions that deals with most of the pain.
If we remove objects and stick to maps and lists, that’s nice in most cases.
I’ve never worked with korma but I do like the concept.
Performance: premature optimization in many cases (and people all too frequently give up on ORMs at the drop of a hat, rather than looking at the ORM’s performance facilities). If you know you’ll need the performance of raw SQL then sure, use it from the start, just like if you know you’ll need the performance of assembly language then you should write your program in it. But most programs don’t.
Migration: I don’t know what the author was doing, but I’ve found ORM facilities very helpful for this. You can keep the definition of your tables in code and avoid writing DDL entirely.
Identifiers: why do you want your database to manage your identifiers? If you need to be able to construct circular data structures and save them in a single transaction or something, UUID-as-pkey works and you can generate it before inserting into the database.
Transactions: there’s a reason we prefer lexical scoping. You would have the same problems of “here’s a useful procedure that can only be used in certain contexts” in SQL - you just don’t notice because SQL doesn’t really support reusable procedures.
I will go to a large amount of trouble to avoid putting logic in SQL because the tooling is so inadequate compared to a general-purpose programming language. There are no unit tests, no libraries, no generics….
Optimization here was not premature. This grew out of many months of profiling and testing. It started out as ORM code and became templated SQL once I found it too complex to try and make the ORM produce queries that were performant.
With respect to migration, ORMs don’t help much when you actually have to move or change data instead of just define tables. If all you are doing is adding columns, then that’s easy. If you have to alter data, that’s something else entirely.
As for identifiers, you are succumbing to the “if I don’t see it, it doesn’t happen” way of thinking. Sometimes, you really are stuck with integer ids generated from a sequence. (And converting them to UUIDs is a good exercise in migration!)
Finally, with transactions, I didn’t say that they don’t affect SQL, I’m just pointing out they don’t fit well in typical programming languages.
Optimization here was not premature. This grew out of many months of profiling and testing. It started out as ORM code and became templated SQL once I found it too complex to try and make the ORM produce queries that were performant.
Well, all I have is my own experience, which is that in the vast majority of cases the vast majority of business functionality can be implemented with acceptable performance under an ORM, and with less initial development time and, far more importantly, a smaller and more maintainable codebase. I’ve seen cases where some hand-coded SQL was necessary, but none that justified wholesale chucking-out of the ORM. But I guess in the absence of a formal study we’re all just tossing anecdotes around.
With respect to migration, ORMs don’t help much when you actually have to move or change data instead of just define tables. If all you are doing is adding columns, then that’s easy. If you have to alter data, that’s something else entirely.
Disagree. Converting data from one format to another is something general purpose programming languages are very good at, and SQL not so much. UPDATE external_id_new = CONCAT(“1:”, CAST(external_id AS VARCHAR)); (real-world example) is already getting unreadable; as a rails migration it’s as trivial as it should be.
Sometimes, you really are stuck with integer ids generated from a sequence.
Sometimes, sure, and it’s a case DBs are optimized for. But even if that’s what you need, it’s really not that hard to do in application code.
with transactions, I didn’t say that they don’t affect SQL, I’m just pointing out they don’t fit well in typical programming languages.
Right, but you can’t consider that in isolation. Is transaction management cumbersome in typical languages? Sure, but the question is whether it’s more or less cumbersome than SQL. (And FWIW in a modern typed language it becomes very easy to accumulate database actions as a monad and control the transactions as we apply them).
I’ve spent a significant amount of time using both ORM’s and SQL in modest sized web applications. For just about every rung in the entire stack using SQL with proper conventions is better. The databases don’t get needlessly punished. Troubleshooting is far more straightforward. Populating response objects is significantly faster. Writing code to query the database doesn’t require a learning curve nearly as steep as with an ORM. Queries tend to be more easily maintained and tested. I’ve seen instances that people didn’t even realize they copied code that queries the DB because of the ORM abstraction. “Yes, you hit the database 35 times to handle that request.” When it comes time to optimize (and that time will come), you’ll be outside the ORM anyhow and then you’ll be maintaining both faculties anyhow. I’ve also seen countless reporting people start to say, “LOOK WHAT WE CAN DO!” The enthusiasm quickly fades once they realize they can only barely do basic queries with an ORM, but much past that you start to hear, “I could just write this in SQL.”
I am plenty familiar with the failure modes of ORM, but seeing the talk here and elsewhere recently about stored procedures and SQL templates stored in files, I have to wonder: am I the only one who routinely has to generate SQL whose structure (not merely contents) won’t be known until runtime? Once you offer even a little bit of query flexibility to your users you are going to want to be able to compose parts of queries, and while you certainly don’t need full-fledged ORM to do that, you certainly need at least a bit of a DSL or some kind of structured representation of SQL. You sure as hell don’t want to be doing it by passing around SQL fragments as strings (two words: table aliases).
I really like the Postmodern Lisp library (disclaimer: I’ve written a couple minor patches) and its SQL DSL, S-SQL. Both make it very easy to build queries dynamically without taking you too far away from the SQL into object magic land.
The three main points that I usually talk about when opposing SP’s are:
They (generally) aren’t stored in source control. Especially in larger codebases, this complicates deployments and integration substantially.
SQL makes expressing lots of kinds of business logic awkward at best and horrific/impossible at worst.
Automated testing facilities for SQL and stored procedures is lackluster or non-existent. In either case they rely on - often complex - data being populated and make de-coupling hard.
They also tend to complicate reasoning about system performance, which isn’t necessarily dispositive but does make me dislike them. Also, the languages are uniformly terrible.
Can’t agree with this enough. The worst argument I see is against embedding one langauge (SQL) inside another, as if the average project isn’t already a mix of shell, program, web, and/or Makefile code.
SQL doesn’t have to mean strings and runtime errors though:
https://hackage.haskell.org/package/esqueleto
Stored procs become more critical when you have many different services smashing the same database and possibly not respecting divisions of responsibility, among other things.
esqueleto is very good and I wish Scala had an equivalent. We are using Slick at work and it is very bad at abstracting SQL in a sensible way.
A type-safe DSL for SQL is all I want!
I’ve had some success with Squeryl
The first thing I do when I read an ORM article is control-f for ‘Hibernate.’ This article has one, though the author has apparently worked with other ORMs as well.
At my first job, our DBA forbid the usage of any kind of ORM. They did, however, provide a sample class which, after find/replacing the names, did provide very simple features, with SQL calls built into each method. Later, after using ActiveRecord, and seeing that most of the basic queries were identical….. yeah.
Basically, what ORMs have taught me is that I don’t need to care about SQL, because by the time that it matters, someone else on my team will have that expertise, and they can simply replace the methods I’m calling with a specialized version that does the efficient query.
Give me
Client.find(10)over
SELECT * FROM clients WHERE (clients.id = 10) LIMIT 1any day. Give me
Category.joins(:posts)over
SELECT categories.* FROM categories INNER JOIN posts ON posts.category_id = categories.ideven more. Give me
Post.includes(:comments).where("comments.visible" => true)over
SELECT "posts"."id" AS t0_r0, ... "comments"."updated_at" AS t1_r5 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id" WHERE (comments.visible = 1)the most.
Yes, functions over pure SQL strings. However, you don’t really address what is common problem with ORMs at any kind of scale: fetching useless information. Often you only need a small portion of the data in an object. In that sense I think ORMs are quite wrong because you really want to think of these things in terms of table of data rather than objects.
Give me
Client.pluck(:id, :name)over
SELECT clients.id, clients.name FROM clientsAny day. Especially when combined with those other, more complex queries.
What does that actually evaluate to, though? My point is not that one should use function calls over SQL, it’s what do these things look like in the end? A bunch of half-filled objects? A bunch of dictionaries? Tuples? The problem with ORMs, IMO, is that it doesn’t make sense to map query to objects (unless you’re ok with half-full objects).
It gives you
That sounds like a good use case for the continued use of the ORM as the database interface in the application. And that’s how my work started too, until it didn’t work any more due to efficiency constraints and logic complexity.
And due to the DevOps movement (as well as the fact that my company doesn’t have enough people), I am both the DBA and the developer. Hence, I’m in the process of changing the mindset of the app: the objects in the app do not map to objects in the database, but rather the results of the queries. The queries have turned into templated SQL using the data descriptions of the tables in the app. At this point, the ORM does little to nothing in the app anymore.
I’ve traded off the flexibility of combining the objects at the app level for the efficiency of doing more work in the database. What apy says is the key: the database is not objects, but data.
I dunno, it sounds like you’ve created an ORM to me, just with ‘query result objects’ instead of domain objects.
As I mentioned in my other response (it just makes more sense here, I should have read more before responding): this is actually the important distinction! Results vs objects is very far apart when you usually do not need all of an object.
Right, and my point is that my ORM can do this for me. I don’t need to lose all of its benefits just to get a result set.
And I think this is an interesting thing about the ORMs I’ve worked with (primarily ActiveRecord though). They do two things, one is the actual mapping, and the other is exposing a powerful DSL to allow for composable queries. There’s a lot of value to a DSL, and the only thing that isn’t clear to me is the path toward optimization. I suppose if we combine a nice DSL with well written functions that deals with most of the pain.
If we remove objects and stick to maps and lists, that’s nice in most cases.
I’ve never worked with korma but I do like the concept.
Performance: premature optimization in many cases (and people all too frequently give up on ORMs at the drop of a hat, rather than looking at the ORM’s performance facilities). If you know you’ll need the performance of raw SQL then sure, use it from the start, just like if you know you’ll need the performance of assembly language then you should write your program in it. But most programs don’t.
Migration: I don’t know what the author was doing, but I’ve found ORM facilities very helpful for this. You can keep the definition of your tables in code and avoid writing DDL entirely.
Identifiers: why do you want your database to manage your identifiers? If you need to be able to construct circular data structures and save them in a single transaction or something, UUID-as-pkey works and you can generate it before inserting into the database.
Transactions: there’s a reason we prefer lexical scoping. You would have the same problems of “here’s a useful procedure that can only be used in certain contexts” in SQL - you just don’t notice because SQL doesn’t really support reusable procedures.
I will go to a large amount of trouble to avoid putting logic in SQL because the tooling is so inadequate compared to a general-purpose programming language. There are no unit tests, no libraries, no generics….
Optimization here was not premature. This grew out of many months of profiling and testing. It started out as ORM code and became templated SQL once I found it too complex to try and make the ORM produce queries that were performant.
With respect to migration, ORMs don’t help much when you actually have to move or change data instead of just define tables. If all you are doing is adding columns, then that’s easy. If you have to alter data, that’s something else entirely.
As for identifiers, you are succumbing to the “if I don’t see it, it doesn’t happen” way of thinking. Sometimes, you really are stuck with integer ids generated from a sequence. (And converting them to UUIDs is a good exercise in migration!)
Finally, with transactions, I didn’t say that they don’t affect SQL, I’m just pointing out they don’t fit well in typical programming languages.
Well, all I have is my own experience, which is that in the vast majority of cases the vast majority of business functionality can be implemented with acceptable performance under an ORM, and with less initial development time and, far more importantly, a smaller and more maintainable codebase. I’ve seen cases where some hand-coded SQL was necessary, but none that justified wholesale chucking-out of the ORM. But I guess in the absence of a formal study we’re all just tossing anecdotes around.
Disagree. Converting data from one format to another is something general purpose programming languages are very good at, and SQL not so much. UPDATE external_id_new = CONCAT(“1:”, CAST(external_id AS VARCHAR)); (real-world example) is already getting unreadable; as a rails migration it’s as trivial as it should be.
Sometimes, sure, and it’s a case DBs are optimized for. But even if that’s what you need, it’s really not that hard to do in application code.
Right, but you can’t consider that in isolation. Is transaction management cumbersome in typical languages? Sure, but the question is whether it’s more or less cumbersome than SQL. (And FWIW in a modern typed language it becomes very easy to accumulate database actions as a monad and control the transactions as we apply them).
I’ve spent a significant amount of time using both ORM’s and SQL in modest sized web applications. For just about every rung in the entire stack using SQL with proper conventions is better. The databases don’t get needlessly punished. Troubleshooting is far more straightforward. Populating response objects is significantly faster. Writing code to query the database doesn’t require a learning curve nearly as steep as with an ORM. Queries tend to be more easily maintained and tested. I’ve seen instances that people didn’t even realize they copied code that queries the DB because of the ORM abstraction. “Yes, you hit the database 35 times to handle that request.” When it comes time to optimize (and that time will come), you’ll be outside the ORM anyhow and then you’ll be maintaining both faculties anyhow. I’ve also seen countless reporting people start to say, “LOOK WHAT WE CAN DO!” The enthusiasm quickly fades once they realize they can only barely do basic queries with an ORM, but much past that you start to hear, “I could just write this in SQL.”
Previous discussion on ORMs: https://lobste.rs/s/fsx3dq/orm_is_an_anti-pattern
Ahaha, and I used almost the exact same examples.
I am plenty familiar with the failure modes of ORM, but seeing the talk here and elsewhere recently about stored procedures and SQL templates stored in files, I have to wonder: am I the only one who routinely has to generate SQL whose structure (not merely contents) won’t be known until runtime? Once you offer even a little bit of query flexibility to your users you are going to want to be able to compose parts of queries, and while you certainly don’t need full-fledged ORM to do that, you certainly need at least a bit of a DSL or some kind of structured representation of SQL. You sure as hell don’t want to be doing it by passing around SQL fragments as strings (two words: table aliases).
I really like the Postmodern Lisp library (disclaimer: I’ve written a couple minor patches) and its SQL DSL, S-SQL. Both make it very easy to build queries dynamically without taking you too far away from the SQL into object magic land.
Out of curiosity, what are the objections to stored procedures?
The three main points that I usually talk about when opposing SP’s are:
They also tend to complicate reasoning about system performance, which isn’t necessarily dispositive but does make me dislike them. Also, the languages are uniformly terrible.