Proper SQL support roadmap

[egormanga]

Abstract

Since SpacetimeDB claims to be an SQL-compatible database, I hereafter propose a checklist on improving (and hopefully completing) the SQL querying & data manipulation support in it.

Note: I decided not to add all the already-implemented features in order to minimize necrobumping noise; feel free to do so if desired.

Syntax

• Timestamp literals: Make printed output of Timestamp objects in all client SDK langs compatible with SQL queries #2450
  • Allowed specifying timestamp literals in SQL #2842
• Tuples: Implemented value tuple literal in SQL #2843
• Arrays: Implemented array literal in SQL #2849
• Enum literals: SQL query syntax for enums #3434
  • Implemented SQL simple enum string literal parsing #2844
• NULL literal: Implemented NULL literal in SQL for Units and Options #3606

Compliance

• Meta: Align with ANSI SQL standards #2993
• Mixed projections: «Column projections are not supported in subscriptions» error in one-off query #2968
• Option compatibility: Implemented Some-typed literals in SQL #3607
• BYTEA literal escape format: Allowed specifying hex strings in the Postgres escape format #3756
• SELECT behavior over PG: Any DML statement containing the word SELECT causes rows reported via PG Wire #3770
  • Refactored SELECT detection in PG SQL #3771

Features

• FORMAT: cli: Support more output format in the sql and subscribe commands #1437
• EXPLAIN: Query plan printer #2058
  • Printer for the plan, ie: EXPLAIN #2075
• ORDER BY: Table Insert Problems #2457
• Foreign keys: Feature request: Foreign key constraints #2608
• Support SumTypes/SumValues in SQL queries (subscription & one-off) #2670
• OUTER JOIN: Implemented OUTER JOIN support #2892
• :owner parameter: Docs - How to access module owner #3229
• Non-index joins: RLS Subscription Error: $Subscriptions require indexes on join columns #3241
• ALTER: Support manual migrations #3272
• Parametrized views: Call parameterized views from sql #3489
  • Call parameterized views from sql: Parsing #3791
• SELECT 1
• OFFSET
• GROUP BY
• CAST()/CONVERT()
• RETURNING
• Implicit joins
• Nested queries

---

Disclaimer: most of the linked PRs are mine. I'm not particularly trying to be pushy, but I found it desirable to categorize all the mess those make and perhaps help the maintainers get them over with imposing less friction.

[bfops]

Hey @egormanga! Thank you for organizing this and linking the relevant issues/PRs. As you've noticed, our best reviewers are pretty busy with other user-facing features at the moment, but we do hope to get to expanding our SQL support!

[cloutiertyler]

Thank you @egormanga!