Exporting large query results as chunked streams with Bun.SQL and Hono streamSSE

Returning hundreds of thousands of rows from a report query directly into an array will blow up server memory and leave the client staring at a blank screen for minutes. When building an operations metrics dashboard, I witnessed 300 MB of JSON being loaded onto the heap, and that was the moment I had to change my approach: instead of collecting the entire result set, split it into chunks and push each one to the client as soon as it's ready.

This post covers how to assemble that pipeline using Bun.SQL, built into the Bun runtime, and Hono's streamSSE helper. It also walks through why SSE was chosen over WebSocket, the pitfalls of LIMIT/OFFSET and the keyset alternative, and operational concerns like proxy timeouts, onAbort cleanup, and connection pooling.

Why SSE — The Choice Over WebSocket

SSE (Server-Sent Events) is a one-way protocol using the text/event-stream MIME type, where the server continuously pushes events to the client. The scenario of "server pushing large query results to the client" is exactly the shape SSE is designed for.

In the old HTTP/1.1 era, SSE connections blocking other requests was a common complaint, due to browsers' per-domain concurrent connection limit (typically 6). Since HTTP/2, stream multiplexing over a single connection has relaxed this constraint. However, to actually benefit from this, the origin and reverse proxy must be configured to serve over HTTP/2. If you're serving over HTTP/1.1 only, you still need to carefully manage the number of concurrent SSE sessions.

Another practical reason to choose this combination: OpenAI and Anthropic use SSE as the default transport format for streaming responses, which means SSE support in frontend libraries, proxies, and monitoring tools has matured significantly.

Bun.SQL: The SQL Client Built Into the Runtime

Bun.SQL is an SQL client you can start with a single line — import { sql } from "bun" — with no separate npm driver required. PostgreSQL support was first introduced in the Bun 1.2 release in January 2025 (Bun v1.2 Release Notes), and subsequent 1.2.x releases expanded support to MySQL/MariaDB and SQLite (Bun v1.2.21 Release Notes). For the exact version in use as of July 2026 and the currently supported databases, it's safest to check the official documentation.

import { sql } from "bun";
 
const rows = await sql`SELECT id, name FROM users WHERE active = ${true}`;

Performance advantages are often cited thanks to the native driver implemented in Zig, but the benchmarks in the official documentation were measured under specific query and dataset conditions, so measure against your own workload.

As of July 2026, the native row-level async iterator API in the form of sql\...`.stream()has not yet been officially released ([GitHub Issue #25307](https://github.com/oven-sh/bun/issues/25307)). The primary example in this post usesLIMIT/OFFSET` batch queries as a workaround, and the limitations and keyset alternative are covered later.

Data Flow at a Glance

sequenceDiagram
    participant Client
    participant Server (Hono + Bun.SQL)
    participant DB
 
    Client->>Server (Hono + Bun.SQL): GET /stream/reports
    Server (Hono + Bun.SQL)-->>Client: 200 text/event-stream headers
    Note over Client,Server (Hono + Bun.SQL): Connection held open, event stream follows
 
    loop Chunk iteration
        Server (Hono + Bun.SQL)->>DB: WHERE id > last_id LIMIT N
        DB-->>Server (Hono + Bun.SQL): N rows
        Server (Hono + Bun.SQL)-->>Client: event: chunk / data: JSON
    end
 
    Server (Hono + Bun.SQL)-->>Client: event: done

Bun.SQL is a library that runs inside the Hono server process, not a separate process, which is why they're grouped in one box. The key insight of this architecture is that the client can begin rendering the UI or processing data as soon as the first chunk arrives.

Basic Implementation

Hono's streamSSE is included in the official streaming helper, and the import path is hono/streaming.

import { Hono } from "hono";
import { streamSSE } from "hono/streaming";
import { sql } from "bun";
 
const app = new Hono();
const CHUNK_SIZE = 50;
 
app.get("/stream/reports", (c) => {
  return streamSSE(c, async (stream) => {
    let lastId = 0;
 
    stream.onAbort(() => {
      console.log("Client disconnected");
    });
 
    while (!stream.aborted) {
      const rows = await sql`
        SELECT id, name, created_at
        FROM large_table
        WHERE id > ${lastId}
        ORDER BY id
        LIMIT ${CHUNK_SIZE}
      `;
 
      if (rows.length === 0) break;
 
      await stream.writeSSE({
        event: "chunk",
        data: JSON.stringify(rows),
        id: String(rows[rows.length - 1].id),
      });
 
      lastId = rows[rows.length - 1].id;
 
      if (rows.length < CHUNK_SIZE) break;
    }
 
    if (!stream.aborted) {
      await stream.writeSSE({ event: "done", data: "" });
    }
  });
});
 
export default app;

A few things worth noting:

• ORDER BY id is not optional — it's required. Pagination without a sort order can result in rows being duplicated or skipped across pages, and the optimizer is free to return rows in a different order on each execution. There must be an index on id to keep the cost of entering each page near constant.
• Check stream.aborted before calling writeSSE. If the reason the while (!stream.aborted) loop exited was an abort, writing a done event to an already-closed stream will throw an exception.
• Putting the last row's PK in the event id means that when EventSource reconnects, it sends that value back in the Last-Event-ID header, and the server can use it as lastId to resume from where it left off.

Why Keyset Instead of LIMIT OFFSET

This is why the draft doesn't use the familiar LIMIT/OFFSET. OFFSET N is an operation that requires the DB to actually read and discard the first N rows, so the cost of fetching one page grows linearly as the offset increases. In the latter half of streaming hundreds of thousands of rows, the last page can be tens to hundreds of times slower than the first.

Keyset pagination (WHERE id > $last_id ORDER BY id LIMIT N) finds the starting position directly in the index and reads only what's needed, so the per-page cost stays constant regardless of how far in you are. The trade-off is that the sort key must be in an index and be unique, and it's unsuitable for UX that requires jumping to arbitrary pages. It's a great fit for workloads like SSE streaming that scan sequentially from start to finish.

Control Flow of the Chunk Loop

Client Side

const source = new EventSource("/stream/reports");
const allRows = [];
 
source.addEventListener("chunk", (e) => {
  const rows = JSON.parse(e.data);
  allRows.push(...rows);
  renderRows(rows);
});
 
source.addEventListener("done", () => {
  source.close();
  console.log(`Received ${allRows.length} rows total`);
});
 
source.onerror = () => {
  console.warn("SSE error, browser will attempt auto-reconnect");
};

EventSource automatically reconnects when the connection drops, and if an event id was set, it sends the last value in the Last-Event-ID header. If the server reads this header and uses it as lastId, streaming resumes from where it was interrupted.

When the user wants to stop streaming, a single source.close() is enough. On the server side, stream.onAbort is triggered immediately, allowing cleanup of any pending queries or intervals.

Common Pitfalls in Production

1. Proxy Idle Timeouts

Reverse proxies will close idle connections that have no data flowing after a certain time. nginx's proxy_read_timeout defaults to 60 seconds, and AWS ALB's idle timeout also defaults to 60 seconds. Cloudflare's value varies by plan, product (Workers, Proxy, etc.), and user settings, so it's worth verifying the actual value for your deployment environment.

If the initial query processing time is long, or if gaps between chunks can stretch out, send periodic heartbeat events to avoid idle state.

app.get("/stream/reports", (c) => {
  return streamSSE(c, async (stream) => {
    const heartbeat = setInterval(async () => {
      if (stream.aborted) return;
      try {
        await stream.writeSSE({ event: "heartbeat", data: "" });
      } catch {
        clearInterval(heartbeat);
      }
    }, 15_000);
 
    stream.onAbort(() => clearInterval(heartbeat));
 
    try {
      // ...chunk streaming logic...
    } finally {
      clearInterval(heartbeat);
    }
  });
});

An exception thrown by await inside a setInterval callback becomes an unhandled promise rejection if left alone. There's also a brief race between checking abort status and the actual write, so wrap it in try/catch and clean up the interval on failure to eliminate the noise.

Set the interval comfortably shorter than the proxy timeout. Since 60 seconds is common, somewhere around 15 seconds is a reasonable starting point.

2. Silently Leaking Resources When onAbort Cleanup Is Missed

In the stream.onAbort callback, you must clean up intervals, DB cursors, event listeners, and any in-progress batch queries. Even after the client closes the tab, if the server loop doesn't check stream.aborted, it will keep issuing queries until the next iteration boundary. Make it a habit to check stream.aborted immediately before each loop iteration and after any long-running operation.

3. Connection Pool Exhaustion

An SSE session stays alive anywhere from a few seconds to several minutes, issuing queries periodically throughout. The LIMIT + keyset pattern returns the connection to the pool between chunks, so pool pressure is lower than with a server-side cursor approach. Even so, if concurrent session count approaches the pool limit, new requests will queue or fail, so it's worth calculating headroom based on pool size, average session duration, and average inter-chunk interval.

4. Tuning Chunk Size

The example code in this post uses CHUNK_SIZE = 50. This value isn't a magic number — it's a tuning parameter that should be adjusted based on row size, network round-trip time, and client rendering cost, using load testing. Too small, and round-trip overhead dominates; too large, and you lose the benefit of a fast first render along with the memory savings.

Trade-offs Summary

This pattern is a good fit when:

• The result set is tens of thousands of rows or more, or its size is hard to predict
• Progressive rendering benefits UX, as in reports or dashboards
• The query is long-running, taking tens of seconds or more to complete

Conversely, if the result is small, the response size is predictable, and the client needs to collect everything before doing any calculation anyway, the complexity of streaming outweighs its benefits.

It's also worth remembering that SSE only supports the server → client direction. If you need fine-grained upward control during streaming — such as changing filters mid-stream or canceling a specific query by ID — you'll need to design in a separate short-lived HTTP endpoint alongside it.

Parts Likely to Change

Once a native async iterator streaming API is added to Bun.SQL (Issue #25307), the keyset batch loop in this post could become much shorter. Conceptually, it would look something like this:

// Conceptual example — assumes an API form not yet officially released as of July 2026
for await (const rows of sql`SELECT * FROM large_table ORDER BY id`.stream(50)) {
  if (stream.aborted) break;
  await stream.writeSSE({ event: "chunk", data: JSON.stringify(rows) });
}

If you want a similar pattern with a server-side cursor in a Bun environment right now, postgres.js is an option. Consuming the async iterable returned by .cursor(N) with for await...of and then breaking will invoke the async iterator's return() per the JS spec, which cleans up the cursor — no explicit close() call needed in your code.

import postgres from "postgres";
import { streamSSE } from "hono/streaming";
 
const pg = postgres(process.env.DATABASE_URL!);
 
app.get("/stream/reports", (c) => {
  return streamSSE(c, async (stream) => {
    const cursor = pg`SELECT id, name FROM large_table ORDER BY id`.cursor(50);
 
    for await (const rows of cursor) {
      if (stream.aborted) break;
      await stream.writeSSE({
        event: "chunk",
        data: JSON.stringify(rows),
        id: String(rows[rows.length - 1].id),
      });
    }
 
    if (!stream.aborted) {
      await stream.writeSSE({ event: "done", data: "" });
    }
  });
});

The cursor approach saves query planning cost by not preparing a new query for each chunk, but be aware that the transaction and connection remain held for the lifetime of the cursor, so concurrent session count needs to be managed accordingly.

Summary

Bun.SQL and Hono's streamSSE are a combination that lets you build a chunk streaming pipeline with minimal boilerplate. However, approaching it with the attitude of "the connection stays open so it'll take care of itself" will lead to quiet failures at points like proxy timeouts, write attempts after stream.aborted, OFFSET performance degradation, and missing onAbort cleanup.

Here's a production checklist:

• ORDER BY on an indexed column + keyset pagination to keep per-page cost constant
• Check stream.aborted both immediately before each loop iteration and immediately before each write
• Wrap heartbeat in try/catch and clean up the interval in onAbort
• Put the last PK in the event id to enable Last-Event-ID-based resumption
• Calculate connection pool headroom based on concurrent sessions × query frequency per chunk

After that, what remains is tuning chunk size and heartbeat interval against your actual workload.

References

• Bun.SQL Official Documentation
• Hono Streaming Helper Official Documentation
• Bun v1.2 Release Notes
• Bun v1.2.21 Release Notes
• Bun SQL native iterator/stream select — GitHub Issue #25307
• postgres.js
• MDN — Server-Sent Events
• MDN — EventSource