SQL

Writes are ordinary SQLite. Live queries are a subset of SQLite SELECT, plus a WITH clause for nested results. If a query isn’t in the subset, Rindle refuses it with a message that says what to change.

# Live queries

SELECT * FROM issue
 WHERE closed = 0 AND title LIKE ?
 ORDER BY id DESC
 LIMIT 50
WITH comments OF comment ON issue_id = id (ORDER BY id DESC LIMIT 10)

One SELECT from one table. Optionally ORDER BY, LIMIT, and any number of WITH clauses.

# SELECT

• *, table.*, or a list of columns.
• A column list must include the primary key and the ORDER BY columns.
• No renaming (AS), no DISTINCT, no expressions, except counts.

# FROM

One table, optionally with an alias. No JOIN: use WITH for related rows and EXISTS to filter by them.

# WHERE

A comparison is always a column and a value. a = b between two columns, arithmetic and function calls are refused. There is no bare NOT; use !=, NOT LIKE, NOT IN, IS NOT or NOT EXISTS.

# EXISTS

Filters by related rows:

SELECT * FROM note
 WHERE owner = ?
    OR EXISTS (SELECT 1 FROM share WHERE share.note_id = note.id AND share.user_id = ?)

The subquery selects 1 or * from one table, and its WHERE ties it to the outer row with =. Qualify the outer column (note.id); an unqualified name means the inner table.

To require a number of related rows, add GROUP BY and HAVING count(*):

SELECT * FROM issue
 WHERE EXISTS (SELECT 1 FROM comment WHERE comment.issue_id = issue.id
               GROUP BY comment.issue_id HAVING count(*) >= 3)

# ORDER BY and LIMIT

ORDER BY any columns, ASC or DESC. Rindle adds the primary key to the end so the order is always total. NULL sorts lowest.

LIMIT n keeps the first n rows, and stays at n as rows come and go.

There is no OFFSET. Page from the last row you have instead:

SELECT * FROM issue
 WHERE (created, id) > (?, ?)
 ORDER BY created, id
 LIMIT 50

The columns in the tuple must be the start of an ascending ORDER BY. >= includes the row.

# WITH

Adds related rows to each result row, as a nested array:

SELECT * FROM issue
WITH comments OF comment ON issue_id = id (
  WHERE deleted = 0
  ORDER BY id DESC
  LIMIT 10
  WITH author OF user ON id = author_id
)
WITH ONE assignee OF user ON id = assignee_id

type Issue = {
  id: number;
  title: string;
  comments: { id: number; body: string; author: User[] }[];
  assignee: User | null;
};

WITH name OF table ON child_column = parent_column (…):

• name is the field on each row. OF table defaults to name.
• ON matches the related table’s column, on the left, to this row’s column, on the right. Join on several columns with AND.
• The parentheses are optional. Inside them goes the related rows’ WHERE, ORDER BY, LIMIT, and more WITH clauses, nested to any depth.
• WITH ONE gives a single row, or null, instead of an array.

WITH clauses come last. Each LIMIT applies per parent row: above, each issue gets its own 10 newest comments.

Inside the parentheses, the related table’s columns are in scope, not the parent’s.

# Counts

A live count of related rows goes in the SELECT list:

SELECT *, (SELECT count(*) FROM comment WHERE comment.issue_id = issue.id) AS comment_count
  FROM issue

sum(column) and avg(column) work the same way. Name it with AS.

# Parameters

?, ?1, or $1, numbered from 1. Values are strings, numbers, booleans and null. Write a list as one placeholder per item: IN (?, ?, ?). Named parameters are not supported in live queries.

# Not supported

JOIN, UNION, DISTINCT, OFFSET, window functions, GROUP BY, and a top-level aggregate such as SELECT count(*) FROM issue. For a count of related rows, see Counts. Run those with db.sql.execute or readOnce; they just aren’t live.

# Writes

db.sql.execute and batch take any SQLite SELECT, INSERT, UPDATE, DELETE or REPLACE, including RETURNING and WITH (a common table expression, here). Parameters are ? or :name.

Not accepted: BEGIN/COMMIT (use withTransaction), PRAGMA, ATTACH and VACUUM.

# Schema changes

Only through migrate:

• CREATE TABLE, DROP TABLE
• ALTER TABLE … ADD COLUMN, ALTER TABLE … DROP COLUMN
• CREATE INDEX, DROP INDEX

Every table needs a primary key. Renaming and changing a column’s type are not supported yet.