SQL as API

Original Article ↗ Hacker News Discussion ↗

Using SQL itself as a public API boundary promises powerful, flexible querying for clients, but raises hard questions about security, performance, and complexity control. Commenters compare this approach to alternatives like custom JSON DSLs, GraphQL, PostgREST-style URL filters, and database-native access controls (RBAC, row-level security, timeouts), arguing over how much power users should have and where to enforce constraints. Many see value in SQL-like query languages or limited subsets translated to SQL, but opinions diverge sharply on whether exposing raw SQL over the internet is ever a good idea.

Overall framing

  • Thread debates using SQL (or SQL-like) as an API/query language rather than bespoke JSON filter structures or REST endpoints.
  • Many see this as inevitably “inventing a query language” anyway; the real question is: which one, and where to enforce constraints?

SQL vs custom DSL / JSON / GraphQL

  • Pro-SQL subset:
    • SQL is expressive, well-known, and naturally extensible; adding more of the language can remain backward compatible.
    • Avoids designing an ad‑hoc structure that may break later or become hard to evolve.
    • Local SQL (e.g., SQLite in browser/WASM) plus sync can blur API vs DB boundaries.
  • Pro-DSL / JSON:
    • Custom JSON/AST or lispy formats are easier to parse, type-check, and transform to SQL or to other backends (e.g., Elasticsearch).
    • “SQL‑looking but not really SQL” DSLs can be confusing; better to be explicit and structured.
    • Existing standards (OData, JSON:API, Google’s filtering AIP/CEL, expression languages, Substrait) already address many needs.
  • GraphQL gets mixed reviews:
    • Some see it as equivalent to “bundling queries” (solving n+1/network trips).
    • Others argue it is mainly a result-shaping language, with limited query power (no unions/recursion) and heavy stack overhead.

Security, permissions, and QoS

  • Concerns: exposing SQL is called insecure, hard to maintain, and risky for performance (table scans, DoS, wide surface area).
  • Counterarguments:
    • Modern DBs offer RBAC, row-level security, constraints, views, timeouts, quotas, and rate limiting; these can bound damage.
    • Statement timeouts and resource limits can curtail pathological queries.
  • Disagreement persists on whether DB‑level security is simpler or more complex than compiling a restricted DSL to SQL.

User-facing query experience

  • For non-technical users (e.g., product search), free-form SQL is seen as too hard; UI controls naturally map to simple AND/OR/facets.
  • Some practitioners report complex OR logic in product search is rarely requested; others often miss richer boolean search in tools.
  • For power users (logs, audits, admin/reporting), textual query languages (SQL-like, Lucene, JQL-style) are considered very valuable.

Existing tools and patterns

  • Examples cited: PostgREST, SQL-like APIs from major vendors, ClickHouse’s controls, crt.sh’s public Postgres access, and “ship the DB” patterns.
  • Some argue doing this “right” approximates what tools like PostgREST already provide; others defend small, context-specific implementations.