At 50 Years Old, Is SQL Becoming a Niche Skill?
zwischenzugs.com
zwischenzugs.com
If you are smart enough to figure out GraphQL, you are smart enough to figure out SQL, and SQL comes without the security/expressiveness tradeoff[1] inherent in front-end data access technologies. You have hypermedia-oriented tools like hotwire, unpoly & htmx pushing data access back onto the server, and at the same time things like React Server Components doing the same.
I'm very bullish on SQL.
1 - https://intercoolerjs.org/2016/02/17/api-churn-vs-security.h...
Without SQL there would have been no way to determine the extent of the issue.
People forget that SQL is the only 4th gen language in common prod use.
Python, Java, C# are only 3rd gen.
[1]: https://htmx.org/
This is in contrast with traditional SPA libraries such as React, which typically access data via JSON APIs and construct a user interface on the client side. You can't make SQL generally available on the client side due to the obvious security issues, but projects like GraphQL have attempted to make the client side more flexible. Unfortunately I believe there is an inherent trade off with client-side data access that I outline here: https://intercoolerjs.org/2016/02/17/api-churn-vs-security.h...
So, as developers rediscover hypermedia & hypermedia oriented libraries, they will suddenly have access to SQL again.
And, as I mention above, new technologies like React Server Components, by putting evaluation on the server, also make SQL more easily accessible for developers.
With distributed systems you probably interface another web api of some type instead of an SQL-DB and often it is just annoying.
Some people pretend that you shouldn't use any SQL-server today and instead use current-hipster-web-accessible-database. But these often just simply don't deliver, are expensive, have performance problems, lacking compatibility and a whole other issues.
edit: Some cloud consultants increasingly say that nobody uses SQL anymore. Makes me chuckle a bit.
More commonly, there are the people who use a SQL server at the lowest level, but then stick another database layer on top in order to provide a more friendly querying experience for the bulk of the users. The prevailing idea is absolutely that most people shouldn't have to use SQL, and most likely that they should use a hipster-web-accessible database, even if SQL exists somewhere inside of a black box.
Niche does not imply that something is nonexistent, but it does seem pretty clear that we go to great lengths to ensure that most people don't have to use SQL, limiting its use to a narrow set of developers who specialize in that niche, while everyone else uses higher level languages to communicate with the database.
Which is not at all what SQL envisioned in its infancy. It was supposed to be the language for all business people far and wide.
All that collective effort to avoid learning one language and thinking about your data structures first is staggering.
Every single job I've had in the software space in the last 6 years (and I've had a bunch) has made pretty extensive use of SQL. Obviously this is a sample size of one, but me being good with SQL wasn't especially impressive to any of my coworkers at any of my jobs.
When I learned SQL at that job, and when I learned you can squeeze a lot of computation out of SQL, it was huge for me. I started doing as much pre-processing with SQL as I could, just because it felt like it was giving me a lot of free performance upgrades.
My view is that the schema + queries is the essential performance consideration. The code that goes from one persisted state to another is largely plumbing implementation detail. Of course there also lots of precise logic there but it's a healthy alternate view. The first thing I do on any project I join is make a big ERD to know the names, cardinalities, and relations (and wonder how they keep changing that without an up-to-date picture).
A proactive solution of hiring a DBA to build things along with the FE/BE developers will likely never get traction due to costs. Businesses regularly put off performance and optimization until later and they still make good money in spite of that.
And then the table structure, relations, indexes, and keys all get changed because it wasn't carefully designed the first time. Then changes propagate into a bunch of queries and code and it's a much bigger deal than doing it better the first time ;-)
https://www.itprotoday.com/sql-server/sql-server-database-en.... Explains role of query plan (can pull either from actual file or from an index(s)) and write-ahead-log (Microsoft gave it the unfortunate name of Transaction Log) for hardening.
https://learn.microsoft.com/en-us/sql/relational-databases/p.... Explains how to use the Query Store to identify poor performing queries.
https://learn.microsoft.com/en-us/sql/relational-databases/s.... Explains that SQL Server handles the problem of two concurrent users attempting to modify the same record at the same time via DEADLOCKS. You want to use Extended Events to troubleshoot deadlocks.
https://learn.microsoft.com/en-us/azure/architecture/pattern.... Explains how CQRS and Event Sourcing architectures can help minimize deadlocks.
https://www.business-case-analysis.com/accounting-cycle.html. I would argue the best architecture to minimize deadlocks is to mimic what accounting systems do. Multiple Clients submit order requests to a batch system that posts/processes them sequentially. Until the central batch system posts, the transaction is pending. Similar to how Amazon accepts your order request instantaneously; but it may take several minutes for your order to be confirmed.
https://sqlperformance.com/2020/09/locking/upsert-anti-patte...
https://learn.microsoft.com/en-us/sql/relational-databases/p...
CREATE TABLE permission_overrides (
-- (...)
granted_by_user_id BIGINT NOT NULL REFERENCES users (id),
granted_to_user_id BIGINT NOT NULL REFERENCES users (id),
-- Ensure both users belong to the same tenant.
CHECK (DEREFERENCE(granted_by_user_id).tenant_id = DEREFERENCE(granted_to_user_id).tenant_id)
);
I understand there are performance implications[1] with such a thing, but being able to prevent invalid data from even existing in the database is one of the main reasons I use a database instead of some dumb store, because I don't want to have weird data the moment the application has a major change (or worse, a rewrite).
[1]: That specific example looks like it would be slow as hell unless that table is mostly append-only. (EDIT: And also that the referenced rows don't change too much.)
CREATE TABLE permission_overrides (
-- (...)
granted_in_tenant_id BIGINT NOT NULL,
granted_by_user_id BIGINT NOT NULL,
granted_to_user_id BIGINT NOT NULL,
FOREIGN KEY (granted_by_user_id, granted_in_tenant_id) REFERENCES users (id, tenant_id),
FOREIGN KEY (granted_to_user_id, granted_in_tenant_id) REFERENCES users (id, tenant_id)
); SELECT AVG(price) FROM cars
WHERE brand='BMW'
GROUP BY year
ORDER BY year
So far, every alternative syntax I have encountered was less human readable and/or less concise.
I think SQL will never be replaced because it is not really something that was invented but rather the simple consequence of wanting to store and retreive data.
You could replace "SELECT" with "GET" or "FETCH". But in the end you need to define what action you want to perform. You could replace "WHERE" with "FILTER" or "CONDITION". But in the end you need to define what you want to retreive. Etc. So by just stating the neccessary information, you always end up with SQL. And every real alternative has the problem of being less concise, less easy to read and less easy to write.
range of c is cars
retrieve (avg(c.price) where c.brand = 'BMW' by c.year)I suppose if your example is using a NoSQL (meaning non-relational) database, that is ironically queried using SQL, then you might need something else. But my understanding is that we're back on the relational kick these days.
And writing the query in SQL is probably also faster than trying to write down the prompt.
Sometimes it can be used to optimize your queries, but it isn't its strength either.
return Cars
.query()
.select(avg('price'))
.where( { brand: "BMW" })
.groupBy('year')
.orderBy('year')On our projects, which are moderately small, I genuinely don't know if it is/was worth the hassle of setting up, vs. writing some repeated code. (We also have a pretty decent testing culture in place, so I would be much more accepting of DRY-violations.)
bmws = Cars.query().where( { brand: "BMW" }).groupBy('year').orderBy('year')
bmwAvg = bmws.select(avg('price'))
bmwMin = bmws.select(min('price'))
bmwMax = bmws.select(max('price'))The trade-offs are usually worth it, vs, doing something like:
const row = db.query('select whatever from wherever');
const obj = {
id: row.id,
brand: row.brand,
... etc
};Some issues come to mind:
Grouping on the client would mean that you have to send the ungrouped data over the wire/air. Which might be orders of magnitude more data than the client needs. What if "cars" is a table of car photos made over the last 15 years and there are 20 million photos of BMWs in there? You send all the 20 million rows to the client to crunch it down to 15 rows?
The ungrouped data might be something the client is not allowed to see.
Depending on the type of grouping, the client would have to reimplement fast and efficient algorithms that have been tested and optimized for decades in RDBMs. Good luck, matching that by writing some JS for the browser.
from cars
filter brand == "BMW"
group year (
aggregate [
average_price = average price
]
)
sort year
select average_price
SQL as a technology did an admirable job of allowing practitioners to express relational queries, but let's be real - the syntax of SQL, especially for advanced queries, was always pretty awful. I think that syntax, more than the underlying DB technologies, was the number one factor driving people towards NoSQL unnecessarily.
What would be amazing is if the RDBMS community could create an industry standard querying language that supported relational algebra but actually had a pleasant development experience.
There is however a weakness of SQL is that it's purely declarative and it's difficult to make sure it does the right thing. I often find myself in front of queries that are badly optimized and I'd welcome a language that would be less declarative where you could tell the database engine which index you want to use and how explicitly. The optimizer has table metrics but with the domain specific knowledge the programmer has generally better insights.
Even if you don't use it all that often, I think that every developer should get some exposure to Functional, Logic and Declarative languages - and SQL is a decent way to learn more about the "declarative way of thinking", even if it isn't used in a fully declarative fashion in many practical situations.
I started at a new place that uses DynamoDB and I can’t articulate how much I loathe it. Currently rewriting it into Postgres as we speak.
For anyone in this situation, maybe try tricking the NoSQL advocate like I did once:
Postgres's json column is just like Mongo, except you also so get the same and more because it's a relational database on top of that. We can do some stuff unstructured, then the rest structured.
I'm pretty sure that's not strictly true - I don't know a lot about Mongo, but neither did the person advocating for it on my team. It was enough though and the vast majority of the data ended up in normal columns instead of the json columns, with zero pushback.
In those early days, ORMs were really bad and didn't feel very ergonomic to use.
But the tooling has come such a long way. Today, ORMs are generally pretty competent for 80-90% of the use cases I would find in a typical business app. Microsoft's Entity Framework always seems to surprise me when I find that I can do fairly complicated queries and have it generate almost perfect SQL. It's migrations are pretty good to the extent that it's rare to have to manually amend its migrations. Even though I think I'm quite good at hand rolling SQL, EF Core just makes it far more productive to work at the application layer instead.
I think the biggest hurdle to SQL today is that the DBA is a dying breed and SQL hasn't really had a "revolution" to meet the (sometimes perceived) needs of younger teams. For example, scaling relational database still requires more forethought and each approach comes with some limitations and gotchas. On the other hand, the rise of NoSQL databases and JavaScript in the application stack means that document-oriented databases like Firestore and CosmosDB are perhaps easier for teams to adopt, even with the limitations, because infrastructure concerns (scaling, replication, etc.) disappear.
We see a lot of complexity moving into the application and the rdbms used mainly as a dumb table storage. The whole idea of normalised models is out the window, which kinda makes sense because that's a carryover from the 70s when storage costs were high and now they are negligible and development costs/time are instead the barrier. Really nobody gives a crap about their database size :P
I do see some stuff like stored procedures for security but the whole complex 10-table hyper optimised join, the kind you tweaked for a week so it could finally run at an acceptable speed and you'd feel like a magician, that stuff seems to be over.
These days if you need some kind of summary you just add it to the business logic and keep a separate table updated. Of course you have total duplicate information and a risk of mismatches but at agile breakneck speeds and basically zero storage costs this is not an issue these days.
In practice though, SQL just seems like a lower-barrier to entry lingua franca than C (for its use)
Unless it’s absolutely essential to your performance -like GIS or data-driven stuff (at which point you might be looking beyond SQL) - I don’t really see it as a highly specialised niche skill
I think most people see it (perhaps rightfully) as a commonly understood but seldom directly used paradigm in isolation
Picking up an ORM is comparatively child’s play but not understanding the basic concepts of SQL is an uphill battle for even the simplest CRUD applications, even if SQL isn’t used
Lingua francas such as C/SQL/HTTP in computing are perhaps even more important as paradigms of thought rather than tools to be used
Especially as we seem to year on year move higher up the tech stack for the foundations to greenfield projects
Good SQL is becoming more specialized for dev. No longer are the DBAs tutoring the devs to avoid inefficiencies. Now, the DEs handle really complex pipelining, and get really good.
But SQL isn't just used to "build" - it's also used to "access." And that's less specialized. The PM, BA, etc can all use ChatGPT/Stack Overflow and get things done quickly. And it's a virtuous cycle: the more people access data, the more the DEs are asked to clean things up, the easier it gets to query, and the more people try/succeed.
So SQL-for-development is increasingly niche, while SQL-for-access is increasingly common.
But at the same time, a fairly basic SQL query I could bang out in two minutes becomes an exercise in acrobatics & research in how to do it right in this version of this particular framework.
Nobody cares about SQL until it takes 15 seconds to load your user facing login dashboard.
“When you’re fed up of keeping up just retire in to SQL, it’s the best pension there is”
but in all seriousness, it depends. adding an index. removing/consolidating indexes. breaking the queries down into individual UoW and forcing intermediate materialization. identifying platform-specific optimization barriers. rearranging sufficiently complex query semantics to force behavior you expect.
99% of the cases i’ve personally had to resolve over the last 15 years have been the result of sql hero queries that try to do everything all at once. this is exacerbated by orms that generated bad sql but was acceptable at low cardinalities. under scaled-up concurrency and data volumes they can’t deliver the necessary performance anymore.
Since MongoDB is so cheap, quick, and easy to get going my team tends to make that the default option. In many cases we've been able to run for years on MongoDB. In other cases, the needs changed such that we need updated DB capabilities and so we migrated that data from MongoDB into PostgreSQL.
That strategy has been working out really well for us.
As far as SQL itself becoming a niche skill, optimized SQL has always been a niche skill, and many data scientists don't have this skill. It seems many applications have that one "killer query" that simply takes too long to execute. A DBA will be able to optimize that query for you. They may have to create new indices and who-knows-what to optimize the query and almost always they use engine-specific SQL to optimize the query. You need people on-hand who can optimize SQL queries for whatever RDBMS you're using.
Skill issues are not the responsibility of SQL itself.
Also you even mention yourself that MongoDB is not sufficient so to me at least, it sounds like you shoehorn in a PostgreSQL when in reality, using the right tool from the start would have avoided all that work.
At the end of the day its a very intuitive query language. I'm yet to see one that surpasses it. Nor am I convinced there is a need to change it.
I am currently in the process of reducing out AWS service coverage. I'm not sure if I've phrased that right, but reducing the amount of scalable services that they suggest and offer and that were promoted to the company I work for.
It's a numbers game, if you are serving a lot of clients, or processing a lot of things, AWS is fine, next question is, what is a lot?
All things are relative to workloads of course, but lets say you're serving 1 million requests a day, not concurrent, but overall, if you have an API gateway tied to aws lambda or step functions etc. Okay, I can understand you might want to scale up some of the 'workload' services.
Out of those 1 million requests, it's worth a companies time to take the step to evaluate, "Hey we have a contract with AWS, maybe a fixed price contract, but do we need it ?"
Fast forward after you realize maybe 10%~ lets say can be left as 'scalable' because they're CPU intesive or they could be consolidated to be a single 'microservice'. Great, now you have your scalable workload, the rest can be thrown on a single server..
> Since our architecture mandates that clients access data via APIs then the clients aren't affected by the change.
Architecture by definition should be forward stable. It shouldn't mandate anything, it should just facilitate the requirement. How you implement it isn't an architects concern (in theory).
AWS kind of makes a lot of tech debt here that is hard to justify to the people above. Because they've already paid. So now as a software engineer, I found a ~40% saving possible, but it doesn't sound right.
I'm honestly getting to discouraged with things. The MAX client list we will ever look at I was told was max 500 people. Maybe 200 concurrent.
Generally-speaking, API based systems and interactions between components in the system, are very scalable. AWS facilitates the creation of such a system. Utilizing their serverless components essentially forces you to utilize best practices when building your system - which can be a benefit to teams, especially readl-world teams struggling with a bit of dysfunction.
no.
"It's 2024. Can We Just Forget About Betteridge's Law Already?"
No, it is not. SQL is a way of thinking about your data, and querying against those assumptions and mistakes. If you think SQL is just a dumb row storage engine that you write garbage SQL for (ex: every single MySQL use I've ever seen), then you failed SQL 101.
A good proper use of a real SQL database (I'm obviously a huge pg and sqlite fan, but no hate towards DBA wizards that fight Oracle, DB2, and MSSQL every day at work) can shepherd your data, maintain its consistency, and conquer some of the weirdest footgun anti-patterns that keep showing up in business code that use 'modern' nosql databases.
To be absolutely fair, all technologies are a set of tradeoffs, and you have to understand them; if you don't, it will be fatal. Not today, probably not tomorrow, but in 3 years when your startup runway ran away. As technology advances, the meaning of those tradeoffs change; how I would answer some questions for people 10-15 years ago isn't how I'd answer them today.
Scalable DBs that make other tradeoffs are slowly being eaten by the march of technology. When someone picked up a mishmash of parts that don't fit, a nosql row store here, a nosql columnar store there, a dedicated event logger somewhere, all glued together with some message passing bus: its all people who don't understand the tradeoffs because they never learned the thought process behind how to use SQL effectively.
If you're asking "is SQL becoming a niche skill", you're really asking, "is thinking a niche skill?". Half of HN seems to absolutely adore the fake AI scams and willingly threw themselves down that flight of stairs, so I'm worried some people here really do think the answer to "thinking is a niche skill" is yes, and a dying one at that.
No data warehouse?
From a language/interface standpoint, there is no alternative.
Every developer I’ve worked with who touches server-side code has eventually said some version of ”Bah damn these ORMs can we just write SQL?”.
And now with local-first there’s a growing trend of putting SQL on the client as well. Working directly with an in-memory (ish) SQLite and syncing to the server occasionally is super nice it turns out.
2c as DBA & researcher: advanced SQL is becoming irrelevant because LLMs are better at writing complex SQL than 99% of the available experts, who routinely botch NULL handling, datatype and index selection, performance optimization, etc. I see no reason why anybody should memorize the arcane syntax of SQL features like sliding window functions, exception handling in stored procedures, etc.
If this seems radical, consider that experts rely on query optimizers and EXPLAIN and people rely on the planner/optimizer for 99+% of queries. That was a radical position in the early 1980s.
And then there is Golang. SQLC ( https://sqlc.dev ) becomes a source of truth not a sink... mix in some yaml and you have your json tags and validation mixed in.
Candidly good engineers are still using SQL...