7 ms·

As a junior dev I had no idea what an ORM was. As a mid-level dev I discovered them and wanted to use them everywhere. As a senior dev I've gone back to manua

As a junior dev I had no idea what an ORM was. As a mid-level dev I discovered them and wanted to use them everywhere. As a senior dev I've gone back to manually writing queries. Such is life.

m0zg 5y ago
Real fun begins if your team has all three stages of seniority. Typically it's somewhere in the middle, so your default is to use ORM, even though as a senior person you know everyone will profoundly regret this in the long run (and you will regret it immediately).
hparadiz 5y ago
I've gone past this and have a very clean and efficient query builder in my ORM. I maintain one for work and another is my open source PHP framework (15 years old now). Mine supports simple joins and relationships but anything beyond that requires writing custom queries.
- unclebucknasty 5y ago
  How does your query builder work? Is it an API that you use to build queries during runtime or do you generate database access code from queries?
  - hparadiz 5y ago
    The framework has 3 layers, Query Builder -> ActiveRecord based ORM -> CRUD Controllers The CRUD controllers are then mapped with a router to different paths. When you create a CRUD controller you extend the base class which has everything you need and then you set a single parameter, className which sets the object for which this CRUD controller is for. The base class works out of the box with front ends like agGrid. The query builder itself is only part of the puzzle. Every single ORM in existence is converting the object's data from the native types of the language into the types of the database and vice versa. There's almost always some configuration for each object type in whatever format from native code to yaml to xml to plain text. Every ORM has a toDatabaseValue() normalization function that also goes in reverse when pulling data from the database. Some ORMs will call this "data mapper" or "mapping" or "normalization". When this becomes evident the queries themselves become suuuuper simple because what you send into your INSERT or UPDATE queries is a complete array of strings with field values already predetermined. This is necessary because a database engine might have dozens of types like enum, string, text, longtext,smalltext,bigtext,varchar, char but all of them in your language could be "string". Advanced ORMs will know the database engine you use and even warn you if you try to save a string over 255 chars to a (255) char database engine but most don't. My code is available here. https://github.com/Divergence/framework/blob/6af1b6b0e56b25cbcfc4e9e13aea1f77477da972/src/Models/ActiveRecord.php#L716-L735 https://github.com/Divergence/framework/blob/6af1b6b0e56b25c... Where people go wrong is trying to do this field normalization logic inside their query builder. That leads to all sorts of problems. The query builder shouldn't know what is going on with your ORM at all. It's just gluing strings together to form a query. You can see my dead simple query builder here: https://github.com/Divergence/framework/tree/develop/src/IO/Database/Query https://github.com/Divergence/framework/tree/develop/src/IO/... I honestly used to not have one as it's so simple but decided it was a good abstraction to assimilate from other frameworks. My query builder does not attempt to sanitize anything. The ActiveRecord class takes care of that through the data mapping conversion functions for all the basic HTTP CRUD functionality.
    - deleted 5y ago
      [deleted]
    - unclebucknasty 5y ago
      Interesting. Thanks for sharing! I wrote something for Java roughly 20 years ago (before ORM was really a thing). Of course I enhanced as needed over the years, but have used it heavily. It supports any DB with a JDBC driver and has easily saved me more time than anything else. There's not even a close second. It's a pure code generator with a UI on top. You write an SQL select stub that references columns/tables for one or more tables, then provide the class name you want to generate. You then add any number of where clauses (including join conditions and parameterized placeholders). For each where clause, you use checkboxes to indicate what you want generated: select, update, insert, upsert, delete. When the code is generated, all column-to-object field mappings, including types, are resolved through inspection of metadata (provided by the JDBC driver). Property names for the resulting domain objects are also generated using snake to camel case translations and, of course, methods resulting from parameterized statements include parameters of the correct types. So, instead of having, say, a User class with a Profile class, which has an Image class, each with its own properties representing the full set of columns for the underlying table, you frequently need a limited subset of each combined into one object. For instance, a ProfileListView might need username, email, first and last from user; profile_pic_id, slug, and short_bio from profile; and thumb_filename from image. There's more to it, but I took this join approach vs the "one table to one object" model because I found early that you frequently need different views of the data, based on different use cases. So, it's a way to flatten the underlying relational model when needed. Not perfect, of course, but the best thing going when I built it. And since, I never found an ORM that was worth the switch.
karmasimida 5y ago
ORM is that one layer that fails to abstract anything substantial yet whose existence is intrusive enough to annoy people. The worst example of leaky abstraction.
- toast0 5y ago
  ORM makes the easy things easier and the hard things harder. No thanks.
  - cageface 5y ago
    So choose an ORM that lets you easily drop down to plain SQL in the rare cases where it's necessary and you get the best of both worlds.
    - devoutsalsa 5y ago
      100%. You don’t have to trash your 5 passenger car because you occasionally need to transport 6 people.
      - laumars 5y ago
        You also don’t buy a 5 seater car to begin with if you already know you need 6 seats.
    - deleted 5y ago
      [deleted]
    - laumars 5y ago
      Or just do away with your ORM entirely and train your staff how to write SQL so they can be effective regardless of the complexity of the queries.
  - aidos 5y ago
    Can you provide some examples? I use sqlalchemy, and the amount of work that it does for me when it comes to handling mutating data is pretty remarkable.
GordonS 5y ago
And when you eventually ascend to true mastery, you'll be able to enter a zen-like trance, in which you'll be able to tell when to use one or not, or indeed whether to use both (e.g. ORM for simple CRUD stuff, hand-written queries for everything else).
- diveanon 5y ago
  This is the way. ORM’s can be a huge time saver and simplify your code base for the day to day CRUD, nobody should be writing that anymore IMO.
- brodo 5y ago
  The problem is that most apps start out as simple CRUD apps. Also each dependency is a liability. People really underestimate how pulling in dependencies can lower your software quality. Most people have security bugs in mind, but there are also performance issues, memory leaks and logging-issues, needles abstractions and incompatibilities.
  - SomeCallMeTim 5y ago
    It's not a problem that they start as CRUD apps. That's exactly the place where ORMs or query generators should be used: CRUD code is always the same and as such should be 100% automated. So then when something different than CRUD happens? You use the ORM, a query generator, or completely custom SQL, depending on which is the most appropriate to the situation. Performance doesn't need to be an issue if the query is extremely rare; if it's core to your app, by all means, write completely optimized SQL. But if it's an extremely rare query or only shows up for your admin page? ORM code should be fine. That's the point of the parent comment: When you get good enough you learn where to use each.
  - inshadows 5y ago
    In my case I always wants to do something that is NOT CRUD, but guides on Internet always want to teach me how to do CRUD apps.
VWWHFSfQ 5y ago
As with everything, it really depends on what you're doing. Do I want to implement an entire CMS and core business backend directly in SQL? No, of course I don't. I want to use Django's ORM because then I get all the nice QuerySet API, integrations with forms and filters, the nice Admin UI, etc. But do I want my end-users to hit my Django ORM APIs 10,000 times per second? Absolutely not. So you write hand-optimized SQL for those and maybe run them in a service a little bit faster than Python/Django/Gunicorn. It's the same database, it's the same "API". But you get the value of everything that Django's ORM provides and you just cheat a little bit by dropping down to the lower level for things that need it, and not everything needs it. Don't throw out the baby with the bath water!
dagmx 5y ago
As a senior dev, I use an ORM unless I have a reason not to, and I'd know why or not fairly early. Why do I use an ORM? Because they're way more maintainable for a wider range of devs, with fewer accidental footguns, and the good ones let me do direct queries too, so there's little downside. So I default to using an ORM like sqlalchemy , because I can hand it off to a junior dev afterwards, and they can easily keep it going without needing to be writing good SQL directly. The other advantage is that I can abstract over multiple databases easily. So I can have an sqlite db for quick local testing, and a postgres DB for more thorough testing and another for production.
- laumars 5y ago
  If you can’t trust your junior devs to write SQL nor have sufficient peer review (like pull requests on feature branches) to assist with their training then you should be using stored procedures and remove that responsibility from your developers entirely. Either that or sending your juniors on training exercises to level up their SQL experience. And you don’t need ORMs to abstract away the RDBMS engine from your main codebase. Nearly every programming language these days (even statically compiled ones which support almost nothing in the way of dynamic code, like Go Lang) will allow you to do this. I’m almost all instances where people think they need ORMs, what they really need is better software development practices in the wider sense and ORMs were just an excuse to hide those larger problems.
  - dagmx 5y ago
    Your first statement applies to anything and everything though. If I make any decision that my junior devs aren't comfortable with, I should either do the work for them or train them up. But that's more time and/or effort, when the easier solution for both of us gets us there faster. What programming languages abstract away all the as many details of the RDBMS as something like sqlalchemy? Sqlite and postgres do enough things just slightly differently that I'd have to take slightly different code paths to support both. At the very least they'd both require different drivers. In almost all instances where someone suggests that what is really needed is better software development practices, they're going to be outperformed by better tooling and people using the easier route for the most people.
ufmace 5y ago
I lean nowadays towards: For ~90% of your database use, ORMs do basically the same queries and get rid of a massive amount of boilerplate and potential errors. Use 'em so you don't spend all day manually mapping object fields to the right slots in SQL statements. For the other ~10% of database use, ORMs make it a massive pain to write a query that does what you actually want, or do something boneheadedly inefficient. So use an ORM that makes it easy to drop to pure SQL when you need to, and don't hesitate to do it if the situation calls for it.

As a junior dev I had no idea what an ORM was. As a mid-level dev I discovered them and wanted to use them everywhere. As a senior dev I've gone back to manually writing queries. Such is life.

m0zg 5y ago

Real fun begins if your team has all three stages of seniority. Typically it's somewhere in the middle, so your default is to use ORM, even though as a senior person you know everyone will profoundly regret this in the long run (and you will regret it immediately).

hparadiz 5y ago

I've gone past this and have a very clean and efficient query builder in my ORM. I maintain one for work and another is my open source PHP framework (15 years old now). Mine supports simple joins and relationships but anything beyond that requires writing custom queries.

unclebucknasty 5y ago
How does your query builder work? Is it an API that you use to build queries during runtime or do you generate database access code from queries?
- hparadiz 5y ago
  The framework has 3 layers, Query Builder -> ActiveRecord based ORM -> CRUD Controllers The CRUD controllers are then mapped with a router to different paths. When you create a CRUD controller you extend the base class which has everything you need and then you set a single parameter, className which sets the object for which this CRUD controller is for. The base class works out of the box with front ends like agGrid. The query builder itself is only part of the puzzle. Every single ORM in existence is converting the object's data from the native types of the language into the types of the database and vice versa. There's almost always some configuration for each object type in whatever format from native code to yaml to xml to plain text. Every ORM has a toDatabaseValue() normalization function that also goes in reverse when pulling data from the database. Some ORMs will call this "data mapper" or "mapping" or "normalization". When this becomes evident the queries themselves become suuuuper simple because what you send into your INSERT or UPDATE queries is a complete array of strings with field values already predetermined. This is necessary because a database engine might have dozens of types like enum, string, text, longtext,smalltext,bigtext,varchar, char but all of them in your language could be "string". Advanced ORMs will know the database engine you use and even warn you if you try to save a string over 255 chars to a (255) char database engine but most don't. My code is available here. https://github.com/Divergence/framework/blob/6af1b6b0e56b25cbcfc4e9e13aea1f77477da972/src/Models/ActiveRecord.php#L716-L735 https://github.com/Divergence/framework/blob/6af1b6b0e56b25c... Where people go wrong is trying to do this field normalization logic inside their query builder. That leads to all sorts of problems. The query builder shouldn't know what is going on with your ORM at all. It's just gluing strings together to form a query. You can see my dead simple query builder here: https://github.com/Divergence/framework/tree/develop/src/IO/Database/Query https://github.com/Divergence/framework/tree/develop/src/IO/... I honestly used to not have one as it's so simple but decided it was a good abstraction to assimilate from other frameworks. My query builder does not attempt to sanitize anything. The ActiveRecord class takes care of that through the data mapping conversion functions for all the basic HTTP CRUD functionality.
  - deleted 5y ago
    [deleted]
  - unclebucknasty 5y ago
    Interesting. Thanks for sharing! I wrote something for Java roughly 20 years ago (before ORM was really a thing). Of course I enhanced as needed over the years, but have used it heavily. It supports any DB with a JDBC driver and has easily saved me more time than anything else. There's not even a close second. It's a pure code generator with a UI on top. You write an SQL select stub that references columns/tables for one or more tables, then provide the class name you want to generate. You then add any number of where clauses (including join conditions and parameterized placeholders). For each where clause, you use checkboxes to indicate what you want generated: select, update, insert, upsert, delete. When the code is generated, all column-to-object field mappings, including types, are resolved through inspection of metadata (provided by the JDBC driver). Property names for the resulting domain objects are also generated using snake to camel case translations and, of course, methods resulting from parameterized statements include parameters of the correct types. So, instead of having, say, a User class with a Profile class, which has an Image class, each with its own properties representing the full set of columns for the underlying table, you frequently need a limited subset of each combined into one object. For instance, a ProfileListView might need username, email, first and last from user; profile_pic_id, slug, and short_bio from profile; and thumb_filename from image. There's more to it, but I took this join approach vs the "one table to one object" model because I found early that you frequently need different views of the data, based on different use cases. So, it's a way to flatten the underlying relational model when needed. Not perfect, of course, but the best thing going when I built it. And since, I never found an ORM that was worth the switch.

karmasimida 5y ago

ORM is that one layer that fails to abstract anything substantial yet whose existence is intrusive enough to annoy people. The worst example of leaky abstraction.

toast0 5y ago
ORM makes the easy things easier and the hard things harder. No thanks.
- cageface 5y ago
  So choose an ORM that lets you easily drop down to plain SQL in the rare cases where it's necessary and you get the best of both worlds.
  - devoutsalsa 5y ago
    100%. You don’t have to trash your 5 passenger car because you occasionally need to transport 6 people.
    - laumars 5y ago
      You also don’t buy a 5 seater car to begin with if you already know you need 6 seats.
  - deleted 5y ago
    [deleted]
  - laumars 5y ago
    Or just do away with your ORM entirely and train your staff how to write SQL so they can be effective regardless of the complexity of the queries.
- aidos 5y ago
  Can you provide some examples? I use sqlalchemy, and the amount of work that it does for me when it comes to handling mutating data is pretty remarkable.

GordonS 5y ago

And when you eventually ascend to true mastery, you'll be able to enter a zen-like trance, in which you'll be able to tell when to use one or not, or indeed whether to use both (e.g. ORM for simple CRUD stuff, hand-written queries for everything else).

diveanon 5y ago
This is the way. ORM’s can be a huge time saver and simplify your code base for the day to day CRUD, nobody should be writing that anymore IMO.
brodo 5y ago
The problem is that most apps start out as simple CRUD apps. Also each dependency is a liability. People really underestimate how pulling in dependencies can lower your software quality. Most people have security bugs in mind, but there are also performance issues, memory leaks and logging-issues, needles abstractions and incompatibilities.
- SomeCallMeTim 5y ago
  It's not a problem that they start as CRUD apps. That's exactly the place where ORMs or query generators should be used: CRUD code is always the same and as such should be 100% automated. So then when something different than CRUD happens? You use the ORM, a query generator, or completely custom SQL, depending on which is the most appropriate to the situation. Performance doesn't need to be an issue if the query is extremely rare; if it's core to your app, by all means, write completely optimized SQL. But if it's an extremely rare query or only shows up for your admin page? ORM code should be fine. That's the point of the parent comment: When you get good enough you learn where to use each.
- inshadows 5y ago
  In my case I always wants to do something that is NOT CRUD, but guides on Internet always want to teach me how to do CRUD apps.

VWWHFSfQ 5y ago

As with everything, it really depends on what you're doing. Do I want to implement an entire CMS and core business backend directly in SQL? No, of course I don't. I want to use Django's ORM because then I get all the nice QuerySet API, integrations with forms and filters, the nice Admin UI, etc. But do I want my end-users to hit my Django ORM APIs 10,000 times per second? Absolutely not. So you write hand-optimized SQL for those and maybe run them in a service a little bit faster than Python/Django/Gunicorn. It's the same database, it's the same "API". But you get the value of everything that Django's ORM provides and you just cheat a little bit by dropping down to the lower level for things that need it, and not everything needs it. Don't throw out the baby with the bath water!

dagmx 5y ago

As a senior dev, I use an ORM unless I have a reason not to, and I'd know why or not fairly early. Why do I use an ORM? Because they're way more maintainable for a wider range of devs, with fewer accidental footguns, and the good ones let me do direct queries too, so there's little downside. So I default to using an ORM like sqlalchemy , because I can hand it off to a junior dev afterwards, and they can easily keep it going without needing to be writing good SQL directly. The other advantage is that I can abstract over multiple databases easily. So I can have an sqlite db for quick local testing, and a postgres DB for more thorough testing and another for production.

laumars 5y ago
If you can’t trust your junior devs to write SQL nor have sufficient peer review (like pull requests on feature branches) to assist with their training then you should be using stored procedures and remove that responsibility from your developers entirely. Either that or sending your juniors on training exercises to level up their SQL experience. And you don’t need ORMs to abstract away the RDBMS engine from your main codebase. Nearly every programming language these days (even statically compiled ones which support almost nothing in the way of dynamic code, like Go Lang) will allow you to do this. I’m almost all instances where people think they need ORMs, what they really need is better software development practices in the wider sense and ORMs were just an excuse to hide those larger problems.
- dagmx 5y ago
  Your first statement applies to anything and everything though. If I make any decision that my junior devs aren't comfortable with, I should either do the work for them or train them up. But that's more time and/or effort, when the easier solution for both of us gets us there faster. What programming languages abstract away all the as many details of the RDBMS as something like sqlalchemy? Sqlite and postgres do enough things just slightly differently that I'd have to take slightly different code paths to support both. At the very least they'd both require different drivers. In almost all instances where someone suggests that what is really needed is better software development practices, they're going to be outperformed by better tooling and people using the easier route for the most people.

ufmace 5y ago

I lean nowadays towards: For ~90% of your database use, ORMs do basically the same queries and get rid of a massive amount of boilerplate and potential errors. Use 'em so you don't spend all day manually mapping object fields to the right slots in SQL statements. For the other ~10% of database use, ORMs make it a massive pain to write a query that does what you actually want, or do something boneheadedly inefficient. So use an ORM that makes it easy to drop to pure SQL when you need to, and don't hesitate to do it if the situation calls for it.