5 ms·

Whenever I read these sort of anti-ORM posts I wonder exactly how simple the data model of these people's software is that they would rather write SQL for every

Whenever I read these sort of anti-ORM posts I wonder exactly how simple the data model of these people's software is that they would rather write SQL for every trivial operation in their data model by hand than use abstraction layers that are nowadays well-established, mature, and far better tested than whatever ad-hoc intermediate layer people make by hand for their data models. Between myself and another guy I maintain a system with hundreds of tables, and if I had to write basic fetch-by-id queries, trivial joins, index statements and a bunch of other things which should demand zero effort from myself, I wouldn't even have the time to deal with that boilerplate. SQL is a remarkably difficult language to compose, a problem that most ORM APIs just don't have. The code necessary to makes dumb joins is long and prone to typing errors. Hell even error messages are usually better at the ORM layer than whatever weird syntax error MySQL or Postgres throw at you. Every large system that does not use an ORM at the beginning eventually end up growing a half-assed pseudo-ORM library which has been objectively worse in every regard than using quality ORM libraries like SQLAlchemy, the Django ORM, Yii's, etc. The impedance mismatch problem is just not there because I do want a database to return objects. The only time that doesn't happen is when I want relational records for analytics and number crunching; even then most ORMs don't fail me. Having to write a couple SQL queries by hand because the ORM fails you for every thousand ORM queries sounds like throwing the baby out with the bath water.

coldtea 10y ago
>Whenever I read these sort of anti-ORM posts I wonder exactly how simple the data model of these people's software is that they would rather write SQL for every trivial operation It's actually the exact inverse: it's ORMs that are only good for fairly simple data models and become pain points for anything more advanced, after which level you end up with hand-rolled SQL through the ORM or complex reinvention of all SQL concepts at the ORM layer in an ad-hoc way. SQL is good for both simple and overly complex models. ORM are ok-ish with simple (but still redundant) and a crappy layer of faux-simplicity and pain on more advanced models. Not to mention that they promote not knowing the details of your data model and letting the ORM create it through another ad-hoc reinvention of the db's DDL.
- Daishiman 10y ago
  I have yet to see a data model so complicated that it cannot be modeled in the more frequently used ORMs. I have tables with two dozen FKs that participate in 7 or 8 joins and it's still not a problem. You mention all these things and yet in 10 years of experience they have not been a problem for anyone I know. Have you even used a good ORM?
  - coldtea 10y ago
    >Have you even used a good ORM? Well, I've professionally used Hibernate in Java, Django's ORM in Python, and Sequelize in Node. Also used Active Record for (more CRUD-ish) Rails stuff. I'm sure someone will point to some obscure "better" ones (like there's always the "use a different distro" answer to Linux issues), but those are the most commonly used ORMs in their respective environments as far as I know. >You mention all these things and yet in 10 years of experience they have not been a problem for anyone I know. I could anecdotally point to people I know, but let's have a measurable metric: the support forums for those ORMs are chock full of questions and reports for such problems. Besides, the description of what an ORM can handle as if it's impressive, namely several FKs and joins ("I have tables with two dozen FKs that participate in 7 or 8 joins and it's still not a problem")is hardly the pinnacle of data modeling that a modern DB is capable of. Simple multiple FKs and JOINs are nothing, or course an ORM should (and could) be able to handle them (although even there you can see users struggling with performance issues, over eager bring-the-world-in queries, etc).
- wvenable 10y ago
  It's not the complexity of the model, it's what you are doing with it. Almost all applications that use a database do some kind of CRUD. These operations, no matter how complex your model is, are easily handled by ORMs. But one of the big advantages of an RDBMS is being able to perform more complex data analysis and transformations. You can do that with an ORM but the result is usually harder to understand and less performant than doing it in raw SQL. Most advocates of ORM are thinking of the former case and most proponents of ORMs are thinking of the latter case. The trick is not to get boxed into one solution.
SatvikBeri 10y ago
I've found that sqlAlchemy core (the layer below the ORM) works exceedingly well for me–it's composable, exposes SQL easily as needed, makes abstraction easy etc. The major benefit is that the objects in the expression builder represent the parse tree, rather than representing SQL strings directly.
- weberc2 10y ago
  I've had the opposite experience. The concept of a query builder is great, but SQLAlchemy is a pain. Its documentation is particularly hard to traverse or search, and it's super unintuitive. Perhaps the nature of query building is deceptively complex, but it seems like the library just tries to be too magical and ends up making things worse instead of better.
  - SatvikBeri 10y ago
    Yeah, the documentation is really bad. I found that there were two types of objects returned by sqlAlchemy: "query objects" (which can then be executed or compiled into SQL) and "selectables" (which are tables, joins, etc. and would compile to queries that have select statement, just something you could select from.) Almost all operations consist of creating or composing these. But this wasn't anywhere in the documentation! Once you figure out these tools there's actually no magic–it's pretty easy to figure out exactly what the expression builder is doing–but finding these took a bunch of trial and error.
    - weberc2 10y ago
      > it's pretty easy to figure out exactly what the expression builder is doing I don't know about that. I touched SQLAlchemy again this very morning for the first time in several months. I was just trying to get the count of a table with several rows, so I ran `table.count()`, but it kept returning 0. The problem happened on MySQL and Postgres. It turns out that this doesn't compile to `select count()`, but instead it returns the count of the first column in the SQLAlchemy table object's column list. In my case, that column was all NULL, so the count was coming back as zero. I thought maybe this was an accident, but here's the body of the count() function from SQLAlchemy's source: ``` if self.primary_key: col = list(self.primary_key)[0] else: col = list(self.columns)[0] return Select( [functions.func.count(col).label('tbl_row_count')], whereclause, from_obj=[self], *params) ``` I run into this sort of unintuitive behavior every time I touch this library. :(
cturner 10y ago
"Whenever I read .. that they would rather write SQL" If they do write SQL. There are ways to structure complex domains that don't involve relational databases. Having said that, the alternatives presented in this article are weak, and suggest the author is not aware of a world beyond relational databases.
weberc2 10y ago
> Whenever I read these sort of anti-ORM posts I wonder exactly how simple the data model of these people's software is that they would rather write SQL for every trivial operation in their data model by hand than use abstraction layers that are nowadays well-established, mature, and far better tested than whatever ad-hoc intermediate layer people make by hand for their data models. I prefer SQL query builders--they remove the pain of writing raw SQL--without the complexity of an ORM. You simply compose your SQL query the way you would compose any other data structure, and for statically-typed languages, you get some compile-time validation via the type system.

coldtea 10y ago

>Whenever I read these sort of anti-ORM posts I wonder exactly how simple the data model of these people's software is that they would rather write SQL for every trivial operation It's actually the exact inverse: it's ORMs that are only good for fairly simple data models and become pain points for anything more advanced, after which level you end up with hand-rolled SQL through the ORM or complex reinvention of all SQL concepts at the ORM layer in an ad-hoc way. SQL is good for both simple and overly complex models. ORM are ok-ish with simple (but still redundant) and a crappy layer of faux-simplicity and pain on more advanced models. Not to mention that they promote not knowing the details of your data model and letting the ORM create it through another ad-hoc reinvention of the db's DDL.

Daishiman 10y ago
I have yet to see a data model so complicated that it cannot be modeled in the more frequently used ORMs. I have tables with two dozen FKs that participate in 7 or 8 joins and it's still not a problem. You mention all these things and yet in 10 years of experience they have not been a problem for anyone I know. Have you even used a good ORM?
- coldtea 10y ago
  >Have you even used a good ORM? Well, I've professionally used Hibernate in Java, Django's ORM in Python, and Sequelize in Node. Also used Active Record for (more CRUD-ish) Rails stuff. I'm sure someone will point to some obscure "better" ones (like there's always the "use a different distro" answer to Linux issues), but those are the most commonly used ORMs in their respective environments as far as I know. >You mention all these things and yet in 10 years of experience they have not been a problem for anyone I know. I could anecdotally point to people I know, but let's have a measurable metric: the support forums for those ORMs are chock full of questions and reports for such problems. Besides, the description of what an ORM can handle as if it's impressive, namely several FKs and joins ("I have tables with two dozen FKs that participate in 7 or 8 joins and it's still not a problem")is hardly the pinnacle of data modeling that a modern DB is capable of. Simple multiple FKs and JOINs are nothing, or course an ORM should (and could) be able to handle them (although even there you can see users struggling with performance issues, over eager bring-the-world-in queries, etc).
wvenable 10y ago
It's not the complexity of the model, it's what you are doing with it. Almost all applications that use a database do some kind of CRUD. These operations, no matter how complex your model is, are easily handled by ORMs. But one of the big advantages of an RDBMS is being able to perform more complex data analysis and transformations. You can do that with an ORM but the result is usually harder to understand and less performant than doing it in raw SQL. Most advocates of ORM are thinking of the former case and most proponents of ORMs are thinking of the latter case. The trick is not to get boxed into one solution.

SatvikBeri 10y ago

I've found that sqlAlchemy core (the layer below the ORM) works exceedingly well for me–it's composable, exposes SQL easily as needed, makes abstraction easy etc. The major benefit is that the objects in the expression builder represent the parse tree, rather than representing SQL strings directly.

weberc2 10y ago
I've had the opposite experience. The concept of a query builder is great, but SQLAlchemy is a pain. Its documentation is particularly hard to traverse or search, and it's super unintuitive. Perhaps the nature of query building is deceptively complex, but it seems like the library just tries to be too magical and ends up making things worse instead of better.
- SatvikBeri 10y ago
  Yeah, the documentation is really bad. I found that there were two types of objects returned by sqlAlchemy: "query objects" (which can then be executed or compiled into SQL) and "selectables" (which are tables, joins, etc. and would compile to queries that have select statement, just something you could select from.) Almost all operations consist of creating or composing these. But this wasn't anywhere in the documentation! Once you figure out these tools there's actually no magic–it's pretty easy to figure out exactly what the expression builder is doing–but finding these took a bunch of trial and error.
  - weberc2 10y ago
    > it's pretty easy to figure out exactly what the expression builder is doing I don't know about that. I touched SQLAlchemy again this very morning for the first time in several months. I was just trying to get the count of a table with several rows, so I ran `table.count()`, but it kept returning 0. The problem happened on MySQL and Postgres. It turns out that this doesn't compile to `select count()`, but instead it returns the count of the first column in the SQLAlchemy table object's column list. In my case, that column was all NULL, so the count was coming back as zero. I thought maybe this was an accident, but here's the body of the count() function from SQLAlchemy's source: ``` if self.primary_key: col = list(self.primary_key)[0] else: col = list(self.columns)[0] return Select( [functions.func.count(col).label('tbl_row_count')], whereclause, from_obj=[self], *params) ``` I run into this sort of unintuitive behavior every time I touch this library. :(

cturner 10y ago

"Whenever I read .. that they would rather write SQL" If they do write SQL. There are ways to structure complex domains that don't involve relational databases. Having said that, the alternatives presented in this article are weak, and suggest the author is not aware of a world beyond relational databases.

weberc2 10y ago

> Whenever I read these sort of anti-ORM posts I wonder exactly how simple the data model of these people's software is that they would rather write SQL for every trivial operation in their data model by hand than use abstraction layers that are nowadays well-established, mature, and far better tested than whatever ad-hoc intermediate layer people make by hand for their data models. I prefer SQL query builders--they remove the pain of writing raw SQL--without the complexity of an ORM. You simply compose your SQL query the way you would compose any other data structure, and for statically-typed languages, you get some compile-time validation via the type system.