Comment by petcat | Hacker News Reader

Comment by petcat | Hacker News Reader

I've loved and used Django ORM and SQLAlchemy for many years. It got me a long way in my career. But at this point I've sworn-off using query-builders and ORMs. I just write real, hand-crafted SQL now. These "any db" abstractions just make for the worst query patterns. They're easy and map nicely to your application language, but they're really terrible unless you want to put in the effort to meta-program SQL using whatever constructs the builder library offers you. CTEs? Windows? Correlated subqueries? It's a lot. And they're always lazy, so you never really know when the N+1s are going to happen.

Just write SQL. I figured this out when I realized that my application was written in Rust, but really it was a Postgres application. I use PG-specific features extensively. My data, and database, are the core of everything that my application does, or will ever do. Why am I caring about some convenient abstractions to make it easier to work with in Rust, or Python, or whatever?

Nah. Just write the good SQL for your database.

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.

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.

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.

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.

> 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.

> 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.

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.

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.

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.

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

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!

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.

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.

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.

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.

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.

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 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.

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)

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 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 love SQL and use it all day long to answer various business questions, but I would never use raw SQL in my code unless there is a good reason for it (sometimes there is). ORMs are there for maintainability, composability, type safety, migrations, etc.. trying to do all that with raw SQL strings doesn't scale in a large code base. You need something that IDE tools can understand and allow things like 'find all references', 'rename instances', compile time type checks, etc.. Raw SQL strings can't get you that. And managing thousands of raw SQL strings in a code base is not sustainable.

ORMs are one of those things that a lot of people think is a replacement for knowing SQL. Or that ORMs are used as a crutch. That has nothing to do with it. Very similar to how people here talked about TypeScript 10 years ago in a very dismissive way. Not really understanding its purpose. Most people haven't used something like Entity Framework either which is game changing level ORM. Massive productivity boost, and LINQ rivals SQL itself in that you can write very small yet powerful queries equivalent to much more complex and powerful SQL.

Sounds like your issue is writing the complex SQL all as strings in your codebase instead of as functions in the database.

No issue at all. There is a place for stored procs and functions in cases where you need to do things an ORM is not capable of. It is an exception, not a rule. Managing procs/functions is overhead and has the same if not more maintenance headaches than raw SQL strings in code.

SQL is such a joy to work with compared to all the baggage ORMs bring. I’m not against ORMs but I like to keep them as thin as possible (mostly to map columns to data objects). I’ve been happily using JDBC and Spring Data JDBC (when I needed to use Repository pattern) for a long time in Java.

ORMs come with a lot of baggage that I prefer to avoid, but it probably depends on the domain. Take an e-commerce store with faceted search. You're pretty much going to write your own query builder if you don't use one off the shelf, seems like.

I once boasted about avoiding ORM until an experienced developer helped me to see that 100% hand‑rolled SQL and customer query builders is just you writing your own ORM by hand.

Since then I've embraced ORMs for CRUD. I still double-check its output, and I'm not afraid to bypass it when needed.

Exactly, and any good ORM will let you drop down to pure SQL if you need to for the weird cases.

Not really. ORMs have defining characteristics that hand-rolled SQL with mapping code does not. Eg, something like `Users.all.where(age > 45)` create queries from classes and method calls, while hand-rolled SQL queries are...well..hand-written.

Every Oracle rep I've ever met said every app should be a SQL app.

I'm curious if you have tried SeaORM? I've used it a little bit (not too extensively) and really like it. It's like sqlalchemy in that you can declare your tables and have a type checked query builder, which is a big win IMO. It's nice to add/change a field and have the compiler tell you everywhere you need to fix things.

I've definitely had issues when using sqlalchemy where some REST API type returns an ORM object that ends up performing many queries to pull in a bunch of unnecessary data. I think this is harder to do accidentally with SeaORM because the Rust type system makes hiding queries and connections harder.

Most of my usage of SeaORM has been as a type query builder, which is really what I want from an ORM. I don't want to have to deal with lining my "?" or "$1" binds or manually manipulate strings to build a query. IMO a good query builder moves the experience closer to writing actual SQL, without a query builder I find myself writing "scripts" to write SQL.

I've been using django & duckdb together, which keeps me from using the ORM. Was this a happy accident for me? For background, I have a scientist background; I don't have as much experience w/ software and designing database apps.

The cargo-cult shibboleth of "never put business logic in your database" certainly didn't help, since a lot of developers just turned that into "never use stored procedures or views, your database is a dumb store with indexes."

There's value in not having to hunt in several places for business logic, having it all in one language, etc. I was ambivalent on the topic until I encountered an 12 page query that contained a naive implementation of the knapsack problem. As with most things dogma comes with a whole host of issues, but in this case I think it's largely benign and likely did more good than harm.

> hunt in several places for business logic

But that is the result of having multiple applications needing to enforce valid states in the database.

"Business logic" is a loose term. The database is the effective store for state so it must enforce states, eg by views, triggers, and procedures.

Other "business logic" can happen outside of the db in different languages. When individual apps need to enforce valid states, then complexity, code, etc grows exponentially.

Other than a few ill-advised attempts to implement microservices infrastructure by well-intentioned co-workers I've not encountered situations where multiple applications needed to access a single data store. While I'm sure there are valid use cases there I suspect they're rare and should be treated like the outliers they are.

Did that 12 page query have any automated tests?

I doubt it was even under version control...

It was absolutely under version control and there was a full test suite. The guy that wrote it is easily in the top 3 smartest human beings I've ever met and an incredibly talented developer. Unfortunately a lot of his stuff required being at the same level on the IQ bell curve, which meant it was functionally unmaintainable by anyone else. If you're familiar with the Story of Mel, it was kinda like that.

genuinely curious, can you steel man stored procedures? views make intuitive sense to me, but stored procedures, much like meta-programming, needs to be sparingly used IMO.

At my new company, the use of stored procedures unchecked has really hurt part of the companies ability to build new features so I'm surprised to see what seems like sound advice, "don't use stored procedures", called out as a cargo cult.

It’s about what you want to tie to which system. Let’s say you keep some data in memory in your backend, would you forbid engineers from putting code there too, and force it a layer out to the front end - or make up a new layer in between the front end and this backend just because some blogs tell you to?

If not, why would you then avoid putting code alongside your data at the database layer?

There are definitely valid reasons to not do it for some cases, but as a blanket statement it feels odd.

Stored procedures can do things like smooth over transitions by having a query not actually know or care about an underlying structure. They can cut down on duplication or round trips to the database. They can also be a nightmare like most cases where logic lives in the wrong place.

My hunch is that the problems with stored procedures actually come down to version control, change management and automated tests.

If you don't have a good way to keep stored procedures in version control, test them and have them applied consistently across different environments (dev, staging, production) you quickly find yourself in a situation where only the high priests of the database know how anything works, and making changes is painful.

Once you have that stuff in git, with the ability to run automated tests and robust scripting to apply changes to all of your environments (I still think Django's migration system is the gold standard for this, though I've not seen that specifically used with stored procedures myself) their drawbacks are a lot less notable.

> My hunch is that the problems with stored procedures actually come down to > version control

Git? (and migrations)

> change management

Again. Just like any other code.

> and automated tests.

Just write an automated test like you write any other kind of test?

It's also about separately scaling your business logic from the data layer

That's exactly what I'm saying. If you do those things stored procedures stop sucking.

You give no reasons why you think it's a sound advice.

My experience is following

1) Tx are faster when they are executed a sql function since you cut down on network roundtrip between statements. Also prevents users from doing fancy shenanigans with network after calling startTransaction.

2) It keeps your business logic separated from your other code that does caching/authorization/etc.

3) Some people say it's hard to test sql functions, but since pglite it's a non issue IMO.

4) Logging is a little worse, but `raise notice` is your friend.

> At my new company, the use of stored procedures unchecked has really hurt part of the companies ability to build new features

Isn't it just because most engineers aren't as well versed in SQL as they are in other programming languages.

Stored procedures are great for bulk data processing. SQL natively operates on sets, so pretty silly to pass a dataset over the wire for processing it iteratively in a less efficient language, and then transfer the resultset back to the database.

Like any tool, you just have to understand when to use it and when not to.

A lot of people probably think it's better to keep database "easy to swap". Which is silly, its MUCH easier to change your application layer, than database.

Indeed, Dapper, myBatis, jOOQ,...

Dapper is an unmitigated joy for me. i get to write the best sql needed for the case and then let the micro-orm handle the rest.

Dapper is fantastic, and I'm happy to see it getting some love. It does exactly what I want: provides strongly-typed mapping and protects against SQL injection. It makes it easy to create domain-specific repositories without leaking anything.

In contrast, every company I've joined that used Entity Framework had enterprise products that ended up being a tightly coupled mess from IQueryable<T> being passed around like the world's favourite shotgun.

In my case, it has been either applications where Dapper is possible, or very high abstractions in CMS like Sitecore and Optimizely, where they come with their own approach which then again, needs a whole indexing engine.

> Nah. Just write the good SQL for your database.

You may not need to use an ORM, but hand writing SQL, especially CRUD, should be a terminable offense. You _cannot_ write it better than a process that generates it.

I agree and have taken the same path.

Reply on news.ycombinator.com