Skip to content
v0.5.1GitHub

Using

LuraSQL

applies to v0.5.1

LuraSQL is the relational engine: tables with typed columns, indexes, views and left joins through one /sql endpoint, row endpoints for browsing, a CSV and TSV import that infers types, and KVREF and JSONREF columns that link rows to keys and documents. It is deliberately small. This page shows what it supports and where it stops.

The examples use the relational domain orders and a Bash shell function sql that sends one statement. Relational domains are separate from key-value and JSON domains, even with the same name:

Terminal window
curl -s -X POST http://127.0.0.1:3000/store-api/rel/domains \
  -H "Authorization: Bearer $KEY" -H "Content-Type: application/json" \
  -d '{"name": "orders"}'


sql() {
  curl -s -X POST http://127.0.0.1:3000/store-api/rel/orders/sql \
    -H "Authorization: Bearer $KEY" -H "Content-Type: application/json" -d @-
}
{ "name": "orders", "created_at": 1793745874, "state": "active" }

The sql shell function

Section titled “The sql shell function”

The examples are Bash. On Windows, run them in WSL or Git Bash; PowerShell and cmd.exe do not understand them.

sql() { … } defines a shell function named sql: a new command that runs the curl call between the braces (Bash manual: shell functions). The name is free to choose. Pasting the definition prints nothing; the shell only remembers it, for as long as the terminal session lasts. To keep it, add the same lines to ~/.bashrc.

-d @- tells curl to read the request body from standard input instead of from the command line (curl manual: -d). The call sql <<'EOF' runs sql and passes everything up to the line EOF as that input, a here document (Bash manual: here documents). The quotes around EOF keep the shell from touching the text, so SQL string literals such as 'Lisbon' need no escaping.

Step by step, once the clients table of the next section exists:

  1. Type sql <<'EOF', the JSON body and EOF, then press Enter.
  2. curl sends the body to /store-api/rel/orders/sql and prints the answer below the command. The answer is JSON, like everywhere in the REST API:
Terminal window
sql <<'EOF'
{"sql": "SELECT id, name FROM clients WHERE city = ? ORDER BY name DESC", "params": ["Lisbon"]}
EOF
{ "columns": [{ "name": "id", "type": "INTEGER" }, { "name": "name", "type": "TEXT" }], "limit_applied": false, "row_count": 2, "rows": [[3, "Lena Fischer"], [1, "Jane Doe"]] }

The answer is ordinary command output, so the shell can send it elsewhere. > file after EOF writes it to a file instead of the terminal; | jq passes it to jq, which pretty-prints it or picks a field:

Terminal window
sql <<'EOF' > lisbon.json
{"sql": "SELECT id, name FROM clients WHERE city = ? ORDER BY name DESC", "params": ["Lisbon"]}
EOF


sql <<'EOF' | jq '.rows'
{"sql": "SELECT id, name FROM clients WHERE city = ? ORDER BY name DESC", "params": ["Lisbon"]}
EOF
[[3, "Lena Fischer"], [1, "Jane Doe"]]

One statement per request

Section titled “One statement per request”

The body of POST /store-api/rel/{domain}/sql has the field sql with exactly one statement, a trailing semicolon is accepted, and two statements answer 400. Values go in as parameters: each ? takes the next entry of params, and null is a valid parameter. The answer depends on the kind of statement:

Statement Answer
CREATE, ALTER, DROP {"ok": true}
INSERT, UPDATE, DELETE {"affected": 3, "last_pk": null}. last_pk is the primary key of the row that a single-row INSERT created, otherwise null.
SELECT columns with name and type, rows as arrays in column order, row_count, limit_applied, and expanded when references were resolved.

Rows are arrays, not objects, because the columns of a join can carry the same name twice. A statement needs the right of its kind on the domain: read for SELECT, write for INSERT, UPDATE and DELETE, ddl for the rest, see Security.

Terminal window
sql <<'EOF'
{"sql": "CREATE TABLE clients (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, city TEXT, email TEXT UNIQUE)"}
EOF
sql <<'EOF'
{"sql": "INSERT INTO clients (name, city, email) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)",
 "params": ["Jane Doe", "Lisbon", "jane@example.com", "Omar Haddad", "Tunis", "omar@example.com", "Lena Fischer", "Lisbon", null]}
EOF
sql <<'EOF'
{"sql": "INSERT INTO clients (name) VALUES (?)", "params": ["Chloé Martin"]}
EOF
sql <<'EOF'
{"sql": "SELECT id, name FROM clients WHERE city = ? ORDER BY name DESC", "params": ["Lisbon"]}
EOF
{ "ok": true }
{ "affected": 3, "last_pk": null }
{ "affected": 1, "last_pk": 4 }
{ "columns": [{ "name": "id", "type": "INTEGER" }, { "name": "name", "type": "TEXT" }], "limit_applied": false, "row_count": 2, "rows": [[3, "Lena Fischer"], [1, "Jane Doe"]] }

Keywords and names are not case-sensitive. A statement is limited to 64 KiB. Each statement is applied as one batch: a multi-row INSERT puts in all its rows or none.

Tables have typed columns

Section titled “Tables have typed columns”

CREATE TABLE takes a name and a list of columns with a type and constraints. The types, and the names that are accepted for them:

Type Also accepted Holds
INTEGER INT, BIGINT, SMALLINT A 64-bit integer.
REAL FLOAT, DOUBLE A 64-bit floating-point number.
TEXT VARCHAR, CHAR UTF-8 text. A length in VARCHAR(255) is accepted and ignored.
BOOLEAN BOOL TRUE or FALSE.
TIMESTAMP DATETIME A point in time, stored in milliseconds. Written as an ISO 8601 string or as Unix milliseconds, read as 2026-01-05T10:00:00Z.
KVREF, JSONREF The key of a key-value entry or of a JSON document with the name of the domain, see Domains and engines.

The constraints of a column are PRIMARY KEY (with AUTOINCREMENT), NOT NULL, UNIQUE, DEFAULT and REFERENCES. A table has exactly one primary key, a single column of type INTEGER or TEXT; AUTOINCREMENT needs an INTEGER primary key and assigns the next number when an insert leaves the column out. A primary key cannot be changed afterwards. DEFAULT takes a literal, or CURRENT_TIMESTAMP for a TIMESTAMP column. REFERENCES clients makes the column a link to the primary key of that table, of the same type; an insert or an update checks that the row exists.

Terminal window
sql <<'EOF'
{"sql": "CREATE TABLE purchases (id INTEGER PRIMARY KEY AUTOINCREMENT, customer_id INTEGER REFERENCES clients, status TEXT DEFAULT 'open', total REAL, active BOOLEAN DEFAULT TRUE, placed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)"}
EOF
sql <<'EOF'
{"sql": "INSERT INTO purchases (customer_id, total) VALUES (1, 42.5), (1, 19), (2, 120.0), (3, 7.9)"}
EOF
sql <<'EOF'
{"sql": "SELECT * FROM purchases"}
EOF
{ "ok": true }
{ "affected": 4, "last_pk": null }
{
  "columns": [
    { "name": "id", "type": "INTEGER" },
    { "name": "customer_id", "type": "INTEGER" },
    { "name": "status", "type": "TEXT" },
    { "name": "total", "type": "REAL" },
    { "name": "active", "type": "BOOLEAN" },
    { "name": "placed_at", "type": "TIMESTAMP" }
  ],
  "limit_applied": false,
  "row_count": 4,
  "rows": [
    [1, 1, "open", 42.5, true, "2026-11-03T22:44:34.092Z"],
    [2, 1, "open", 19.0, true, "2026-11-03T22:44:34.092Z"],
    [3, 2, "open", 120.0, true, "2026-11-03T22:44:34.092Z"],
    [4, 3, "open", 7.9, true, "2026-11-03T22:44:34.092Z"]
  ]
}

A table has at most 128 columns and 16 indexes, a domain at most rel.max_tables_per_domain tables, a text value at most 64 KiB and a row at most rel.max_row_size bytes, see Limits and defaults. The browse endpoints list tables and describe their columns, indexes and references.

SELECT reads rows

Section titled “SELECT reads rows”

A SELECT names columns or * or COUNT(*), one table, optional LEFT JOINs, and optionally WHERE, ORDER BY and LIMIT … OFFSET …. A column can have an alias with AS, a table too, and columns are qualified as alias.column.

In WHERE, a comparison is =, <>, !=, <, <=, > or >= between a column, a literal or a parameter. Besides that, a column can be tested with IS NULL, IS NOT NULL, IN (…) and NOT IN (…) with literals, and LIKE and NOT LIKE with a string literal (% for any text, _ for one character). AND, OR, NOT and parentheses combine them.

Terminal window
sql <<'EOF'
{"sql": "SELECT id, name AS client FROM clients WHERE city IN ('Lisbon', 'Tunis') AND name LIKE '%a%' ORDER BY city, name LIMIT 2 OFFSET 1"}
EOF
sql <<'EOF'
{"sql": "SELECT name FROM clients WHERE city IS NULL OR email IS NULL"}
EOF
sql <<'EOF'
{"sql": "SELECT COUNT(*) FROM purchases WHERE total >= ?", "params": [19]}
EOF
{ "columns": [{ "name": "id", "type": "INTEGER" }, { "name": "client", "type": "TEXT" }], "limit_applied": false, "row_count": 2, "rows": [[3, "Lena Fischer"], [2, "Omar Haddad"]] }
{ "columns": [{ "name": "name", "type": "TEXT" }], "limit_applied": false, "row_count": 2, "rows": [["Lena Fischer"], ["Chloé Martin"]] }
{ "columns": [{ "name": "count", "type": "INTEGER" }], "limit_applied": false, "row_count": 1, "rows": [[3]] }

Some rules to know:

  • Comparing with NULL is refused. email = NULL and a null parameter in a comparison answer 400 with the hint to use IS NULL.
  • A parameter must match the column type. A text parameter against an integer column answers 400.
  • Without ORDER BY the order is that of the access path, in practice primary-key order for a plain scan; do not rely on it. ORDER BY takes columns with ASC or DESC. Unless one indexed NOT NULL column or the primary key is sorted, the server sorts in memory, and it refuses a sort of more than rel.max_sort_rows rows.
  • The number of rows is capped. A SELECT without LIMIT returns at most 1000 rows, and no statement returns more than rel.max_limit. limit_applied is true when more rows matched than were returned. A response over 32 MiB answers 413.

LEFT JOIN combines tables

Section titled “LEFT JOIN combines tables”

LuraSQL has one kind of join, LEFT [OUTER] JOIN table ON column = column, which keeps every row of the left table and fills the columns of the right table with null when nothing matches. Up to 8 joins, or joins plus resolved references, fit in one statement.

Terminal window
sql <<'EOF'
{"sql": "SELECT p.id, p.total, c.name FROM purchases p LEFT JOIN clients c ON p.customer_id = c.id WHERE p.total > 10 ORDER BY p.total DESC"}
EOF
{ "columns": [{ "name": "id", "type": "INTEGER" }, { "name": "total", "type": "REAL" }, { "name": "name", "type": "TEXT" }], "limit_applied": false, "row_count": 3, "rows": [[3, 120.0, "Omar Haddad"], [1, 42.5, "Jane Doe"], [2, 19.0, "Jane Doe"]] }

The column of the right table in ON has to be its primary key or indexed. Otherwise the statement is refused with 400 and the statement that fixes it, unless rel.allow_unindexed_joins is on, which scans the right table for every left row, bounded by rel.max_sort_rows:

Terminal window
sql <<'EOF'
{"sql": "SELECT c.name, p.total FROM clients c LEFT JOIN purchases p ON c.id = p.customer_id"}
EOF
sql <<'EOF'
{"sql": "CREATE INDEX purchases_customer ON purchases (customer_id)"}
EOF
sql <<'EOF'
{"sql": "SELECT c.name, p.total FROM clients c LEFT JOIN purchases p ON c.id = p.customer_id ORDER BY c.id"}
EOF
join column 'purchases.customer_id' is neither the primary key nor indexed; CREATE INDEX <name> ON purchases (customer_id)
{ "ok": true }
{ "columns": [{ "name": "name", "type": "TEXT" }, { "name": "total", "type": "REAL" }], "limit_applied": false, "row_count": 5, "rows": [["Jane Doe", 42.5], ["Jane Doe", 19.0], ["Omar Haddad", 120.0], ["Lena Fischer", 7.9], ["Chloé Martin", null]] }

A reference can embed the row it points to

Section titled “A reference can embed the row it points to”

With expand, a SELECT also returns the row that a reference column points to, next to the plain result. expand takes the names of REFERENCES, KVREF and JSONREF columns, or ["*"] for all of them. For a REFERENCES column the embedded value is the row of the target table as an object:

Terminal window
sql <<'EOF'
{"sql": "SELECT id, customer_id, total FROM purchases WHERE id < 3", "expand": ["customer_id"]}
EOF
{
  "columns": [{ "name": "id", "type": "INTEGER" }, { "name": "customer_id", "type": "INTEGER" }, { "name": "total", "type": "REAL" }],
  "expanded": { "customer_id": [{ "city": "Lisbon", "email": "jane@example.com", "id": 1, "name": "Jane Doe" }, { "city": "Lisbon", "email": "jane@example.com", "id": 1, "name": "Jane Doe" }] },
  "limit_applied": false,
  "row_count": 2,
  "rows": [[1, 1, 42.5], [2, 1, 19.0]]
}

expanded holds one entry per row of the result, in order. A reference whose target is gone resolves to null: deleting a row that other rows point to is not blocked. expand works for SELECT only; with another statement it answers 400. How KVREF and JSONREF columns resolve, what happens when a target domain disappears, and why they need read permission on the target are on Domains and engines.

INSERT, UPDATE and DELETE change rows

Section titled “INSERT, UPDATE and DELETE change rows”

INSERT INTO table [(columns)] VALUES (…), (…) takes literals and parameters, no expressions. Columns that you leave out get their DEFAULT, or null, or the next number of an AUTOINCREMENT key. UPDATE table SET column = value, … [WHERE …] and DELETE FROM table [WHERE …] change the rows that match; without WHERE they change every row. The checks that fail answer with a status and a line:

Statement Answer
An insert with a primary key that exists 409: duplicate primary key in table 'clients'
A value that breaks UNIQUE 409: unique constraint violated on index 'clients_email_key'
NULL for a NOT NULL column 400: column 'name' of table 'clients' must not be NULL
A REFERENCES target that does not exist 409: REFERENCES target of column 'customer_id' is missing in table 'clients'
An update of the primary key 400: primary key 'id' of table 'clients' is immutable
A write to a view 400: 'open_totals' is a view and is not writable
Terminal window
sql <<'EOF'
{"sql": "UPDATE purchases SET status = ? WHERE customer_id = ?", "params": ["shipped", 1]}
EOF
sql <<'EOF'
{"sql": "DELETE FROM purchases WHERE total < 10"}
EOF
{ "affected": 2, "last_pk": null }
{ "affected": 1, "last_pk": null }

ALTER and indexes change the schema

Section titled “ALTER and indexes change the schema”

ALTER TABLE adds a column, renames a column, renames the table and drops a column:

Terminal window
sql <<'EOF'
{"sql": "ALTER TABLE clients ADD COLUMN vip BOOLEAN DEFAULT FALSE"}
EOF
sql <<'EOF'
{"sql": "ALTER TABLE clients RENAME COLUMN city TO town"}
EOF
sql <<'EOF'
{"sql": "ALTER TABLE clients ADD COLUMN code TEXT UNIQUE"}
EOF
{ "ok": true }
{ "ok": true }
invalid schema: ADD COLUMN does not support PRIMARY KEY/AUTOINCREMENT/UNIQUE in v1; add a UNIQUE index with CREATE UNIQUE INDEX instead

An added column cannot be a key or unique, and a NOT NULL column needs a literal DEFAULT. A column that is the primary key or has an index cannot be dropped; drop the index first. CREATE [UNIQUE] INDEX name ON table (column) indexes one column, and DROP INDEX name removes it; an index speeds up WHERE and ON on that column. DROP TABLE removes a table with its rows and indexes; it is refused while a view uses the table. A rename or a drop that a view depends on is refused with 409 as well.

A view saves a query

Section titled “A view saves a query”

CREATE VIEW name AS SELECT … stores a query under a name. A view is read like a table, including in joins and in other views, up to three levels deep. It is read-only. The body is a plain SELECT: no ORDER BY, no LIMIT, no COUNT(*) and no ? parameters, because a view yields rows and has no inputs; apply those to the outer query.

Terminal window
sql <<'EOF'
{"sql": "CREATE VIEW open_totals AS SELECT p.id, p.total, c.name FROM purchases p LEFT JOIN clients c ON p.customer_id = c.id WHERE p.status = 'open'"}
EOF
sql <<'EOF'
{"sql": "SELECT name FROM open_totals WHERE total > 100"}
EOF
sql <<'EOF'
{"sql": "DROP TABLE purchases"}
EOF
sql <<'EOF'
{"sql": "DROP VIEW open_totals"}
EOF
{ "ok": true }
{ "columns": [{ "name": "name", "type": "TEXT" }], "limit_applied": false, "row_count": 1, "rows": [["Omar Haddad"]] }
{ "ok": true }
'purchases' is referenced by view(s) ["open_totals"] and cannot be changed

GET …/views lists the views with their SQL text. A view stores the text of its query and runs it at every read.

Tables can be browsed without SQL

Section titled “Tables can be browsed without SQL”

Besides /sql, the engine has endpoints for tables and rows. They need no SQL and are useful for tools and for a first look:

Request Answer
GET /store-api/rel/{domain}/tables The tables, with links.
GET /store-api/rel/{domain}/tables/{table} The columns with type, nullability, key flags, default and references, and the indexes.
GET /store-api/rel/{domain}/views The views with their SQL.
GET …/tables/{table}/rows Rows as objects; limit, offset, expand, and every other parameter is an equality filter on the column of that name.
GET …/tables/{table}/rows/{pk} One row by primary key.
GET …/tables/{table}/count The number of rows.
POST …/tables/{table}/rows Inserts a row from a JSON object; 201 with a Location header.
PUT …/tables/{table}/rows/{pk} Sets the columns in the body and leaves the others; a primary key that does not exist is 404, never a create.
DELETE …/tables/{table}/rows/{pk} Deletes the row.
Terminal window
curl -s "http://127.0.0.1:3000/store-api/rel/orders/tables/clients/rows?town=Lisbon&limit=10" \
  -H "Authorization: Bearer $KEY"
curl -si -X POST http://127.0.0.1:3000/store-api/rel/orders/tables/clients/rows \
  -H "Authorization: Bearer $KEY" -H "Content-Type: application/json" \
  -d '{"name": "Noah", "town": "Oslo"}'
{ "limit": 10, "limit_applied": false, "offset": 0, "row_count": 2, "rows": [{ "email": "jane@example.com", "id": 1, "name": "Jane Doe", "town": "Lisbon", "vip": false }, { "email": null, "id": 3, "name": "Lena Fischer", "town": "Lisbon", "vip": false }] }
HTTP/1.1 201 Created
content-type: application/json
location: /store-api/rel/orders/tables/clients/rows/5


{"affected":1,"last_pk":5}

A filter on a column that does not exist answers 400. Views have no row endpoints; read them with /sql.

A CSV file becomes a table

Section titled “A CSV file becomes a table”

POST …/tables/from-file creates a table from a CSV or TSV file and imports its rows. You name the table and the format, csv or tsv; there is no format guessing. The first line is the header unless you pass header=false, and pk names a column to use as primary key, otherwise the table gets an INTEGER column _row that counts the lines. The server infers one type per column from its values. It needs the ddl level on the domain.

Terminal window
printf 'sku,name,price,in_stock\nA-100,Widget,9.5,true\nA-101,Gadget,12,false\nA-102,Thing,,true\n' > products.csv
curl -s -X POST "http://127.0.0.1:3000/store-api/rel/orders/tables/from-file?name=products&format=csv&pk=sku" \
  -H "Authorization: Bearer $KEY" --data-binary @products.csv
{
  "table": "products",
  "columns": [
    { "name": "sku", "type": "TEXT", "primary_key": true, "source_header": "sku" },
    { "name": "name", "type": "TEXT", "primary_key": false, "source_header": "name" },
    { "name": "price", "type": "REAL", "primary_key": false, "source_header": "price" },
    { "name": "in_stock", "type": "BOOLEAN", "primary_key": false, "source_header": "in_stock" }
  ],
  "imported": 3,
  "failed": 0,
  "errors": []
}

A row that fails is listed under errors and the import goes on. The body is limited to rel.import_body_limit_bytes bytes and held in memory as a whole.

Where LuraSQL stops

Section titled “Where LuraSQL stops”

LuraSQL leaves out what a small embedded store does not need, and says so with a 400 rather than ignoring a clause:

  • No transactions across statements, no BEGIN. One statement is one atomic step.
  • No INNER, RIGHT or FULL joins, no join on an expression, no subqueries.
  • No GROUP BY, HAVING, DISTINCT or aggregate functions other than COUNT(*), and no scalar functions or computed columns in the select list.
  • No BETWEEN, no IN with a subquery or with parameters, no -- comments.
  • One primary-key column per table, one column per index, and no foreign-key action on delete.
  • Values in INSERT and UPDATE are literals and parameters.

An error in a statement is a 400 with the position for a syntax error, 404 for a table, column or domain that does not exist, and 409 for a collision. The text is for people; read the status.

Related settings rel.enabled · rel.max_tables_per_domain · rel.max_row_size · rel.max_limit · rel.max_sort_rows · rel.allow_unindexed_joins · rel.import_body_limit_bytes · rel.lsm.max_key_length · rate_limit.default_read_iops ·

rate_limit.default_write_iops