4 ms·

I do not really understand the desire for ORMs to try and recreate the experience of writing SQL. Why not just use SQL, then? I understand that the ORM is tryi

I do not really understand the desire for ORMs to try and recreate the experience of writing SQL. Why not just use SQL, then? I understand that the ORM is trying to smooth over differences in implementation, providing the possibility of change from one DB access layer to another. I have yet to see anyone do that on a real project, which makes me really wonder at the point of using an ORM at all. I cannot quantify the amount of time I've lost figuring what the hell I must do to generate a relatively simple SQL statement.

benselme 12y ago
Well, ORMs are useless if you're of the opinion that string interpolation and concatenation are a good way of composing logical units. Many of us prefer a proper API for doing that, and good ORMs like SQLAlchemy provide us with one.
- mantrax5 12y ago
  "foo > 123 or foo IS NULL and bar < 12" Now write this in SQLAlchemy without strings, without concatenation, and let's see which provides you with more clarity.
  - anton_gogolev 12y ago
    LINQ. Composable queries rock.
    - personZ 12y ago
      LINQ is a black-box that is often disastrous for application performance. The more LINQ in a project, the more likely the project is headed to failure.
      - anton_gogolev 12y ago
        First of all, are you aware that LINQ is not only about database access? Second, how is it a black box? It is the particular query provider implementation that is a black box. Third, there's absolutely no correlation between the amount of LINQ and success of a project.
        personZ 12y ago
        are you aware that LINQ is not only about database access Yes, I am aware. How is this relevant at all to the context of this thread? The rest of your comment: You are simply wrong. A heavy use of LINQ is one of the surest sign that one needs to run from a project. It is almost always used and embraced by people who have no concept of the consequences, imagining that the conciseness of LINQ = programming goodness, when the opposite is generally true.
  - benselme 12y ago
    (FooBar.foo > 123) | (FooBar.foo == None) & (FooBar.bar < 12) No strings, and I get an actual expression object instance that I can, for example, store in a variable and reuse in any number of queries.
    - coleifer 12y ago
      Yes! Plus if you have a syntax error Python will catch it for you. Peewee works the same way.
- datashaman 12y ago
  PDO in PHP has parameter binding and a way of loading a row from a resultset into a class. Safe, simple, and flexible. That's good enough for me. YMMV.
watwut 12y ago
ORM is most supposed to save you from repeating the same load-db-put-data-into-objects over and over. It is also supposed to do caching and other similar optimizations for you. If we insist on it, SQL itself was supposed to smooth over differences in db implementation. Ehm. If all you need in one query, then figuring out how to make ORM running may be waste of time. If you need gazimilion of them and you need results cached reasonably and so on and so forth, ORM starts being very useful.
- datashaman 12y ago
  A SQL statement will return the results as plain data. Data never goes out of fashion, it's easy to use, it's usually text, and it's usually returned in convenient lists. If you want to select a gazmillion rows, you're probably doing something wrong if you're doing it with an ORM. The overhead of storing all that metadata and the object structure overhead for each row would make it a poor choice for massive volumes. Caching results from a database seems a bit pointless to me, since databases do that already anyway. Rather figure out how to optimize your SQL so that you don't have to build a cache. The database is almost never the bottleneck with well-written SQL.
  - vertex-four 12y ago
    Of course, there's things like SQLAlchemy Core which give you a lot of the syntax of an ORM for building dynamic, complex queries (no more gluing strings together when the shape of your query is dynamic), while returning proper cursors which return arrays and dictionaries.
  - wlievens 12y ago
    He is not talking about fetching a gazillion rows but about writing a gazilkion similar-but-not-the-same queries!
  - marcosdumay 12y ago
    If texts and lists were easy to use, nobody would have invented structures, objects and the other thousands of abstractions available on modern languages. An ORM will give you a description of the database you can use by reflection, and an abstraction layer that avoids using hard to use lists and tables. The first one is way more usefull than the later, but both are good things. Also, application servers are normaly much more numerous than database servers, and much easier to scale up. Thus, anything that offloads work from the database to the application will simplify your environment when you grow.
cwp 12y ago
Absolutely. In my latest project, I just have a very thin facade between the application and the DB client library, just enough to simplify the process of getting a connection from the pool, fishing the required data out of a response, handling errors etc. The application doesn't build any queries dynamically; they're all string literals with parameters that get filled in by the DB client library. I also wrote a little utility to handle migration. Each migration is a file with SQL in it; forward migrations only, no going back. The tool makes it easy to apply a given migration to a given database (dev, staging, production or whatever) and does the book-keeping to ensure they're only applied once. Couldn't be simpler. I'm really happy with this set-up. It gives access to all the features of the database instead of just the lowest common denominator, and makes it much easier to read the code and know exactly what's going on. The only times I've seen an ORM work well in a non-CRUD application is when it was designed and written from scratch to work with one database to supply exactly the operations and semantics that the application needs.

benselme 12y ago

Well, ORMs are useless if you're of the opinion that string interpolation and concatenation are a good way of composing logical units. Many of us prefer a proper API for doing that, and good ORMs like SQLAlchemy provide us with one.

mantrax5 12y ago
"foo > 123 or foo IS NULL and bar < 12" Now write this in SQLAlchemy without strings, without concatenation, and let's see which provides you with more clarity.
- anton_gogolev 12y ago
  LINQ. Composable queries rock.
  - personZ 12y ago
    LINQ is a black-box that is often disastrous for application performance. The more LINQ in a project, the more likely the project is headed to failure.
    - anton_gogolev 12y ago
      First of all, are you aware that LINQ is not only about database access? Second, how is it a black box? It is the particular query provider implementation that is a black box. Third, there's absolutely no correlation between the amount of LINQ and success of a project.
      - personZ 12y ago
        are you aware that LINQ is not only about database access Yes, I am aware. How is this relevant at all to the context of this thread? The rest of your comment: You are simply wrong. A heavy use of LINQ is one of the surest sign that one needs to run from a project. It is almost always used and embraced by people who have no concept of the consequences, imagining that the conciseness of LINQ = programming goodness, when the opposite is generally true.
- benselme 12y ago
  (FooBar.foo > 123) | (FooBar.foo == None) & (FooBar.bar < 12) No strings, and I get an actual expression object instance that I can, for example, store in a variable and reuse in any number of queries.
  - coleifer 12y ago
    Yes! Plus if you have a syntax error Python will catch it for you. Peewee works the same way.
datashaman 12y ago
PDO in PHP has parameter binding and a way of loading a row from a resultset into a class. Safe, simple, and flexible. That's good enough for me. YMMV.

watwut 12y ago

ORM is most supposed to save you from repeating the same load-db-put-data-into-objects over and over. It is also supposed to do caching and other similar optimizations for you. If we insist on it, SQL itself was supposed to smooth over differences in db implementation. Ehm. If all you need in one query, then figuring out how to make ORM running may be waste of time. If you need gazimilion of them and you need results cached reasonably and so on and so forth, ORM starts being very useful.

datashaman 12y ago
A SQL statement will return the results as plain data. Data never goes out of fashion, it's easy to use, it's usually text, and it's usually returned in convenient lists. If you want to select a gazmillion rows, you're probably doing something wrong if you're doing it with an ORM. The overhead of storing all that metadata and the object structure overhead for each row would make it a poor choice for massive volumes. Caching results from a database seems a bit pointless to me, since databases do that already anyway. Rather figure out how to optimize your SQL so that you don't have to build a cache. The database is almost never the bottleneck with well-written SQL.
- vertex-four 12y ago
  Of course, there's things like SQLAlchemy Core which give you a lot of the syntax of an ORM for building dynamic, complex queries (no more gluing strings together when the shape of your query is dynamic), while returning proper cursors which return arrays and dictionaries.
- wlievens 12y ago
  He is not talking about fetching a gazillion rows but about writing a gazilkion similar-but-not-the-same queries!
- marcosdumay 12y ago
  If texts and lists were easy to use, nobody would have invented structures, objects and the other thousands of abstractions available on modern languages. An ORM will give you a description of the database you can use by reflection, and an abstraction layer that avoids using hard to use lists and tables. The first one is way more usefull than the later, but both are good things. Also, application servers are normaly much more numerous than database servers, and much easier to scale up. Thus, anything that offloads work from the database to the application will simplify your environment when you grow.

cwp 12y ago

Absolutely. In my latest project, I just have a very thin facade between the application and the DB client library, just enough to simplify the process of getting a connection from the pool, fishing the required data out of a response, handling errors etc. The application doesn't build any queries dynamically; they're all string literals with parameters that get filled in by the DB client library. I also wrote a little utility to handle migration. Each migration is a file with SQL in it; forward migrations only, no going back. The tool makes it easy to apply a given migration to a given database (dev, staging, production or whatever) and does the book-keeping to ensure they're only applied once. Couldn't be simpler. I'm really happy with this set-up. It gives access to all the features of the database instead of just the lowest common denominator, and makes it much easier to read the code and know exactly what's going on. The only times I've seen an ORM work well in a non-CRUD application is when it was designed and written from scratch to work with one database to supply exactly the operations and semantics that the application needs.