Composable SQL

Original Article ↗ Hacker News Discussion ↗

Attempts to make SQL more composable and testable—such as adding “functor”-like table-valued abstractions—highlight long‑standing frustrations with SQL’s reliance on global tables, poor reuse, and brittle, hard-to-unit-test business logic. Some argue these ideas already exist in various forms (views, table-valued functions, macros) or should instead be handled in application code via ORMs and query builders, keeping SQL as a thin persistence layer. Others maintain that pushing more logic into the database is essential for performance and data integrity, defending constraints and foreign keys while calling for better language and optimizer support rather than abandoning SQL altogether.

Functors and Composability Concept

  • Core idea: “functors” are table-valued abstractions that take tables as inputs and return tables, enabling reusable, testable SQL components.
  • Motivation: current SQL encourages copy-paste of complex JOINs / filters; views are global, unparameterized, and not easily testable in isolation.
  • Goal: let the planner inline these abstractions into a single optimized query, unlike many stored procedures.

Comparison to Existing SQL Features

  • Many note prior art: table-valued functions, stored procedures, table macros, CTEs, and virtual tables in engines like Postgres, SQL Server, Oracle, DuckDB, SQLite, BigQuery.
  • Key distinction argued: most existing TVFs and stored procs either
    • can’t take tables as arguments,
    • are optimization fences / cursor-based and hurt performance, or
    • live as heavyweight schema objects, not lightweight composable fragments.
  • DuckDB’s table macros are highlighted as very close, though some limitations around nesting and query_table are discussed.

Testing SQL and Where Business Logic Belongs

  • One camp: SQL is bad for business logic; treat SQL as an output of a real language, use ORMs / query builders and unit-test the host code instead.
  • Others: SQL unit testing is possible (e.g., pgTAP, tSQLt, dbt), and functors/macros can help make queries testable by eliminating global tables.
  • Concern: embedding functors in schema mixes data and logic, complicating maintenance.

Constraints and Foreign Keys Debate

  • Strong disagreement around whether FKs are “business logic” that should be in the DB.
  • Pro-FK side: constraints are essential defense-in-depth; they prevent invalid states and catch bugs from multiple clients.
  • Anti-FK / skeptical side: FKs entrench volatile business rules, make schema evolution hard, and couple all consumers tightly to internal representation.

Optimizer, CTEs, and Engine Behavior

  • Original article criticizes CTEs and views as optimization barriers; several replies note modern Postgres and “enterprise” engines can inline and optimize aggressively.
  • Some argue relying on “sufficiently smart” optimizers is risky; others note MSSQL/Oracle show that good inlining and plan caching are feasible.

Alternative Languages and Frameworks

  • Multiple alternatives mentioned: LINQ, Kusto, Malloy, PRQL, Trilogy, Ecto, SQLAlchemy, HoneySQL, and various query builders / DSLs.
  • View: composable query builders in host languages already provide “functor-like” composition with better tooling and testing, though none are seen as a perfect SQL replacement.