Composable SQL

Original Article ↗ Hacker News Discussion ↗

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.