I agree with Jamie. I think SQL is irritatingly non-composable, many operations require gymnastics to express, and I'd like a more expressive and regular language to write queries in.
I also suspect that such a language would be harder to optimize, and might be more practical to implement if it was less declarative and closer to the plan.
I also think that as you scale up, offloading computation to the database is a false economy. It's closer to the data, but the database itself is a bottleneck. As you scale, you want to limit the complexity of your queries. But this is actually an argument for things like Materialize, i.e. making copies of the data elsewhere that bake in some of the computation ahead of time.
One approach to radically simplify operations with data is to use mathematical functions (in addition to mathematical sets) which is implemented in Prosto data processing toolkit [0] and (new) Column-SQL [1].
[0] https://github.com/asavinov/prosto Prosto is a data processing toolkit - an alternative to map-reduce and join-groupby
[1] https://prosto.readthedocs.io/en/latest/text/column-sql.html Column-SQL (work in progress)
On paper, a more expressive language that spits out SQL queries sounds great, but I've never seen a single one not become a pain in the ass to use.
That's precisely because of some the flaws of SQL outlined in the article.
Generating SQL is complex. Generating portable SQL is impossible.
Take a look at the queries sent by something like Power BI to MS SQL Server when running in direct-query mode. It's just obscene how complex the queries can get!
I've tried to write some SQL generators before, of various types. I always got bogged down in the ludicrous complexity.
The author of the article makes so many good points that it's easy to gloss over entire categories of mistakes in the SQL spec. For example, he briefly mentions that getting a column schema back in query is weird and non-standard. The real problem here is that SQL is not homoiconic: tables, columns, and their types could have been represented as tables, including when defining them. E.g.: instead of "CREATE TABLE" the syntax should have been more like "INSERT INTO sys.tables ... ". (Similarly for columns, constraints, foreign keys, etc...)
Instead, we have a language designed for data that until recently had implementation-defined read-only system views for the schema data! Writing into that data uses an entirely different set of key words and syntax, which is "not really SQL" in the sense that it isn't relational and cannot use tables as input unless coded in an external language like Java, or built up using string manipulation an called via some sort of non-standard "exec string". Of course, then you have to worry about things like not having a built-in standard escape/unescape function! (QUOTENAME is the MS SQL version of this, surely non-standard).
Decoupling the materialisation of a schema from its theoretical representation would make all migrations "instant". Many column-oriented databases can do this, or nearly so, but most row-oriented databases can't or won't.
I.e.: ideally database engines should be able to keep track of multiple "versioned" physical schemas and transparently map in-flight queries to them. Big data migrations often implement this kind of thing manually with "writeable views" or similar techniques. This shouldn't be manual, the database engine should be able to do it behind the scenes with minimal human involvement.
e.g. when I use sqlalchemy, I end up writing queries using core than ORM, because it becomes simple for me to optimize versus having to keep printing str(query_statement) to analyze sqlalchemy thinks it should do versus what it really should do.
Do remember to find a good DB optimization expert (hint: not the type that answers "add an index" when asked how they would speed up a query) and you'd be surprised how well SQL continues to scale today.
And for those of you about to say "but KV/NoSQL", by all means don't shoehorn SQL into every single thing; use the right tool for the right job; SQL will handle that 80+% of what you need, then implement the "other" stuff in memcached/redis/mongo/kafka/whatever you find is "easiest" to implement, deploy, manage and maintain.
ORM generally aren’t meant to totally replace sql, it’s an anti pattern. There was a quote by Gavin king explicitly calling this out; ORM for crud operations on object hierarchies, sql for everything else (reporting, adhoc queries etc). I try to educate this whenever I can but so many teams resist it.
https://old.reddit.com/r/programming/comments/2cnw8x/what_or...
The problem with most abstractions on top of SQL is that the first thing they abstract away is the relational model; you end up with an opaque result set, a mapped object, or an untyped collection. In my opinion, in order for a higher-level SQL language to be useful it must retain all the low-level data type information. The abstraction should be on the operation level (DML), not the data level.
End result? Abstractions target the easier of the two markets: people who don't want to learn relational modeling.
As an example, ActiveRecord allows you to define scopes. If you have an account things like:
platinum: -> value >100000
churn_risk: -> churn>.95
You can then chain these together i.e Account.platinum.churn_risk to get all the platinum accounts at risk of churn.
Afaik there's nothing similar in SQL. If your definition of a platinum customer changes you have to change a bunch of different queries instead of one definition.
I’ve plucked that low hanging fruit before, and it can taste great even in SQL sauce.
It breaks down in really stupid and unintuitive ways. Now you're not just learning LINQ, but also the ideosyncracies of how LINQ translates queries to SQL and why sometimes your performance plummets even though you expressed yourself in the same way, but if you put some query in a variable first and then continue grouping it does a query that takes milliseconds. It's the best example of a leaky abstraction I've ever come across.
The context for my generators was querying over data whose schema was customer defined. I wrote two generators in Ruby and one in Java. They were designed to take advantage of quirks and performance characteristics of MySQL when doing arbitrary ad-hoc sorts and filters, and the optimization was mostly about delaying joins if they weren't necessary for the predicates, and creating an inside out nest of subtable queries to control the execution plan.
Doing this without a generator of some kind is impossible - the columns are dynamic - and then you leave a lot of performance on the table if you don't tune.
I find that if my query is getting overly complex, that some of the logic is often better suited in the app layer.
At best, you are spending time learning something niche with little upside. At worst, you are introducing buggy bad performing complexity.
While I agree with you, it's too bad you can't create macros over common patterns. Not sure about you but I copy and paste a ton for this.
Really, I'd just want a coffee script, or moon script (https://moonscript.org/) where you have the idea for a query in your head, and know what the constructs will ultimately generate.
If you have bounced disappointingly off ORMs like I have since the 1990s, EntityFramework is a great step forward.
Adding another abstraction layer on top would only increase this frustration, as they'd have even less control.
Speaking of which, what has happened to hadopp and HDFS? Used to be flavor of the month about 10 years ago, but now I hardly ever hear people talk about it.
Trying to change to the Dvorak layout taught me a lot about enacting change on such a grand scale.
After a lot of hassle switching machines and OSes, typing on other user‘s computers, them typing on mine and general headaches among internationalization and inaccessible key combinations, I switched back to QWERTY in the end.
As said in other threads, there is no shortage of attempts to replace SQL. A lot of them are pretty good. But having learned SQL the hard way, I feel zero urge to learn another language from scratch right now.
It‘s why nearly all big databases eventually switched to it despite the FORTRAN vibe and its general ugliness.
Anyway, probably time to SET TRANSACTION LEVEL READ UNCOMMITTED and call it a day ^^
For example, in iOS development (and, more in general, on Apple platforms), there has been a huge shift from Objective-C to Swift.
The same arguments should apply there. Swift is much better, but Objective-C got the work done, and many codebases were written in it, especially at Apple. And yet, the whole community switched pretty quickly.
One could argue that Swift was easier to pick up for newcomers. While that's true, I would then expect the argument to apply also to SQL alternatives.
So, what is the difference here?
Apple is automatically the loudest voice in the room for iOS development. If they embrace Swift, the writing is on the wall for Objective-C. It's not just sticks, I'm sure they also put a lot of effort into making the transition as easy as possible.
In SQL - there is no equivalent. I think a better example is x86.
It took a generational new form of computing (mobile) to give ARM the momentum to seriously challenge x86. It's been almost 15 years since the original iPhone and we're only now seeing ARM based processors in computers.
SQL IMO is EVEN harder to displace than x86. x86 has a massive ecosystem but only only two serious manufacturers. SQL has a similarly massive ecosystem AND is the de facto language of the vast majority of major databases. Going to be very hard to unseat.
Are we just ignoring the Acorn Archimedes series of computers which gave rise to the ARM processors in the first place, fully 20 years before the first iPhone was launched?
The consensus among relational theorists appears to be that data types can be arbitrarily complex. For instance, C.J Date and Hugh Darwen write the following ([1] page 56):
Third, we remind you that types are not limited to simple things like integers. Indeed, we saw in Chapter 1 that values and variables can be arbitrarily complex—and that is so precisely because the types of those values and variables can be arbitrarily complex. Thus, to paraphrase a remark from that chapter, a type might consist of geometric points, or polygons, or X rays, or XML documents, or fingerprints, or arrays, or stacks, or lists, or relations (and on and on).
I think the same can be said of Java -> Kotlin, C -> Python (I know, I know), and lots of other medium-to-large scale language migrations over the past several decades. When people move to a new language, it's because there's strong interoperability with what came before that everyone would like to quit using but can't because they have too much invested in it.
This suggests to me that anything that wants to beat SQL will in fact have to compose with it - probably partly by generating it, but also by having a fairly solid "drop down to SQL" story. In other words, a language that, at least on the read-side, can somehow take two separate SQL queries and automatically rewrite them as subparts of a different SQL query. It might not be fast, but it needs to work, because you're going to want to reuse that work on that one gnarly query you did that gets all the important business metrics, and you also are going to want your results to be free of consistency issues.
This is only true for hobbyists and indie/startup developers. The largest and most popular iOS apps remain Obj-C (with a lot of C++ and in-house frameworks in the mix). There’s no incentive to rewrite something like the Facebook app in Swift.
If one company had all of SQL market share and they switched to a new lang, everyone else would have too.
But that's not the case here.
On the other hand, the fact that many new time series databases (and other engines, including Materialize which the author of the article worked on) wanted sql (or fake sql-like languages when they couldn’t manage sql) suggests that not being sql can be a big hindrance.
Off-topic, but changing layouts is easy. Also, using other people's keyboards is not the best for hygiene even before covid.
Interesting to note that Emacs standard key-bindings don't have the issue (from what I heard). However, I use evil in Emacs and Colemak-dhm layout and I decided to rebind all evil / vim keys. This fixes the position issues, mnemonic issues, and gave me the opportunity to improve on a bunch things over the defaults in QWERTY.
I very rarely type on anyone else's computer. Usually when I do it's my wife's computer (maybe 5 time's a year). I have a small 40% keyboard with QMK firmware so it's easy to transport and everything I need is flashed into the firmware. No need for configuration of the keyboard layout on their OS.
Stop fighting SQL so much, and just focus on bringing a better solution. If potential users see it has significant benefits they'll start using it.
Many of these blog articles seem to be written by devs that have only really experienced the user-facing application side of things, and really don’t realise the sheer number of financial, analytic & business systems, that keep the world spinning round, that are all happily buzzing away using SQL.
Moaning about JSON manipulation in SQL is madness. A client should use appropriate joins to get the data from the database & then do the transformation. Storing JSON when you actually need to perform operations on said data makes 0 sense. It’s a huge overhead, an utter nightmare to index etc.
Please don't set up "object joins" against an object/key/value table. It's better to flatten the highly used bits of the json out into the table that hosts the "source json" as a blob in one column. There is a place in our codebase where someone did these "object joins", and it's a nightmare to work with. After about two months, there is a comment in one of the tests I found, "I really regret this pattern now". Now we have enough customer data in that object "kv table" that we can't migrate back to json + select flattened fields without extreme pain.
I think that's why NoSQL is where the gains are being made. You can't magically improve things with a query language without putting the effort into performance, but you can get huge gains by changing your assumptions about how data is structured.
Then there's the whole object/relational impedance missmatch, which if you insist on using OOP, there's a variety of object databases that make a small persistance layer much easier.
What's an example of data that's not relational?
Another case is when users are effectively designing their own schemas but you want to keep them, for example I have a database for a CMS where users can define nested hierarchies of keys with translations that would be impossible to fit into a schema. You could just toss this data into a JSON column, but its an example of nonrelational data.
There were multiple optimizing compilers which can do a lot of these asks, allowing composable queries that return nested types, but produce SQL queries. I think the pathfinder compiler had the most real-world use, it was meant to efficiently mix SQL and xquery to query SQL+XML documents stored in postgres. It had a c# linq frontend, but also fairly hefty compile times.
But in my world, SQL is much more of a Human Data Interface than Application Programming Interface. SQL is used for both ad-hoc and automated data querying and transformation. It is something manufacturing intelligence and supply chain experts and researchers and others learn to empower themselves to get to the data - and yes, you won't see any CEO running reports in SQL themselves, but it is not for programmers only.
Those people would not benefit from syntax with more brackets instead of words, easier way to define and call functions, or the ability to install libraries - in fact I think it would make it harder and less accessible for them.
The OP is right that for machine-to-machine interface, all the syntax baroqueness is not worth it. And of course, having more portability would be great.
But while machine-to-machine interfaces written by skilled developers who knows exactly what are they doing might actually be the most common use of SQL (since half the internet runs on Wordpress and MySQL and most smartphone apps might have SQLite in them), it is not where the majority of complexity or time is spent - that one is with the people working with data.
In the event that nothing exists that is right for the job: don't worry about replacing SQL, worry about creating/using a matched DSL/API/(whatever is appropriate) and data store for the particular problem you're trying to solve that SQL isn't a good fit for. If you create something that solves its particular problem even half as well as SQL did for relational databases you'll probably have a winner in that space that people with the same problems would be happier using.
Scalable databases are just so difficult that we’re still driving a ‘64 IMPALA
Most of this opinion comes from “SQL” being vendor-specific. Is JSON vendor-specific? Is anything else, that we actually use by choice?
Mad at you too, Graph DBs, for sending us on another snipe hunt by adding vendor-imposed innovations, because it makes the enterprise marginally profitable. It’s how the world works, I’m still not happy about it.
(Disclaimer: I possibly just traveled back to 2002 and said this on slashdot)
Two things that come to mind are Markdown with all its flavors and Regex with multiple engines.
edit: and to a lesser extent maybe C/C++ compilers and JS engines.
edit2: also JVM, Python and Ruby runtimes
But both edits describe technologies with an official spec and slightly different implementations. Markdown/Regex are more comparable to SQL because they have vendor-specific syntax.
SQL standard only covers the basics and is only useful to humans, not machines.
What type of database engine is used to execute that query is independent of the language. It could be highly scalable or it could be focussed on single user, single process.
What is the problem that needs solving?
Totally! SQL is like shell, c and js that somehow generate this "never ever try to improve them for real, lets stay suffering for all eternity!"
And the AMOUNT of energy in mask them (transpilers, linters, "good practiques", tricks, etc) is a monumental waste of effort that cost so much than actually go and fix the core issues.
And in this case, SQL is the most trivial to fix. It could get simplified and streamlined far easier that is to replace C, for example.
And a lot of interactions to the DBs are using bridges already. Create a "good sql" is similar to create WASM and then for legacy reasons support in EOL fashion all the old sql and all the new go against the new way.
But this mean at least 2 or 3 major database vendors go for it (I dream: sqlite & postgresql).
P.D: I'm exploring building a relational language, so already have a simplified dialect for it: https://tablam.org/tutorial
In this you might be able to guess my objection to many of OPs points. Because the key thing we look for when we decide to implement tech isn’t how good it is, but how easy it is to work with and more importantly, how easy it is to find other people who can pick up when someone’s career take them forward in life.
SQL is the one technology that has been stable enough over the years that no matter who we hire, we can always count on them being capable of working with our Databases. Yes, some of the more expansive functions, especially some of the recursive ones, take a little time to get accustomed to and there will always be some back-and-forth preferences in regards to stores procedures, but on a whole, it’s the one technology that never causes us any pain and never requires retraining.
I’m sure it’s not efficient, and OP is completely correct about the parts about the untapped potential, and I fully support people developing rational alternatives to SQL, I’m simply pointing out why SQL has been the power horse it has since, well basically, since we moved beyond punch cards and why it’ll likely still be so in 50 years.
Because at the end of the day, technology doesn’t really matter to the business. Cost vs benefit does, and as long as it’s easier and therefor cheaper to have people work with SQL (in the big picture), it’ll be what businesses work with.
The only reason you don’t see something similar in declarative programming (or even functional) is really beyond me but it probably has to do with how things evolved and how much easier it is to change that part of the tech stack compared to your databases. If we get a new hire who can do magic with JavaScript/Python/whatever we may consider allowing him/her to do that. We don’t want to, again because the big picture is easier to manage with fewer technologies, but it’s much easier to justify the cost-benefit of that compared to someone wanting to use a different database than the one in our managed cluster. Already you need more staff than that person to maintain it, as developers don’t run database server operations, aren’t part of the contingency staff and so on.
Like I said, I fully encourage the direction OP wants to go. Enterprise rarely invent things, we’d be happy with ball frames if that’s what made us the most money, so we need people like OP to invent things for us to adopt.
I also happen to really dislike how the author hasn't capitalized the syntax like SELECT FROM WHERE or CREATE TABLE which, to me, poorly affects the legibility and therefore makes me less interested in reading the argument overall.
In the end this makes spotting SQL-specific functions much clearer. I find it much easier to read SQL with this convention since my brain immediately knows when it's looking at context/project-specific data or a FUNCTION.
>> The core message [...] is that there is potentially a huge amount of value to be unlocked by replacing SQL
To me, a lot of people defends SQL saying that "perfect is the enemy of good" and that SQL simply works. Not the favourite of anyone, but everyone kinda accepts it.
And yeah, it's true. People use SQL because it's good enough, and trying to reinvent the wheel would take more work (individually speaking) than just dealing with SQL as it is right now. For large organizations where the effort could be justified, all your engineers already know SQL anyway, so it's not so great either.
But for something so relevant as relational databases, perfect is not the enemy of good. We do deserve better. We generally agree that SQL has many pitfalls, it's not great for any kind of user (for non-technical users, a visual programming language would work well here, more like what Airtable does, closing the bridge between spreadsheet and hardcore database, and for technical users, it does feel unwieldy and quirky). We should be more open to at least consider critiques and proposals for better. We might find out that people, from time to time, are making some good points.