SQL Mode (Experimental)

Swytch ships in two modes: a Redis-compatible mode that’s the production-ready face of the product, and a SQL mode that’s currently experimental. This page describes what experimental means in concrete terms (what works, what doesn’t, why, and where contributors can help).

If you’re evaluating Swytch for a SQL workload today, read this page first. If you’re evaluating Swytch for a Redis workload, the Redis mode docs are the right starting point and SQL mode is largely irrelevant to your decision.

Swytch’s SQL mode speaks the PostgreSQL wire protocol, with SQLite as the underlying query engine. For a substantial subset of SQL workloads, it works:

• Standard CRUD operations: SELECT, INSERT, UPDATE, DELETE
• Joins, subqueries, aggregates, common-table expressions
• Transactions via BEGIN/COMMIT, with serializable semantics through Swytch’s envelope-commit
• Indexes (partially)
• Most of the SQLite type system and built-in functions

For workloads that fit inside that envelope, SQL mode behaves like a distributed SQLite over the PostgreSQL wire protocol. Your psql client connects. Your queries return rows. Your transactions commit serializably across nodes.

Three significant gaps in the current implementation, all rooted in how SQL mode is built today (Swytch as a virtual-table backend behind the SQLite parser):

UDFs registered at the SQLite library level execute in the process that registered them. In a distributed system, that means a UDF call on one node can’t be replayed deterministically on another node, because the function body isn’t part of the database; it’s part of one node’s process memory.

This isn’t a fix-it-in-the-next-release problem. Cross-node UDF execution requires either RPC-ing every UDF call back to its originating node (latency and availability nightmare) or requiring deterministic bytecode for UDFs (no SQL system in production does this). For Swytch’s model, where every node serves reads and writes locally, UDFs as SQLite implements them are architecturally incompatible.

SQLite’s virtual-table API doesn’t surface autoincrement column metadata to the virtual-table implementation. When Swytch sits behind the SQLite parser as a vtab, it doesn’t know which columns are autoincrement, which means the vtab can’t generate IDs correctly, can’t reason about gap-filling on conflict, and breaks in subtle ways on INSERT semantics that any real application depends on.

This is a SQLite limitation, not a Swytch design choice. The vtab interface predates the modern autoincrement semantics and never got the metadata pipe through.

SQLite’s parser routes INSERT ... ON CONFLICT through a code path that doesn’t reach the virtual-table interface. Which means even if Swytch wanted to handle UPSERT correctly, the parser rejects the syntax before Swytch ever sees the statement. There’s been a TODO in the SQLite codebase to support this through vtabs for nearly ten years.

UPSERT is heavily used in real applications. WordPress’s options table relies on it. Most modern ORMs generate it for save-or-update operations. The absence of UPSERT support is a significant practical limitation, not a corner case.

The three issues above share a root cause: Swytch’s SQL mode is currently built on top of SQLite’s parser, query planner, and virtual-table interface. That architecture made the initial implementation faster to ship (SQLite’s parser is well-tested, its planner handles a lot of edge cases, and the vtab interface gives a clean integration point). But it constrains Swytch in ways that matter for production SQL workloads.

A proper fix means a from-scratch parser and query planner, designed around Swytch’s actual data model:

• Parser: speaks PostgreSQL syntax (or a meaningful subset) without inheriting SQLite’s vtab limitations. This is the path to UPSERT, proper autoincrement, and a longer list of SQL features that SQLite’s vtab interface doesn’t surface.

• Planner: cost-based, with awareness of data locality. For a distributed database, the cost of a query depends on what data is locally subscribed, what indexes are available locally, and whether a remote subscription is faster than a local table scan. None of SQLite’s planner heuristics capture this. A planner built for Swytch needs to reason about DAG-level data placement as a first-class input.

• Storage interface: a proper separation between the parser/planner and the underlying causal-DAG storage, so the planner can make subscription and execution decisions that match Swytch’s distributed model rather than working around SQLite’s local-first assumptions.

This is real work (months at minimum, more likely a year or more of focused engineering). We’re prioritizing Swytch Cloud and Redis-mode hardening ahead of it. SQL mode will stay experimental for the foreseeable future.

For experimentation, exploration, or workloads that fit the supported subset: yes. The serializable semantics work. The PostgreSQL wire protocol means your tooling connects. The architectural properties Swytch offers (leaderless replication, partition tolerance, multi-region active-active) all apply to SQL mode the same way they apply to Redis mode.

For production: no. The gaps above are real and they will hit you. If you’re considering Swytch as a system of record, run Postgres or your existing relational database for that purpose. SQL mode is for the curious, not for the load-bearing.

This is exactly the kind of project where outside help matters. Swytch is open source (AGPL), and the SQL parser/planner work is well-scoped enough that a database engineer with the right background can make a meaningful dent.

Three areas where contributions would have outsized impact:

• Parser work. A from-scratch PostgreSQL-syntax parser is a substantial project but a tractable one. There’s prior art (the Postgres parser itself, ANTLR-based grammars, hand-rolled parsers in other database projects). If you’ve worked on a database parser before and want a project where the architecture decisions are genuinely open, this is one.

• Cost-based planner with locality awareness. This is the harder, more interesting piece (query planning that treats data subscription state as a planning input). Most database planners assume the data is local; Swytch’s needs to reason about which data is local here, which is local elsewhere, and what the cost of subscription is for each query. There isn’t a lot of prior art for this exact problem, which makes it a research-flavored contribution as much as an engineering one.

• Vtab approach hardening. While the long-term path is a new parser, the current SQLite-vtab implementation can still be improved within its constraints. Better error messages when the parser rejects a query, clearer documentation of what works and what doesn’t, identifying additional features that can be supported through the vtab interface but aren’t yet. Smaller-scoped, but valuable for the experimental users running SQL mode today.

The repository is on GitHub. Open an issue to discuss before starting on something substantial; we want to make sure your work lands in a place that fits the broader architecture, and we’d rather coordinate up front than refactor afterward.