6 ms·

SQLAlchemy author here. I would just note that these two statements are contradictory: > The name pureORM reflects both that it is pure ORM (there is no query

by zzzeek 5y ago

SQLAlchemy author here. I would just note that these two statements are contradictory: > The name pureORM reflects both that it is pure ORM (there is no query builder dimension) and then > Specifying all the columns is tedious; lets use BaseBo.getSQLSelectClause() to get them for free. the "getSQLSelectClause()" is absolutely a query builder function. Building out the columns to select from is in fact where things get very complicated if you are for example using SQL aliases, selecting the entity from subqueries, etc. I would predict this method would have to be very complicated to truly be useful in such real world scenarios, so you'd end up with a "pure" ORM that still has a significant query builder, just one that has its own particular brand of awkwardness in that the textual SQL you write has to match up with the assumptions of getSQLSelectClause().

BiteCode_dev 5y ago
It's always the same with the anti orm crowd, they either avoid any wrapper and prevent having a standard api to build uppon and inspect, or they create a light wrapper that ends up being a poor badly tested and documented implementation of 1% of SQLA.
- gabereiser 5y ago
  Took the words right out of my mouth. As someone who has been adamant about using orm’s for the last 15 years, it shocks me to no end that an engineer (or group of engineers) think they can roll a DAO better than a battle hardened orm like SQLAlchemy, Hibernate, Entity Framework. It’s hubris in the least and straight arrogance at best.
  - ckmar 5y ago
    YOLO
  - blacktriangle 5y ago
    It depends what you're doing with the data. If you're trying to map into an object based view of your data, then yes ORMs have at least thought of most of the issues there. However if you'd like to view your relational data gasp relationally, then the ORM is a giant anchor around your neck.
    - BiteCode_dev 5y ago
      Good orms like sqla comes in layers, and the object relationships mapping is the highest one. You can use lower level layers as you need, and retain the benefit of a strong battle tested abstraction that gives you introspection and a standard API. Sometimes I use sqla just for the connection pool and session handling and dump raw sql in it. It's not raw sql vs orm, as usual it's use a spectrum with good use cases for each part of it, and you usually benefit from all of it. But orm have pareto value in my xp.
      - blacktriangle 5y ago
        ORMs only have value IF there's an object model you're mapping to. When working in a language that has good support for relational data, ie support for sets of heterogeneus maps, you can skip the whole object model and avoid the whole need for mapping in the first place. Ruby and Python in particular really missed the boat on this one. I feel like they cargo culted practices from Java rather than embrace the implications of their own langauge's flexibility.
        ad404b8a372f2b9 5y ago
        Isn't that what pandas does kind of? You can execute sql queries and get the results in a dataframe which is a dictionary with some convenience functions on top. Do you feel python could have gone further to take advantage of specific language features to improve that workflow?
        blacktriangle 5y ago
        I was more taking shots at Django. pandas is a great example of doing things right imo and me ripping on python at large was more my web-dev myopia shining through.
        spfzero 5y ago
        Right, OOP is sometimes not the best paradigm for the given problem. If you're using C# or Java, sure, ORMs fit right in. Ruby and Python, not to mention Lisp etc., offer a multi-paradigm palette where forcing objects in, just to access relational data, just obscures things.
  - OJFord 5y ago
    What do you do when what you want/need is not representable in the ORM? For example: https://stackoverflow.com/questions/65596920/use-django-subquery-as-from-table-or-cte-in-order-to-have-a-window-over-an-aggre https://stackoverflow.com/questions/65596920/use-django-subq... Composite types & triggers, for some other examples, are only (at least they are!) available via third-party plugin/'installed app's too. You cannot defer a Django unique 'constraint', because it happens to implement it as a unique index instead. That's just the few that come to mind.
    - gabereiser 5y ago
      The problem is not the orm, it’s how you are treating data. The ORM has features for doing “out of the box” sql if you happen to fuck up your relational model. Which you clearly have in your s/o post. It’s ok, we all make mistakes. SQL modeling has been screwed up many many many times. For these rare occasions where the orm doesn’t match the object model or the desired action you are trying to achieve, there are ways to use raw sql, when necessary. Your case, it’s necessary as the relational model is broken.
      - OJFord 5y ago
        So, is anything that is representable in SQL but not in Django's tiny subset a 'fuck up'? Like many 9s of development are, it's brownfield anyway, so you're saying the ORM should only be used if one's prepared to make large schema changes?
        gabereiser 5y ago
        That’s not what I said at all.
        fuckf4ce 5y ago
        Sure sounds like it was.
        DangitBobby 5y ago
        Django's default subset is enough to do 99.9% of what you'd need to do (I know from experience) and there are various packages you can install to extend the ORM if necessary or if it just makes your life easier. Of course, as the other person said, you can always always always descend into raw SQL if you must or if it just saves you time.
  - spfzero 5y ago
    What might actually qualify as hubris, is thinking you've discovered the best solution for everyone else's future problems.
- dec0dedab0de 5y ago
  It's not really an either or thing. There are plenty of reasons to avoid an ORM. When I talk to python devs who don't like ORMs I recommend SQLAlchemy Core. It's all the most important parts without the ORM. In my experience most people against ORMs are not dead set on writing SQL by hand, they just don't like OOP. Of course there are some DBAs disguised as programmers running around, but they're more of an exception than the rule :-).
- OJFord 5y ago
  I'm quite anti-ORM, though I've never not used one professionally (just because it's already been there / not my decision), so that might be different if I did for a while. I do think something's necessary though, and it maybe is a bit arbitrary where we draw the line for what's an 'ORM'? For example, I really enjoyed using rusqlite for a little side project/personal thing, but found apart from anything else just the repetition of `(?,?,?,?,?,?)` any time I wanted to insert something was annoying, for example. I started working a bit on [0] .. I don't know if I'd call it an ORM, just so I didn't have to do that, and could write `Model { ... }.insert(&conn)` instead. But still write plain SQL queries, I just want easy mapping between db table and language 'model' struct/class; column and field/attr. [0] - https://github.com/OJFord/rusqlite-model https://github.com/OJFord/rusqlite-model
  - chousuke 5y ago
    I'm personally not that much against query builders, but I dislike things that try to hide the database. Any datastructure that transparently performs database queries as you access items in it goes swiftly in my "nope" pile. A reasonable exception can be made for streaming iterators in a statically typed context, though that has its own issues. I especially dislike "load" methods on objects since they hide what is potentially a very expensive operation behind something that looks cheap and is easy to call in a loop; what I want is to query data first and then load it into objects if needed rather than create objects for querying data.
ckmar 5y ago
Yeah, when you're doing the 1% of super complicated stuff, definitely don't use this (and opt for the underlying database driver directly). But when you're doing the 99%, it's great! Unlike with stateful ORMs, there is no temptation to do the 1% using some obscure orm methods and never heard of option params. Just write the sql.
kstrauser 5y ago
I was handed maintenance of a database with horrid schemas, like table_1 joined table_2 on "substr(table_1.foo, 4, 3) = upper(table_2.bar) and table_1.type = table_2.first_name", and crazy stuff like that. One table had an index on "upper(substr(purchase_id,1,1))". Another was indexed on "substring(table_3.xmlvars,'<KEY>(.*)</KEY>')". (I just looked up the old codebase and copied and pasted. These aren't exaggerations.) I had zero chance of fixing the schema because a giant legacy internal application was built with it, and I had to make my (new) application adapt to its schema instead. I spent a couple of months exhaustively documenting these relationships in the form of SQLAlchemy models, so that I could eventually write table_1.join(table_2) and have it do the right thing without having to remember (and implement!) that complexity everywhere those two tables touched. It was sanity saving, and for that I’m hugely grateful. Thank you. If you’re starting with a brand new DB schema, shiny and pure, perhaps you can get away with hardcoding a bunch of SQL. I’ve rarely had that luxury, so query builders are one of my favorite things.
- capitainenemo 5y ago
  Seems to me if there's a bunch of apps dependent on that legacy data, that cleanup could have been done more usefully and elegantly in the database with some views anyone could use. Or, if for some reason views couldn't be added (lack of access) a set of generic CTE includes?
  - kstrauser 5y ago
    Sigh, if only. OK, so the rest of the story was that the original app was written in Visual FoxPro. I was hired to build a web frontend for it. Well, turns out 1) there is, or at least was, no native VFP client for Unix, and 2) the Windows client was single threaded per host, so you couldn't even farm the connectivity out to a Windows process. After struggling with this for a while -- a cluster of Win XP hosts running a single-threaded VFP client and serving results to a Linux webserver via XMLRPC, as was the custom at the time -- I finally said "screw this, let's brute force it." That turned into https://github.com/kstrauser/pgdbf https://github.com/kstrauser/pgdbf, which is an app to convert a VFP table into a PostgreSQL table. We had it running on a cron job, which worked fine because the website was read-only and it was the VFP app that was actually writing to the tables. It was alright to have up to a few hours of latency between the VFP view of the data and the PostgreSQL view of it. When the company later committed to rewriting the VFP app in a sane language, they wrote it to run directly against PostgreSQL. That was quite a few years after I'd started there, though.

by zzzeek 5y ago

BiteCode_dev 5y ago

It's always the same with the anti orm crowd, they either avoid any wrapper and prevent having a standard api to build uppon and inspect, or they create a light wrapper that ends up being a poor badly tested and documented implementation of 1% of SQLA.

gabereiser 5y ago
Took the words right out of my mouth. As someone who has been adamant about using orm’s for the last 15 years, it shocks me to no end that an engineer (or group of engineers) think they can roll a DAO better than a battle hardened orm like SQLAlchemy, Hibernate, Entity Framework. It’s hubris in the least and straight arrogance at best.
- ckmar 5y ago
  YOLO
- blacktriangle 5y ago
  It depends what you're doing with the data. If you're trying to map into an object based view of your data, then yes ORMs have at least thought of most of the issues there. However if you'd like to view your relational data gasp relationally, then the ORM is a giant anchor around your neck.
  - BiteCode_dev 5y ago
    Good orms like sqla comes in layers, and the object relationships mapping is the highest one. You can use lower level layers as you need, and retain the benefit of a strong battle tested abstraction that gives you introspection and a standard API. Sometimes I use sqla just for the connection pool and session handling and dump raw sql in it. It's not raw sql vs orm, as usual it's use a spectrum with good use cases for each part of it, and you usually benefit from all of it. But orm have pareto value in my xp.
    - blacktriangle 5y ago
      ORMs only have value IF there's an object model you're mapping to. When working in a language that has good support for relational data, ie support for sets of heterogeneus maps, you can skip the whole object model and avoid the whole need for mapping in the first place. Ruby and Python in particular really missed the boat on this one. I feel like they cargo culted practices from Java rather than embrace the implications of their own langauge's flexibility.
      - ad404b8a372f2b9 5y ago
        Isn't that what pandas does kind of? You can execute sql queries and get the results in a dataframe which is a dictionary with some convenience functions on top. Do you feel python could have gone further to take advantage of specific language features to improve that workflow?
        blacktriangle 5y ago
        I was more taking shots at Django. pandas is a great example of doing things right imo and me ripping on python at large was more my web-dev myopia shining through.
      - spfzero 5y ago
        Right, OOP is sometimes not the best paradigm for the given problem. If you're using C# or Java, sure, ORMs fit right in. Ruby and Python, not to mention Lisp etc., offer a multi-paradigm palette where forcing objects in, just to access relational data, just obscures things.
- OJFord 5y ago
  What do you do when what you want/need is not representable in the ORM? For example: https://stackoverflow.com/questions/65596920/use-django-subquery-as-from-table-or-cte-in-order-to-have-a-window-over-an-aggre https://stackoverflow.com/questions/65596920/use-django-subq... Composite types & triggers, for some other examples, are only (at least they are!) available via third-party plugin/'installed app's too. You cannot defer a Django unique 'constraint', because it happens to implement it as a unique index instead. That's just the few that come to mind.
  - gabereiser 5y ago
    The problem is not the orm, it’s how you are treating data. The ORM has features for doing “out of the box” sql if you happen to fuck up your relational model. Which you clearly have in your s/o post. It’s ok, we all make mistakes. SQL modeling has been screwed up many many many times. For these rare occasions where the orm doesn’t match the object model or the desired action you are trying to achieve, there are ways to use raw sql, when necessary. Your case, it’s necessary as the relational model is broken.
    - OJFord 5y ago
      So, is anything that is representable in SQL but not in Django's tiny subset a 'fuck up'? Like many 9s of development are, it's brownfield anyway, so you're saying the ORM should only be used if one's prepared to make large schema changes?
      - gabereiser 5y ago
        That’s not what I said at all.
        fuckf4ce 5y ago
        Sure sounds like it was.
      - DangitBobby 5y ago
        Django's default subset is enough to do 99.9% of what you'd need to do (I know from experience) and there are various packages you can install to extend the ORM if necessary or if it just makes your life easier. Of course, as the other person said, you can always always always descend into raw SQL if you must or if it just saves you time.
- spfzero 5y ago
  What might actually qualify as hubris, is thinking you've discovered the best solution for everyone else's future problems.
dec0dedab0de 5y ago
It's not really an either or thing. There are plenty of reasons to avoid an ORM. When I talk to python devs who don't like ORMs I recommend SQLAlchemy Core. It's all the most important parts without the ORM. In my experience most people against ORMs are not dead set on writing SQL by hand, they just don't like OOP. Of course there are some DBAs disguised as programmers running around, but they're more of an exception than the rule :-).
OJFord 5y ago
I'm quite anti-ORM, though I've never not used one professionally (just because it's already been there / not my decision), so that might be different if I did for a while. I do think something's necessary though, and it maybe is a bit arbitrary where we draw the line for what's an 'ORM'? For example, I really enjoyed using rusqlite for a little side project/personal thing, but found apart from anything else just the repetition of `(?,?,?,?,?,?)` any time I wanted to insert something was annoying, for example. I started working a bit on [0] .. I don't know if I'd call it an ORM, just so I didn't have to do that, and could write `Model { ... }.insert(&conn)` instead. But still write plain SQL queries, I just want easy mapping between db table and language 'model' struct/class; column and field/attr. [0] - https://github.com/OJFord/rusqlite-model https://github.com/OJFord/rusqlite-model
- chousuke 5y ago
  I'm personally not that much against query builders, but I dislike things that try to hide the database. Any datastructure that transparently performs database queries as you access items in it goes swiftly in my "nope" pile. A reasonable exception can be made for streaming iterators in a statically typed context, though that has its own issues. I especially dislike "load" methods on objects since they hide what is potentially a very expensive operation behind something that looks cheap and is easy to call in a loop; what I want is to query data first and then load it into objects if needed rather than create objects for querying data.

ckmar 5y ago

Yeah, when you're doing the 1% of super complicated stuff, definitely don't use this (and opt for the underlying database driver directly). But when you're doing the 99%, it's great! Unlike with stateful ORMs, there is no temptation to do the 1% using some obscure orm methods and never heard of option params. Just write the sql.

kstrauser 5y ago

I was handed maintenance of a database with horrid schemas, like table_1 joined table_2 on "substr(table_1.foo, 4, 3) = upper(table_2.bar) and table_1.type = table_2.first_name", and crazy stuff like that. One table had an index on "upper(substr(purchase_id,1,1))". Another was indexed on "substring(table_3.xmlvars,'<KEY>(.*)</KEY>')". (I just looked up the old codebase and copied and pasted. These aren't exaggerations.) I had zero chance of fixing the schema because a giant legacy internal application was built with it, and I had to make my (new) application adapt to its schema instead. I spent a couple of months exhaustively documenting these relationships in the form of SQLAlchemy models, so that I could eventually write table_1.join(table_2) and have it do the right thing without having to remember (and implement!) that complexity everywhere those two tables touched. It was sanity saving, and for that I’m hugely grateful. Thank you. If you’re starting with a brand new DB schema, shiny and pure, perhaps you can get away with hardcoding a bunch of SQL. I’ve rarely had that luxury, so query builders are one of my favorite things.

capitainenemo 5y ago
Seems to me if there's a bunch of apps dependent on that legacy data, that cleanup could have been done more usefully and elegantly in the database with some views anyone could use. Or, if for some reason views couldn't be added (lack of access) a set of generic CTE includes?
- kstrauser 5y ago
  Sigh, if only. OK, so the rest of the story was that the original app was written in Visual FoxPro. I was hired to build a web frontend for it. Well, turns out 1) there is, or at least was, no native VFP client for Unix, and 2) the Windows client was single threaded per host, so you couldn't even farm the connectivity out to a Windows process. After struggling with this for a while -- a cluster of Win XP hosts running a single-threaded VFP client and serving results to a Linux webserver via XMLRPC, as was the custom at the time -- I finally said "screw this, let's brute force it." That turned into https://github.com/kstrauser/pgdbf https://github.com/kstrauser/pgdbf, which is an app to convert a VFP table into a PostgreSQL table. We had it running on a cron job, which worked fine because the website was read-only and it was the VFP app that was actually writing to the tables. It was alright to have up to a few hours of latency between the VFP view of the data and the PostgreSQL view of it. When the company later committed to rewriting the VFP app in a sane language, they wrote it to run directly against PostgreSQL. That was quite a few years after I'd started there, though.