[fatbird]

I worked on the pokemon.com website for several years. It has over 2 million LoC in python (~2.5 once you add in frontend JS). It's a Django site that uses Django's ORM; I can't recall ever seeing any raw SQL. As you might imagine, the site has high traffic and high performance requirements. We never found the ORM layer to be either a hindrance or a performance barrier.

[taffer]

Before the invention of SQL, people wrote software using VSAM files and navigational databases with extremely limited computing and memory resources and it worked.

That isn't the point, though. Some people, myself included, find that processing the data in a declarative language directly in the database makes your code simpler and less prone to bugs.

[grim_io]

How? Stored procedures?

I find that to be insane.

How do you version that code, and how do you reason with the business logic split all over the DB and code?

[taffer]

> How? Stored procedures?

Not neccesarily. jOOQ[1] and sqlc[2] are great options if you don't like stored procedures, but for a small app or a prototype, just having plain SQL strings in your app is fine.

My point isn't that the code has to be stored in the database, but rather that the processing happens in one place where your data is stored and your middle tier just gets the results. Pure, stateless data. This means you don't have to synchronise shared mutable state between your app and your DB server, cutting out all the headaches of ORMs, such as having to specify your data model in two separate places, n+1 queries, locking, lifecycle management, dirty tracking, eager loading, caching, and optimistic concurrency control. All of this adds to complexity and congnitive load.

SQL also provides a declarative approach to defining your business logic. You define the what, not the how. In addition to greater productivity, the programming model is much simpler because you aren't complecting control flow with data flow. With JSON support in Postgres, your query results don't have to be flat tables either. You can get your data in the exact shape you need.

> How do you version that code

You put it into your VCS. SQL is part of your code base, you can and should version control it just like any Python, Ruby or Java code. When using stored procedures, I recommend putting them in a separate schema, so that the schema can be dropped and recreated in a single transaction during deployment. See [3] for an example of stored procedures under version control.

> how do you reason with the business logic split all over the DB and code

You separate your concerns instead of mixing them. The core business logic is in SQL, with your middle tier doing the plumbing, orchestration of external services and presentation.

[1] https://www.jooq.org/

[2] https://sqlc.dev/

[3] https://github.com/sivers/store

[grim_io]

I don’t get this at all, and I tried to understand it.

I'm good at SQL. When necessary (maybe once a year), I can drop into pure SQL instead of Django ORM like it's nothing.

The thing is, I can't imagine why I would ever want to.

All these anti ORM comments read like they want to be as close to the DB as possible.

This is not what I want. I don't care about guaranteeing the most efficient query, because it is never the bottleneck, not even close.

I want to be as expressive as I can be in the business logic, and I don't feel like I am using pure SQL.

[taffer]

Again, performance is not my point. If you want maximum performance, use a low-level key-value store, hence my VSAM analogy. On the contrary, for me it is about simplicity.

SQL is the most high level language in common use. It abstracts away everything: Storage, memory, concurrency, and, most importantly, control flow. Complexity comes from complecting things, simplicity comes from decomplecting [1] things. SQL decomplects the what (data flow) from the how (control flow) which means less cognitive load, higher developer productivity and better maintainability.

In my experience, writing business logic in SQL results in fewer bugs and less code. I have replaced 50-line Java methods with 15-line SQL projections multiple times. With Python, the ratio is closer to 2:1, but it's still impressive.

And all of this without having to consider type impedance, eager versus lazy loading, result set mappings, second-level caching, dirty tracking, lifecycle management, OCC, or obscure savepoint bugs. Performance is just a nice, but welcome side effect.

[1] https://www.youtube.com/watch?v=SxdOUGdseq4

[grim_io]

The results would be opposite for me.

I need dynamic query building in most places.

Doing those inline in SQL would result in a mess of unmaintainable manual string concatenation and parameter interpolation.

A code uglier does not exist.

[lukaseder]

jOOQ is also a great option if you do like stored procedures

[cies]

I cannot imagine a 2MLOC codebase with rdbms persistence that does not need custom SQL. I believe you, but I also think that's a special case.