Things I want in a modern relational query language - the sporks space

Things I want in a modern relational query language - the sporks space

Skip to content

Search

Search for:

This was a very old draft I’ve had sitting around for years. The recent discussions of new query languages like Acadia spurred me to revisit, revise, and publish this.

I think one of the biggest causes of NoSQL is that while SQL is a powerful language because of the ideas behind it, it’s often implemented in clumsy and archaic ways. A language that learns from SQL could make relational data better to manipulate for programmers. I’ll try to think of things similar to those that I have dealt with in real-world situations and how a better query language could have helped. I’d love discussion on what else could be done.

For what it’s worth, my background with RDBMSes is mostly in MySQL and Db2, but I have used SQLite, SQL Server, Oracle, and Postgres in anger enough (in descending order of familiarity).

Better syntax

I’m not picky myself about aesthetics, but many others are. Programmers are like toddlers, they want their Kraft Dinner and not the broccoli. Basing syntax off of PL/I is a 1970’s IBM choice that probably wouldn’t fly today. Due to popular demand, such a language probably would pick up C or Python aesthetics syntactically, though perhaps with some ML or Prolog influence (as i.e. Rust shows).

With better syntax I hope can come better parsers. I especially loathe MySQL’s parser, which never actually tells you where problems lie or what it is, if it isn’t some syntax absurdity like DELIMITER. Better SQL parsers do exist in conventional implementations though – Oracle is surprisingly good at reporting errors by telling you what it expects.

The examples I write are just for show; I’m not wed to anything nor do I demand what syntax must be. My influences in these examples are most likely from F# (ML family), Erlang (Prolog-esque), and Elixir (Erlang and Ruby like).

A functional programming language that isn’t hostile to functional programming

SQL’s 4GL qualities where you describe how you want your data instead of looping over it by hand is SQL’s most powerful weapon. This is pretty close to a lot of functional programming paradigms like lazy evaluation – hello Haskell. Unfortunately, the standard library of most SQL dialects is somewhat anemic on this front; being optimized for 1980’s procedural programs. Most SQL dialects ended up supporting stored procedures, which are inherently… procedural; going against the grain of SQL’s declarative nature. This ends up reflected in most user SQL code, where they imitate the style that the language and standard library make easy, which involves a lot of dealing with mutable state (cursors…) and procedures over functions. Defaults matter.

Less opaque query planners

While being a 4GL is a strength with how powerful compilers and optimizers are optimizing most code, it can be easy to make a mistake that makes a query more expensive, but planners can be cryptic unless you’re already an SQL optimization expert. (Again, special mention to how bad MySQL’s "explain"ing tools are for this.) While not strictly PLT related, it is something weak in current SQL implementations that computer scientists have learned a lot about.

Better user defined types

While some RDBMSes offer the concept of domains for specifying user-defined data types (and is an optional part of the SQL spec), they can be limited in what they can do (usually just sugar around ranges or checks). Postgres was the only one that seems to support it; Oracle apparently only got support recently (though it seems perhaps more flexible than Postgres). Unfortunately, I haven’t used either enough to be very familiar with how it works in practice. However, domains are covered in Codd’s The Relational Model, which is the foundational text for RDBMSes. Considering Postgres’ heritage in Ingres, which was based on QUEL, which in turn was closer to Codd’s vision of RDBMSes than SQL was, it makes sense Postgres ended up following that.

Sum types, discriminated unions, and pattern matching

One schema that illustrates how modern functional programming techniques could be applied here is this function that returns stack frame information. For context, IBM i, the operating system mentioned here, provides many SQL functions for system administration under the "Services" umbrella. While this is very useful for DBAs-turned-system administrators in the heat of debugging, it is unfortunately clumsy, because effectively there’s "groups" of columns that are effectively mutually exclusive, lots of nullables because of that, and string fields that are effectively enums.

Some of these are just poor schema design (perhaps not helped by the fact it must be returned in a single table – returning multiple tables would also be an interesting direction to go in); the stringy enums can be fixed with a foreign key constraint on a table that acts as an...