5 ms·

The main problem I've encountered with complaints surrounding ORMs usually tend to be the result of trying to overfit the ORM in a certain way. ORMs are, for t

The main problem I've encountered with complaints surrounding ORMs usually tend to be the result of trying to overfit the ORM in a certain way. ORMs are, for the most part, good at the CRUD operations - that is to say, they easily translate SELECT, UPDATE, INSERT and DELETE operations between conventional class objects and database rows. Things they usually aren't very good at are when you start trying to do things that require a lot of optimization - it's very easy to have an ORM accidentally retrieve way more data than you need or to have it access a bunch of foreignkey data too many times (in Django you can thankfully preload the latter by specifying it in a queryset). That's less an issue for basic CRUD, but is an issue if you're doing say, mass calculation and only need one column and none of the foreignkey data for speed reasons. Basically - an ORM is good but don't let yourself feel suffocated by it. If it's not a good fit for an ORM, then don't do it in the ORM, either use SQL code to do it in the DB server or do a simpler SELECT (in the ORM) and do the complex operation in your regular application before INSERTing it back in the db (if that's a goal for the operation anyway). If it's outside of the CRUD types of DB access already, the extra maintenance overhead you get from having non-ORM database code (if you're doing the SQL approach) in the application would be there anyway, you'd just get a very slow application instead of a hard error, and the latter is easier to troubleshoot (and often fix), while with the former you need to start pulling up profiling tools.

pydry 3y ago
>Things they usually aren't very good at are when you start trying to do things that require a lot of optimization I find this ends up being, like, 1 or 2% of queries. It's also very hard if not impossible to guess which queries will end up in that group. You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize. There is also a small % of queries which use some feature of your database engine which the ORM won't support.
- nicoburns 3y ago
  I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick. And doubly so for "list" type endpoints where you're selecting many records. I tend to use the ORM function CUD operations, and just write raw SQL for SELECTs unless they're super-simple. > It's also very hard if not impossible to guess which queries will end up in that group. > You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize. I disagree on this. It's almost no extra work to just write these optimised in the first place (if you're not trying to squash everything into an ORM workflow). So it makes sense just to write them all optimised. Ditto for doing batch inserts and updates rather than looped inserts/updates (although you can usually use the ORM for this).
  - michaelmior 3y ago
    > It's almost no extra work to just write these optimised in the first place Not everyone is capable of quickly optimizing SQL, and I don't think it's an absolutely necessary skill to build a decent application. Junior devs can pick up on this skill over time and as long as they can manage to avoid any obvious footguns, using the ORM is fine most of the time. Writing queries that are optimized in the first place just means now you have to maintain a bunch of SQL and you have to rely on anyone modifying that SQL later understanding those optimizations. Sometimes it's necessary, but if it's not, I think it's much nicer to stay in ORM-land even if the query might not be optimal.
    - nicoburns 3y ago
      > Not everyone is capable of quickly optimizing SQL I mean that's true, but equally not everyone is capable of using an ORM. I don't think SQL is inherently any harder to learn. At my last job, I had juniors who had never used SQL at all productive in SQL within a couple of weeks, and using "complex" SQL like JSON aggregation and windows function with a few months. They were a little intimidated by it when they started, but didn't find it too hard to learn in the end.
    - sgarland 3y ago
      Honestly, I think I could get someone with no raw SQL experience writing code that’s at least 50% faster within a few hours. There are so many footguns that ORMs completely ignore, and never warn you about. Using a case-sensitive filter (default for Django) in a DB with case-sensitive collation (default in Postgres)? Django will helpfully cast the tuple and your query to UPPER to match it for you, and the former wrecks indexing. Checking if a string ends with something else? Goodbye, index. I _think_ the latter can be worked around in PG with a GIN index, but I’m not positive (I work with MySQL much more). And in any case, you’d have to know to create that, and I imagine most devs won’t. Fixing seemingly tiny things like that have a massive impact on large table query speed.
      - michaelmior 3y ago
        With your example of optimization, I think anyone who is able to make this optimization could certainly also implement it on top of an ORM.
- CuriouslyC 3y ago
  Nah, ORMs just encourage a lot of bad behavior, and come with edge cases and code bloat. You're better off using an API generator such as postgrest/hasura for the simple cases, and hand crafted queries for anything more complex than basic crud.
- dgb23 3y ago
  Or you use something much simpler and more light weight, like query builders or a tool that generates code from sql queries. This approach is more bottom up. You end up with uni directional data flow, better separation of concerns, data coupling instead of object dependencies and better performance right out of the bat. The cost? In my experience just some basic familiarity with SQL.
- hobs 3y ago
  The problem is that what you typically get is one group of people who have no idea how to do anything outside of the ORM and play off the problems because hey, its only a problem query or two that use up 100% of the system resources of the databases and bring them crashing down, but might as well throw more system resources at it because we've spent no time understanding queries the last N years of building.
agumonkey 3y ago
I realized too late that objects, as of now, are not capable of synthetizing a new class/type based on joins.
- Silhouette 3y ago
  It's strange that this point doesn't get more attention. ORMs by their nature tend to be built around a 1:1 mapping between fields on some object type and columns in some table. Bulk queries get you multiple objects corresponding to multiple rows. Relationships get you multiple objects with some of the fields being references to the other objects. Obviously I'm simplifying here and there have also been some attempts to do things in other ways but this is basically how most of the popular ORMs work today. However in reality a lot of useful queries return a list of flat data structures or even just a single flat data structure with some subset of the columns of all of the relevant tables and maybe a few extra columns that are calculated on demand and not stored directly in any database table. If that's the data I've read then what I really want is something like a properly-typed dataclass with exactly those fields/columns and nothing else that might add confusion or ambiguity. Unfortunately that doesn't really fit the classic ORM and OO model. Instead we often have to work with multiple objects with some form of nesting to follow the relationships, ambiguity about which fields have actually been read from the database and can safely be accessed, possibly some inaccuracy with the types such as nullable fields that have just been read from not null columns in the database, and a lottery to see what happens if we try to access fields on those objects that might not have been read by any previous database query anywhere in the system at any point since that particular ORM-backed object was created. I find it's one of those things where the popular approach - using an ORM in this case - works for relatively simple needs and in practice a lot of work does only have relatively simple needs so that's OK. But when I start doing more complicated things it can become a pain to work with because the whole model fundamentally doesn't fit what I'm actually doing.
  - wruza 3y ago
    Something like this is implemented in russian 1C system. They use an extended query language (bilingual as the whole system) and the query executor returns a special dataset object with all values wrapped into regular business-logic classes. So when you “select …, agent, … from … join …”, you can access record.agent.manager.phone in your code later. Everyone knows the difference between selecting it in query and in code. All primitive types get wrapped too: dates to dates, bools to bools. It has no static typing, but the query result fields are all of “platform” types, not raw values like ids or json/int dates. Don’t get me wrong, 1C products are regular enterprise crap on top of shitty language that stuck in the last century. The latest attempt to refit it as-is to www was a paradigmal disaster. But the platform (the runtime) itself may teach Django a volume or two about query integration. They do it since the '90s and 1C developers who traditionally weren’t even considered developers had no issues with programming these systems without any deep stack knowledge. There’s no stack basically, it’s all homogeneous once you learn the fundamentals. Edit: yes, I find it very strange too. There’s no popular/generic and simple open source platform which could bind it all together into a nice runtime. The whole ORM vs SQL and pitfalls feels so strange, as it’s not something hard in my book.

pydry 3y ago

>Things they usually aren't very good at are when you start trying to do things that require a lot of optimization I find this ends up being, like, 1 or 2% of queries. It's also very hard if not impossible to guess which queries will end up in that group. You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize. There is also a small % of queries which use some feature of your database engine which the ORM won't support.

nicoburns 3y ago
I find this ends up being 90% of SELECT queries. Usually when selecting data you want to retrieve a bunch of related objects too. Often with complex criteria for which objects to pick. And doubly so for "list" type endpoints where you're selecting many records. I tend to use the ORM function CUD operations, and just write raw SQL for SELECTs unless they're super-simple. > It's also very hard if not impossible to guess which queries will end up in that group. > You're better off building it with the ORM first and breaking out SQL later when you are trying to performance optimize. I disagree on this. It's almost no extra work to just write these optimised in the first place (if you're not trying to squash everything into an ORM workflow). So it makes sense just to write them all optimised. Ditto for doing batch inserts and updates rather than looped inserts/updates (although you can usually use the ORM for this).
- michaelmior 3y ago
  > It's almost no extra work to just write these optimised in the first place Not everyone is capable of quickly optimizing SQL, and I don't think it's an absolutely necessary skill to build a decent application. Junior devs can pick up on this skill over time and as long as they can manage to avoid any obvious footguns, using the ORM is fine most of the time. Writing queries that are optimized in the first place just means now you have to maintain a bunch of SQL and you have to rely on anyone modifying that SQL later understanding those optimizations. Sometimes it's necessary, but if it's not, I think it's much nicer to stay in ORM-land even if the query might not be optimal.
  - nicoburns 3y ago
    > Not everyone is capable of quickly optimizing SQL I mean that's true, but equally not everyone is capable of using an ORM. I don't think SQL is inherently any harder to learn. At my last job, I had juniors who had never used SQL at all productive in SQL within a couple of weeks, and using "complex" SQL like JSON aggregation and windows function with a few months. They were a little intimidated by it when they started, but didn't find it too hard to learn in the end.
  - sgarland 3y ago
    Honestly, I think I could get someone with no raw SQL experience writing code that’s at least 50% faster within a few hours. There are so many footguns that ORMs completely ignore, and never warn you about. Using a case-sensitive filter (default for Django) in a DB with case-sensitive collation (default in Postgres)? Django will helpfully cast the tuple and your query to UPPER to match it for you, and the former wrecks indexing. Checking if a string ends with something else? Goodbye, index. I _think_ the latter can be worked around in PG with a GIN index, but I’m not positive (I work with MySQL much more). And in any case, you’d have to know to create that, and I imagine most devs won’t. Fixing seemingly tiny things like that have a massive impact on large table query speed.
    - michaelmior 3y ago
      With your example of optimization, I think anyone who is able to make this optimization could certainly also implement it on top of an ORM.
CuriouslyC 3y ago
Nah, ORMs just encourage a lot of bad behavior, and come with edge cases and code bloat. You're better off using an API generator such as postgrest/hasura for the simple cases, and hand crafted queries for anything more complex than basic crud.
dgb23 3y ago
Or you use something much simpler and more light weight, like query builders or a tool that generates code from sql queries. This approach is more bottom up. You end up with uni directional data flow, better separation of concerns, data coupling instead of object dependencies and better performance right out of the bat. The cost? In my experience just some basic familiarity with SQL.
hobs 3y ago
The problem is that what you typically get is one group of people who have no idea how to do anything outside of the ORM and play off the problems because hey, its only a problem query or two that use up 100% of the system resources of the databases and bring them crashing down, but might as well throw more system resources at it because we've spent no time understanding queries the last N years of building.

agumonkey 3y ago

I realized too late that objects, as of now, are not capable of synthetizing a new class/type based on joins.

Silhouette 3y ago
It's strange that this point doesn't get more attention. ORMs by their nature tend to be built around a 1:1 mapping between fields on some object type and columns in some table. Bulk queries get you multiple objects corresponding to multiple rows. Relationships get you multiple objects with some of the fields being references to the other objects. Obviously I'm simplifying here and there have also been some attempts to do things in other ways but this is basically how most of the popular ORMs work today. However in reality a lot of useful queries return a list of flat data structures or even just a single flat data structure with some subset of the columns of all of the relevant tables and maybe a few extra columns that are calculated on demand and not stored directly in any database table. If that's the data I've read then what I really want is something like a properly-typed dataclass with exactly those fields/columns and nothing else that might add confusion or ambiguity. Unfortunately that doesn't really fit the classic ORM and OO model. Instead we often have to work with multiple objects with some form of nesting to follow the relationships, ambiguity about which fields have actually been read from the database and can safely be accessed, possibly some inaccuracy with the types such as nullable fields that have just been read from not null columns in the database, and a lottery to see what happens if we try to access fields on those objects that might not have been read by any previous database query anywhere in the system at any point since that particular ORM-backed object was created. I find it's one of those things where the popular approach - using an ORM in this case - works for relatively simple needs and in practice a lot of work does only have relatively simple needs so that's OK. But when I start doing more complicated things it can become a pain to work with because the whole model fundamentally doesn't fit what I'm actually doing.
- wruza 3y ago
  Something like this is implemented in russian 1C system. They use an extended query language (bilingual as the whole system) and the query executor returns a special dataset object with all values wrapped into regular business-logic classes. So when you “select …, agent, … from … join …”, you can access record.agent.manager.phone in your code later. Everyone knows the difference between selecting it in query and in code. All primitive types get wrapped too: dates to dates, bools to bools. It has no static typing, but the query result fields are all of “platform” types, not raw values like ids or json/int dates. Don’t get me wrong, 1C products are regular enterprise crap on top of shitty language that stuck in the last century. The latest attempt to refit it as-is to www was a paradigmal disaster. But the platform (the runtime) itself may teach Django a volume or two about query integration. They do it since the '90s and 1C developers who traditionally weren’t even considered developers had no issues with programming these systems without any deep stack knowledge. There’s no stack basically, it’s all homogeneous once you learn the fundamentals. Edit: yes, I find it very strange too. There’s no popular/generic and simple open source platform which could bind it all together into a nice runtime. The whole ORM vs SQL and pitfalls feels so strange, as it’s not something hard in my book.