database

---
name: "database"
description: "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://)."
license: "Apache-2.0"
---

# 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`).
- You want to react to Postgres row-level changes once logical replication
  streaming ships (`database::row-change` trigger — see below).

## Boundaries

- Not a migration tool, ORM, or schema designer — pass raw SQL only.
- Not a general pub/sub bus — use `database::row-change` only for Postgres
  table change feeds, not for application events.
- `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` — list every configured database with its
  driver, credential-redacted connection URL, pool settings, and TLS mode.
  Config details only; no health checks or live pool statistics.

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.

## Reactive triggers

Register a `database::row-change` trigger when a function should run
automatically on Postgres INSERT/UPDATE/DELETE for specific tables — without
polling with `database::query`.

Reach for it when:

- A downstream worker or workflow must react to row mutations in near real
  time on Postgres.
- You need decoded row payloads (old/new values) from logical replication
  rather than polling an outbox table.

Do not bind when:

- The writer already has the new row in its `execute` or `transactionExecute`
  return payload.
- You are on SQLite or MySQL — this trigger type is Postgres-only.
- You need events today — v1.0.0 returns `UNSUPPORTED` on `registerTrigger`
  pending an upstream `tokio-postgres` replication API release.

### How to bind

1. Register a handler: `registerFunction('stream::on-row-change', handler)`.
2. Register the trigger:

```typescript
iii.registerTrigger({
  type: 'database::row-change',
  function_id: 'stream::on-row-change',
  config: {
    db: 'primary',
    schema: 'public',
    tables: ['orders', 'payments'],
    // optional: slot_name, publication_name — see get function info
  },
})
```

Config: `db`, `schema` (default `public`), `tables`. Slot/publication names
derive from `trigger_id` unless overridden. For event payload shape, call
`get function info` on the trigger type or handler function id.