Database

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 what THIS worker wrote, on commit (capture: statements, the default) or, on a capture: native database, any client's committed writes.
• database::query is read-oriented; use database::execute for writes. Keep reads on query: it runs them read-only, execute does not.
• 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.
• Rows come back from a RETURNING clause written into the SQL (SQLite, Postgres; MySQL has none). The returning option never adds the clause: SQLite rejects it without one, Postgres and MySQL ignore it. 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 the rows of a RETURNING clause in the SQL.
• 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.