How to control transaction isolation levels and handle large-scale queries using Bun 1.2's built-in PostgreSQL driver

When I first started evaluating a runtime switch to Bun, my immediate question was "what about the DB driver?" I had always taken pg or postgres.js for granted in Node.js, so when I heard that Bun 1.2 (released January 2025) shipped a built-in PostgreSQL client that requires no external npm packages, I was skeptical at first.

After using it, it turned out to be better than expected. A native driver written in Zig communicates directly with the PostgreSQL wire protocol, and the API follows a Tagged Template Literal style inspired by postgres.js, so the migration burden wasn't too heavy. In this post, I'll focus on how to actually handle transaction isolation levels with Bun SQL, and what approaches are realistic today for processing large volumes of data — the parts that the official documentation doesn't cover well.

All time references throughout this post are standardized to August 2026.

The Context Behind bun:sql and Where It Stands Today

Why a Zero-Dependency Driver Was Built

In the Node.js ecosystem, the PostgreSQL driver choice has always been between node-postgres(pg) and postgres.js. Both libraries are excellent, but the fact that they're external packages creates management overhead — bundle size, security audits, version conflicts.

Bun addressed this with bun:sql (or Bun.SQL). The driver is bundled with the runtime itself, eliminating one dependency from package.json. The Bun v1.2 release notes present internal benchmarks showing row-read speeds roughly twice as fast as pg in certain scenarios — though since these are Bun's own figures, it's best to interpret them alongside your own measurements on real workloads.

In August 2025, MySQL/MariaDB support was added in v1.2.21, extending the approach to cover PostgreSQL, MySQL, and SQLite under a single API.

When Transaction Isolation Levels Start Getting Confusing

Most developers know READ COMMITTED is the default, but far fewer teams in practice clearly distinguish when to use REPEATABLE READ versus SERIALIZABLE. I initially thought "SERIALIZABLE is the safest, so why not just use it for everything?" — but in reality, the higher the isolation level, the greater the cost of conflict detection and retries.

Based on the PostgreSQL official documentation, here's the breakdown. Note that PostgreSQL internally upgrades READ UNCOMMITTED requests to READ COMMITTED, so the observable anomalies for both levels are identical:

Here's the decision flow for choosing an isolation level:

Bun SQL Isolation Level Control in Code

Connection Setup and Basic Queries

import { SQL } from "bun";
 
const sql = new SQL({
  url: "postgres://user:pass@localhost/mydb",
});
 
const users = await sql`SELECT * FROM users WHERE email = ${email}`;

The ${email} part is handled as parameter binding, not string interpolation, so you can use it without worrying about SQL injection. If you've used postgres.js, the API will feel nearly identical.

Scenario 1: Financial Transfer — SERIALIZABLE with Retries

PostgreSQL's SERIALIZABLE is implemented via SSI (Serializable Snapshot Isolation), which tracks read-write dependencies between transactions without holding locks. When a dangerous dependency graph is detected, it throws a SQLSTATE 40001 serialization_failure error at commit time (or during execution) and aborts the transaction.

There are two mistakes to avoid here.

• Don't combine with FOR UPDATE. FOR UPDATE is a pessimistic row lock and is fundamentally different from SSI's optimistic approach. Adding FOR UPDATE to a SERIALIZABLE example creates the false impression that explicit locking is required even with SERIALIZABLE. If you're relying on SSI, use plain SELECT/UPDATE without any lock hints.
• Don't propagate the exception upward without retry logic. 40001 is a transient error meaning "try again shortly," so an application-level wrapper that reopens the transaction is essential.

async function transfer(fromId, toId, amount, { maxRetries = 3 } = {}) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    try {
      await sql.begin("isolation level serializable", async (tx) => {
        const [from] = await tx`
          SELECT balance FROM accounts WHERE id = ${fromId}
        `;
        const [to] = await tx`
          SELECT balance FROM accounts WHERE id = ${toId}
        `;
 
        if (from.balance < amount) {
          throw new Error("Insufficient balance");
        }
 
        await tx`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${fromId}`;
        await tx`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${toId}`;
      });
      return;
    } catch (err) {
      if (err.code === "40001" && attempt < maxRetries - 1) {
        const backoff = 2 ** attempt * 50 + Math.random() * 25;
        await new Promise((r) => setTimeout(r, backoff));
        continue;
      }
      throw err;
    }
  }
  throw new Error("Transfer retry limit exceeded");
}

The sql.begin() API accepts the isolation level as a string, which is passed directly to PostgreSQL's BEGIN statement — forms like "isolation level serializable read write" or "isolation level repeatable read read only".

One concern is that this string is not type-safe. A typo like "isolation levl serializable" won't be caught at compile time, so it's safer to extract these into constants:

const ISOLATION = {
  serializable: "isolation level serializable",
  repeatableReadOnly: "isolation level repeatable read read only",
  readCommitted: "isolation level read committed",
};
 
await sql.begin(ISOLATION.serializable, async (tx) => { /* ... */ });

Scenario 2: Read-Only Reports — REPEATABLE READ

When multiple queries need to compute results against a consistent snapshot of a single point in time — such as a daily sales report — use REPEATABLE READ. Declaring READ ONLY alongside it lets PostgreSQL reduce related locking and predictive check overhead, making it well-suited for reporting workloads.

async function getDailyReport(today) {
  return await sql.begin(
    "isolation level repeatable read read only",
    async (tx) => {
      const [summary] = await tx`
        SELECT SUM(amount) AS total FROM orders WHERE order_date = ${today}
      `;
      const items = await tx`
        SELECT product_id, SUM(quantity) AS qty
        FROM order_items
        WHERE order_date = ${today}
        GROUP BY product_id
      `;
 
      return { total: summary.total, items };
    }
  );
}

Because PostgreSQL MVCC fixes the snapshot at transaction start time, data changes made by other transactions during report execution won't affect what's visible inside this transaction.

Scenario 3: Partial Rollback with Savepoints

When you want to roll back only a specific section rather than the entire transaction, Savepoints are useful. Bun SQL supports this via tx.savepoint():

await sql.begin(async (tx) => {
  await tx`INSERT INTO audit_logs (event, created_at) VALUES ('batch_start', NOW())`;
 
  try {
    await tx.savepoint(async (sp) => {
      await sp`INSERT INTO risky_table (data) VALUES (${riskyData})`;
      await sp`UPDATE counters SET value = value + 1 WHERE key = 'risky'`;
    });
  } catch (err) {
    console.warn("Risky operation failed, skipping:", err.message);
  }
 
  await tx`INSERT INTO audit_logs (event, created_at) VALUES ('batch_end', NOW())`;
});

If an error occurs inside the tx.savepoint() callback, only that savepoint is rolled back and the outer transaction continues. This pattern is well-suited for cases where you want to keep audit logs while selectively skipping risky operations.

Processing Large Volumes of Data — Compromising with Reality

As of August 2026, the native AsyncIterator API in the form of sql\SELECT ...`.stream()` — which I was most looking forward to when researching — is not yet officially supported. A feature request is open at GitHub Issue #25307 with ongoing discussion.

So how can large volumes of data be handled in a Bun environment?

Option 1: Use postgres.js in parallel

postgres.js supports server-side cursors via its .cursor() API and works fine on the Bun runtime. If streaming is strictly required, using this library alongside Bun SQL is the most practical approach:

import postgres from "postgres";
 
const pg = postgres(process.env.DATABASE_URL);
 
for await (const [row] of pg`SELECT * FROM large_table`.cursor(100)) {
  await processRow(row);
}
 
await pg.end();

Option 2: Keyset Pagination (Alternative to OFFSET)

OFFSET-based pagination degrades sharply in performance as pages go deeper. Using keyset pagination — fetching the next page based on the last processed key — is more stable when working with Bun SQL alone.

Note that the example below assumes id is a monotonically increasing integer PK (e.g., bigserial) and the sort column is unique. It cannot be used as-is with UUIDv4, non-sequential sequences, or composite PKs — in those cases, a separate design such as a sortable UUIDv7 or a (created_at, id) composite cursor is needed.

let lastId = 0;
 
while (true) {
  const rows = await sql`
    SELECT * FROM large_table
    WHERE id > ${lastId}
    ORDER BY id
    LIMIT 1000
  `;
 
  if (rows.length === 0) break;
 
  for (const row of rows) {
    await processRow(row);
    lastId = row.id;
  }
}

Here's the decision flow for choosing an approach when streaming is needed:

Trade-offs Encountered in Practice

Full Comparison (As of August 2026)

The PgBouncer Transaction Pooling and prepare: false Trap

Bun SQL uses Prepared Statements by default. The problem is that PgBouncer's transaction pooling mode does not support Prepared Statements, which causes connection errors. Related cases are documented in GitHub Issue #17044.

The workaround is prepare: false:

const sql = new SQL({
  url: process.env.DATABASE_URL,
  prepare: false,
});

There is a critical trade-off to understand here. Enabling prepare: false means parsing and planning costs are incurred on every query. It's accurate to say that most of the performance advantages Bun SQL emphasizes — reuse of parsed plans, reduced binding overhead — are effectively eliminated by this single option. If you need to keep PgBouncer transaction pooling, you must make a conscious decision: "we're sacrificing some driver performance in exchange for connection pool benefits." If your project is on Supabase, migrating to Supavisor may be worth considering.

Combining with an ORM

For projects using Drizzle or Prisma, as of August 2026, native integrations that use Bun.SQL directly as the driver layer are limited. Layering Drizzle on top of postgres.js remains the most stable approach, and Prisma uses its own driver. For projects where an ORM is essential, the realistic starting point is the combination of "Bun runtime + postgres.js driver + ORM" rather than Bun SQL alone.

Closing Thoughts: Two Decision Criteria

There are two points I want to emphasize from this post.

First, SERIALIZABLE comes as a package deal with retry logic. Being able to raise the isolation level with a single sql.begin("isolation level serializable", ...) call doesn't automatically make things safe. SSI is optimistic — when a conflict occurs, it throws 40001 and kills the transaction, so without an application-level wrapper that reopens it with exponential backoff, you'll actually see higher failure rates. Remembering not to mix in FOR UPDATE and blend lock-based thinking is part of the same lesson.

Second, streaming requirements are the branching point for using Bun SQL standalone. Isolation level control, Savepoints, and the transaction callback API are already at a level that works without issues in production. But if server-side cursor-based streaming is essential to your workload, as of August 2026, using postgres.js in parallel is the closest thing to an answer. First check whether the workload can be replaced with keyset pagination, and if not, don't force everything onto Bun SQL alone.

On top of that, if you're using PgBouncer transaction pooling, going in with the awareness that prepare: false significantly offsets the driver performance advantages will let you make an informed decision — and you'll largely avoid unexpected regressions from the Bun migration.

References

• Bun Official SQL Documentation
• Bun.TransactionSQL API Reference
• SQL.begin Method Reference
• Bun v1.2 Release Notes
• Bun v1.2.21 Release Notes — MySQL/MariaDB Support Added
• Bun SQL Native Streaming Feature Request (GitHub Issue #25307)
• PgBouncer Transaction Pooling Compatibility Issue (GitHub Issue #17044)
• PostgreSQL Official Transaction Isolation Documentation
• PostgreSQL Error Code Reference (Class 40 — Transaction Rollback)