Open a SQLite database or inspect the current one.

Open (# Database with path):

# Database
path: /path/to/mydb.db

If the file does not exist, it is CREATED as an empty database and the response contains created: true.

Inspect (# Database with no fields):

# Database

Returns path, table count, and a list of all tables.

Frontmatter policy: observable tolerance — unknown keys are echoed in the response as 'ignored-keys: ...'.

Read records or schema via a JMD document (https://github.com/ostermeyer/jmd-spec).

Data (# Label): exact-match lookup.

# Order
id: 42

Query (#? Label): QBE filter; optional pagination, projection, aggregation, and JOIN.

#? Order
status: pending

Schema (#! Label): describe the table — columns, types, modifiers, and any sub-section constraints.

#! Order

Reserved DDL-object labels (dispatch to per-kind shape unless a real table by that name exists):

#! Database              list tables, indexes, triggers, views
#! Index / name: foo     index: table, columns, unique, where
#! Trigger / name: foo   trigger: when, event, condition, body
#! View / name: foo      view: select

QBE operators in #? bodies: = equality (default)

greater than >= greater or equal < less than <= less or equal | alternation (OR), e.g. 'Germany|France' ~ substring (case-insensitive), e.g. '~Corp' ^ regex (implicit full-match anchoring) ! negation, composable with any operator

Frontmatter keys: page-size, page pagination count return only the row count select projection (comma-separated columns) join cross-table JOIN, e.g. 'Table on Col' sum, avg, min, max aggregations with optional 'as alias' group, having, sort GROUP BY / HAVING / ORDER BY

Frontmatter policy: observable tolerance — unknown keys are echoed back as 'ignored-keys: …'. Debug: 'debug: sql, timing, table, plan, filters, resolved, coercions' (composable; 'debug: true' enables all).

Write a record or define a DDL object (https://github.com/ostermeyer/jmd-spec).

Data (# Label): INSERT OR REPLACE one record.

# Order
id: 42
status: shipped
total: 149.99

Bulk insert (# Label[]):

# Order[]
- id: 42
  status: shipped
- id: 43
  status: pending

Schema (#! Label): full SQLite DDL surface.

Per-column types: integer, float, string, boolean (int in SQLite). Per-column modifiers: readonly primary key (single-col); do not supply on insert optional NULL allowed = DEFAULT value (number, string, CURRENT_TIMESTAMP) a|b|c enum form — generates a CHECK (col IN (…))

#! Order
id: integer readonly
status: pending|shipped|cancelled
total: float = 0
customer_id: integer

Table-level constraints as sub-sections:

primary-key[] composite PK (column names)

unique[] UNIQUE constraints (each entry one or more cols)

check[] raw CHECK expressions

references[] single-col FKs in form 'local: Table.foreign'

Inline DDL objects under #! Table (created atomically with the table):

Index[] name, columns, optional unique / where

Trigger[] name, when, event, body, optional condition

Top-level DDL-object documents:

#! Index
name: idx_order_status
table: Order
columns: status
unique: false
where: deleted_at IS NULL

#! Trigger
name: trg_log_orders
table: Order
when: AFTER          # BEFORE | AFTER | INSTEAD OF
event: INSERT        # INSERT | UPDATE [OF cols] | DELETE
condition: NEW.status = 'pending'
body: "INSERT INTO Log(msg) VALUES ('x');"

#! View
name: PendingOrders
select: "SELECT id FROM \\\"Order\\\" WHERE status='pending'"

Virtual tables — using: <module> modifier on #! Table:

#! Notes
using: fts5
title: string
body: string
## unindexed[]
- memory_id
## options[]
- "tokenize = 'porter unicode61'"

Existing tables: writes are additive — only new columns are added. Constraint or column-type changes need 'action: rebuild' frontmatter, which runs the SQLite table-rebuild dance atomically (data preserved over the column intersection; pre-existing dependent indexes and triggers must be redeclared inline in the rebuild document).

Frontmatter policy: observable tolerance. Frontmatter keys: action (= 'rebuild'), debug. Debug: 'debug: sql, timing, table'.

Delete records or drop a DDL object (https://github.com/ostermeyer/jmd-spec).

Delete (#- Label): filter-based delete (≥1 field required).

#- Order
id: 42

Bulk delete (#- Label[]): primary-key list (or objects for composite keys).

#- Order[]
- 42
- 43

Schema (#! Label): drop a DDL object. Each kind needs its own confirm-key:

Tables / views (legacy): confirm: drop-table

#! Order

Indexes: confirm: drop-index

#! Index
name: idx_order_status

Triggers: confirm: drop-trigger

#! Trigger
name: trg_log_orders

Views (preferred over the legacy drop-table path): confirm: drop-view

#! View
name: PendingOrders

Frontmatter policy: strict refusal — unknown keys cause an error. Accepted keys: confirm, debug. Debug: 'debug: sql, timing, table'.