| name | database |
| description | Run SQL against PostgreSQL, MySQL, or SQLite from the iii engine — reads, writes, transactions, and prepared statements over managed connection pools. |
database
The database worker connects to PostgreSQL, MySQL, and SQLite through a
managed per-database connection pool. Every callable surface lives under
the database::* namespace. The driver is chosen from each database URL
scheme (sqlite:, postgres://, postgresql://, mysql://).
Runtime settings live in the configuration worker under id database;
pools hot-reload when the value changes. SQLite is the recommended starting
point. Placeholder syntax: ? for SQLite and MySQL, $1/$2/… for Postgres.
When to Use
- You need to read rows from a configured database (
database::query).
- You need to insert, update, delete, or run DDL and read affected-row
counts or autoincrement ids (
database::execute).
- Several statements must commit or roll back together as one unit
(
database::transaction, database::executeBatch, or the interactive
transaction surface).
- The same parameterized SQL will run many times and you want to skip
per-call parse/plan cost (
database::prepareStatement +
database::runStatement).
- You need read-your-writes across round-trips with logic between steps
(
database::beginTransaction … commitTransaction / rollbackTransaction).
Boundaries
- Not a migration tool, ORM, or schema designer — pass raw SQL only.
- Not a general pub/sub bus.
database::row-changed reports only what THIS
worker wrote, on commit — not change data capture; a write from psql or
another worker is invisible to it.
database::query is read-oriented; use database::execute for writes.
Running a SELECT through execute discards rows.- Prepared handles pin a pool connection until TTL expiry — not transactions.
Batch
database::transaction / database::executeBatch need every
statement up front; use the interactive surface when code must branch
between steps.
- MySQL ignores the
returning option on execute (warn-once). SQLite
degrades read_committed / repeatable_read isolation to serializable.
- For filesystem or shell operations, use the
shell worker instead.
Functions
database::query — run read-only SQL and return rows, row count, and
column metadata.database::execute — run write SQL (INSERT/UPDATE/DELETE/DDL) and
return affected rows, optional last insert id, and optional RETURNING rows.database::executeBatch — convenience form of transaction: statements
may be bare SQL strings or {sql, params} objects (prefer params for
dynamic values). Same atomic semantics, envelope, and failed_index
reporting as transaction.database::prepareStatement — parse and plan SQL once; return a handle
that pins a pool connection until TTL expiry.database::runStatement — re-execute a prepared handle with new bind
params; response shape matches query.database::transaction — run an ordered batch of statements atomically;
rolls back on first failure and reports failed_index.database::beginTransaction — open an interactive transaction and
return an id plus expiry deadline.database::transactionQuery — read SQL inside an open interactive
transaction; same envelope as query.database::transactionExecute — write SQL inside an open interactive
transaction; same envelope as execute. Rejects bare transaction-control
SQL — finalize via commitTransaction or rollbackTransaction.database::commitTransaction — commit and finalize an interactive
transaction.database::rollbackTransaction — roll back and finalize an interactive
transaction.database::listDatabases — every configured database with its driver,
credential-redacted URL, pool settings and TLS mode. Config only; use
database::health for live state.
Interactive transactions auto-roll back when timeout_ms elapses (default
30 s, max 5 min). Prepared handles default to a 1 h TTL (max 24 h) with no
explicit release call — let them expire or stop using them when done.
Reading the schema
One shape across all three drivers — prefer these over hand-writing
sqlite_master / information_schema / PRAGMA.
database::listTables — tables and views, with kind and (postgres) schema.database::describeTable — columns with type, nullability, default, primary
key and a structured foreign_key of { schema, table, column }; plus
indexes and a planner row estimate.database::describeSchema — the same for every table in one pass. Use this
rather than looping describeTable.database::schemaDiagram — positioned nodes, routed foreign-key edges, hub
degree and isolated tables. For reasoning about a schema's shape, not
only for drawing it.
Reading data
database::browseTable — paged, sorted, filtered reads with no SQL. Filters
are { column, op, value } and total honours them. Follow a foreign key
with an equality filter at page_size: 1.database::explain — the plan as a tree with costs and warnings. analyze
runs the statement, so it defaults to false and is refused for anything
that is not a single read.database::columnStats — planner statistics by default (approximate,
labelled source: planner); exact: true scans. To profile rows you already
hold, use the fp worker on a browseTable result instead.
Operations and reuse
database::health — pool occupancy, active queries, table sizes, locks,
cache ratio. Each section is available, unsupported or denied, so a
driver gap is never mistaken for an empty result.database::terminateQuery — end a session, or cancel its statement with
cancel_only. Takes an id from database::health.database::saveQuery, database::listSavedQueries,
database::deleteSavedQuery — named queries per database, kept in the
state worker.database::history — recent queries, newest first. Best effort, not an audit
log; bind database::row-changed for that.
Reacting to writes
Register a database::row-changed trigger to be told when this worker commits
a change, instead of polling:
{ "trigger_type": "database::row-changed", "config": { "db": "primary", "table": "orders", "ops": ["insert"] } }
The event is { db, table, op, affected_rows, returning?, at }. It fires on
commit — an interactive transaction's writes are announced by
commitTransaction, and a rollback announces nothing. table is null when the
statement's table cannot be read off the SQL (a CTE-wrapped write), and
runStatement does not fire because it has no affected-row count to report.
Delivery is best-effort: it is not durable with the commit and has no replay or
exactly-once guarantee.
