6 ms·

My dislike of ORMs mainly stems from the tendency to treat modern SQL engines as glorified dumb bit buckets. Where a CTE or LATERAL join or RETURNING clause wo

by ttfkam 3y ago

My dislike of ORMs mainly stems from the tendency to treat modern SQL engines as glorified dumb bit buckets. Where a CTE or LATERAL join or RETURNING clause would simplify processing immensely or (better yet) remove the possibility of inconsistent data making its way into the data set, ORMs are largely limited to simplistic mappings between basic tables and views to predefined object definitions. Even worse when the ORM is creating the tables. SQL is at its heart a transformation language as well as a data extraction tool. ORMs largely ignore these facets to the point where most developers don't even realize anything exists in SQL beyond the basic INSERT/SELECT/UPDATE/DELETE. Pivot tables. Temporal queries. CUBE/ROLLUP. Window functions. Set-returning functions. Materialized views. Foreign tables. JSON processing. Date processing. Exclusion constraints. Types like ranges, intervals, domains. Row-level security. MERGE. It's like owning a full working tool shed but hiring someone to hand you just the one hammer, screwdriver, and hacksaw and convincing you it's enough. Folks go their whole careers without knowing they had a full size table saw, router, sander, and array of wedges just a few meters away.

marginalia_nu 3y ago
To be fair, a lot of those tools have that one weird caveat you need to know, and are also strongly dependent on your dialect. For example there's an aggregate function in DB/2, LISTAGG, that joins strings... the caveat being that if they get too long, the query blows up. It's in OracleSQL too, which has a syntax where you can tell it to truncate the string. SELECT, INSERT, UPDATE, DELETE work more or less the same whether you're on MariaDB, Postgres, DB/2, OracleSQL, etc.
- ttfkam 3y ago
  As opposed to the dizzying array of ORMs that all have multiple caveats you need to know and utterly incompatible in fundamental ways between each other? Folks go on about how they're "stuck" with a single dialect of SQL but completely ignore how utterly impossible it is to switch ORMs without a full app rewrite. And ORMs are all language-specific, so if you have two different app clients in different languages, you can't always share even basic API access patterns. Got a Java Spring app alongside a Django app? Good luck!
  - marginalia_nu 3y ago
    Well yeah, I do think there is a fundamental difference given the DBMS is effectively hidden in this scenario, whereas the ORM is not.
  - quickthrower2 3y ago
    IoC (not just interfaces, not just DI) should avoid this. Inject me an object that gets people objects that meet an interface (ok might need to be async) and you can switch out how that is done later. But it is more boilerplate
freetime2 3y ago
Most ORM frameworks have the ability to execute raw SQL, so using an ORM does not preclude you from using these features. ORMs tend to put you in the mindset of using simple CRUD statements most of the time - but I think that’s probably for the best, and these more advanced features should be used sparingly.
- ttfkam 3y ago
  Not only does each ORM have a different API and access pattern to do this, once you hit this point, you're managing the object model ALONG WITH custom SQL for migrations. There is also non-trivial danger in letting your ORM decide what data types your database schema should use. Got UUIDs? The ORM will store them as strings. Need start and stop timestamps? A range type with an exclusion constraint may be the right tool. What you appear to consider "advanced features" are what some others of us consider "perfectly normal." Let's be honest: most devs consider knowing the difference between a LEFT JOIN and an INNER JOIN to be an advanced topic. Devs will wax poetic about the marginal benefits of monads in limited scenarios, but throw up their hands in defeat when you mention a window function for a dashboard. They'd rather calculate it in the app layer with all the added latency that implies.
  - freetime2 3y ago
    > Not only does each ORM have a different API and access pattern to do this I think it is to be expected that different ORM frameworks would have differing APIs and access patterns. Similar to how different RDBMS's often have differences in the syntaxes and features that they support. It's not a big deal to look up the correct syntax in my opinion. > you're managing the object model Which means in the worst case that for some portion of your app you're basically back to where you were if you weren't using an ORM. Although I've found that JPA, for example, plays pretty nicely with native SQL queries (caching gets more difficult, but I think that's to be expected when moving logic out of the app and into the database regardless of whether you are using an ORM or not). > ALONG WITH custom SQL for migrations Personally I've always used custom SQL for all migrations. While ORMs often come with a tool to automatically generate a schema, I've never worked on a project that actually used this tool in production. > There is also non-trivial danger in letting your ORM decide what data types your database schema should use. Got UUIDs? The ORM will store them as strings. Some data types can be a bit tricky, but any competent ORM should at the very least have hooks to override the type on a field or implement your own custom handlers. [1] > What you appear to consider "advanced features" are what some others of us consider "perfectly normal." Let's be honest: most devs consider knowing the difference between a LEFT JOIN and an INNER JOIN to be an advanced topic. I said "more advanced features" - as in more advanced than basic SELECT/INSERT/UPDATE statements. I don't think any feature that you mentioned is particularly challenging to understand. And literally every entry-level developer I've ever hired has easily been able to explain the difference between LEFT and INNER joins in an interview. [1] https://stackoverflow.com/a/12824827 https://stackoverflow.com/a/12824827
lmm 3y ago
SQL may be a language for all those things, but unfortunately it's horribly bad at it. Wilfully obtuse syntax. Incredibly poor compositionality. No testability worth the name. The deployment model is a half-baked pile of perl scripts, and that's if you're lucky. So yeah, I use SQL like https://xkcd.com/783/ https://xkcd.com/783/ . I'm sure you have a bunch of cool data analysis tools in there, but please just shut up, give me the contents of my table, and let me process it in a real programming language where I have map/reduce/filter as regular composable functions that I can test and reuse, with a syntax that doesn't make my eyes bleed.
- ttfkam 3y ago
  map/filter/reduce? Give you the contents of your table, so you can process it in the app tier with map/filter/reduce?! Ah, the hubris of devs who honestly believe they can whip out a solution in a day that beats a dedicated army of developers singularly focused on the task of large data management, storage, and serialization. And almost always forgetting that serialization off of disk and over a network isn't free. There's a reason why SQL is going on 50 years when most technologies are lucky to remain dominant past 10 in this industry. And if you think it's just because of inertia or lack of imagination, you're deluded. SQL isn't perfect (nothing is), but as a DSL for set theory, it does a damn good job, even 50 years later. Far better than any map/filter/reduce whipped up yet again by someone who doesn't fully understand the scope of the problems being solved. It's doubly troubling when you can't grok that SELECT = map, WHERE = filter, and GROUP BY + aggregator = reduce. I sincerely hope you aren't avoiding JOIN by loading both tables ahead of time.
  - roflyear 3y ago
    Yeah, if your devs are loading entire tables into code and using filter on them ... I haven't personally seen that, but oh god. Not that it isn't valid in some cases. It is. If for example, you're using all of the table in your logic and need to filter on to do something like pull out certain items for a certain need. Sure. Then it is good, absolutely!
  - lmm 3y ago
    > Ah, the hubris of devs who honestly believe they can whip out a solution in a day that beats a dedicated army of developers singularly focused on the task of large data management, storage, and serialization. A "dedicated army of developers" who've been "going on 50 years" but whose flagship solutions are still single-point-of-failure, still noncompositional, still untestable, still have bizarre and incomprehensible performance characteristics. Yeah, no, I'm going to do stuff in regular application code, thanks.
throw_m239339 3y ago
> My dislike of ORMs mainly stems from the tendency to treat modern SQL engines as glorified dumb bit buckets. At scale, databases are OFTEN used as glorified dumb bit buckets for performance purposes with heavy denormalization techniques.
- ttfkam 3y ago
  If you have fewer than ten thousand simultaneous users, this is not you. After ten million, you're not using an ORM in front of a bare relational database either. Scale at high numbers adheres to no rules or off-the-shelf tools.
  - Pxtl 3y ago
    I wish. I'm constantly having to reinvent materialized views on engines that don't have good support for the concept (ms SQL, sqlite) because my users want to sort/filter on a calculated column, meaning that I have to revert to "dumb bucket of bits" for records in seven figures and user-counts in the dozens. Relational algebra is good. I like the idea of nornalized data. I hope to be able to use it one day instead of SQL.
    - ttfkam 3y ago
      So… use one of the engines with better support for materialized views? This sentiment is like saying, "Java doesn't support traits, so I hate all programming languages." If an engine doesn't support a feature you need, use a different engine. If your workplace does not allow this, that's an issue you have with your workplace, not the tools.
mirekrusin 3y ago
Exactly, no wonder many devs don't see difference between sql and nosql.
alkonaut 3y ago
I'm lucky enough to work on projects usually lacking any kind of database anywhere. But when I do, I also tend to make sure to use a tiny subset of DB features. Simply because I consider "code" to be the part of the world I have any control over and "database" to be the dangerous and out-of-reach part where errors show up later (Such as in slower/larger integration tests). Any logic that is happening in a DB happens outside of low level testing reach too and that scares me. I'll probably never even use a select-from-select query... I'll happily leave most of the tools in the shed.
- ttfkam 3y ago
  > Simply because I consider "code" to be the part of the world I have any control over and "database" to be the dangerous and out-of-reach part where errors show up later Sounds more like a personal phobia than a substantive critique of databases, especially ACID databases. You can test any query outside of a huge integration test by… running the query in a smaller, more targeted test suite. This can be done for performance checks, conformance, sanity, etc. The thing is, compared to most other programming languages, SQL is largely side effect free. As long as no one is dropping tables or indexes, the query you ran today will return the same result structure you run tomorrow. Obviously changes in the volume of data will affect performance, but that's true no matter what data store you use. "But what about testing writes," you ask? Same deal. Start a transaction, run the INSERT/UPDATE/DELETE and then ROLLBACK. You get your performance timing, your ability to detect errors, and no permanent changes to your dataset. Data storage and persistence is hard. It doesn't become easier using NoSQL or avoiding data altogether. But if data makes you personally uncomfortable, perhaps it's best you keep your distance. It greatly limits your career choices, but that's ultimately your choice.
  - alkonaut 3y ago
    There are tons of antipatterns out there. Often the production database is completely different from anything I can or want to set up on my local machine (E.g. it's a cluster of database type A, while my local dev env is a single node of type B. > But if data makes you personally uncomfortable I wouldn't say uncomfortable, simply bored. Not because I think database are bad tech, they are awesome. But we still haven't solved the impedance mismatch between programs and data, so any time you need to work with them, you spend 10% of your time on business logic and 90% on deployments, migrations, ORM and CRUD. Which is just something I'm lucky enough to be able to avoid. I don't like NoSQL either (just like I don't like dynamic typing). I don't think anyone can "avoid data" in programming, but my way of staying sane has been to avoid the web.
perrygeo 3y ago
I've worked on a few projects in the past that "didn't need all that fancy database stuff". It's frustrating to deal with the consequences. You spend most of your time building elaborate workarounds to re-invent basic data integrity safeguards. Roll your own database might be a fun learning experience, but not for production apps.

by ttfkam 3y ago

marginalia_nu 3y ago

To be fair, a lot of those tools have that one weird caveat you need to know, and are also strongly dependent on your dialect. For example there's an aggregate function in DB/2, LISTAGG, that joins strings... the caveat being that if they get too long, the query blows up. It's in OracleSQL too, which has a syntax where you can tell it to truncate the string. SELECT, INSERT, UPDATE, DELETE work more or less the same whether you're on MariaDB, Postgres, DB/2, OracleSQL, etc.

ttfkam 3y ago
As opposed to the dizzying array of ORMs that all have multiple caveats you need to know and utterly incompatible in fundamental ways between each other? Folks go on about how they're "stuck" with a single dialect of SQL but completely ignore how utterly impossible it is to switch ORMs without a full app rewrite. And ORMs are all language-specific, so if you have two different app clients in different languages, you can't always share even basic API access patterns. Got a Java Spring app alongside a Django app? Good luck!
- marginalia_nu 3y ago
  Well yeah, I do think there is a fundamental difference given the DBMS is effectively hidden in this scenario, whereas the ORM is not.
- quickthrower2 3y ago
  IoC (not just interfaces, not just DI) should avoid this. Inject me an object that gets people objects that meet an interface (ok might need to be async) and you can switch out how that is done later. But it is more boilerplate

freetime2 3y ago

Most ORM frameworks have the ability to execute raw SQL, so using an ORM does not preclude you from using these features. ORMs tend to put you in the mindset of using simple CRUD statements most of the time - but I think that’s probably for the best, and these more advanced features should be used sparingly.

ttfkam 3y ago
Not only does each ORM have a different API and access pattern to do this, once you hit this point, you're managing the object model ALONG WITH custom SQL for migrations. There is also non-trivial danger in letting your ORM decide what data types your database schema should use. Got UUIDs? The ORM will store them as strings. Need start and stop timestamps? A range type with an exclusion constraint may be the right tool. What you appear to consider "advanced features" are what some others of us consider "perfectly normal." Let's be honest: most devs consider knowing the difference between a LEFT JOIN and an INNER JOIN to be an advanced topic. Devs will wax poetic about the marginal benefits of monads in limited scenarios, but throw up their hands in defeat when you mention a window function for a dashboard. They'd rather calculate it in the app layer with all the added latency that implies.
- freetime2 3y ago
  > Not only does each ORM have a different API and access pattern to do this I think it is to be expected that different ORM frameworks would have differing APIs and access patterns. Similar to how different RDBMS's often have differences in the syntaxes and features that they support. It's not a big deal to look up the correct syntax in my opinion. > you're managing the object model Which means in the worst case that for some portion of your app you're basically back to where you were if you weren't using an ORM. Although I've found that JPA, for example, plays pretty nicely with native SQL queries (caching gets more difficult, but I think that's to be expected when moving logic out of the app and into the database regardless of whether you are using an ORM or not). > ALONG WITH custom SQL for migrations Personally I've always used custom SQL for all migrations. While ORMs often come with a tool to automatically generate a schema, I've never worked on a project that actually used this tool in production. > There is also non-trivial danger in letting your ORM decide what data types your database schema should use. Got UUIDs? The ORM will store them as strings. Some data types can be a bit tricky, but any competent ORM should at the very least have hooks to override the type on a field or implement your own custom handlers. [1] > What you appear to consider "advanced features" are what some others of us consider "perfectly normal." Let's be honest: most devs consider knowing the difference between a LEFT JOIN and an INNER JOIN to be an advanced topic. I said "more advanced features" - as in more advanced than basic SELECT/INSERT/UPDATE statements. I don't think any feature that you mentioned is particularly challenging to understand. And literally every entry-level developer I've ever hired has easily been able to explain the difference between LEFT and INNER joins in an interview. [1] https://stackoverflow.com/a/12824827 https://stackoverflow.com/a/12824827

lmm 3y ago

SQL may be a language for all those things, but unfortunately it's horribly bad at it. Wilfully obtuse syntax. Incredibly poor compositionality. No testability worth the name. The deployment model is a half-baked pile of perl scripts, and that's if you're lucky. So yeah, I use SQL like https://xkcd.com/783/ https://xkcd.com/783/ . I'm sure you have a bunch of cool data analysis tools in there, but please just shut up, give me the contents of my table, and let me process it in a real programming language where I have map/reduce/filter as regular composable functions that I can test and reuse, with a syntax that doesn't make my eyes bleed.

ttfkam 3y ago
map/filter/reduce? Give you the contents of your table, so you can process it in the app tier with map/filter/reduce?! Ah, the hubris of devs who honestly believe they can whip out a solution in a day that beats a dedicated army of developers singularly focused on the task of large data management, storage, and serialization. And almost always forgetting that serialization off of disk and over a network isn't free. There's a reason why SQL is going on 50 years when most technologies are lucky to remain dominant past 10 in this industry. And if you think it's just because of inertia or lack of imagination, you're deluded. SQL isn't perfect (nothing is), but as a DSL for set theory, it does a damn good job, even 50 years later. Far better than any map/filter/reduce whipped up yet again by someone who doesn't fully understand the scope of the problems being solved. It's doubly troubling when you can't grok that SELECT = map, WHERE = filter, and GROUP BY + aggregator = reduce. I sincerely hope you aren't avoiding JOIN by loading both tables ahead of time.
- roflyear 3y ago
  Yeah, if your devs are loading entire tables into code and using filter on them ... I haven't personally seen that, but oh god. Not that it isn't valid in some cases. It is. If for example, you're using all of the table in your logic and need to filter on to do something like pull out certain items for a certain need. Sure. Then it is good, absolutely!
- lmm 3y ago
  > Ah, the hubris of devs who honestly believe they can whip out a solution in a day that beats a dedicated army of developers singularly focused on the task of large data management, storage, and serialization. A "dedicated army of developers" who've been "going on 50 years" but whose flagship solutions are still single-point-of-failure, still noncompositional, still untestable, still have bizarre and incomprehensible performance characteristics. Yeah, no, I'm going to do stuff in regular application code, thanks.

throw_m239339 3y ago

> My dislike of ORMs mainly stems from the tendency to treat modern SQL engines as glorified dumb bit buckets. At scale, databases are OFTEN used as glorified dumb bit buckets for performance purposes with heavy denormalization techniques.

ttfkam 3y ago
If you have fewer than ten thousand simultaneous users, this is not you. After ten million, you're not using an ORM in front of a bare relational database either. Scale at high numbers adheres to no rules or off-the-shelf tools.
- Pxtl 3y ago
  I wish. I'm constantly having to reinvent materialized views on engines that don't have good support for the concept (ms SQL, sqlite) because my users want to sort/filter on a calculated column, meaning that I have to revert to "dumb bucket of bits" for records in seven figures and user-counts in the dozens. Relational algebra is good. I like the idea of nornalized data. I hope to be able to use it one day instead of SQL.
  - ttfkam 3y ago
    So… use one of the engines with better support for materialized views? This sentiment is like saying, "Java doesn't support traits, so I hate all programming languages." If an engine doesn't support a feature you need, use a different engine. If your workplace does not allow this, that's an issue you have with your workplace, not the tools.

mirekrusin 3y ago

Exactly, no wonder many devs don't see difference between sql and nosql.

alkonaut 3y ago

I'm lucky enough to work on projects usually lacking any kind of database anywhere. But when I do, I also tend to make sure to use a tiny subset of DB features. Simply because I consider "code" to be the part of the world I have any control over and "database" to be the dangerous and out-of-reach part where errors show up later (Such as in slower/larger integration tests). Any logic that is happening in a DB happens outside of low level testing reach too and that scares me. I'll probably never even use a select-from-select query... I'll happily leave most of the tools in the shed.

ttfkam 3y ago
> Simply because I consider "code" to be the part of the world I have any control over and "database" to be the dangerous and out-of-reach part where errors show up later Sounds more like a personal phobia than a substantive critique of databases, especially ACID databases. You can test any query outside of a huge integration test by… running the query in a smaller, more targeted test suite. This can be done for performance checks, conformance, sanity, etc. The thing is, compared to most other programming languages, SQL is largely side effect free. As long as no one is dropping tables or indexes, the query you ran today will return the same result structure you run tomorrow. Obviously changes in the volume of data will affect performance, but that's true no matter what data store you use. "But what about testing writes," you ask? Same deal. Start a transaction, run the INSERT/UPDATE/DELETE and then ROLLBACK. You get your performance timing, your ability to detect errors, and no permanent changes to your dataset. Data storage and persistence is hard. It doesn't become easier using NoSQL or avoiding data altogether. But if data makes you personally uncomfortable, perhaps it's best you keep your distance. It greatly limits your career choices, but that's ultimately your choice.
- alkonaut 3y ago
  There are tons of antipatterns out there. Often the production database is completely different from anything I can or want to set up on my local machine (E.g. it's a cluster of database type A, while my local dev env is a single node of type B. > But if data makes you personally uncomfortable I wouldn't say uncomfortable, simply bored. Not because I think database are bad tech, they are awesome. But we still haven't solved the impedance mismatch between programs and data, so any time you need to work with them, you spend 10% of your time on business logic and 90% on deployments, migrations, ORM and CRUD. Which is just something I'm lucky enough to be able to avoid. I don't like NoSQL either (just like I don't like dynamic typing). I don't think anyone can "avoid data" in programming, but my way of staying sane has been to avoid the web.

perrygeo 3y ago

I've worked on a few projects in the past that "didn't need all that fancy database stuff". It's frustrating to deal with the consequences. You spend most of your time building elaborate workarounds to re-invent basic data integrity safeguards. Roll your own database might be a fun learning experience, but not for production apps.