7 ms·

I dunno. I think there is quite a lot of us that have consistently expressed the stupidity that is wrapping a powerful query language in a mediocre, limited, bu

by lugg 8y ago

I dunno. I think there is quite a lot of us that have consistently expressed the stupidity that is wrapping a powerful query language in a mediocre, limited, buggy, half baked, shoe horned into OO shit what was I saying? Oh yea, ORM. I've always veiwed it as a legitimate (though always over engineered) solution to wrap some repetitive database operations in a consistent manner. The biggest issue with ORM isn't the solution they came up with, half baked and buggy as they are, but the problem they are actually employeed to "solve": developers afraid of SQL. Whether they can't write it, can't learn it, or just straight hate the string representation in their code, it's the main reason people sign on for ORM. Then they prop it up with all manner of baseless reasoning. Theb there is the leakyness. ORMs are usually quite fluid in their language, they make it easy to represent the database in code. The only issue is that it isn't there. It's a separate thing. When you see database code it should be obvious and very far away from business logic. What does ORM do? Mix it all up On purpose. Have fun with all the mocks.

sfilipov 8y ago
I don't disagree overall but I'm using SQLAlchemy (Python) on a daily basis and it works quite well. I agree that if you are building ORM with the intention of hiding so many SQL details that the developers don't need to learn SQL, you'll run into problems. SQLAlchemy on the other hand starts simple, but is flexible enough that you can do inner/outer joins, you have different loading strategies etc. It is quite nice if you are proficient enough in SQL. When writing complicated select queries, I'd start by writing the raw SQL then spend my time representing it in SQLAlchemy. It's a bit less than double the work sometimes, but working with the SQLAlchemy objects in Python is quite nice as long as you pull them in an efficient manner. SQLAlchemy just feels like a well thought out ORM.
- lugg 8y ago
  Never heard of SQLAlchemy. The core part of the lib looks quite nice, the problem is its design is driven by the needs of its ORM. If you define your database properly typed, defaulted, and not null unless an update field that sort of layer isn't needed at all. Using the correct native types on your function params or class structures means you'll never run into type mismatch or cast failures loading or unloading from the db. So I just don't get why the need for ORM. The only thing that needs mapping is the table schema to your data type object/structs. ORMs don't offer anything of value their and often make things you need to do in your job harder.
  - leejo 8y ago
    I'm sick of the argument that those who advocate for the use of an ORM have a dislike of SQL, or simply don't understand it. Perhaps this is true of newer developers who have "grown up" on ORMs, but for the most part advocating for an ORM is simply wanting to employ the "don't repeat yourself" thing and having enough battle scars to know that trying to compose your own SQL statements results in vulnerabilities, bugs, and a mess. > If you define your database properly typed, defaulted, and not null unless an update field that sort of layer isn't needed at all. Which is a laudable goal but not realistic if you've worked on any backend that has even a hint of organic growth or legacy. I know of at least two books on the subject of dealing with anti patterns in and the refactoring of database schemas. Database schemas are the mortgage of technical debt, they can take literally decades to pay off if you don't get them "right" in the first place, or you pivot, or you're growing quickly, or you have a large team. Using an ORM allows you to abstract away and isolate a lot of the technical debt, create single entry points, and then fix those. And in the part where using the ORM's syntax would obfuscate or complicate compared to SQL, then just write your own SQL query anyway and put this in a virtual view. Sure, you can create your own entry points that handle the madness and domain knowledge, with your own SQL handling to put the pieces together that return a nicely mapped object or data structure. Then what have you done? You've written your own ORM.
    - lugg 8y ago
      Vulnerabilities from writing SQL? ORMs do not solve this. Prepared statements and parameterised queries solve this. Bugs? You're shitting me, from what? Untested queries? A mess? Clean it up. You can make a mess with an ORM too. > Which is a laudable goal but not realistic if you've worked on any backend that has even a hint of organic growth or legacy. Except it is realistic and I have cleaned up a lot of "organic growth" and "legacy code" like this. > I know of at least two books on the subject of dealing with anti patterns in and the refactoring of database schemas. Yea.. where do you think I got these ideas? > Database schemas are the mortgage of technical debt, they can take literally decades to pay off if you don't get them "right" in the first place, or you pivot, or you're growing quickly, or you have a large team. Database design is not hard and your schemas/changes should be peer reviewed like any code. It's also not static, yes, some, SOME, very few situations are essentially unchangeable. But most database design is easily extended without causing breaking change. Breaking change is more problematic but not in any way more or less than any other breaking change. Then again, I'm kind of curious how any of these problems are solved by an ORM.
Demiurge 8y ago
Why is it such a 'one vs other' argument? This is like arguing against levels of APIs, a completely moot argument without proper context for usage.'Wrapping' doesn't prohibit you to go 'lower' if you want.
- lugg 8y ago
  Why is it one vs other? It's not, you can run them both. The question is why you would. What does an ORM solve for you? This is the only mapping I need from an ORM: result = (object) row row = (array) result SQL does everything an ORM does better. I'm not sure how you can call moving from a low level programming language to a domain specific language like SQL "going lower". It's not and ORMs are usually so bad they're a regression in what you can do. Also, when was the last time a change to the SQL spec broke your app?what about the last times you had to migrate ORM code?
  - ken 8y ago
    > Also, when was the last time a change to the SQL spec broke your app?what about the last times you had to migrate ORM code? That's kind of an arbitrary categorization. An ORM is just a library. If you asked me when the last time I had to update my code because a library changed interfaces, I'd have an answer for you (less than a month ago, I'm sure). I'm not going to stop using libraries, though. I've had to update my programs several times because my compiler was updated, too. That may make me choosier in the future about which compiler I pick, but I'm not going to stop using compilers altogether.
- cmiles74 8y ago
  I think things have very much moved away from the "one vs. the other" stance. Back in the late 90's, I think people really did think that they could spend time on an ORM tool and never have to think about the database. In my own work, the ORM is there for lots of boring stuff, like fetch the data and push it into an edit form and then push the contents of the edit form back into the database. When it comes to q query of any consequence I end up writing SQL and then presenting a data set to the ORM, something that it can understand. Maybe it's my age, but I'm still not a big stored procedure guy. But you look at some of the interesting things you can do with SQL, it seems silly to try and interact with it only through someone else's ORM tool.
wswope 8y ago
Could you share some good practices for separating db code and business logic? I feel like when I don't use an ORM, there winds up being inline SQL all over the codebase.
- _bohm 8y ago
  I'm a big fan of the pattern introduced by Yesql for Clojure [0], also ported to Python as anosql [1] [0] https://github.com/krisajenkins/yesql https://github.com/krisajenkins/yesql [1] https://github.com/honza/anosql https://github.com/honza/anosql
  - wswope 8y ago
    Wow, I wish I'd learned about anosql a long time ago! I love the elegance of it - thank you for the rec!
  - WhatIsDukkha 8y ago
    I call this pattern "dotsql". (I've made private golang and rust crates for it). You avoid inline sql like the plague and your editor can have a nice dedicated sql buffer with all the bells and whistles. I wish it was a more common pattern, it just makes soooo much more sense.
- btschaegg 8y ago
  I don't think there's a one-size-fits-all solution to this, since different domains require different guarantees from your storage medium. For example, if you need to combine multiple actions with a certain level of transaction safety, defining an interface for that can get tricky. Overall, I tend to agree with Uncle Bob's "Clean Architecture" [1][2] approach when it comes to DBs, though: The DB usually (rembember that there is an exception to every rule!) has no business of being the "center" of an application. It should provide you with functionality, not dictate how your business logic works. If you can pull out your DB and give it a sensible interface, you still might end up having a lot of inline SQL queries, but they are not littered all over your codebase; they live inside a narrow scope (a plugin, say). Also, the SQL schema and queries in this case often are quite boring -- the tricky bit is coming up with a good interface that provides all the guarantees you need to uphold (complicated transactions maybe?). And remember that you need these guarantees because of how your business domain works, not because of your DB. Perhaps you can get rid of some of them if you organize your storage (i.e. DB) in a specific way? [1]: http://blog.cleancoder.com/uncle-bob/2012/08/13/the-clean-architecture.html http://blog.cleancoder.com/uncle-bob/2012/08/13/the-clean-ar... [2]: https://youtu.be/o_TH-Y78tt4?t=2565 https://youtu.be/o_TH-Y78tt4?t=2565 Edit: Also note that there's no reason not to use a ORM in this way, too (if it suits you). The important part is the same: The ORM has no business dictating how your business logic should be implemented, and if you try to remove it from your core application, you will end up doing the same things. Now, if your ORM makes the implementation easier for you, go ahead and use it. But don't expect things to change if you put ORM-generated code instead of inline SQL at the center of your application.
ken 8y ago
> the problem they are actually employeed to "solve": developers afraid of SQL. Whether they can't write it, can't learn it, or just straight hate the string representation in their code, it's the main reason people sign on for ORM. This sounds awfully like a straw man. I've worked on several projects that used an ORM, and it was never because developers couldn't write SQL. ORMs solve a real problem: SQL isn't composable. That's it. Sooner or later, every non-trivial program needs to compose arbitrary pieces of one query with arbitrary pieces of another, so you'll either use an ORM (i.e., native data structures instead of raw strings), or essentially write your own ad-hoc one. It has nothing to do with lack of ability. The staunchest ORM advocate I've worked with was also the first to pull out the debugger and single-step the MySQL driver to track down a bug (that one was nasty -- it was indeed in the driver). I use an HTML generator, and a compiler, too, and it's not because I'm "afraid" of assembly language or that I "can't learn" HTML. These abstractions have real value.
- karmakaze 8y ago
  > ORMs solve a real problem: SQL isn't composable. That's it. How so? I've always found the opposite. I can build up a compilex query referencing many tables in SQL without impediment but am unable to do the same with an ORM.
  - platz 8y ago
    OP means combining two different queries or clauses into a bigger query/clause You cannot easily "modify" a sql statement. There are type-safe DSLs in certain languages (if they have an expressive enough type system) that let you do this, but they are uncommon
    - 1stranger 8y ago
      They're not that uncommon. You can do this with SQLAlchemy in Python or jOOQ in Java.
    - vp8989 8y ago
      You could use views. Although personally I dislike adding this layer of obfuscation. Generally devs are very underskilled in SQL (relative to it's importance) and ORMs hiding the SQL IMO is one of the reasons why. There is great value in having the entire SQL easily visible in your source code. If there is a performance issue with a specific query my app is running, I can very quickly pull it out and into a database IDE and see what's going on using the more detailed tools available (REPL, execution plan/statistics usage analysis etc). If the query is composed from a bunch of ORM function calls, I have to step through the program to first generate it. Reducing friction around debugging allows your devs to become more capable in fixing things or making them work better, and SQL is no exception to that.
rdiddly 8y ago
I laughed at this because it's so true... yet I still use ORMs gleefully so I can remain in my pathetic little comfort zone when the queries get especially complex. (Although ironically, my particular ORM of choice tends to fall apart when queries get complex, and you have to do clumsy iterations etc. Also after viewing this presentation I start to wonder how many of the built-in optimizations are being countermanded/ruined by the ORM.)

by lugg 8y ago

sfilipov 8y ago

I don't disagree overall but I'm using SQLAlchemy (Python) on a daily basis and it works quite well. I agree that if you are building ORM with the intention of hiding so many SQL details that the developers don't need to learn SQL, you'll run into problems. SQLAlchemy on the other hand starts simple, but is flexible enough that you can do inner/outer joins, you have different loading strategies etc. It is quite nice if you are proficient enough in SQL. When writing complicated select queries, I'd start by writing the raw SQL then spend my time representing it in SQLAlchemy. It's a bit less than double the work sometimes, but working with the SQLAlchemy objects in Python is quite nice as long as you pull them in an efficient manner. SQLAlchemy just feels like a well thought out ORM.

lugg 8y ago
Never heard of SQLAlchemy. The core part of the lib looks quite nice, the problem is its design is driven by the needs of its ORM. If you define your database properly typed, defaulted, and not null unless an update field that sort of layer isn't needed at all. Using the correct native types on your function params or class structures means you'll never run into type mismatch or cast failures loading or unloading from the db. So I just don't get why the need for ORM. The only thing that needs mapping is the table schema to your data type object/structs. ORMs don't offer anything of value their and often make things you need to do in your job harder.
- leejo 8y ago
  I'm sick of the argument that those who advocate for the use of an ORM have a dislike of SQL, or simply don't understand it. Perhaps this is true of newer developers who have "grown up" on ORMs, but for the most part advocating for an ORM is simply wanting to employ the "don't repeat yourself" thing and having enough battle scars to know that trying to compose your own SQL statements results in vulnerabilities, bugs, and a mess. > If you define your database properly typed, defaulted, and not null unless an update field that sort of layer isn't needed at all. Which is a laudable goal but not realistic if you've worked on any backend that has even a hint of organic growth or legacy. I know of at least two books on the subject of dealing with anti patterns in and the refactoring of database schemas. Database schemas are the mortgage of technical debt, they can take literally decades to pay off if you don't get them "right" in the first place, or you pivot, or you're growing quickly, or you have a large team. Using an ORM allows you to abstract away and isolate a lot of the technical debt, create single entry points, and then fix those. And in the part where using the ORM's syntax would obfuscate or complicate compared to SQL, then just write your own SQL query anyway and put this in a virtual view. Sure, you can create your own entry points that handle the madness and domain knowledge, with your own SQL handling to put the pieces together that return a nicely mapped object or data structure. Then what have you done? You've written your own ORM.
  - lugg 8y ago
    Vulnerabilities from writing SQL? ORMs do not solve this. Prepared statements and parameterised queries solve this. Bugs? You're shitting me, from what? Untested queries? A mess? Clean it up. You can make a mess with an ORM too. > Which is a laudable goal but not realistic if you've worked on any backend that has even a hint of organic growth or legacy. Except it is realistic and I have cleaned up a lot of "organic growth" and "legacy code" like this. > I know of at least two books on the subject of dealing with anti patterns in and the refactoring of database schemas. Yea.. where do you think I got these ideas? > Database schemas are the mortgage of technical debt, they can take literally decades to pay off if you don't get them "right" in the first place, or you pivot, or you're growing quickly, or you have a large team. Database design is not hard and your schemas/changes should be peer reviewed like any code. It's also not static, yes, some, SOME, very few situations are essentially unchangeable. But most database design is easily extended without causing breaking change. Breaking change is more problematic but not in any way more or less than any other breaking change. Then again, I'm kind of curious how any of these problems are solved by an ORM.

Demiurge 8y ago

Why is it such a 'one vs other' argument? This is like arguing against levels of APIs, a completely moot argument without proper context for usage.'Wrapping' doesn't prohibit you to go 'lower' if you want.

lugg 8y ago
Why is it one vs other? It's not, you can run them both. The question is why you would. What does an ORM solve for you? This is the only mapping I need from an ORM: result = (object) row row = (array) result SQL does everything an ORM does better. I'm not sure how you can call moving from a low level programming language to a domain specific language like SQL "going lower". It's not and ORMs are usually so bad they're a regression in what you can do. Also, when was the last time a change to the SQL spec broke your app?what about the last times you had to migrate ORM code?
- ken 8y ago
  > Also, when was the last time a change to the SQL spec broke your app?what about the last times you had to migrate ORM code? That's kind of an arbitrary categorization. An ORM is just a library. If you asked me when the last time I had to update my code because a library changed interfaces, I'd have an answer for you (less than a month ago, I'm sure). I'm not going to stop using libraries, though. I've had to update my programs several times because my compiler was updated, too. That may make me choosier in the future about which compiler I pick, but I'm not going to stop using compilers altogether.
cmiles74 8y ago
I think things have very much moved away from the "one vs. the other" stance. Back in the late 90's, I think people really did think that they could spend time on an ORM tool and never have to think about the database. In my own work, the ORM is there for lots of boring stuff, like fetch the data and push it into an edit form and then push the contents of the edit form back into the database. When it comes to q query of any consequence I end up writing SQL and then presenting a data set to the ORM, something that it can understand. Maybe it's my age, but I'm still not a big stored procedure guy. But you look at some of the interesting things you can do with SQL, it seems silly to try and interact with it only through someone else's ORM tool.

wswope 8y ago

Could you share some good practices for separating db code and business logic? I feel like when I don't use an ORM, there winds up being inline SQL all over the codebase.

_bohm 8y ago
I'm a big fan of the pattern introduced by Yesql for Clojure [0], also ported to Python as anosql [1] [0] https://github.com/krisajenkins/yesql https://github.com/krisajenkins/yesql [1] https://github.com/honza/anosql https://github.com/honza/anosql
- wswope 8y ago
  Wow, I wish I'd learned about anosql a long time ago! I love the elegance of it - thank you for the rec!
- WhatIsDukkha 8y ago
  I call this pattern "dotsql". (I've made private golang and rust crates for it). You avoid inline sql like the plague and your editor can have a nice dedicated sql buffer with all the bells and whistles. I wish it was a more common pattern, it just makes soooo much more sense.
btschaegg 8y ago
I don't think there's a one-size-fits-all solution to this, since different domains require different guarantees from your storage medium. For example, if you need to combine multiple actions with a certain level of transaction safety, defining an interface for that can get tricky. Overall, I tend to agree with Uncle Bob's "Clean Architecture" [1][2] approach when it comes to DBs, though: The DB usually (rembember that there is an exception to every rule!) has no business of being the "center" of an application. It should provide you with functionality, not dictate how your business logic works. If you can pull out your DB and give it a sensible interface, you still might end up having a lot of inline SQL queries, but they are not littered all over your codebase; they live inside a narrow scope (a plugin, say). Also, the SQL schema and queries in this case often are quite boring -- the tricky bit is coming up with a good interface that provides all the guarantees you need to uphold (complicated transactions maybe?). And remember that you need these guarantees because of how your business domain works, not because of your DB. Perhaps you can get rid of some of them if you organize your storage (i.e. DB) in a specific way? [1]: http://blog.cleancoder.com/uncle-bob/2012/08/13/the-clean-architecture.html http://blog.cleancoder.com/uncle-bob/2012/08/13/the-clean-ar... [2]: https://youtu.be/o_TH-Y78tt4?t=2565 https://youtu.be/o_TH-Y78tt4?t=2565 Edit: Also note that there's no reason not to use a ORM in this way, too (if it suits you). The important part is the same: The ORM has no business dictating how your business logic should be implemented, and if you try to remove it from your core application, you will end up doing the same things. Now, if your ORM makes the implementation easier for you, go ahead and use it. But don't expect things to change if you put ORM-generated code instead of inline SQL at the center of your application.

ken 8y ago

> the problem they are actually employeed to "solve": developers afraid of SQL. Whether they can't write it, can't learn it, or just straight hate the string representation in their code, it's the main reason people sign on for ORM. This sounds awfully like a straw man. I've worked on several projects that used an ORM, and it was never because developers couldn't write SQL. ORMs solve a real problem: SQL isn't composable. That's it. Sooner or later, every non-trivial program needs to compose arbitrary pieces of one query with arbitrary pieces of another, so you'll either use an ORM (i.e., native data structures instead of raw strings), or essentially write your own ad-hoc one. It has nothing to do with lack of ability. The staunchest ORM advocate I've worked with was also the first to pull out the debugger and single-step the MySQL driver to track down a bug (that one was nasty -- it was indeed in the driver). I use an HTML generator, and a compiler, too, and it's not because I'm "afraid" of assembly language or that I "can't learn" HTML. These abstractions have real value.

karmakaze 8y ago
> ORMs solve a real problem: SQL isn't composable. That's it. How so? I've always found the opposite. I can build up a compilex query referencing many tables in SQL without impediment but am unable to do the same with an ORM.
- platz 8y ago
  OP means combining two different queries or clauses into a bigger query/clause You cannot easily "modify" a sql statement. There are type-safe DSLs in certain languages (if they have an expressive enough type system) that let you do this, but they are uncommon
  - 1stranger 8y ago
    They're not that uncommon. You can do this with SQLAlchemy in Python or jOOQ in Java.
  - vp8989 8y ago
    You could use views. Although personally I dislike adding this layer of obfuscation. Generally devs are very underskilled in SQL (relative to it's importance) and ORMs hiding the SQL IMO is one of the reasons why. There is great value in having the entire SQL easily visible in your source code. If there is a performance issue with a specific query my app is running, I can very quickly pull it out and into a database IDE and see what's going on using the more detailed tools available (REPL, execution plan/statistics usage analysis etc). If the query is composed from a bunch of ORM function calls, I have to step through the program to first generate it. Reducing friction around debugging allows your devs to become more capable in fixing things or making them work better, and SQL is no exception to that.

rdiddly 8y ago

I laughed at this because it's so true... yet I still use ORMs gleefully so I can remain in my pathetic little comfort zone when the queries get especially complex. (Although ironically, my particular ORM of choice tends to fall apart when queries get complex, and you have to do clumsy iterations etc. Also after viewing this presentation I start to wonder how many of the built-in optimizations are being countermanded/ruined by the ORM.)