Guides

Reading and writing data

Query with SQL, write rows, change many rows at once, and undo mistakes.

Reads are open: any single SELECT through run_sql. Writes are structured: insert_rows, update_rows, delete_rows and import_csv, which check every value against its field type. There are no SQL writes.

Reading with run_sql

One SELECT per call, with ? placeholders filled from params. Joins, aggregates, WITH and window functions work. At most 1,000 rows come back, so aggregate or page with LIMIT/OFFSET for big tables.

How values are stored:

  • checkbox: 0 or 1
  • date: YYYY-MM-DD text; datetime, created_at and updated_at: ISO 8601 text in UTC (2026-11-01T14:30:00Z), so they compare and sort as text
  • multi_select and list: JSON arrays
  • link: the linked row's id
  • relationship: no column; the pairs live in a join table named after both tables (people_organizations with people_id and organizations_id)

Useful patterns:

-- Count by status
SELECT status, COUNT(*) AS n FROM applications GROUP BY status ORDER BY n DESC

-- Rows with a tag (multi_select or list)
SELECT books.* FROM books, json_each(books.tags) WHERE json_each.value = 'fiction'

-- Follow a link
SELECT applications.role, companies.name AS company
FROM applications JOIN companies ON companies.id = applications.company

-- Through a relationship
SELECT organizations.name, COUNT(*) AS people
FROM organizations
JOIN people_organizations po ON po.organizations_id = organizations.id
GROUP BY organizations.id

-- Added in the last 7 days
SELECT * FROM applications WHERE created_at >= strftime('%Y-%m-%dT%H:%M:%SZ', 'now', '-7 days')

The metadata tables _airdash_tables and _airdash_fields can be read too, but get_database is the easier way to see the schema.

Writing rows

  • insert_rows, update_rows and delete_rows take up to 100 rows per call, and each call is all or nothing.
  • update_rows changes only the fields you send. Send null to clear a value.
  • List and multi_select values are replaced as a whole: to add an item, send the current items plus the new one.
  • Relationships take an array of ids (replaces all), or {"add": [3], "remove": [7]} to change some.
  • Write calls return counts and ids. Pass return_rows: true only when you need the rows back.

Changing many rows

Find the ids with SQL, then change them in batches of 100:

  1. run_sql: SELECT id FROM applications WHERE status = 'Applied' AND applied_on < '2026-09-01'
  2. update_rows with [{"id": 4, "status": "Rejected"}, {"id": 9, "status": "Rejected"}, ...]

The same works for delete_rows, which takes a list of ids. Confirm large deletes with the user first.

Importing files

import_csv loads up to 5,000 rows into an existing table. Pass a url to the file when there is one, so the data doesn't pass through your context. Column names are matched to field names or labels. Every row is checked before anything is written, so a bad value means nothing is imported and the error says which line and why. Rows are then written in batches of 100; if a batch still fails (for example on a unique value), the error says where it stopped, and the batches before it are kept.

Undo

restore_database rolls a whole database back to any minute in the last 30 days, for example to just before a bad import. It returns an undo_bookmark that undoes the restore. It replaces everything written after that moment, so confirm with the user first.

Limits

Writes count toward the free plan's rows written per month (inserts and updates, not deletes). Rate limits are high and only there to stop runaway loops; if you hit one, slow down rather than retrying straight away. See Free plan and limits.

Previous ← Designing a database Next Recurring imports and syncs →