# Connection pool

A web app should not open a connection for each request. `DbPool` (`import DbPool from "pkg/db/db_pool"`) keeps a few connections open and lends one to a block of code at a time, on top of the [generic pool](https://agentica-lang.github.io/agentica-db/production/generic-pool/index.md). The block gets a `Db.Connection`, so `Db`, `Orm` and `Migrate` work in it as always.

```agentica
import Db from "pkg/db/db"
import DbPool from "pkg/db/db_pool"
import Orm from "pkg/db/orm"
import Sqlite from "pkg/db/sqlite"

struct User
  id: Int = 0
  name: String
end

def main() -> Void
  users = Orm.schema("users", User.codec)
  pool = DbPool.start(DbPool.Options(url: "sqlite::memory:", drivers: [Sqlite.driver()])).unwrap!
  setup = DbPool.with(pool) do |conn|
    try Db.execute(conn, "create table users (id integer primary key, name text not null)", [])
    try Db.execute(conn, "insert into users (name) values (?)", [Db.Value.Text("Ana")])
    return ok()
  end

  setup.unwrap!
  found = DbPool.with(pool) do |conn| Orm.find(conn, users, Db.Value.Int(1)) end
  puts(found)
  DbPool.close(pool, 1000)
end
```

```output
ok(some(User(id: 1, name: "Ana")))
```

Start the pool once, in a task that lives as long as it does, such as `main`: each connection is opened in a task of the pool of its own, which owns it, and the pool ends with the task that started it. Hand the `pool` value to the tasks that serve requests. In a web handler, one `DbPool.with` is one request: `with` waits in line for a connection, runs the block, and gives the connection back. A task that has used the pool counts its crash as observed, because the pool watches its callers: a run whose only unobserved crash is in such a task exits 0 and prints no "task crash was not observed" summary (the crash itself still prints). The fix belongs in the language ([agentica-lang/agentica#1522](https://github.com/agentica-lang/agentica/issues/1522)).

## Options

`DbPool.start(DbPool.Options(url: ..., drivers: ...))` opens `size` connections at once, so a wrong url or a database that is down fails `start`, not the first request. `url` and `drivers` are what [`Db.open`](https://agentica-lang.github.io/agentica-db/basics/connecting/index.md) takes. The other fields:

- `size` (0): how many connections. 0 takes the size of the database: 4 for SQLite, 10 for PostgreSQL and MySQL or MariaDB. A private in-memory SQLite database (`sqlite::memory:`, `sqlite:file::memory:` or a `file:` name with `mode=memory`) has one connection, and a `size` or `max_overflow` that asks for more is refused with `db error: a pool of a private in-memory database can have one connection only` (each connection would be an empty database of its own). With `cache=shared` in the name (`sqlite:file:app?mode=memory&cache=shared`) every connection sees one database, and the size is free.
- `max_overflow` (0): in a spike, up to this many connections more are opened, so `size + max_overflow` is the most there are.
- `overflow_idle_ms` (1000): an overflow connection that nobody needs is closed after this long.
- `checkout_ms` (5000): how long a caller waits for a connection.
- `lease_ms` (30000): how long a block may run. 0 runs the block in the caller with no limit: a crash in it is the caller's own, the check-in of the connection after it is not bounded, and a block still running when the drain of `DbPool.close` ends finds its connection closed (on SQLite its next call crashes the caller; with a `lease_ms` above 0 the block is cancelled and its caller gets `pool error: closed`).
- `max_age_ms` (0): a connection this old is closed and replaced when it is idle. 0 turns it off.
- `idle_ms` (0): a connection unused this long is closed. It needs `lazy: true`, which opens connections only when callers need them (default `false`: always connected).
- `max_waiting` (1000): how many callers may wait in line for a connection at once, 0 or more. One more is refused with `pool error: queue full (max_waiting 1000)`. 0 lets nobody wait: a caller that finds every connection busy is refused at once. Raise it for a burst of many callers (thousands of WebSockets that connect together), or lower it to refuse load early.
- `health_ms` (5000): how often the idle connections are pinged and `max_age_ms` and `idle_ms` applied. 0 turns the check off, and then those two must be 0.
- `checkpoint_frames` (1000), `checkpoint_ms` (1000) and `checkpoint_cap` (0): the checkpoints of a SQLite file in WAL mode, see [below](https://agentica-lang.github.io/agentica-db/production/pooling/index.md#sqlite-checkpoints). Other databases ignore them.

The url is anything `Db.open` takes, `timeout_ms` included: every statement of the pooled connections is then bounded, and the `timeout_ms` argument of `Db.query` and `Db.execute` works as on any connection.

## What happens when the block ends

The connection goes back to the pool, whatever the block did:

- A transaction the block left open is rolled back (`Db.in_transaction?` tells, without a round trip), so the next caller starts clean. Use `Db.transaction(conn)` in the block to commit.
- A connection the block closed or lost (`Db.connected?` is false) is closed and replaced, so the next caller does not get a dead one. The database client only learns that the server ended a connection when a call on it fails, so the call that fails does so in the block that made it, and the pool replaces the connection after it.
- On PostgreSQL too, a connection that the server ended quietly while it was idle fails the first caller that gets it, up to `size` callers, until the health check (`health_ms`) pings it and replaces it.
- A block that crashes ends its connection: the pool rolls it back, closes it and opens another. The caller gets `error("task crashed: ...")`.
- A block that runs past `lease_ms` ends its connection too, and its caller gets `pool error: lease timed out after N ms`.
- A caller that is cancelled while it holds a connection cancels its block, and the connection is interrupted, closed and replaced, as after a crash.
- A connection whose rollback fails is closed and replaced.
- Idle connections are pinged with `Db.healthy?` every 5 seconds, and one that does not answer is replaced.

State that belongs to one connection (a temporary table, `SET` on the session, a prepared statement) is not safe to count on between two blocks: the next block may get another connection. The pool undoes only an open transaction: other session state that the block leaves (an advisory lock, `SET autocommit=0` on MySQL, a changed `search_path`) reaches the next caller, and so does a `conn` the block keeps after it ends, which then belongs to someone else. Use `conn` only inside the block, and undo what you set.

## SQLite writers take turns

SQLite has one writer at a time. The connections of a SQLite pool wait in line at `Db.transaction`: a writer waits for the one before it to commit, and they get their turn in the order they came, instead of each trying its `BEGIN IMMEDIATE` again every few milliseconds until the lock is free. A writer waits for its turn up to `busy_timeout`, and at most half of `lease_ms`; then its `BEGIN` takes the lock if it is free and fails with `sqlite error 5: database is locked` if not, as it would without the line. A block that crashes or runs past `lease_ms` inside its transaction gives the turn back when the pool takes its connection back. A statement outside `Db.transaction` does not wait in line, and `busy_timeout` stays its wait for the writers of other pools and processes.

Do not start a transaction on a second connection of the pool inside a transaction: it waits for the turn that the first one holds, and fails after `busy_timeout` with `sqlite error 5: database is locked`.

## SQLite checkpoints

In WAL mode a commit appends its pages to the `-wal` file, and a checkpoint copies them into the database file. By default SQLite checkpoints in the `COMMIT` that brings the WAL past 1000 pages, so that one commit pays for the copy of many. A pool of a SQLite file with the native driver takes the checkpoints off the commits: its connections turn SQLite's automatic checkpoint off, and a checkpointer task of the pool, with a connection of its own, runs a passive checkpoint (`PRAGMA wal_checkpoint(PASSIVE)`) on the compute pool when a commit leaves `checkpoint_frames` pages or more in the WAL that no checkpoint has copied, and every `checkpoint_ms` in any case. A passive checkpoint never waits for a reader or a writer, so it copies what it can and leaves the rest for the next one.

If the checkpointer falls behind, a commit that leaves `checkpoint_cap` such pages or more checkpoints itself, as SQLite would. 0 means four times `checkpoint_frames`. That keeps the WAL near the cap as long as no reader holds an older snapshot: as with SQLite's own checkpoints, a passive checkpoint cannot copy the pages that a long read still needs, and the WAL grows until it ends. `checkpoint_frames` 0 turns the checkpointer off and leaves the checkpoints to SQLite. `DbPool.close` stops the checkpointer and closes its connection. A value below 0 (below 1 for `checkpoint_ms`) does not start the pool: `db error: checkpoint_ms must be 1 or more, got 0`.

Since its `COMMIT` no longer checkpoints, the `COMMIT` of a transaction of short writes runs in a short call on the scheduler (see [the drivers](https://agentica-lang.github.io/agentica-db/databases/sqlite/index.md#drivers)) when `synchronous` is `normal` (the default in WAL) or `off`: with `full` or `extra` a commit waits for the disk and runs on the compute pool. The `COMMIT` of a transaction that ran a write on the compute pool, or more than 64 short ones, runs on the compute pool too. So does every write of the pool while a checkpoint runs, and the transaction after a checkpoint that copied the whole WAL: its commit starts the WAL again from the start and syncs its header, a disk flush even with `synchronous=normal`. The pool does not see a checkpoint that another connection runs (another process, a connection outside the pool, or SQLite's own when the last connection closes), so the commit after it may sync on the scheduler. A `PRAGMA` that sets `journal_mode`, `synchronous` or `locking_mode` on a connection of the pool sends its writes to the compute pool from then on, and `PRAGMA wal_checkpoint`, or one that sets `wal_autocheckpoint`, the writes of the whole pool; a `PRAGMA` that only reads one of them changes nothing. An in-memory database, the FFI driver, `checkpoint_frames` 0 and a connection outside a pool keep SQLite's automatic checkpoint.

## Errors and timeouts

- `pool error: checkout timed out after N ms`: every connection was busy for `checkout_ms`.
- `pool error: lease timed out after N ms`: the block ran too long.
- `pool error: queue full (max_waiting N)`: `max_waiting` callers wait already (1000 by default).
- `pool error: closed`: the pool is closed.
- `sqlite error 9: interrupted`, `postgres error 57014: ...` and the like: a statement ran over its `timeout_ms`.

`DbPool.timeout?(e.message)` is true for the first two and for a statement that ran over its `timeout_ms` (the same as `Db.timeout?`), so a handler can answer "busy, try later" to all of them. The other errors are those of the block. The errors of the pool have kinds (`pool.timeout.checkout`, `pool.timeout.lease`, `pool.full` and `pool.closed`), which `e.kind` gives; their texts are the ones above.

## A block that runs past `lease_ms`

The caller gets `pool error: lease timed out after N ms` when the time is over, and the pool cancels the block, but a block that is inside a native call cannot be stopped from outside, so it can keep running for a while. What the pool does with its connection depends on the database:

- SQLite: the statement is interrupted, so the connection closes at once and the next caller gets a new one.
- PostgreSQL: the pool sends a cancel request for the statement, so the server stops it, and closes the connection.
- MySQL and MariaDB: the pool runs `KILL QUERY` for the statement on a new connection to the same url (its connect waits at most 5 s), so the server stops it, and closes the connection. The kill is skipped when that connection reaches another server, as behind a load balancer, or the server restarted since. Still set `timeout_ms` in the url below `lease_ms`, so that the server ends a statement before the lease does.

In all cases a `timeout_ms` below `lease_ms` makes the statement fail by itself (`Db.timeout?`), as an ordinary error of the block, and the connection is kept.

`DbPool.close(pool, drain_ms)` refuses new callers, lets the running blocks finish for up to `drain_ms`, and closes every connection. A block that runs in a task of the pool (`lease_ms` above 0) and outlasts the drain is cancelled, and its caller gets `pool error: closed`. With `lease_ms` 0 the block runs in its caller and is not cancelled: it finds its connection closed, so drain for longer than your blocks run.
