---
canonical: "https://agi-labo.com/en/tools/wings/docs/databases-sql"
title: "Databases and SQL"
description: "Create a Database and read or write it with supported SQL."
section: "Core features"
order: 8
pageType: "how-to"
lastVerifiedVersion: "1.4.16"
lastVerifiedAt: "2026-10-15"
draft: false
surfaces: ["cli", "console", "api"]
plans: ["free", "member"]
---

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

```sh
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`:

```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

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

Run it with the ID from the list:

```sh
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](/en/tools/wings/docs/run-duration).
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 ...)`.

| Family | Functions |
| --- | --- |
| Scalar | `abs`, `changes`, `char`, `coalesce`, `concat`, `concat_ws`, `glob`, `ifnull`, `iif`, `instr`, `last_insert_rowid`, `length`, `like`, `likelihood`, `likely`, `lower`, `ltrim`, `nullif`, `octet_length`, `random`, `round`, `rtrim`, `sign`, `substr`, `total_changes`, `trim`, `typeof`, `unhex`, `unicode`, `unlikely`, `upper` |
| Date | `date`, `time`, `datetime`, `julianday`, `unixepoch`, `strftime`, `timediff` |
| Math | `acos`, `acosh`, `asin`, `asinh`, `atan`, `atan2`, `atanh`, `ceil`, `cos`, `cosh`, `degrees`, `exp`, `floor`, `ln`, `log`, `log10`, `log2`, `mod`, `pi`, `pow`, `radians`, `sin`, `sinh`, `sqrt`, `tan`, `tanh`, `trunc` |
| JSON | `json`, `json_array`, `json_array_length`, `json_error_position`, `json_extract`, `json_insert`, `json_object`, `json_quote`, `json_remove`, `json_replace`, `json_set`, `json_type`, `json_valid`, `jsonb`, `jsonb_array`, `jsonb_extract`, `jsonb_insert`, `jsonb_object`, `jsonb_remove`, `jsonb_replace`, `jsonb_set` |
| Aggregate | `count`, `sum`, `total`, `avg`, `min`, `max` |
| Window | `row_number`, `rank`, `dense_rank`, `percent_rank`, `cume_dist`, `ntile`, `lag`, `lead` |

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`.

| Item | Limit |
| --- | --- |
| Rows scanned per operation | 10,000 |
| Rows returned per operation | 1,000 |
| LIKE / GLOB / like() / glob() / instr() uses per statement | 2 |
| Text concatenations per statement | 8 |
| Function calls per statement, including `->` / `->>` | 16 |
| Comparisons between column or expression values per statement | 16 |
| Values in one IN | 100 |
| WHEN branches in one CASE | 32 |
| Pattern matches across a batch | 4 |
| Text concatenations across a batch | 16 |
| Function calls across a batch | 64 |
| Comparisons between column or expression values across a batch | 64 |

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`