SQL Reference
Cinch SQL databases are full SQLite. Everything SQLite supports works, with a few restrictions for security and multi-tenancy.
What works
- DDL: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, CREATE VIEW
- DML: SELECT, INSERT, UPDATE, DELETE, REPLACE, UPSERT (ON CONFLICT)
- Joins: INNER, LEFT, RIGHT, CROSS, NATURAL, self-joins
- Subqueries, CTEs (WITH), window functions
- Transactions: BEGIN, COMMIT, ROLLBACK
- Full-text search: FTS5
- JSON functions: json_extract, json_each, json_group_array, etc.
- Aggregate functions: COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT
- Math, string, and date functions
- Parameterized queries: positional (
?) and named (:name,$name,@name) - Read-only pragmas:
PRAGMA table_info(),PRAGMA table_list
Data types
| Type | Notes |
|---|---|
| NULL | SQL null |
| INTEGER | 64-bit signed integer |
| REAL / FLOAT | 64-bit IEEE floating point |
| TEXT | UTF-8 string |
| BLOB | Binary data (base64-encoded in JSON responses) |
Limitations
| Limit | Value |
|---|---|
| Max SQL statement length | 1 MB |
| Max statements per batch | 256 |
| Query timeout | 5 seconds per query |
Blocked for security
ATTACH DATABASE,DETACH DATABASEload_extension()readfile(),writefile()- Dangerous pragmas:
journal_mode,page_size,cache_size,wal_checkpoint
i
Read-only tokens
If you connect with a read-only token, all write operations (INSERT, UPDATE, DELETE, CREATE TABLE, etc.) are blocked and return an error.
See SQL for connection examples.