Visions for a Modern Relational Query Language

Originally a long-standing draft, this piece was revisited and updated following recent discourse surrounding emerging query languages such as Acadia.

The Core Thesis

The rise of NoSQL wasn't necessarily a rejection of relational theory, but rather a reaction to the ~~clumsy~~ archaic implementation of SQL. While the underlying concepts of SQL are incredibly potent, the way they are delivered to developers is often frustrating. A modern language that evolves from SQL's successes could significantly improve how programmers interact with relational data.

Author's Technical Pedigree

The perspective here is shaped by extensive experience with various RDBMSes, ranked by familiarity:

MySQL & Db2 (Primary)
SQLite
SQL Server
Oracle
Postgres

1. A Need for Superior Syntax

While the author isn't personally obsessed with aesthetics, the broader developer community is. To put it bluntly: Programmers are like toddlers; they want their Kraft Dinner, not the broccoli.

The decision to base SQL syntax on PL/I was a 1970s IBM-era choice that feels completely out of place today. A modern alternative would likely adopt the "look and feel" of C or Python, perhaps infused with the logic of ML or Prolog.

The Parser Problem

A major pain point is the quality of error reporting.

The Bad: MySQL's parser is notoriously opaque, often failing to pinpoint the exact location or nature of an error (e.g., the absurdity of DELIMITER .).
The Good: Oracle's parser is surprisingly helpful, explicitly stating what it expected to find.

Note on Examples: The syntax used in the following suggestions is illustrative, drawing influence from F# (ML family), Erlang (Prolog-style), and Elixir.

2. Embracing Functional Programming (FP)

SQL's greatest strength is its nature as a 4GL (Fourth Generation Language). Instead of writing manual loops, you describe the desired result—a concept very similar to lazy evaluation in languages like Haskell.

However, most SQL dialects are hampered by a standard library designed for 1980s procedural programming. This has led to a reliance on stored procedures, which are inherently procedural and contradict SQL's declarative soul.

The Procedural Trap

Feature	Declarative (Ideal)	Procedural (Current Reality)
State	Immutable / Set-based	Mutable state / Cursors
Logic	Functions $\rightarrow$ Results	Procedures $\rightarrow$ Side effects
Flow	"What I want"	"How to get it"

3. Demystifying the Query Planner

The "black box" nature of 4GL compilers is a double-edged sword. While optimizers are powerful, a single mistake can lead to a catastrophic performance hit. Currently, query planners are often too cryptic for anyone who isn't a dedicated SQL optimization expert.

$\text{Performance Cost} \propto \text{Planner Opacity}$

(Again, MySQL's EXPLAIN tools are cited as a particularly poor example of this.)

4. Robust User-Defined Types (UDTs)

While "domains" exist in the SQL spec to allow for user-defined types, they are often relegated to simple "syntactic sugar" for range or check constraints.

Postgres: Historically the strongest supporter of this concept.
Oracle: Recently added more flexible support.

This focus on domains traces back to E.F. Codd, the father of the relational model.

"The Relational Model" by Codd serves as the foundational text here. Postgres's heritage via Ingres (which used QUEL) kept it closer to Codd's original vision than standard SQL did.

5. Sum Types and Pattern Matching

Modern FP techniques could solve significant schema design headaches. Consider the example of retrieving stack frame information on the IBM i operating system.

The Current Problem: "The Clumsy Schema"

Currently, system administration functions return tables where:

Groups of columns are mutually exclusive.
There are excessive NULL values because of this exclusivity.
Enums are represented as simple strings.

The Proposed Solution: Discriminated Unions

Instead of a wide, sparse table, a modern language should support Sum Types. This allows for a more precise definition of data:

// Conceptual modern type definition
type MachineInterfaceInfo = { 
    ActivationGroup: long; 
    ASP: long; 
    Library: string; 
}

// Each frame type has different semantics/qualifications
type FrameInfo = 
    | JavaFrame of MachineInterfaceInfo
    | OPMFrame of MachineInterfaceInfo
    | ILEFrame of MachineInterfaceInfo

Modern Language Wishlist

Implement Sum Types/Discriminated Unions.
Integrate native Pattern Matching.
Support returning multiple tables from a single query.
Replace string-based enums with first-class type constraints.

Things I want in a modern relational query language