Handling PostgreSQL Transactions and Prepared Statements with Bun.sql: What the Built-in Pool and 2PC Actually Do

When running a Node.js backend, there comes a moment where you think: "Can't I simplify this pg + pg-pool combination somehow?" Dependencies keep piling up, the external pool configuration has to be managed separately, and honestly, you end up using prepared statements without really knowing how they work under the hood.

Bun.sql, introduced in Bun 1.2, targets that exact pain point head-on. You can use PostgreSQL without any external npm packages, and transactions and prepared statements are handled as built-in driver behavior. One common misconception here: what Bun.sql eliminates is the external pool package (like pg-pool), not the concept of a connection pool itself. The built-in connection pool still exists, and as we'll see later, the initialization strategy of this built-in pool can become an issue in production.

In this article, we'll look at how Bun.sql handles transactions and prepared statements internally, walk through savepoints, distributed transactions, and connection reservation scenarios with code, and cover the real pitfalls you're likely to encounter — such as PgBouncer compatibility issues and known production bugs.

Why Bun.sql Now

Layer Structure of the Existing Stack

There are two main paths for using PostgreSQL in Node.js. pg binds to libpq (a C library), while postgres.js implements the wire protocol in pure JS. Since the two approaches are architecturally fundamentally different, it's more accurate to look at them separately.

Each layer has its own version management, configuration, and troubleshooting points. pg-pool handles only the external connection pool, prepared statement caching behaves differently per library, and transactions required manually managing client.query('BEGIN') / COMMIT / ROLLBACK or learning library-specific helpers.

How Bun.sql Reduces These Layers

Bun.sql implements the PostgreSQL wire protocol directly in Zig. It doesn't depend on libpq and runs on top of Bun's own event loop. There's no separate C binding overhead, and npm packages like pg or postgres.js aren't needed. Connection pool management is also built into the driver, so there's no need to attach an external pool package.

As of August 2026, Bun v1.2.21 has also added a MySQL/MariaDB Zig driver, expanding it into a single zero-dependency SQL client.

Prepared Statements as the Default

Bun.sql defaults to prepare: true. When you repeatedly execute queries with the same structure, it caches them as named prepared statements on the PostgreSQL server side, skipping the parsing and plan generation cost on subsequent requests. On top of that, it uses binary wire protocol and pipelining to batch Bind/Execute messages together, reducing round trips.

Performance gains depend heavily on the workload. To cite quantitative figures, you need to benchmark in your own environment — and it's safe to say that for CRUD where DB I/O dominates, the difference between drivers tends to be relatively small. The practical case for adopting Bun.sql lies more in reduced dependencies and a structure that blocks SQL injection at the API level than in performance.

Code by Scenario

Basic Connection and SQL Injection Prevention

The key is enforcing the tagged template literal style. When you interpolate a value, it's automatically converted to a parameterized query ($1, $2), making raw string insertion structurally impossible.

const sql = new Bun.SQL("postgres://user:pass@localhost/mydb");
 
const userId = req.params.id;
const users = await sql`SELECT * FROM users WHERE id = ${userId}`;

You can't build a query through string concatenation ("SELECT ... WHERE id = " + userId). The API design itself is the structure that prevents injection.

Transactions: Automatic COMMIT/ROLLBACK

Passing a callback to sql.begin() reserves a single connection to be used exclusively within the transaction. If the callback returns normally, it auto-commits; if an exception is thrown, it auto-rolls back.

const sql = new Bun.SQL("postgres://localhost/mydb");
 
await sql.begin(async (tx) => {
  const [user] = await tx`
    INSERT INTO users (name, email) VALUES (${"Alice"}, ${"alice@example.com"})
    RETURNING id
  `;
  await tx`
    INSERT INTO audit_log (user_id, action) VALUES (${user.id}, ${"created"})
  `;
});

There's no need to manually write BEGIN / COMMIT / ROLLBACK, and the connection is returned automatically.

Savepoints: Rolling Back Only Part of a Transaction

Savepoints are useful for scenarios like order processing where "even if the inventory update fails, the order itself should persist."

await sql.begin(async (tx) => {
  await tx`INSERT INTO orders (user_id) VALUES (${userId})`;
 
  try {
    await tx.savepoint(async (sp) => {
      await sp`UPDATE inventory SET stock = stock - 1 WHERE product_id = ${productId}`;
      if (outOfStock) {
        throw new Error("Out of stock");
      }
    });
  } catch (err) {
    // exceptions inside the savepoint are caught here, preserving the outer transaction
  }
 
  await tx`INSERT INTO order_log (action) VALUES ('order_placed')`;
});

If an exception occurs in the savepoint callback, it rolls back only to that savepoint. If you don't rethrow the exception, the outer transaction continues. Conversely, if you want to fail the entire transaction, let the exception propagate up.

Distributed Transactions (Two-Phase Commit)

When multiple services need to participate in the same transaction, use sql.beginDistributed(). The API names below are based on PR #16381 and the TransactionSQL reference — it's recommended to verify the exact signature from the current documentation before use.

The 2PC flow precisely is as follows:

• Phase 1 (Prepare): After executing DML inside the callback, upon normal completion PREPARE TRANSACTION is called, preserving the transaction on the server in a "prepared" state.
• Phase 2 (Commit/Rollback): From any session thereafter, the final commit or rollback decision is made using the global transaction ID.

// Phase 1: Execute DML + PREPARE TRANSACTION
await sql.beginDistributed("txn-xyz-001", async (tx) => {
  await tx`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${fromId}`;
});
 
// Phase 2: commit or rollback based on coordinator decision
try {
  await sql.commitDistributed("txn-xyz-001");
} catch (err) {
  await sql.rollbackDistributed("txn-xyz-001");
}

The important caveat is that 2PC is a feature with significant operational overhead. If the coordinator dies before making its Phase 2 decision, "orphaned prepared transactions" remain on the server — they won't be automatically cleaned up, continue to appear in pg_prepared_xacts, and even interfere with vacuum. You need to configure PostgreSQL's max_prepared_transactions in advance and must have manual cleanup procedures and monitoring ready for failure scenarios. Most microservice scenarios can be handled sufficiently with eventually consistent approaches like the outbox pattern or saga, so 2PC is safest to introduce only at boundaries where atomicity is truly required.

Reserving a Single Connection

For cases like PostgreSQL advisory locks where "this must run consecutively on this one connection," use sql.reserve() to explicitly check out a single connection.

const reserved = await sql.reserve();
try {
  // reserve() is not a transaction, so use SET instead of SET LOCAL
  await reserved`SET statement_timeout = '5s'`;
  const result = await reserved`SELECT * FROM large_table`;
  return result;
} finally {
  reserved.release();
}

SET LOCAL is only valid inside a transaction block and reverts when the transaction ends. The connection returned by reserve() is open without a transaction, so you need SET for session parameters to apply as intended. Also, forgetting release() in finally means that connection never returns to the pool — be careful.

Pitfalls in Production

Use prepare: false with PgBouncer Transaction Mode

prepare: true (the default) caches named prepared statements on the PostgreSQL server per session. However, PgBouncer's transaction pooling mode routes each query to a different backend connection, so a previously prepared statement won't exist on the next connection.

const sql = new Bun.SQL({
  url: "postgres://localhost/mydb",
  prepare: false, // use unnamed prepared statements
});

The background is explained in detail in the Crunchy Data documentation. In PgBouncer session mode, you can keep prepare: true as-is.

Hang Bug After Constraint Violation

This is reported as Issue #22395. After certain constraint violations, subsequent queries enter an infinite wait state, with the risk of stalling the entire connection pool. When reproducing the conditions, it's practical to verify with commonly occurring SQLSTATEs like 23505 (unique violation) or 23503 (foreign key violation). As of August 2026, if you're considering production adoption, it's recommended to confirm the fix version before introducing it.

Eager Initialization of the Built-in Pool

Based on Issue #30631, Bun.sql's built-in pool uses an eager strategy that immediately creates connections up to the maximum count, and there is no lazy initialization option yet. In environments running multiple instances or workflows with frequent restarts during development, the DB-side connection count can fill up faster than expected.

On Performance

Rather than reproducing drift-prone benchmark numbers, it's more accurate to view the practical benefits of Bun.sql through a structural lens rather than a performance one. It's true that binary wire protocol and pipelining give it an advantage in raw benchmarks, but in actual services, DB I/O, indexes, and query plans are the dominant factors, so it's hard to expect dramatic changes from a driver swap alone. Eliminating pg-pool and reducing the dependency tree, blocking injection risk at the API level via tagged templates, and unifying transactions and savepoints under the callback pattern — these three are closer to the gains you'll actually feel in practice.

Using with Drizzle ORM

If you need a type-safe query builder, Drizzle officially supports a bun-sql adapter, so the combination is viable. As of 2026, it's established itself as one option for new Bun-based PostgreSQL projects.

Wrapping Up

Synthesizing everything covered here, the judgment is fairly clear. If you're building a new Bun-based service, can operate PgBouncer in session mode (or with prepare: false), and your team resolves most atomicity needs within transaction boundaries, then going with Bun.sql alone is a reasonable choice. The benefit of removing external SQL packages and getting a unified codebase with the tagged template · sql.begin() pattern is concrete.

On the other hand, if any of the following conditions apply, it's safer to defer adoption or start with a partial rollout:

• You're already heavily using PgBouncer transaction mode and can't switch to prepare: false — you lose the server-side statement caching benefit and routing side effects remain.
• You routinely use 2PC-based distributed transactions — the API is ready, but the operational burden is high, so it's better to first consider other patterns like saga or outbox.
• You need to run Node.js and Bun runtimes side by side — Bun.sql is a Bun-specific API, so client code becomes tied to the runtime.
• Production stability is the top priority and it's hard to confirm the fix for #22395 — the hang issue can affect the entire pool.

Technology choices ultimately come down to your team's operational environment. Bun.sql is not "a thin wrapper" but a driver rewritten from the protocol up — it's a card well worth watching mature over the coming quarters.

References

• Bun Official SQL Documentation
• Bun TransactionSQL TypeScript Interface
• Bun SavepointSQL TypeScript Interface
• Bun ReservedSQL Reference
• SQL and Database APIs - DeepWiki (oven-sh/bun)
• Bun v1.2.21 Release Notes
• PR #16381: feat(sql) transactions, savepoints, connection pooling and reserve
• Issue #22395: Postgres driver hangs after constraint violation
• Issue #30631: Customizable Connection Pool Strategy
• Drizzle ORM - Bun SQL Integration Docs
• Crunchy Data: PgBouncer transaction mode and prepared statements