JSON Columns
Query and update JSON columns with dotted paths and SQLite JSON functions
Schema
Declare a column as type: 'json' in the schema (or json in the DSL) and the ORM treats it as a first-class JSON document. Whole-object writes are stringified, reads are parsed back, and dotted keys against JSON columns compile to SQLite JSON SQL.
const schema = defineSchema({
name: 'app',
version: 1,
tables: {
events: {
id: 'integer primary key',
kind: 'text not null',
payload: 'json',
},
},
});
Reads
Dotted keys become ->> expressions:
// payload is { user: { tier: 'pro' } }
await db.events.where({ 'payload.user.tier': 'pro' }).many();
// SELECT * FROM events WHERE payload ->> '$.user.tier' = ?
await db.events.where({ 'payload.score': { gte: 50 } }).many();
// SELECT * FROM events WHERE payload ->> '$.score' >= ?
await db.events.where({ 'payload.tier': { in: ['pro', 'enterprise'] } }).many();
// payload ->> '$.tier' IN (SELECT value FROM json_each(?))
await db.events.orderBy({ 'payload.priority': 'desc' }).many();
// ORDER BY payload ->> '$.priority' DESC
await db.events.select(['id', 'payload.user.email']).many();
// SELECT id, payload ->> '$.user.email' AS "payload.user.email" FROM events
Array indexing works inline:
db.events.where({ 'payload.tags[0]': 'urgent' });
// payload ->> '$.tags[0]' = ?
Writes
Dotted keys become json_set calls:
await db.events.where({ id: 1 }).update({ 'payload.tier': 'pro' });
// UPDATE events SET payload = json_set(payload, '$.tier', ?) WHERE id = ?
Multiple paths on the same column merge into a single json_set call:
await db.events.where({ id: 1 }).update({
'payload.tier': 'pro',
'payload.seats': 5,
});
// UPDATE events SET payload = json_set(payload, '$.tier', ?, '$.seats', ?) WHERE id = ?
Object and array values are bound as JSON via json(?) so SQLite stores them as nested JSON, not literal strings:
await db.events.where({ id: 1 }).update({ 'payload.flags': { beta: true } });
// UPDATE events SET payload = json_set(payload, '$.flags', json(?)) WHERE id = ?
// bound: '{"beta":true}'
Whole-object writes still work and replace the entire blob:
await db.events.where({ id: 1 }).update({ payload: { whole: 'replace' } });
// UPDATE events SET payload = ? WHERE id = ?
Why this matters
Without surgical JSON SQL, filtering on payload.tier would require pulling every row's payload, parsing it in your Worker, then filtering in JavaScript. With first-class JSON support, SQLite does the work and only matching rows cross the wire.