v0.3.0

This release closes some of the most commonly encountered gaps in the SQL dialect. Each form is new syntax that was previously rejected, so an existing statement keeps its meaning.

BETWEEN and NOT BETWEEN are accepted wherever a WHERE clause is, including in aggregates, joins, UPDATE, DELETE, and prepared statements. price BETWEEN $1 AND $2 means exactly price >= $1 AND price <= $2, with the same type checking and NULL behavior as those two comparisons, and it is inclusive at both ends. There is no SYMMETRIC form, so a range whose bounds are reversed matches nothing rather than being swapped.

A single-table SELECT can now rename its columns with AS, as aggregate and join queries already could: SELECT id AS task_id, title FROM tasks. The alias becomes the result field name, ORDER BY can refer to it, and one column may be returned under several names. As in PostgreSQL, an ORDER BY name matches an output alias before a source column of the same name. Output names must still be distinct.

SELECT DISTINCT removes duplicate rows from a single-table or join projection, which is especially useful for collapsing the rows a many-to-many join multiplies, as in SELECT DISTINCT tag.name FROM post_tags JOIN tags AS tag ON post_tags.tag_id = tag.id. NULLs compare as equal to one another, and LIMIT and OFFSET count distinct rows. As in PostgreSQL, ORDER BY must name projected columns. The projection must list its columns explicitly and cannot contain JSON columns or aggregates, and DISTINCT ON and aggregate DISTINCT, such as COUNT(DISTINCT column), remain unsupported. A column named distinct remains usable.

INSERT ... ON CONFLICT makes an upsert one atomic statement rather than an UPDATE followed by a conditional INSERT inside a transaction, which held every tab sharing the database for the duration. ON CONFLICT (id) DO NOTHING skips conflicting rows, and ON CONFLICT (id) DO UPDATE SET value = EXCLUDED.value rewrites the stored row. The target is the primary key or the columns of one unique index, and DO NOTHING may omit it to treat every unique constraint as an arbiter. Rows are handled one at a time as in PostgreSQL, so a statement that would update the same row twice fails with CONSTRAINT_VIOLATION. SET accepts literals, parameters, DEFAULT, and EXCLUDED columns, but cannot read the existing row or change its primary key, and DO UPDATE ... WHERE is not supported. RETURNING, the row count, and changed keys include only inserted and updated rows, so a statement that skips every row reports no change. See upserts.

LIKE, ILIKE, and their NOT forms match a text column against a pattern in which % matches any run of characters and _ exactly one, so a search box no longer has to filter rows in JavaScript: WHERE title ILIKE $1 with '%' + term + '%'. Backslash escapes a literal % or _ unless an ESCAPE clause names another character, and a pattern that ends in its escape character is rejected. A search term from user input should have its own %, _, and backslash characters escaped before being wrapped in wildcards. ILIKE folds only ASCII letters, as PostgreSQL does under the C locale, which is consistent with TinyJoin's code-point ordering, so 'É' ILIKE 'é' is false. Pattern matching always scans candidate rows rather than using an index.

Upgrading from v0.2.0 needs no action. Install, rebuild, and redeploy. The Worker protocol stays at version 8 and the page format stays at format 2, so an existing database opens with no migration and no OPFS namespace change. The new syntax was previously rejected, so statements that already worked keep their results.

The engine is now built with Rust 1.98.1 rather than 1.91.1. That more than offsets the new SQL, so the compressed WebAssembly engine is about 4 KiB smaller than in v0.2.0.