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.
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.
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.
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.
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.
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.
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.
You train them up. You don’t do the work for them nor do you intentionally cripple your application to avoid your responsibility of training them up.
A healthy organisation will have feature branches, pull requests with peer reviews. You’ll have unit tests, and CI/CD to enforce those tests. You’ll have code coverage analysis too. This is all pretty standard stuff these days so there’s no excuse in wussing out from supporting your junior engineers.
> 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.
You don’t initiate the drivers inside your SQL queries. In fact Perl has had tools to abstract that away for 2 decades! PHP, Java and Go abstract away the driver initiation too. In fact of the dozen or so different backend languages I’ve used over the years, I can’t think of any where you couldn’t go that. More over it is the correct way to architect one’s code. This is basic stuff, web 101.
Yeah some SQL syntax might differ from one RDBMS to another. If that’s the case then your dev environment should mirror the production environment (frankly that should be the case anyway otherwise you’d end up back in the “it works on my machine” arguments of the last decade; back before we learned how to do this shit correctly).
> 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.
But you’re doing the exact opposite of that by not using git feature branches, CI/CD, abstracting away your DB connection (which leads me to wonder if you’re even using connection pooling), unit tests, consistent development environments and all the other advancements of the last 20 years.
This isn’t a hypothetical argument either. I’ve been in the industry for decades, have worked on workflows like you’ve described as well as ones like I have. When I’ve worked in shops like you describe and the quality of the output was embarrassing. I’ve also managed places where I’ve then introduced the aforementioned and the number of bugs in production plummeted, the amount of QA time needed dropped, the number of times we’ve had to rollback a release have dropped to zero, the junior developers are now snake to work across more projects (giving more room for more projects to happen) and the CEOs impression of the team has gone up significantly.
In fact the majority of your argument isn't even focused on the discussion of an ORM but on a situation you made up.
----
That said, let's address your frankly odd rebuttal...
Training takes time , and in a lot of cases is going to have a negligible gain over just using the ORM to begin with.
Regarding everything else like CI, branches etc... (you're now making a strawman but let's address it anyway) Of course those are important, and they have a way smaller cost per dev than learning a new language, and making sure every PR is valid against the current schemas of the DB, without introducing accidental performance footguns.
In the end it's all about all the forms of cost, which is exactly what my initial post said and you seem to have glossed over. Using an ORM isn't free for every use case, but in many use cases it's cheaper than training up people, especially when this isn't a regular part of their job. Perhaps you're assuming a job where everyone is constantly working on DB interactions but a lot of gigs have stuff like this as a small part of the job.
If they were only doing db interactions, then obviously the cost calculation changes.
I'm also not talking about cost in monetary terms. I'm talking about cost in terms of time, effort, financials, planning etc...
I'm not sure why you suddenly suggest I wouldn't be using git branches or CI to try and make your point? Again, those have a better cost to benefit ratio. Every single decision is down to that.when picking a project.
No, the vast majority was explaining how development best practices resolve the common use case that is being presented here for why ORMs are a good thing (namely “my junior devs are somehow too stupid to learn SQL”).
> That said, let's address your frankly odd rebuttal...
What’s odd is that you think setting up guardrails to prevent human error and then empowering your developers to write good code is itself odd.
> In the end it's all about all the forms of cost, which is exactly what my initial post said and you seem to have glossed over.
I covered that point with my anecdote, where I talked about the improvements that best practices bring.
> Using an ORM isn't free for every use case, but in many use cases it's cheaper than training up people, especially when this isn't a regular part of their job. Perhaps you're assuming a job where everyone is constantly working on DB interactions but a lot of gigs have stuff like this as a small part of the job.
It doesn’t cost any extra to train people up using the development practices I’ve outlined. Training just becomes a side effect of day to day reviews. However ORMs have a very real performance cost that will have an ongoing bottom line in your infrastructure performance and hosting costs.
One is a free win/win scenario and the other is a lazy hack that adds costs.
> If they were only doing db interactions, then obviously the cost calculation changes.
You’re underestimating your juniors ability to learn. Or you happen to work in some really toxic environments (which might explain your other view points). But you don’t need to clock up 40 hours a week on SQL to become competent enough to write most queries.
Remember we are not talking about making them DBAs, just then being competent enough to be at least as good as your average ORM (which, frankly, isn’t hard).
> I'm also not talking about cost in monetary terms. I'm talking about cost in terms of time, effort, financials, planning etc...
I’ve been factoring all of them in as well. I’m a cost centre owner and people manager and so I’m well versed in how to equate costs correctly. I’ve had to do exactly these kinds of studies professionally.
> I'm not sure why you suddenly suggest I wouldn't be using git branches or CI to try and make your point?
Because you make the argument that getting juniors to write SQL will lead to footguns and I’m explaining how you manage that correctly.
> Again, those have a better cost to benefit ratio. Every single decision is down to that.when picking a project.
No, those practices should be default for any all commercial projects. If you’re knocking up a POC or building something for a hobby then feel free to do it however you want (within reason). But anything built as part of an internal system or external product, and especially if it needs to be maintained for any duration of time, they should all be developed using the principles I’ve described.
Every thing has an associated cost. An ORM in a familiar language, like sqlalchemy in Python, is easier to learn than learning SQL from scratch, and it provides guardrails like schema enforcement that SQL does not. That means anyone, not just a junior, can drop into a codebase using it and be able to quickly use it
You seem to be under the impression that an ORM has to have a significant performance penalty. While there's of course going to be some, for the vast majority of tasks that I use it for, it is negligible versus the rest of the IO happening. If there is a significant penalty in some edge cases, I can still use direct SQL queries. Again, like my original post, I know when and where to use an ORM and as I've tried to explain to you multiple times, I know it's not a silver bullet solution.
In your previous comment you simply went off the rails talking about branching and CI without tying it into your argument. If your argument is that CI will catch the issues, CI can only catch some issues. It too isn't a silver bullet, and relies on code review to catch some other issues. Why not just use an ORM to add that security up front? It doesn't replace CI or code review, but it greatly reduces the risk of each stage failing.
> they should all be developed using the principles I’ve described
Okay, you do you. You've dissalowed any reply of situational pragmatism for your own fastidious absolutism.
Easy conclusion to make when every post is full of reasons why juniors can’t learn SQL ;)
> is easier to learn than learning SQL from scratch,
I very much doubt many juniors are learning SQL front scratch. They’d have learned some already at college. And if they don’t genuinely don’t have any experience with databases what do ever then you bloody well need to skill them up irrespective of whether they use an ORM. So again, your argument here has no teeth.
> In your previous comment you simply went off the rails talking about branching and CI without tying it into your argument.
I’ve explained how that ties in several times now. There’s a difference between you not understanding best practices and me not explaining them
> If your argument is that CI will catch the issues, CI can only catch some issues. It too isn't a silver bullet,
I agree
> and relies on code review to catch some other issues.
Not just code review, automated tests, vets and so forth too. But I also agree that code review is a key part.
> Why not just use an ORM to add that security up front?
Because ORMs aren’t a silver bullet either. Except you’re trading in due diligence with lazy abstractions that add runtime overhead.
> It doesn't replace CI or code review, but it greatly reduces the risk of each stage failing.
It doesn’t reduce the risk of it failing. It reduces the ability for code to be vetted. It adds overhead and runtime costs. It adds complexity and the only benefit is you can keep your juniors blissfully in the dark about how your database works. That strikes me as a stupid compromise.
> Okay, you do you. You've dissalowed any reply of situational pragmatism for your own fastidious absolutism.
I’m not being absolute. I’ve said POCs and hobbies can bypass these best practises. And I’m being pragmatic when I say that every organisation I’ve worked at, both as an engineer and as a leader, has benefited from these best practices. Hence why they’re called “best practices” rather than “pointless processes”. Every organisation I’ve worked at where I’ve seen teams bypassing these best practices have been dysfunctional. That might work for you but it results in shit code — which is likely why you’re dependent on ORMs to fix things for you. And I urge you to read more on this typic rather than just taking my word for it. :)
This is such a pointless argument anyway. You aren't bothering to read what I'm writing, you're twisting what I'm saying in what little you do read by filling in your own blanks, and you're insinuating that I won't use other best practices just because I won't agree with your subjective definition of best practices.
I have read many arguments about this, and again, I am experienced enough to know when to use it. The issue here is you have a completely condescending view of anyone who's not sharing your exact view. Your posts repeatedly insinuate that your experience outweighs my own, without actually providing anything other than YOU think ORMs are bad.
Not every college teaches SQL, not every junior comes from the same technical background, and you're assuming a lot more about someone else's workplace than you have any basis to.
You're also kind of a jerk by further insinuating that I put up with shit code. Perhaps I just don't put up with shit personalities who have a chip on their shoulders.
Then why invest so much effort trying to convince me that they shouldn’t learn SQL or that ORMs are better than learning how the underlying database manages their relational data? ;)
If I come across arrogant it’s because I’ve spent meany years managing fixing a great many failing dev teams and the comments you’re making resonate heavily with those who are usually against the changes I make. They always come round to agreeing that those changes are for the better after living with them for a few months.
But I guess we’ll never prove that on a message board so I’m happy to agree to disagree for the sake of etiquette.
The worst example of leaky abstraction.
Sometimes you can add indexes to fix things, but sometimes you really just need to do a simple query followed by a large number of single row queries unioned together, but that doesn't seem to be a feature of the ORMs I've been adjacent to.
Sqlalchemy is not that kind of orm. If anything is running slow in production I can find it from the logs and then track it to the code quickly - then you can do any sort of query you want in its place. For me it’s never the orm loading stuff that’s slow, it’s always the more complicated queries we’ve done for reporting or whatever.
Also use static table design when applicable and avoid running any query that locks the table up. If the query takes any longer than 500 ms you should be running it as a background job instead.
Even without that, show process_list or whatever it is tells you how long the query has been running, with mytop you can just look for anything that's been running for more than a couple screen refreshes and start yelling from there.
Usually, when I'm brought in, somebody else designed the tables, poorly, too. There's usually not a whole lot I can do about that, because I don't usually have enough authority to demand a data migration.
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.
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!
Mine supports simple joins and relationships but anything beyond that requires writing custom queries.
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/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/...
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.
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.