Databases and SQL

A Database stores data you can read and write with SQL from the CLI or a Handler. Start by creating a small table of notes.

1. Create a table and save a note

wings db create notes
wings db exec notes "CREATE TABLE notes (id INTEGER PRIMARY KEY, text TEXT)"
wings db exec notes "INSERT INTO notes (text) VALUES (?)" --params '["Hello"]'
wings db query notes "SELECT id, text FROM notes ORDER BY id DESC LIMIT 10"

You should see a row whose text is Hello. If you already have a Database called notes, use another name for this example.

2. Save the Handler code

Save this as notes-reader.js:

export default async function (event, env) {
  return env.db.query('SELECT id, text FROM notes WHERE id = ?', [event.id])
}

3. Deploy and check the Handler

wings handler deploy --name notes-reader --file notes-reader.js --db notes
wings handler list

Run it with the ID from the list:

wings run <handler-id> --event '{"id":1}'
wings handler logs <handler-id>

You should get the first Hello row and a successful Run.

Supported SQL and alternatives

Each call runs one statement and reads at most one table. Use only tables you created. Pass values as parameters with ?, as above, instead of inserting them into the SQL text.

Unsupported SQL is refused before it runs with db_sql_forbidden. The reason explains why, and the message suggests an alternative.

• Use env.db.batch() for several statements.
• Instead of JOIN, run separate queries and combine their results in JavaScript.
• Instead of a subquery, fetch the values first and pass them to IN (?, ?).
• Use query to read rows from RETURNING. exec and batch return counts only.

Storage and time

Storage is counted across the whole account. A write that would exceed the limit is rolled back. You can still read, make writes that do not increase storage, or delete a Database.

Space freed by DELETE can be reused later. It does not necessarily reduce the displayed storage size. After a downgrade, the oldest Databases up to the plan's count remain writable. The rest are read-only.

There is no separate daily time allowance for Databases. Operations from the CLI and Console also use your daily time allowance. If an operation has to wait or is paused, follow the message returned with db_time_quota_exceeded.

SQL reference

The lists below describe supported statements, functions, and limits. The three steps above are enough to try your first reads and writes.

Statements and functions

Supported statements include SELECT, INSERT, REPLACE, UPDATE, DELETE, CREATE TABLE, CREATE INDEX, ALTER TABLE, DROP, and EXPLAIN. SELECT can read one table, or json_each() / json_tree(). Aggregates support DISTINCT, GROUP BY, and HAVING. Supported Window functions accept OVER (PARTITION BY ... ORDER BY ...).

Aliases follow the rules for the original function. substring uses substr, ceiling uses ceil, and power uses pow. format uses printf and string_agg uses group_concat, so those aliases are also rejected.

Expression and row limits

trim, ltrim, rtrim, and unhex accept one argument only. instr requires a text literal of up to 64 characters as its second argument. LIKE / GLOB patterns are limited to 50 bytes. Text concatenation uses ||, concat, or concat_ws.

Exceeding an expression limit returns reason: expression_limit. For multi-column search, maintain one search column in the Handler and apply one LIKE to it. Generate IDs in the Handler with crypto.randomUUID().

Table definitions and foreign keys

Generated columns, expression indexes, and partial indexes are not supported. CHECK allows comparisons of columns or simple functions with constants, among other simple expressions. For example, CHECK (length(name) <= 100) works. IN, BETWEEN, NULL tests, and NOT / AND / OR are also supported.

CHECK allows length, octet_length, typeof, abs, lower, upper, and single-argument trim, ltrim, and rtrim. A statement's CHECK expressions can use 32 comparisons, 8 function calls, and 4 comparisons between column or expression values. Legacy tables that exceed these restrictions remain readable but cannot be written. Remove the index or copy the data to a new table. ALTER TABLE ... ADD COLUMN also counts as a write for this restriction.

Foreign-key child columns need indexes matching the parent key's type and collation. For example, use an INTEGER child column for an INTEGER parent key. The foreign_key_index message names the required CREATE INDEX. Deferred foreign keys are not supported.

Unsupported features

Reading or writing a VIEW is rejected, as is writing to a table with a TRIGGER. A normal table is not made read-only just because a VIEW refers to it. Use DROP to remove legacy views or triggers.

JOIN, WITH, subqueries, compound SELECT, aggregate Window functions, first_value, last_value, nth_value, named Windows, and Window frames are not supported. Neither are virtual tables, PRAGMA, ATTACH, VACUUM, ANALYZE, REINDEX, or transaction statements. Names starting with sqlite_ or _cf_ are reserved. Built-ins such as sqlite_schema, sqlite_master, sqlite_stat1, sqlite_sequence, and pragma_* cannot be read.

These functions are also unsupported:

json_group_array, json_group_object, jsonb_group_array, jsonb_group_object, group_concat, printf, replace, hex, quote, randomblob, zeroblob, json_pretty, json_patch, jsonb_patch