Docs / SQL
SQL compatibility
Gauntlet speaks the SQLite dialect over the PostgreSQL wire protocol. Those are two separate promises.
Partial View Markdown
# 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
Connect this project to my existing Gauntlet database.
Context:
- Documentation index for agents: https://gauntlet-db.stavros.xyz/llms.txt
- Quickstart: https://gauntlet-db.stavros.xyz/docs/quickstart.md
- SQL compatibility and error codes: https://gauntlet-db.stavros.xyz/docs/sql/compatibility.md
- Database: the one already configured for this project
- Protocol: PostgreSQL wire or the native HTTP JSON API, whichever this project already uses
- Connection values are in environment variables: GAUNTLET_DATABASE_URL (PostgreSQL wire),
GAUNTLET_HTTP_URL (HTTP base URL) and GAUNTLET_DATABASE_TOKEN (the database token).
These names are a convention, not a shipped SDK setting. Keep this project's own names if it has them.
Before editing, read the Gauntlet documentation above and inspect how this project
already talks to its database. Gauntlet is pre-beta software; where the documentation
and my deployment disagree, tell me instead of guessing.
What Gauntlet is:
- A distributed SQL database. SQL is the SQLite family, run by the Turso engine.
- The PostgreSQL wire protocol is a transport only. There is no PostgreSQL SQL dialect,
no pg_catalog or information_schema, no extensions, and no ORM is certified yet.
- libSQL client compatibility is not available yet. Do not assume it.
Connecting:
- PostgreSQL wire: dbname is the database name, the user name is ignored, and the
password is the database token. Authentication is SCRAM-SHA-256. Keep TLS
verification on (sslmode=verify-full with the deployment's CA certificate).
- HTTP: POST <GAUNTLET_HTTP_URL>/db/<name>/sql with a JSON body
{"sql": "...", "params": [...]} and the header "Authorization: Bearer <token>".
POST /db/<name>/tx runs a list of statements atomically.
SQL rules that differ from PostgreSQL:
- Every table needs an explicit primary key, and INSERT supplies the key value.
- CREATE TABLE is a standalone statement outside a transaction. There is no ALTER TABLE,
DROP TABLE or CREATE INDEX; an index comes only from a UNIQUE constraint at creation.
- Parameters are $1, ?1 or ?. Names are single-part (no schema.table).
- Column types follow SQLite affinity. In prepared statements ordinary columns are
described as text; CAST(x AS INTEGER) when the driver needs a number.
Errors you must handle (SQLSTATE):
- 40001: conflict. Roll back and retry the whole transaction. Locking is no-wait, so
this is normal under contention.
- 40003: commit outcome unknown. Do NOT retry. Look the transaction up with
GET /db/<name>/txn/<txid> and replay only if the status is `aborted`.
- 57P03: cannot serve right now. Roll back, then retry the whole transaction with backoff.
- 53300: too many connections. Back off; keep the pool below the node's limit.
Credentials and scope:
- Use only the database token for this one database. Keep it server-side and out of
source, logs, browser bundles and this conversation.
- Never use or ask for a deployment admin token, a client token or bucket credentials.
- Do not create, drop, back up, restore or branch databases, and do not rotate credentials.
Task:
1. Add the smallest connection integration that fits this project.
2. Run one explicit `SELECT 1` connectivity check. It may activate this database if it
is idle; do not probe or wake any other database.
3. Do not change the schema or write application data unless I ask for it.
Report the files changed, the protocol and client used, the result of the check, and
anything this project needs that Gauntlet does not support.- 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,?1or a bare?. Named parameters are refused. - Names are single-part.
schema.tableis refused. - Types use SQLite affinity. A declared length or precision is not enforced.
TEXTis UTF-8. - Every table needs an explicit primary key. Integer keys are not generated for you: your
INSERTsupplies 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, literalDEFAULT,WITHOUT ROWID. UNIQUEis how you get an index. It is built with the table, while the table is empty.IF NOT EXISTSonly 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.