Language-Integrated Recursive Queries (Full Version)

TL;DR

This paper introduces TyQL, a safe, recursive query language integrated with SQL, that ensures correctness and safety while achieving significant performance improvements for fixed-point computations.

Contribution

It proposes a formal calculus for deriving properties of recursive queries and implements TyQL, a Scala-based system that guarantees safety and performance in recursive database queries.

Findings

TyQL achieves no performance penalty compared to raw SQL.

TyQL provides a three-orders-of-magnitude speedup over non-recursive SQL.

The calculus automatically detects unsafe recursive queries.

Abstract

Performance-critical industrial applications, including large-scale program, network, and distributed system analyses, rely on fixed-point computations. The introduction of recursive common table expressions (CTEs) using the WITH RECURSIVE keyword in SQL:1999 extended the ability of relational database systems to handle fixed-point computations, unlocking significant performance advantages by allowing computation to move closer to the data. Yet with recursion, SQL becomes a Turing-complete programming language and, with that, unrecoverable safety and correctness risks. SQL itself lacks a fixed semantics, as the SQL specification is written in natural language, full of ambiguities that database vendors resolve in divergent ways. As a result, reasoning about the correctness of recursive SQL programs must rely on isolated mathematical properties of queries rather than wrestling a unified formal model out of a language with notoriously inconsistent semantics. To address these challenges, we propose a calculus that automatically derives mathematical properties from embedded recursive queries and, depending on the database backend, rejects queries that may lead to the three classes of recursive query errors - database errors, incorrect results, and non-termination. We introduce TyQL, a practical implementation in Scala for safe, recursive language-integrated query. Using Named-Tuples and type-level pattern matching, TyQL ensures query portability and safety, showing no performance penalty compared to raw SQL strings while unlocking a three-orders-of-magnitude speedup over non-recursive SQL queries.