User story: SQL dialect/feature breadth parity with SQLite (Postgres-flavored)

[sphildreth]

User story

As a developer migrating an embedded application from SQLite, I want DecentDB to support (at minimum) SQLite-level SQL dialect and built-in feature breadth (and ideally remain aligned with PostgreSQL SQL syntax/semantics), so that common applications can run without major SQL rewrites.

Why

• DecentDB already targets “PostgreSQL-like SQL syntax” via libpg_query, which is a strong base.
• The biggest adoption friction for SQLite users is typically not storage/durability, but missing SQL surface area: DDL/DML variants, transaction controls, scalar/aggregate functions, and a few SQLite-specific behaviors.

Reference

• Adoption guide: design/DECENTDB_ADOPTION_CONCERNS.md (Section 3. SQL Dialect and Feature Breadth) — includes current support notes and a list of SQLite features that have no PostgreSQL equivalent.

Scope (this issue)

This issue is an epic/user-story umbrella to:

1. Inventory the remaining gaps vs SQLite’s commonly-used SQL surface area.
2. Prioritize and implement missing pieces while keeping PostgreSQL-style syntax as the default when feasible.
3. Add differential tests (SQLite + PostgreSQL where applicable) for every new supported feature.

Out of scope (unless separately ADR’d)

• Anything that changes persistent formats, WAL formats, checkpoint strategy, locking semantics, isolation guarantees, or SQL dialect decisions that could break existing databases.
• Loadable extensions / UDF plugin API (tracked separately in the adoption guide).

Parity gap checklist (initial cut)

Query features

• WITH RECURSIVE (recursive CTEs)
• RIGHT JOIN / FULL OUTER JOIN (or explicit parser/binder rejection to avoid silent mis-semantics)
• Set operations: allow ORDER BY / LIMIT / OFFSET on UNION / INTERSECT / EXCEPT (SQLite supports via parentheses / compound selects)
• INTERSECT ALL / EXCEPT ALL
• VALUES (...) in FROM (table value constructor) where SQLite allows it

DDL / schema

• CREATE TEMP TABLE / CREATE TEMP VIEW (session-scoped objects)
• Table-level foreign keys (CONSTRAINT ... FOREIGN KEY (...) REFERENCES ...)
• Composite PRIMARY KEY / UNIQUE constraints (and enforcing semantics)
• Generated columns (GENERATED ALWAYS AS (...) STORED/VIRTUAL) (SQLite feature; decide whether/how to map to Postgres-style semantics)

Transaction control

• SAVEPOINT, RELEASE SAVEPOINT, ROLLBACK TO SAVEPOINT
• BEGIN IMMEDIATE / BEGIN EXCLUSIVE (or documented non-support + reasoning)
• Deferred FK constraints / deferrable constraints (SQLite supports DEFERRABLE in limited ways; Postgres supports deferrable constraints)

Built-in scalar + aggregate functions (high-ROI)

• Date/time SQL surface area commonly used in SQLite: date(...), datetime(...), strftime(...), unixepoch(...) (DecentDB currently has none)
• String: instr, char, hex
• Math: sqrt, power, mod (or ensure operator-level % covers typical mod usage)
• Aggregates: total, avg(DISTINCT ...) (and other DISTINCT aggregate forms)
• JSON breadth (SQLite JSON1-ish): json_each, json_tree, and/or JSON path operators (->, ->>) if we want compatibility

SQLite-only surfaces with no PostgreSQL equivalent (needs an explicit decision)

These are called out in the adoption guide’s “SQLite Features Without a PostgreSQL Equivalent” section:

• PRAGMA ...
• rowid/_rowid_
• WITHOUT ROWID
• ATTACH DATABASE

For these, acceptance is either: implement a compatibility layer (SQLite mode) or explicitly document “not supported” with recommended alternatives.

Acceptance criteria

• A maintained, test-backed feature matrix for SQL dialect coverage (SQLite vs DecentDB; optionally Postgres mapping).
• For every new feature:
  • Parser/binder/planner/exec supports it correctly, or rejects it explicitly.
  • Adds focused unit tests and harness-level differential tests.
• No durability/isolation regressions and no file-format changes without ADR.