HHY Database 1.0: Building Database Access for Long-Running Applications

After working on HHY Web Runtime (Chinese article), I started looking more closely at the database layer.

A handler can remain loaded and a route can return JSON, but a real CMS soon asks harder questions. Can saving an article also save its revision atomically? Will exporting hundreds of thousands of records keep consuming memory? When a request times out, has its database write actually stopped?

Those questions shape what I want HHY Database 1.0 to mean: an application should be able to understand the outcome of an operation and know who is responsible for closing its connection, transaction, and result set.

DB 1.0.0 shipped on September 7, 2026 alongside HHY 1.5.0. The database extension is independently versioned. Packages support macOS arm64 and Linux arm64/x86_64; the Windows Runtime archive does not include DB. Transaction callbacks, scoped resources, and database Streams require HHY 1.5.0 or later. Runtime release notes

This article reviews the shipped implementation and its tradeoffs, checked against repository snapshot 3af2f67. Performance figures come from the existing acceptance record, not a new benchmark run performed while writing this article.

From four calls to managed resource lifetimes

Version 0.2.0 used a C extension with libpq and the MySQL client library, exposing ping, query, execute, and transaction. Value binding already existed: PostgreSQL used $1; MySQL used ? with native statement binding.

Version 1.0 preserves those four entry points and the default String/null result contract. It adds structured data sources, bounded pools, read/write transactions, savepoints, reusable statements, batches, incremental cursors, exact types, and cancellation cleanup. Extension changelog

I particularly like that compatibility does not require hiding every distinction. Existing scripts keep their result format. New code explicitly selects typed: true or Stream. Remote connections use authorized configuration and a TLS policy instead of quietly ignoring unfamiliar URL options.

The meaningful change is that connections, transactions, cursors, and statements now have lifetimes that can end, expire, and be cleaned up. Those are guarantees a persistent service needs.

Compare responsibilities across Go, Python, and PHP

A database driver is not the language itself. The Go comparison is database/sql plus a driver. Python means DB-API plus a driver and pool component. PHP means PDO plus its database driver. Calling all three a language-provided connection pool obscures meaningful differences.

These distinctions are defined in the respective interfaces and documentation. Go connections, Go transactions, PEP 249, Psycopg Pool

PDO persistent connections allow reuse, but their lifecycle depends on the process model. They are not interchangeable with Go's concurrent pool. Reuse also makes session cleanup important. PDO connection management

I did not reproduce three sets of APIs in HHY. The useful comparison is whether ordinary application work gets explicit, testable behavior for connections, transactions, types, and errors.

A pool manages borrowing and return

Short connections repeatedly pay for connection establishment, authentication, and potentially TLS negotiation alongside a small query. Reuse reduces that repeated work. Keeping connections in an array does not, by itself, produce a reliable pool.

The 1.0 pool follows an explicit lifecycle:

Wait for capacity → Borrow → Exclusive use → Clean session → Return
                                                └→ Uncertain state → Discard

Before return, the implementation must deal with unconsumed results, unfinished transactions, and modified session settings. A request should not silently inherit a previous request's time zone or transaction state.

Pool identity also cannot be just the host. Database, user, TLS policy, and credential version affect whether a connection is suitable for reuse. Sharing a pool across incompatible identities turns a performance optimization into an isolation problem.

I care more about identifying connections that must not return than about minimizing every connection close. Discarding an uncertain connection costs another handshake, but avoids passing a failure to the next borrower.

Version 1.0 can serve up to eight concurrent protocol requests per extension, with at most 64 queued calls. Each data-source pool defaults to four open connections and permits 1–32; the extension also has a 64-native-session aggregate limit.

A single HHY call remains synchronous. Web concurrency comes from workers, and database calls are not advertised as freely transferable HHY parallel-closure work. Protocol concurrency, pool capacity, and language-level concurrency are separate limits. Connection and concurrency contract

With multiple workers, connection budgets multiply

After introducing multiple Web Runtime workers, I cannot size a database pool by looking at one process.

Consider two application instances, four workers each, one database extension instance per worker, eight connections per data-source pool, and two data sources:

Total connection budget = 2 × 4 × 1 × 8 × 2 = 128

This is an illustrative aggregate budget, not a default configuration. The connections may target different servers; each server's capacity must be checked against the pools that actually connect to it. Overlapping old and new instances during deployment, migrations, and administrative scripts add to the budget.

HHY 1.5.0 lazily starts each Web worker's extension after fork, without sharing parent pipes or database sockets. Saturation has bounded waiting, with DB_POOL_TIMEOUT and DB_QUEUE_FULL distinguishing failure modes. MySQL cancellation also uses a short-lived control connection, which needs additional server capacity. Concurrency and cancellation

A small pool also exposes an easy deadlock: a transaction owns the only connection, then code inside it issues a query through the ordinary pool. It waits for the connection it already holds. Go's documentation explicitly describes the lock-like waiting relationships introduced by connection limits. Go connection management

Transaction operations should therefore use their bound connection rather than borrow again.

Transactions must keep business decisions on one connection

Publishing an article can involve reading its version, checking for another editor's changes, updating the content, inserting a revision, and committing.

A fixed SQL list works when every operation is known in advance. Reading and branching require a scoped transaction.

The following simplified control flows show the same idea in three ecosystems. They omit connection setup, application SQL, and complete error handling; they illustrate ownership rather than provide deployable application code:

// Go: all transaction work goes through tx.
tx, err := db.BeginTx(ctx, nil)
if err != nil { return err }
defer tx.Rollback()

if err := updateArticle(ctx, tx); err != nil { return err }
return tx.Commit()

# Psycopg Pool: the borrowed connection scopes the transaction.
with pool.connection() as conn:
    update_article(conn)
# On normal exit, an open transaction commits; on exception, it rolls back.

// PDO: use the same connection and explicitly handle failure.
$pdo->beginTransaction();
try {
    updateArticle($pdo);
    $pdo->commit();
} catch (Throwable $error) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $error;
}

Go supplies a transaction object, Psycopg Pool supplies a connection context, and PDO exposes explicit transaction methods. Attempting rollback in a catch block does not establish that a COMMIT whose response was lost failed. Go transactions, Psycopg Pool, PDO transactions

HHY crosses an additional process boundary: the business function runs in the host, but the connection belongs to the extension. Version 1.5.0 keeps the closure in the host and uses with_transaction to bind its database operations to one transaction resource.

This is supported MySQL transaction syntax in 1.0. config is trusted application configuration, never supplied by an HTTP request; parameter values are illustrative:

import database

let before = config |> database.with_transaction { tx ->
    let current = database.query(tx,
        "SELECT balance FROM accounts WHERE id = ? FOR UPDATE", [1])
    database.execute(tx,
        "UPDATE accounts SET balance = balance - ? WHERE id = ?", [10, 1])
    current.rows
}

The trailing callback belongs to pipeline syntax, not database.with_transaction(config) { ... }. Normal callback completion commits; failure rolls back. Every operation uses tx. This illustrates scope; a real debit also needs balance, account-state, and idempotency checks. Transaction API

Resource tokens are randomly generated by the extension and bound to the host request scope. They cannot be transferred across requests or workers or serialized as durable IDs. Transactions and cursors retain leases tied to their initial operation deadline; closed resources become invalid.

Web requests and embedded hhy_call boundaries clean up leaked database resources on success and failure. Recovery belongs to request execution rather than timely garbage collection. When a connection is lost, attempting rollback still cannot prove that the database returned to its previous state. Host integration

Savepoints also need their actual database semantics. They allow an outer transaction to return to an intermediate point; they are not independently committed nested transactions.

Streaming must start at the database read

HHY's Flow and Stream abstractions fit an export pipeline well. But it is easy to build a superficially streaming interface: buffer the complete result in the driver, wrap it in a Stream, and let the application iterate.

That changes consumption syntax without changing peak memory.

The 0.2.0 analysis identified complete-result buffering. Version 1.0 retains a bounded ordinary query, but provides incremental cursors and a lazy Stream for larger results. Ordinary queries return at most 10,000 rows and may hit the byte budget earlier; callers must inspect truncated.

The lazy database Stream propagates demand upstream:

HTTP client can receive more
    → Output requests another batch
    → HHY Stream requests fetch
    → Extension reads a bounded database batch
    → Transform and write, then wait for further demand

Each batch needs row, byte, and field-size limits, as well as an overall execution budget. One large text row can outweigh a thousand small records. Protocol v1's 1 MiB line limit means that JSON and binary-encoding expansion also belong in the budget.

fetch defaults to 100 rows. Fields are limited to 64 KiB, with approximately 128 KiB decoded and 256 KiB encoded row or batch budgets. These limits apply together; hexadecimal binary encoding also consumes protocol capacity.

This is not a promise that arbitrary data can never exceed a transient memory target. The README explicitly notes that libpq can receive a large row before the field limit is checked. Streaming memory depends on the batch and the largest incoming driver row, not just the rows retained afterward. Result limits

Early take, exceptions, and request completion trigger cleanup. An unfinished or poisoned connection cannot become idle pool capacity. Early result termination inside an owning transaction can invalidate the transaction and require rollback, so callbacks must consume their Streams before returning.

Streaming also has a cost: slow consumers occupy connections and can extend transaction snapshots. Deadlines, export concurrency budgets, and sometimes separate background jobs remain application concerns. Stream lifecycle

Exact types matter more than convenient numeric conversion

Returning strings or null has one practical advantage: the implementation has not casually converted exact decimals to floating point. Applications still need to distinguish money, identifiers, dates, and ordinary text.

Version 1.0 enables typed results explicitly with typed: true. Representable scalars become HHY Int/Float/Bool. Decimal, oversized integers, temporal values, and JSON retain explicit type / value representations. Type contract

For example, converting 9007199254740993 through binary64 can lose information that converting back to a string cannot recover. A protocol's convenient universal number type can accidentally change a business identifier.

Generated identifiers belong to the operation that produced them. MySQL results must come from the corresponding write connection; PostgreSQL can express the result through RETURNING. Borrowing an arbitrary connection afterward to ask what was just generated is not a sound abstraction.

Affected rows deserve equal care. The current MySQL configuration uses CLIENT_FOUND_ROWS. Matching a row whose values did not change is different from modifying a row. A common access interface should not erase that distinction.

Before retrying, establish whether retry is justified

One error category I particularly value is DB_COMMIT_UNKNOWN.

Suppose the database receives COMMIT and completes it, but its acknowledgment is lost. The application sees a timeout. That timeout alone cannot establish that the transaction failed.

Automatically replaying the write could create another revision, decrement stock again, or duplicate a business record.

Version 1.0 distinguishes failure stages; applications must use that information when choosing a response:

The extension does not replay application SQL after errors. Connection loss or timeout during commit reports DB_COMMIT_UNKNOWN, not a successful rollback. Transaction and error semantics

Cancellation has the same distinction. A timeout received by the caller means it stopped waiting, not necessarily that the database stopped executing. Go Context provides a way to propagate cancellation; HHY 1.5.0 connects Runtime cancellation with its database extension. Go database cancellation

MySQL cancellation uses a separate short-lived control connection. libpq 17+ uses asynchronous cancellation; older versions use the legacy API. Failed cancellation can guarantee connection disposal, not immediate completion of server-side work. Applications must reconcile uncertain writes.

Timeouts also participate in resource leases. Native HHY Duration values convert to milliseconds, making timeout_ms: 5s valid configuration. The option name contains _ms, but application code can still use the language's time units. MySQL native connection and socket settings retain second-level granularity, so a common option does not imply identical driver precision. Time and cancellation contracts

Cloud connections and a CMS make the abstractions concrete

Remote sources now use structured configuration with explicit host:port authorization through allow. Legacy URLs remain loopback-only. Authorization comes from trusted script configuration, not an OS network sandbox. HTTP requests must not directly supply credentials, SQL, or an allow-list. Data-source configuration

TLS distinguishes required, verify_ca, and verify_identity, among other modes. Identity verification is the default, and verification failure does not fall back to plaintext. Builds using the MariaDB client support identity verification but reject CA-only verification because the client interface differs.

RDS can be configured as a remote source, but actual RDS networking, authentication, and failover acceptance still awaits a dedicated instance. Shipping remote connectivity is different from certifying a particular cloud deployment. Connection boundaries

The CMS install.hhy workflow tests another boundary. The driver executes operations and reports reliable outcomes. The installer owns migration order, progress records, recovery, and configuration writes.

Some MySQL DDL implicitly commits, so wrapping every table-creation statement in a transaction cannot guarantee atomic installation rollback. The installer should record completed steps and verify them before resuming. MySQL implicit commits

This first application also helps constrain scope. DB 1.0 should make ordinary MySQL and PostgreSQL access dependable. ORMs, sharding, and distributed transactions do not belong in the driver merely because other ecosystems offer them.

The evidence I find most useful

The acceptance record covers pool, transaction, cursor, and type behavior against MySQL 8.4 and PostgreSQL 17, plus AST/Bytecode execution, two prefork workers, abandoned-resource cleanup, TLS negative cases, and extension crash recovery. CMS tests use a minimal installation fixture covering interruption, repeated installation, failed upgrades, and logical backup/restore; a complete CMS is not bundled with the extension. Acceptance record

The local macOS incremental-result test used a 64-byte payload plus an ID per row:

For both paths, ten times as many rows increased measured extension peak RSS by only 229,376 bytes, or 224 KiB, on this workload. That is more informative about incremental consumption than a standalone rows-per-second figure. It measures the extension, not the complete Web service, and does not guarantee the same behavior for arbitrary row widths or query plans. Measurements and reproduction

A separate 300-second local single-client test completed 1,583,501 SELECT iterations on MySQL and 2,752,244 on PostgreSQL. Each ended with one open connection and zero pinned or in-use connections. This supports connection reuse and return behavior; it is neither multi-worker CMS throughput nor a Go/Python/PHP comparison.

A 24-hour soak and real RDS failover validation remain outstanding. Cross-language performance work still needs equivalent databases, SQL, indexes, TLS, network conditions, and aggregate connection budgets, with process models disclosed. Acceptance scope

The opportunity in HHY Database 1.0 is to connect Flow, Stream, structured errors, and isolated extensions to database semantics that survive long-running application work. A successful query is easy to demonstrate. Understanding what happened after a pool fills, an export stops, or a commit response disappears is what would make me comfortable attaching real business data.

References and baseline

DB 1.0.0 shipped on September 7, 2026 alongside HHY 1.5.0. This article starts from the earlier design document and checks the delivered scope against the formal documentation and acceptance record at repository snapshot 3af2f67f8a889afd65a0215dd5423430e7ea677e.

• HHY 1.5.0 release notes

• DB 1.0 API, platforms, and resource limits

• Database changelog, acceptance data and limits

• Go: connections, transactions, cancellation

• Python: PEP 249, Psycopg Pool

• PHP: PDO connections, transactions, prepared statements

• MySQL: statements that cause implicit commits