The Rise of SQL:the second programming language everyone needs to know (opens in new tab)

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.

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

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

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.

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

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.

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

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.

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.

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.

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.

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.

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.

Hmm, I sort of learned ad-hoc. Joe Celko's books were good back in the day. I never read something a lot later that was an "aha" for me. I think I was a little resistant to "NoSQL" databases for a while but eventually they made sense to me. I can't think of a single resource or turning point. There are probably some very good books out there now. The key thing is not /everything/ has to be SQL. And SQL databases like Postgres and SQLite can be used for a lot more than SQL now. Also, don’t be afraid to just throw protos/JSON/whatever into a database with no or mininal schema to get going. But manage data design debt ruthlessly, it can haunt you.

My biggest learnings:

Don't prematurely normalize data, but if it is obvious it can always stay normalized, normalize it. Read the normal forms. Learn about indexing and how data is actually being stored on disk. Just knowing about indexes is a huge advantage even today. Understand and know when to use different styles of data storage: row oriented, column oriented, key value, bigtable style (2d key value), document (rare). Pick good systems. Spend more time than you think you should designing your data. The system is often easy if the data is right. Learn ACID and CAP theorem. Learn when and where you can trade on fundamental database principles in your data model for performance or ease of development. Honestly, a lot of this stuff senior engineers at big tech are just expected to know these days, but it still isn't really obvious and not everyone has big tech problems. Still if you know how to solve the problems at scale and you can get out of your own way it is much easier to write smaller systems (most problems people have).

So in terms of resources, go learn about each of those concepts. Read papers. Ask an LLM about them. Play with databases and storage systems. Maybe try to write your own simple database. Go read about how people design massively scaled distributed systems and what systems they use to manage data. Just like with programming languages, be flexible and open minded. Read about how distributed systems work (CAP theorem). Almost all data systems make tradeoffs in that realm to meet cost/performance/implementation goals.

table of contents of the manual of the RDBMS your project is using is a good start and this is not a joke. most senior engineers (by job title, anyway) haven't gone that far.

From-before-select has nothing to do with composition as far as I can think of? That’s to solve the auto-complete issue — put the table first so the column list can be filtered.

Things like allowing repeat clauses, compute select before where, etc are what solve for composition issues

Is that really how it should be judged? If I need to get data from state A to state B, SQL is the most elegant and efficient way to do it. If I need to modify the states, or break them up to optimize for memory, SQL is the easiest way to do it. It’s been around for over 50 years and no one’s come up with anything better.

And some very simple computations become much more cumbersome in SQL, e.g. Panda's df.diff() or df.cumsum() is much more awkward using window "functions".

Agreed with that. As with writing SQL by hand you have to be very specific with instructing an LLM. There are many ways to get to a solution in SQL all present different tradeoffs and corner cases. I found that people that don't understand SQL and the basic of a given schema produce garbage both by hand and with LLMs

I wouldn't think so, knowing SQL has been a benefit, and knowing how to use LLMs to write SQL even more so.

Microservices are older than those, hence why Sun had the motto "The network is the computer". :)

By the time you are referring to, we were already on the classical 3 tier architecture, the

There are indeed Web frameworks for RDMS, that allow to expose the database as microservices, like Oracle's APEX, which grew out of Oracle's Visual Basic version, which used PL/SQL instead of BASIC.

How do you present the interrelations between the tables when you're dealing with complex table structures?

sonnet 4.5 was really bad at anything more than simple queries. even GPT 5 was not great. gemini was consistently good even at 2.5; caught multiple bugs in outputs of either. I haven't tested Opus 4.5 properly at SQL yet, but I've got a feeling Anthropic doesn't prioritize it in training and google does.

Agree, but for example, migration scripts are still often just a bunch of long .sql files (unless it's Liquibase with its own cross-DBMS XML syntax), or test/staging/benchmark schemas. Even today.

And subling commenters say that all you need is raw SQL and results mapping to your code. Which I did for a while, but found that mapping is a lot of copy-pasta with minor diffs, a burden to maintain. So it's easier to use a thin library like JOOQ for mapping, or use only the mapper part of a bigger ORM framework like Django/Hibernate.

And my argument is that it's easier to map to/from a concise strongly-typed ABI/API structs instead of one raw SQL string with its structure designed for human reading/writing, like SELECT before FROM. There are such ABI-s, but they are DBMS-specific, while SQL is less so.

Only for simple queries. E.g. it's hard to parameterize table names.

Also it makes an extra round-trip to server to prepare the query.

It should have arrays, and loops and conditionals.

This is completely wrong. The SQL spec isn't Turning complete but multiple DBs provide Turing complete extensions (like pgplsql) that make it so. Also, even without the extensions, it is still very much a programming language by any definition of the term. Like most takes on SQL, it is more about your understanding (or lack thereof) of SQL.

It can do loops and recursion. It can use as much memory as it is allowed. It can do general programming via functions and stored procedures.

I was not aware that IBM copied Ada.

I was aware that EnterpriseDB developed "deep Oracle compatibility" and sold the resulting code to IBM for Db2 several years ago.

I think you are [more than] a bit behind the times?

https://www.cnet.com/culture/ibm-puts-oracle-to-the-sword-wi...