Comment by danem
2 days ago
Anytime this topic comes up, this opinion is invariably at the top of the comments. However I've never seen a non-trivial application made this way. Mind sharing one? More than the query generation, I think people reach for ORMs for static typing, mapping, migrations, transactions, etc.
I'm not doubting that it can be done, I'm just curious to see how it's done.
I formerly worked for a travel company. It was the best codebase I've ever inherited, but even so there were select N+1's everywhere and page loads of 2+ seconds were common. I gradually migrated most of the customer-facing pages to use hand-written SQL and Dapper; getting most page loads below 0.5 seconds.
The resulting codebase was about 50kloc of C# and 10kloc of SQL, plus some cshtml and javascript of course. Sounds small, but it did a lot -- it contained a small CMS, a small CRM, a booking management system that paid commissions to travel agents and payments to tour operators in their local currencies, plus all sorts of other business logic that accumulates in 15+ years of operation. But because it was a monolith, it was simple and a pleasure to maintain.
That said, SQL is an objectively terrible language. It just so happens that it's typically the least of all the available evils.
Anytime this topic comes up, I ask: Why not both? I don't want to modify my SQL strings every time I change a column. Django ORM lets me combine custom SQL snippets with ORM code. I never hesitate to use custom SQL, but its just not a reasonable default for basic CRUD operations that my IDE can autocomplete. Not only that, but also provide nice feautures pike named arguments, walking relationships, sanitizations, etc. At the same time, I can do a UNIONS, CTES, anything I want. I just don't understand why it's worth arguing against ORMs, when no one is forcing you to stop using raw SQL.
I completely agree, it is absolutely essential to understand what SQL is emitted, and how SQL works. Perhaps the strawman argument against ORMs is that they preclude you from knowing SQL. They don't.
YouTube is one from my experience. The team there had a pretty strong anti-orm stance. DB performance was an existential necessity during the early scaling. The object fetching and writing tended to be focused through a small number of function calls with well scrutinized queries and write through memcaching.
The company I work for is one such example. We write inline SQL in a Python Flask+Celery app which processes >$3bn of salaries a month. The stated goal from the CTO, who was an early engineer, is simplicity.
I’ve started a few projects with SQL over the years, and it makes for a much slower development cycle. Instead of solving business problems, you’re spending too much time focusing on query optimization, and any iteration that touches the db requires all the mapping logic to be painstakingly reconstructed. For me writing queries in SQL is for later optimization. But then again, I’m pretty strict about abstracting even the ORM stuff away into a data access layer, so maybe people run into problems trying to thread ORM models all the way through to their frontend code or whatever crazy things people get up to.
In addition to the great replies folks are sharing, I've found LLMs are quite good at authoring non-trivial SQL. Have effectively been using these to implemnt + learn so much about Postgres
It's not the sql that is non trivial, it's the patterns where I need to mix and match filtering clauses etc which make running a layer on top of the sql necessary. Unless you're sort of patching together your own query building.
Many great SQL examples have long existed on stackoverflow and similar sources, but until the recent past were buried by lower quality questions and answers or SEO spam.
You will find that if you check sources they are lifted almost verbatim. LLMs are a way to cut through the noise, but they are rarely "authoring" anything here.
It's wild how far a little marketing can go to sell the same or an arguably worse product that used to be free and less unethical.
That's not how LLMs work. There are several sources covering the basics nowadays!
I work in logistics, warehouse management systems (in particular the one I've specialized in) have incredibly complex databases with a lot of business logic baked in. This is due to being very data-crentric applications.
Also, in many non-tech companies the database admins were historically a consistent IT resource even when no other developers were available, so SQL gets leveraged extensively. When your only tool is a hammer, most of your problems end up being weirdly nail shaped.
I worked for a publicly traded corporate elearning company that was written this way. Mainly sprocs with a light mapping framework. I agree this is better as long as you keep the sprocs for accessing data and not for implementing application logic.
ORMs are way more trouble than they’re worth because it’s almost easier to write the actual SQL and just map the resulting table result.
I've worked on a few, nothing I can share. I don't mind using an data mappers like Dapper in C# that will give you concrete types to work against with queries. Easy enough with data types for parameterized inputs as well.
My current company is built like this, and it’s great. I can’t think of a single production bug that’s come from it, which was my main concern with the approach. It’s really, really nice to be able to see the SQL directly rather than having to reason about some layer of indirection in addition to reasoning about the query you’re actually trying to build.
Cerner millennium is built entirely this way. There is a custom sql language called CCL that is used for everything interacting with the DB. All the applications just call CCL scripts.
I have written the entire backend of a fintech using nothing but postgresql, integration over http and webhook receival included (the last bit was with postgrest, but you get the point)
Every single time. Where are these developers? Orms are a god send 98% of the time. Sure, write some SQL from time to time, but the majority of the time just use the ORM.
We have a POS system where entire blogic is postgres functions.
There are many others as well. Sure Rails/Laravel/Django people use the ORM supplied by their framework, but many of us feel it's un-necessary and limiting.
Limiting because for example many of them don't support cte queries(rails only added it a couple of years ago). Plus it get weird when sometimes you have to use sql.raw because your ORM can't express what you want.
Also transactions are way faster when done in a SQL function than in code. I have also seen people do silly things like call startTransaction in code and the do a network request resulting in table lock for the duration of that call.
Some people complain that writing postgres functions make testing harder, but with pglite it's a non issue.
As an aside I have seen people in finance/healthcare rely on authorization provided by their db, and just give access to only particular tables/functions to a sql role owned by a specific team.
I worked at a company where we used Dapper with plain SQL. Like the sibling commenter said, simplicity. There were never [ORM] issues to debug and queries could easily be inspected.
> Sure, write some SQL from time to time, but the majority of the time just use the ORM
So add another layer that has to be maintained/debugged when you don't have to?
> Orms are a god send 98% of the time.
People who write percentages make shit up 98% of the time.
Or in other words: Source?
In context learning for ya: it’s not supposed to be a literal scientific claim.
> Orms are a god send 98% of the time.
Writing queries is trivial and in any marginally complex case I'll write something which beats the ORM for efficiency. I suppose they are a god send if you don't know SQL but you can learn SQL quite quickly.