SQL needed structure

Original Article ↗ Hacker News Discussion ↗

Modern SQL, JSON, and hierarchical results

  • Many commenters argue the article’s “SQL has no structure” claim is outdated: modern Postgres/SQLite support JSON, arrays, composite types, LATERAL, and JSON aggregation, which can output page-shaped nested JSON in one query.
  • Examples are given of using json_agg, jsonb_build_object, and lateral joins to build exactly the IMDB-style hierarchical response.
  • Others note JSON as a result format is convenient but type-poor (numbers, UUIDs, decimals become “stringly typed”) and sometimes awkward compared to native nested types (arrays/structs, Arrow, union types).

Where logic lives: database vs application

  • Some advocate pushing business logic and shape-building into the DB: views, stored procedures, schemas for denormalized JSON, and test frameworks like pgtap.
  • Benefits cited: fewer round trips, less slow application-side row munging, more powerful constraints, better query optimization.
  • Skeptics point to weak tooling for SQL/procedural languages (debugging, testing, canaries, autocompletion, linters) and prefer to keep most logic in application code, often combined with caching of prebuilt view models.

Relational vs document/graph and nested relations

  • Commenters stress that SQL’s flat relations are great for storage, constraints, and analytics, but awkward for directly returning the hierarchical structures UIs want.
  • Some see document stores (MongoDB, JSON blobs in SQL) and graph DBs as appealing for nested, highly connected data, but note real-world pain: migration to relational systems for analytics, denormalization, schema drift.
  • Several propose a middle ground: relational at rest, but first-class nested relations or graph querying on top (BigQuery-style nested structs, Property Graph Query in SQL, systems like DuckDB, XTDB, EdgeDB).

ORMs, “impedance mismatch,” and query patterns

  • Many view ORMs and repeated client-side “re-joins” as reinventions of views or network databases, driven by misunderstanding of tabular data or normalization.
  • Others argue the mismatch is real: SQL result sets flatten natural 1‑to‑many shapes, create row explosion, and force awkward reconstruction; ORMs and custom ORMs/DSLs (GraphQL-like, EdgeQL-like) try to hide this.
  • Multiple techniques are discussed for hierarchical queries: recursive CTEs, adjacency lists, nested sets, closure tables, CTE pipelines, or “full outer join on false” patterns, each with tradeoffs and performance pitfalls.

Syntax, terminology, and standards friction

  • Broad agreement that SQL’s syntax and error messages are clunky, and tooling for DBAs is often miserable, but the relational model itself remains highly valued.
  • Some note the high political/financial barrier to evolving the SQL standard, leading developers to bolt on new systems rather than refine SQL itself.
  • There’s confusion over “structured vs unstructured” terminology; some prefer “schema-on-write vs schema-on-read” to distinguish SQL tables from JSON/XML blobs.