8 ms·

Do you always run into problems when you change your stack and the ORM of choice then isn't absolutely stable? I never find a room to use ORM as it's just an u

by h1d 8y ago

Do you always run into problems when you change your stack and the ORM of choice then isn't absolutely stable? I never find a room to use ORM as it's just an unnecessary and ineffective layer.

sanderjd 8y ago
I'm always very skeptical of this sort of claim. How are you constructing queries? Bespoke string concatenation? That's a recipe for disaster. How do you compose queries? That is, given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? Is that a completely separate question? If so, that quickly has unmanageable multiplicative effects. Do you write a layer that knows how to take the two original queries in some abstract form and build the correct WHERE ... AND ... clause for the composed query? Well, now you have a bespoke and immature version of an ORM. I can understand peoples' disdain for ORMs (which tend to be large dependencies that are more frameworks than libraries), but I don't understand not having a query construction and composition layer, and usually the popular ORMs for a language have the most mature implementations of that. In Ruby, there is actually both ActiveRecord (ORM) and Arel (query composition). Nobody really uses Arel directly, though, but I think that's a shame.
- michaelt 8y ago
  How are you constructing queries? Entirely with bind variables. given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? The API our service presents isn't an advanced search API, so it doesn't need to support searching on arbitrary combinations of database columns. Obviously, if your software needs to provide an arbitrary search capability, your needs will be different to mine and an ORM might make sense :)
  - sanderjd 8y ago
    What are "bind variables"? I'm not talking about a search API, I'm talking about code units that ask myriad questions of the database in order to implement myriad business logic. If you don't have the problem of asking myriad questions of the database, fair enough, but that is a simpler database-connected-software use case than any I've come across (including the ones I thought were pretty simple). I'm very curious now what your service does :)
    - michaelt 8y ago
      Bind variables are the ? in "SELECT fname, lname, pcode FROM cust WHERE id = ?" and they're a better choice than concatenating the ID you want into the SQL string because (a) it prevents SQL injection and (b) it lets the query optimiser cache plans. We have plenty of complicated queries - when we've got complex SQL queries, we find it valuable to have pure SQL as you can paste it into a tool that lets you execute it on the database and view the results, the explain plans, and suchlike.
      - ntqz 8y ago
        Another bonus - depending on your DB/driver, it may set this up as a prepared statement, so executions after the first one happen much quicker.
    - gaius 8y ago
      What are "bind variables"? I’m sorry, but bind variables are Database 101. I have a hard time believing your use cases are particularly “myriad” given lack of the very basics.
      - dahauns 8y ago
        No. "bind variables" are Oracle 101. Everyone else calls them "query parameters".
- h1d 8y ago
  What is wrong with writing a raw SQL for such a simple thing? I never want to fight with a tool for achieving such a simple query. Tools are supposed to help and when queries tend to get complicated, I think that's when ORM starts to fail with performance. So, I don't find a room for it. I'd rather learn how SQL works instead of how to get random ORM of the time to work like it should.
  - nulagrithom 8y ago
    > What is wrong with writing a raw SQL for such a simple thing? Mostly query composition. I run in to this a lot. Say I need to write a less-than-simple query and reuse it with several different WHERE clauses. Sometimes I think I just want migrations, not so much an ORM. I want to define views and stored procs in my repo and have them move to prod automatically, but I still want to write in straight SQL.
    - sanderjd 8y ago
      This resonates with me. But the other super critical thing (which was the first part of my original comment, and which nobody has really highlighted here) is a mature query construction library that knows how to properly protect against injection.
      - ris 8y ago
        In all fairness a properly used sql connector library will hopefully do this too.
    - ntqz 8y ago
      Although the majority of what I do is in Python, I've taken to using Flyway for DB migrations. A lot of what I do is using some of the more esoteric features of PG (think domains, custom aggregate functions, etc), so the ability to run migrations as direct SQL is great.
  - msbarnett 8y ago
    If you're not doing anything to manage and autogen your queries, does that imply that any change to the model's table entails manually updating all N different queries that touch the table in your application? I'd be really concerned about the fragility of having that many disparate sources of truth about the structure of your data. Particularly when errors are only going to be discovered when the query actually runs. You don't need to have an ORM, but at the same time, having no abstraction above "hardcode every query" is its own enormous set of problems.

by h1d 8y ago

Do you always run into problems when you change your stack and the ORM of choice then isn't absolutely stable? I never find a room to use ORM as it's just an unnecessary and ineffective layer.

sanderjd 8y ago

I'm always very skeptical of this sort of claim. How are you constructing queries? Bespoke string concatenation? That's a recipe for disaster. How do you compose queries? That is, given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? Is that a completely separate question? If so, that quickly has unmanageable multiplicative effects. Do you write a layer that knows how to take the two original queries in some abstract form and build the correct WHERE ... AND ... clause for the composed query? Well, now you have a bespoke and immature version of an ORM. I can understand peoples' disdain for ORMs (which tend to be large dependencies that are more frameworks than libraries), but I don't understand not having a query construction and composition layer, and usually the popular ORMs for a language have the most mature implementations of that. In Ruby, there is actually both ActiveRecord (ORM) and Arel (query composition). Nobody really uses Arel directly, though, but I think that's a shame.

michaelt 8y ago
How are you constructing queries? Entirely with bind variables. given the questions "give me all posts owned by user X" and "give me all posts newer than timestamp Y", how do you ask for all posts owned by user X that are newer than timestamp Y? The API our service presents isn't an advanced search API, so it doesn't need to support searching on arbitrary combinations of database columns. Obviously, if your software needs to provide an arbitrary search capability, your needs will be different to mine and an ORM might make sense :)
- sanderjd 8y ago
  What are "bind variables"? I'm not talking about a search API, I'm talking about code units that ask myriad questions of the database in order to implement myriad business logic. If you don't have the problem of asking myriad questions of the database, fair enough, but that is a simpler database-connected-software use case than any I've come across (including the ones I thought were pretty simple). I'm very curious now what your service does :)
  - michaelt 8y ago
    Bind variables are the ? in "SELECT fname, lname, pcode FROM cust WHERE id = ?" and they're a better choice than concatenating the ID you want into the SQL string because (a) it prevents SQL injection and (b) it lets the query optimiser cache plans. We have plenty of complicated queries - when we've got complex SQL queries, we find it valuable to have pure SQL as you can paste it into a tool that lets you execute it on the database and view the results, the explain plans, and suchlike.
    - ntqz 8y ago
      Another bonus - depending on your DB/driver, it may set this up as a prepared statement, so executions after the first one happen much quicker.
  - gaius 8y ago
    What are "bind variables"? I’m sorry, but bind variables are Database 101. I have a hard time believing your use cases are particularly “myriad” given lack of the very basics.
    - dahauns 8y ago
      No. "bind variables" are Oracle 101. Everyone else calls them "query parameters".
h1d 8y ago
What is wrong with writing a raw SQL for such a simple thing? I never want to fight with a tool for achieving such a simple query. Tools are supposed to help and when queries tend to get complicated, I think that's when ORM starts to fail with performance. So, I don't find a room for it. I'd rather learn how SQL works instead of how to get random ORM of the time to work like it should.
- nulagrithom 8y ago
  > What is wrong with writing a raw SQL for such a simple thing? Mostly query composition. I run in to this a lot. Say I need to write a less-than-simple query and reuse it with several different WHERE clauses. Sometimes I think I just want migrations, not so much an ORM. I want to define views and stored procs in my repo and have them move to prod automatically, but I still want to write in straight SQL.
  - sanderjd 8y ago
    This resonates with me. But the other super critical thing (which was the first part of my original comment, and which nobody has really highlighted here) is a mature query construction library that knows how to properly protect against injection.
    - ris 8y ago
      In all fairness a properly used sql connector library will hopefully do this too.
  - ntqz 8y ago
    Although the majority of what I do is in Python, I've taken to using Flyway for DB migrations. A lot of what I do is using some of the more esoteric features of PG (think domains, custom aggregate functions, etc), so the ability to run migrations as direct SQL is great.
- msbarnett 8y ago
  If you're not doing anything to manage and autogen your queries, does that imply that any change to the model's table entails manually updating all N different queries that touch the table in your application? I'd be really concerned about the fragility of having that many disparate sources of truth about the structure of your data. Particularly when errors are only going to be discovered when the query actually runs. You don't need to have an ORM, but at the same time, having no abstraction above "hardcode every query" is its own enormous set of problems.