Sessions

Default behavior: a fresh session per call

await env.db.exec("select 1");

That single line connects, runs the query, syncs, and disconnects. As a result, no session state survives between calls.

• BEGIN does not carry over to the next call.
• SET settings do not apply to the next call.
• Prepared statements and temporary tables do not survive either.

Why connections are not reused

Reusing a connection requires a guarantee that every trace of state left by the previous request has been removed. PostgreSQL's DISCARD ALL is meant to do that, but we have not yet confirmed that it safely clears all state in this engine.

Reusing connections before that is confirmed would let one request's SET settings or temporary tables leak into another user's request. So for now, we close the connection and open a new one every time. No session state passes between requests.

Performance characteristics

The 5 ms on a table lookup is the cost of opening a new session and warming the catalog on every call. It can be reduced once safe session reuse is confirmed. If you connect directly over the PostgreSQL protocol and keep the session open, the same query takes 0.55 ms.

Using a session directly

If you need to run several SQL statements in the same session, use a session handle.

const s = await env.db.session();
try {
  await s.exec("begin");
  await s.exec("insert into a values ($1)", [1]);
  await s.exec("insert into b values ($1)", [2]);
  await s.exec("commit");
} finally {
  await s.close();
}

In @runlot/pg, pool.connect() plays the same role.

Rules

• close() is idempotent. You can call it more than once.
• close() rolls back an open transaction. To commit, you must send COMMIT yourself.
• Running SQL on a closed handle raises an 08003 error.
• After 30 seconds without a SQL statement, the session is reclaimed and any open transaction is rolled back.

Keep it inside the request scope

Do not store a session handle in a global variable or pass it outside the request. It can hit the 30-second limit and have its transaction rolled back, and while it holds the session other requests have to wait.

Concurrent requests are processed in order

Database requests are handled one at a time per project. If a request holds a session for a long time, other requests wait. A long transaction can increase response times across the whole project.

The default execution limit for a single SQL statement is 10 seconds. Queries that exceed it are aborted.