>SQL is an old language and not designed for large scale programming, it is not a language that developers will love. Even worse, they will not be very productive with it.
What the hell. I stopped reading at this point.
What the hell. I stopped reading at this point.
SQL is basically a DSL for data processing just like XSLT for example. Would you call XSLT "designed for large scale programming"?
The "they will not be very productive with it" comment is a bit of a stretch - it can be very productive in the domain it is designed for - but you can often replace SQL at its job (like for example use Dataframe API in Spark instead of SQL API) while there are many tasks that SQL is a very poor fit for.
It's true people try to replace it, but most solutions are of rather questionable quality. I personally argue against ORMs in pretty much any situation.
> SQL is basically a DSL for data processing
Yeah and what's wrong with that? SQL databases power everything, including data sources with billions of records that serve millions of customers, with hundreds of developers/DBAs working on them. Does that not count as programming in the large?
I'm compelled to agree with this under protest. There was a language called Dataphor based on D and the Third Manifesto, it's unfortunately hard to find even sample code any longer, but it would be a strict improvement on SQL.
Did I mention you can't even find a corpus? Good luck running any implementation on a modern system.
I'm reasonably content writing SQL, but I know a syntax with the same power but lacking several disadvantages is possible, and I'd rather use a mature implementation of that instead, if I were able.
The reason why I'm skeptical of most proposed alternatives is that I'm fundamentally in your exact same position: I know SQL isn't perfect but I'm reasonably content with it; and they invariably all end up throwing away the "good parts" of SQL (relational model, declarative, easy stuff is easy).
The first step of an hypothetical solution that replaces SQL isn't "SQL sucks", it's "SQL is extremely good at what it does but has problems that are only fixable with a new language".
There's other "D" type projects. Rel is one. Don't have URL handy but it's fairly easy to find. They publish their grammar and example code.
None of these tools have ever matured or become popular. I have theories why, we could discuss forever.
I think the issue has more to do with two things: a) most people don't understand the relational model fully, so they have no idea what they're missing b) new databases (and existing) simply cannot afford to rock the boat here because they need customers. And in terms of the architecture of the DB system, the SQL parser is one of the lower effort items (when compared to query planner, optimization, storage impl and storage optimization, replication, etc.) So why invest there for little value? Customers aren't asking for it.
The company I'm contracting for right now has some interest in working in this space.
Date & Darwin did good work with "The Third Manifesto" but it went almost completely ignored, and the tone and target of it may have been off. Frustratingly we went through a phase where SQL went out of style and then back into style ("NewSQL") and so there may have been a window missed there where alternative query languages (but still based on the relational model) could have risen. But instead "NoSQL" was too interested in jettisoning the relational model along with SQL (mostly I would argue because they don't understand it).
There has been some recent rise in Datalog implementations in the Clojure community. That is interesting, though not strictly as an alternative to SQL.
I mostly agree, though I think that SQL's popularity comes mostly from network effects.
> Yeah and what's wrong with that?
Nothing is wrong wrong with that. I just think that it's reasonable to interpret the words "programming in the large" as "being usable as general purpose programming language" (which I admit is not a very strict definition) whereas SQL (or XSLT or awk or bash...) seem well suited for certain niches (important niches nonetheless).
On the DML side I've found SQL to be very reusable at any company I've worked for - grep/search repos for the tables of interest, throw the queries in a CTE and you're off to the races. If the data infrastructure is robust you can probably just query from ([un]materialized) views - quite literally SQL code reuse. And even across vastly different domains, even if not directly resuable, SQL queries are still highly transferable. I can see it being more true on the DDL side but even there, at least anecdotally most of the DBAs/devops/infrastructure engineers etc I've met seemed to have favorable impressions of SQL when needed in between the tooling.
>while there are many tasks that SQL is a very poor fit for.
You probably don't want to try and perform graphical rendering tasks, and it's true certain use cases like dynamically generating SQL statements from table/column names _and then executing_ them requires some manual input. But which data/processing related tasks is SQL a very poor fit for?
In my experience it's much more common to actually see the opposite - inefficient usage of ORMs, exporting datasets only to then perform pre/postprocessing, often requiring building programming "jigs" to circumvent bottlenecks, etc when it could've been done in a more streamlined manner in the DB/warehouse.
> And even across vastly different domains, even if not directly reusable, SQL queries are still highly transferable.
That's the point, they are probably transferable in the "copy, paste and tweak" sense which would be highly frowned upon in other popular languages.
> But which data/processing related tasks is SQL a very poor fit for?
I specifically called SQL "a DSL for data processing", I probably should have written "there are many domains that SQL is a very poor fit for" to be more clear.
>In my experience it's much more common to actually see the opposite - inefficient usage of ORMs, exporting datasets only to then perform pre/postprocessing, often requiring building programming "jigs" to circumvent bottlenecks, etc when it could've been done in a more streamlined manner in the DB/warehouse.
Totally agree. Lukas Eder has some nice presentations about it.
>Totally agree. Lukas Eder has some nice presentations about it.
Thanks - I googled him and instantly recognized the JOOQ blog, quality stuff.
Thanks for the shout out! For the record, that's probably the referenced talk: https://www.youtube.com/watch?v=wTPGW1PNy_Y
I have seen people go crazy in stored procedures or via ORMs with super complex queries.
Avoid complex queries (with more foresight about how to structure, cache and query data) and you might not need the SQL code reuse.
There might be some less than ideal bits you would live to DRY up and would if it were OO or Functional Programming but perhaps let it be in SQL.
SQL is performance programming anyway so you are allowed!
I assume you mean libraries of SQL code (e.g. query fragments/templates) as opposed to libraries for working with SQL?
I'm criticizing SQL as a language which is lacking in composability (as opposed to criticizing relational algebra or relational data model).
Libraries for working with SQL (full-fledged ORMs or simpler query builders) are themselves written in a different language so they don't necessarily prove anything about SQL itself, though one could argue that if people want to use them then they might not be satisfied with raw SQL.
Or alternatively, if it's for things that really are mutating state and involving biz logic... a stored procedure.
Date & Darwin proposed the addition of "operators" to the relational algebra in their "Tutorial D" description of alternatives to SQL. That is, instead of "stored procedure", the addition of a type system matching on relations ("tables") and their tuples ("rows") and then the ability to create user-defined programmatic "operators" (a bit like OO methods) for those types.
I feel that it isn't SQL that is actually difficult to re-use. It's what SQL was designed to describe that is difficult to re-use: business entities.
Unlike "true" programming languages (not to get into a debate about SQL's completeness, we all know it isn't practically a general-purpose language) this is usually best approached differently than debugging a program. Whenever I encountered a mess of a query, I'd ask myself two questions:
1. What is the purpose of the query/what is the desired output?
2. What are the source tables used in the query?
At this point I'd typically rewrite the query, taking inspiration from the non-broken parts of the original if possible, and otherwise rewriting from scratch. In most cases this was faster (and the rewritten result often more efficient) than trying to debug a truly busted statement.
It is tho, SQL is a declarative language, so the execution model is largely opaque, by language design.
But even if it were solely an implementation detail, Closi would still be wrong: they literally stated that “it’s not any better” to try debugging a 100 lines python script than a 100 lines query.
> As it is, you often have to take the whole damn thing apart from the inside to see what's going on, then put it all back together again. That's... suboptimal, to say the least.
Which is the point? Sounds like you agree that it is significantly easier to debug a 100 lines python script than a 100 lines SQL query.
I don't mind writing a query, or even a complex query at all. Sometimes it can be draining if you need to nest more queries than you'd like rather than have some joins, but I personally think SQL is a fine language.
Could just be Stockholm Syndrome.
That's not implying that large, successful projects cannot be built upon SQL. We see them in the real world all the time.
We have awesome implementations of the language, but any productivity is in spite of a quirky language designed in the early 1970s, not because of it.
This says nothing about the quality of SQL as a language.
What I think is more interesting is: if you were to greenfield a language for a relational database, how much would it look like SQL?
> if you were to greenfield a language for a relational database, how much would it look like SQL?
Save for secondary concerns like syntax, it would be exactly the same in all respects except easier composability.
There is no low-level API to SQL servers to create competition, so this is like saying CSS has no competition in browsers whatsoever. Yes, it does not, because it was never allowed. Let's go make a browser competition as part of our weekend, or at least RDBMS, sure /s. ORMs with their "relations" etc are basically better DSLs over SQL databases which try to hide rough edges from an innocent user.
concisely, effectively, efficiently expressing ideas, it's unquestionably successful
Yeah, "group by case when cond1 then v1 when cond2 then v3 else v3 end" instead of "group by alias1". Because, you know, it's declarative, but not really: https://stackoverflow.com/a/3841804/3125367
Or fetching thousands of rows out of explosion of "select t.a, t.b, p.propname, p.propvalue from items t left join props p on t.id = p.item_id". Or inability to join two different subtables like 'props' and 'per_city_prices' simultaneously without exploding into infinity. Of course you can always make 3 queries and join at your place, which is neither effective nor efficient nor concise. A business wants simple [{a, b, props:[{name, value}, ...], per_city_prices:[{city, price}, ...], ...]. Can SQL do that? It can not.
Or getting corresponding non-aggregate values along with min/max results, which requires very funny self-joins if it's already e.g. a windowed aggregation.
SQL is cool for these relational... matrices(?), but sucks as a programming language in both syntactic and semantic parts. Some servers fixed few dumb parts of it, but not generally.
Yes... but there is only one Web, and many databases. Some of them don't use SQL. They are, in my opinion, and in the opinion of many other developers, inferior. They lost in the market in a fair fight.
Also if SQL sucks so much you can compile to SQL, like TS->JS. Many such solutions exist. All worse than SQL.
> ORMs with their "relations" etc are basically better DSLs over SQL databases which try to hide rough edges from an innocent user.
ORMs are developed by software engineers for software engineers. It's kind of silly to picture them as "innocent" or incapable to understand the relational model.
> https://stackoverflow.com/a/3841804/3125367
Does having an operational semantics make a language not declarative? That's not how I use words. Is Haskell not functional because it runs on a GRS?
For the rest of your post you're arguing about either syntax, stuff you solve with CTEs, or stuff you would just hand off to the programming language, so I'll just leave it at that.
Perhaps SQL is so good that it couldn't be replaced all these years. But yes, debugging could have been better.
It is as though we lived in a world where, like our own, Lisp invented garbage collection. But unlike our own world, in 2022, Lisp is the only garbage collected language anyone uses. Others were invented but that mostly stopped by the 80s.
Many people would like to replace Lisp, and keep in mind this is Lisp so lexical scope is only available in some implementations and people who want cross-platform compatibility don't use it. But without it, you have to manage your own memory.
That's SQL and relational databases. Relational databases with ACID guarantees aren't optional, they aren't a nice-to-have, they're foundational.
And we talk to them in SQL because... we talk to them in SQL.
This grunt work of sql =>spark translation is easily the most boring part of my job.
Other than that, I do kind of agree that sql isn't really good at large scale programming. There is no ide support while you are working, hard to extract common logic , hard to reuse , hard to compose, hard to unit test. All these things have been mainstays of conventional programming.