# SQL compatibility

> Status: Partial. Gauntlet speaks the SQLite dialect over the PostgreSQL wire protocol. Those are two separate promises.

| | |
|---|---|
| **Works** | SELECT with joins, CTEs, subqueries and compound queries; INSERT with upsert and RETURNING; UPDATE; DELETE; CREATE TABLE; BEGIN, COMMIT, ROLLBACK; BEGIN READ ONLY; EXPLAIN; SHOW TABLES. |
| **Differs** | Types use SQLite affinity. Every table needs an explicit primary key. CREATE TABLE is standalone and commits at once. |
| **Missing** | ALTER TABLE, DROP TABLE, CREATE INDEX, savepoints, COPY, CHECK, foreign keys, triggers, PostgreSQL catalogs and extensions. Each is refused with 0A000 or 42601. |
| **Operational impact** | A refused statement changes nothing. Nothing returns success and does nothing. |
| **Evidence** | A tested subset checked against SQLite. Passing tests do not mean complete SQLite compatibility. |

- The **wire protocol** lets PostgreSQL drivers connect, authenticate, send statements and read results.
- The **SQL** is run by Turso, a SQLite-family engine. PostgreSQL syntax, catalogs (`pg_catalog`, `information_schema`), extensions and types are not there.

## What works

| Statement | Notes |
|---|---|
| `SELECT` | Joins, CTEs, subqueries and compound queries, as far as they are tested |
| `INSERT` | Multi-row `VALUES`, `INSERT … SELECT`, `DEFAULT VALUES`, `ON CONFLICT` (upsert), `OR IGNORE`/`OR REPLACE`, `RETURNING` |
| `UPDATE`, `DELETE` | One table, with `WHERE` and `RETURNING` |
| `CREATE TABLE` | With limits; see below |
| `BEGIN`, `COMMIT`, `ROLLBACK` | Interactive transactions |
| `BEGIN READ ONLY` | A consistent read-only image of the whole database. A write inside it returns `25006` |
| `EXPLAIN` | For `SELECT` and plain DML |
| `SHOW TABLES` | Lists the tables. This is the only `SHOW` |

Other things to know:

- **Parameters** are `$1`, `?1` or a bare `?`. Named parameters are refused.
- **Names are single-part.** `schema.table` is refused.
- **Types use SQLite affinity.** A declared length or precision is not enforced. `TEXT` is UTF-8.
- **Every table needs an explicit primary key.** Integer keys are not generated for you: your `INSERT` supplies them.

## What is refused

These return `0A000` (feature not supported), or `42601` when the parser rejects them.

| Refused | Use instead |
|---|---|
| `ALTER TABLE`, `DROP TABLE`, `CREATE INDEX` | Decide the schema and its `UNIQUE` constraints at `CREATE TABLE`. There is no way to change a table yet |
| `SAVEPOINT`, `ROLLBACK TO`, `COMMIT AND CHAIN` | Retry the whole transaction |
| `BEGIN` with a mode other than `READ ONLY` | Plain `BEGIN` |
| `UPDATE` or `DELETE` with `ORDER BY` or `LIMIT` | Select the keys first |
| Several statements in one prepared statement | One statement per prepare |
| Writing `rowid`, `_rowid_` or `oid` | Use your own key column |
| `COPY`, `SHOW <setting>`, SQL `DEALLOCATE`, procedural blocks | — |
| `CHECK`, foreign keys, triggers, generated columns, `STRICT`, non-`BINARY` collations in a table definition | Enforce in the application |
| Cancel requests | Ignored. The statement runs to its end or to the 20 s limit |

## CREATE TABLE

```sql
CREATE TABLE IF NOT EXISTS orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  ref TEXT UNIQUE,
  qty INTEGER DEFAULT 1
);
```

- **One standalone statement.** Send it outside a transaction and not mixed with other statements. Otherwise it returns `25001`.
- **It commits at once.** A later error in the same batch does not undo it. This is not PostgreSQL's transactional DDL.
- **An explicit primary key is required**, inline or as a table constraint. Composite keys are fine.
- **Supported:** `UNIQUE` (column or table), `NOT NULL`, literal `DEFAULT`, `WITHOUT ROWID`.
- **`UNIQUE` is how you get an index.** It is built with the table, while the table is empty.
- **`IF NOT EXISTS`** only checks the name. It does not check that the existing table matches your definition.
- Limits: 4,096 tables, 1,024 indexes per table, 2,000 columns per new table.

## Not inherited from Turso

Gauntlet runs Turso's SQL layer over its own storage. Features that live in Turso's own storage are **not** available: its indexes, full-text search, vector index, change capture and MVCC. Do not read a Turso feature list as a Gauntlet feature list.

## Size limits

| Limit | Value | Past it |
|---|---|---|
| One transaction | 16 MiB, including overhead | `54000` |
| One value / one key | 8 MiB / 64 KiB | `54000` |
| One statement | 20 s or 500 million engine instructions | `57014` |
| Sort, `DISTINCT` and `GROUP BY` temporary data | 64 MiB | `54000` |
| Result columns | 2,000 | `54000` |

Queries build their intermediate results in memory. There is no spill to disk, so a large unindexed join can be slow or hit a limit.

## Coming

`ALTER TABLE … ADD COLUMN`, `CREATE INDEX`, `DROP TABLE`, generated integer keys, `DEFAULT CURRENT_TIMESTAMP`, a schema introspection surface, and a certified list of ORMs are v1 roadmap items. See the [roadmap](https://gauntlet-db.stavros.xyz/docs/roadmap.md).

---
Source: https://gauntlet-db.stavros.xyz/docs/sql/compatibility