Database dumps do not work on large databases

Describe the bug
If you try to use any of the database dump endpoints such as SQL, CSV or JSON the data is loaded into memory and then created as a dump file. To support any size database we should investigate enhancements to allow any sized database to be exported. Currently the size limitations are 1GB for Durable Objects with 10GB in the future. Operate under the assumption that we might be attempting to dump a 10GB database into a .sql file.

Another consideration to make is because Durable Objects execute synchronous operations we may need to allow for "breathing intervals". An example might be we allow our export operation to run for 5 seconds, and take 5 seconds off if other requests are in a queue, then it can pick up again. The goal here would be to prevent locking the database for long periods of time.

But then poses the questions:

How do we continue operations that need more than 30 seconds to work?
Where is the data stored as it's being created? (R2, S3, something else)?
How do we deliver that dump information to the user after its completed?

To Reproduce
Steps to reproduce the behavior:

Hit the /export/dump endpoint on a large database
Will eventually fail when the 30 second request response time window closes

Run the following command in Terminal (replace the URL with yours) and if your operation exceeds 30 seconds you should see a failed network response instead of a dump file.

curl --location 'https://starbasedb.YOUR-ID-HERE.workers.dev/export/dump' \
--header 'Authorization: Bearer ABC123' \
--output database_dump.sql

If you can't create a large enough test database feel free to add code in to sleep for 29 seconds before proceeding with the /export/dump functional code and should also see the failure.

Expected behavior
As a user I would expect any and all of the specified data to be dumped out without an error and without partial results. Where it ends up for the user to access if the operation takes more than 30 seconds is up for discussion. Ideally if shorter than 30 seconds it could be returned as our cURL above works today (downloads the file from the response of the origin request), but perhaps after the timeout it continues on uploads it to a destination source to access afterwards?

Proposed Solution:

For backups require an R2 binding
Have a .sql file that gets created in R2 with the filename like dump_20240101-170000.sql where it represents 2024-01-01 17:00:00
Create the file and continuously append new chunks to it until reaching the end
May need to utilize a DO alarm to continue the work after X time if a timeout occurs & mark where it currently is in the process in internal memory so it can pick up and continue.
Provide a callback URL when the operation is finally completed so users can create custom logic to notify them (e.g. Email, Slack, etc)

/bounty $250

@Brayden

Hi, @Brayden. instead of using DO alarm API we can use queues if the database is size greater than certain size (say 200 or 500)?

My suggestion is to attach the cloudflare queues to the new private endpoints where the consumer and producer are a single worker which will export the data into R2 for certain time say (20 to 30sec) depends on usage limits, this way we can have the queues with the capability of the last record of the export queue and there's blockage of any request/response.

https://developers.cloudflare.com/durable-objects/api/sql-storage/#databasesize we can depend on this property of DO to trigger the queue or something like.

@b4s36t4

Hey @b4s36t4!

Instead of using the DO alarm API, I think queues is also an acceptable approach. Just to talk through it a bit more so I fully understand how you're thinking of approaching it. A couple of questions:

When you mention attaching to new private endpoints, would this be a new Worker altogether? Could it instead be a Starbase plugin perhaps? Here's a blog outlining how they work and some examples are also included in the repo (https://www.outerbase.com/blog/extensibility-with-plugins/)
When you say we can base it off if the database is size greater than some defined number (e.g. 200 or 500) would we fetch the database size at some interval to see if the difference in size from the last export meets the criteria and auto-run the backup?
Could we allow for users to define say like "backup daily" or "backup weekly" and trigger at that time?

Thanks for looking into this!

@onyedikachi-david

/attempt #59

Algora profile	Completed bounties	Tech	Active attempts	Options
@onyedikachi-david	14 bounties from 7 projects	TypeScript, Python, JavaScript & more		Cancel attempt

@onyedikachi-david

💡 @onyedikachi-david submitted a pull request that claims the bounty. You can visit your bounty board to reward.

/attempt #59 may I ask you for cloudflare paid account because I don't have one (it may resolved without cloudflare account following your guidances)

/cancel #59

@Brayden

/attempt #93

Hey @Brayden
Implementation Plan for Database Dump Feature:

Core Implementation:
- Create chunked processing system for database dumps
- Implement progress tracking mechanism
- Add R2 storage integration for dump files
- Build callback notification system
Key Functions to Implement:
- startChunkedDumpRoute(): Initialize dump process
- processDumpChunk(): Handle data chunks
- getDumpFileRoute(): Retrieve dump files
- finalizeDump(): Complete dump process
Testing Strategy:
- Unit tests for all core functions
- Integration tests for the complete flow
- Edge case handling (empty DBs, large datasets)
- Error scenarios and recovery
Features to Cover:
- Large database support through chunking
- Progress monitoring
- SQL escaping
- Callback notifications
- R2 storage integration
Timeline:
- Core implementation: Completed
- Testing: Completed
- Documentation: Completed
- PR ready for review

Status: Implementation complete and ready for review. All tests passing.

Options

Cancel my attempt

@Kunal-Darekar

💡 @Kunal-Darekar submitted a pull request that claims the bounty. You can visit your bounty board to reward.

@Brayden

@Brayden Apologies, I mistakenly deleted my original PR while making some update .I’ve created a new PR #93 and included the necessary documentation, including the wrangler.toml variables and the cURL command/endpoint for triggering. Everything should be clear and ready for testing. Could you please check? Thanks for your patience!

@Brayden

hey @Brayden nice to know you!, are you still having these kind of issues? or it has already solved? let me know!, thanks @Brayden

/attempt #59

Implementation plan (primary):

Add an async export job flow for large dumps (/export/dump) with a small sync fast-path for tiny DBs.
Persist job state/checkpoints in DO storage (current table, last row cursor, bytes written, status) so work can resume safely.
Stream chunked SQL/CSV/JSON output to R2 in bounded time slices (under request limits), then continue via DO Alarm until complete.
Add job status + artifact endpoints (e.g. job id, progress, downloadable URL) and optional callback webhook on completion/failure.
Keep DB responsive by yielding between chunks and enforcing cooperative "breathing" windows when request queues are non-empty.
Add tests for resume/idempotency/error handling + docs for required bindings/env vars and curl examples.

I will start with SQL dump first (end-to-end), then extend the same job pipeline to CSV/JSON.

feat: implement streaming export for large databases (outerbase#59)

Fix Database dumps do not work on large databases

/attempt #59

Implementation Plan:

Detect DB size on export request; if small enough to complete under 30s, return directly as before
For large exports, initiate a chunked background export using DO alarms to resume across timeout boundaries
Write chunks progressively to R2 with filename format dump_YYYYMMDD-HHmmss.sql
Track cursor position in DO internal storage so alarms can resume from where they left off
Add breathing intervals to avoid locking the DO for other requests
On completion, hit a user-provided callback URL with the R2 file location
Expose a /export/status/:jobId endpoint for polling

/attempt #59

/claim #59

PR: #279

The breathing-interval idea probably needs an explicit export job state machine. For large dumps, it would help to track cursor/page token, output byte offset, format-specific footer state, and cancellation/error state separately from the HTTP request that starts the export. That would let SQL/CSV/JSON share the same resumable streaming core while still handling each format's header/footer and escaping rules correctly.

Full implementation is up: #298 🚀

Chunked, resumable dump engine (ChunkedDumpEngine): rowid-batched scans, time-budgeted cycles with breathing intervals, crash-safe progress in DO storage
R2 spill when a bucket is bound + streamed reassembly (client never holds the full dump in memory)
30s dumps upgrade to a DO-alarm-driven job; GET /export/dump/status for progress + download
Small dumps keep the existing synchronous response (backwards compatible)
12 unit tests passing (chunkedDump.test.ts), including resume-mid-schema without duplicate emission and SQL-injection-safe identifier filtering

/attempt #59

Correcting my earlier comment — Git Bash rewrote the leading /attempt into a Windows path (C:/Program Files/Git/attempt), so the command never reached the bot and the attempt was never registered.

The implementation is up in PR #298: chunked, resumable dumps with breathing intervals, plus R2 overflow for objects that exceed the storage limit.

/claim #59

Automated Pull Request submitted: #302

Includes complete code patch, tests, and documentation.

feat(export): stream database dumps with bounded pagination and optio…

I have resolved this issue in PR #308.

Replaced full-table buffering with incremental LIMIT 1000 OFFSET ${offset} generator (generateDumpChunks).
Implemented escapeSqlValue supporting SQLite NULL, booleans, bigints, and X'...' hex BLOB literals.
Direct dumps are streamed via ReadableStream with Content-Type: application/x-sqlite3.
Added optional R2 multipart offload via ?location=r2 (DATABASE_DUMP_BUCKET binding) with ctx.waitUntil returning 202 Accepted immediately and notifying an optional callback URL on completion.
17/17 Vitest tests passing.

/claim #59

🛠️ Production Implementation & Technical Verification

Root Cause Analysis & Architectural Design:

dumpDatabaseRoute() currently calls executeOperation() for every full table, which reaches cursor.toArray() inside the Durable Object. It then concatenates the entire database into dumpContent and creates one Blob. A 10GB database therefore requires multiple 10GB-scale allocations before the first byte is sent.

The fix is a pull-driven ReadableStream with bounded page reads. It:

Sends headers immediately, avoiding time-to-first-byte timeout.
Reads at most pageSize rows per RPC.
Uses keyset pagination (_rowid_ > ?) where available; this prevents OFFSET becoming O(n²).
Falls back to LIMIT/OFFSET for WITHOUT ROWID tables.
Quotes identifiers and serializes SQLite values safely.
Yields to the runtime between pages so the Durable Object can service queued work.
Never creates an in-memory dump file or Blob.

This directly replaces the failure mode in the current implementation. The existing source material confirms that the old route builds one full string after cursor.toArray() materializes query output. Current dump implementation

Production-Grade Implementation Source Code:

Production Source (`src/export/dump.ts`)

import { executeOperation } from '.'
import { StarbaseDBConfiguration } from '../handler'
import { DataSource } from '../types'
import { createResponse } from '../utils'

const encoder = new TextEncoder()
const DEFAULT_PAGE_SIZE = 500

type Row = Record<string, unknown>

function quoteIdentifier(identifier: string): string {
    return `"${identifier.replace(/"/g, '""')}"`
}

function sqlLiteral(value: unknown): string {
    if (value === null || value === undefined) return 'NULL'

    if (typeof value === 'number') {
        if (!Number.isFinite(value)) {
            throw new Error('Cannot export non-finite numeric SQLite values')
        }
        return String(value)
    }

    if (typeof value === 'bigint') return value.toString()

    if (typeof value === 'boolean') return value ? '1' : '0'

    if (value instanceof Uint8Array) {
        let hex = ''
        for (const byte of value) hex += byte.toString(16).padStart(2, '0')
        return `X'${hex}'`
    }

    return `'${String(value).replace(/'/g, "''")}'`
}

async function breathe(): Promise<void> {
    await new Promise<void>((resolve) => setTimeout(resolve, 0))
}

async function getTables(
    dataSource: DataSource,
    config: StarbaseDBConfiguration
): Promise<string[]> {
    const result = await executeOperation(
        [
            {
                sql: `
                    SELECT name
                    FROM sqlite_master
                    WHERE type = 'table'
                      AND name NOT LIKE 'sqlite_%'
                    ORDER BY name
                `,
            },
        ],
        dataSource,
        config
    )

    return result.map((row: Row) => String(row.name))
}

async function getSchema(
    table: string,
    dataSource: DataSource,
    config: StarbaseDBConfiguration
): Promise<string | null> {
    const result = await executeOperation(
        [
            {
                sql: `
                    SELECT sql
                    FROM sqlite_master
                    WHERE type = 'table' AND name = ?
                `,
                params: [table],
            },
        ],
        dataSource,
        config
    )

    return result.length > 0 && typeof result[0].sql === 'string'
        ? result[0].sql
        : null
}

async function fetchRowIdPage(
    table: string,
    afterRowId: number,
    pageSize: number,
    dataSource: DataSource,
    config: StarbaseDBConfiguration
): Promise<Array<Row & { __starbase_rowid__: number }>> {
    return executeOperation(
        [
            {
                sql: `
                    SELECT _rowid_ AS __starbase_rowid__, *
                    FROM ${quoteIdentifier(table)}
                    WHERE _rowid_ > ?
                    ORDER BY _rowid_
                    LIMIT ?
                `,
                params: [afterRowId, pageSize],
            },
        ],
        dataSource,
        config
    ) as Promise<Array<Row & { __starbase_rowid__: number }>>
}

async function fetchOffsetPage(
    table: string,
    offset: number,
    pageSize: number,
    dataSource: DataSource,
    config: StarbaseDBConfiguration
): Promise<Row[]> {
    return executeOperation(
        [
            {
                sql: `
                    SELECT *
                    FROM ${quoteIdentifier(table)}
                    LIMIT ? OFFSET ?
                `,
                params: [pageSize, offset],
            },
        ],
        dataSource,
        config
    ) as Promise<Row[]>
}

async function* dumpChunks(
    tables: string[],
    dataSource: DataSource,
    config: StarbaseDBConfiguration,
    pageSize: number
): AsyncGenerator<string> {
    yield 'BEGIN TRANSACTION;\n'

    for (const table of tables) {
        const schema = await getSchema(table, dataSource, config)
        if (!schema) continue

        yield `\n-- Table: ${quoteIdentifier(table)}\n${schema};\n`

        let rowId = 0
        let offset = 0
        let usesRowId = true

        while (true) {
            let page: Row[]

            if (usesRowId) {
                try {
                    page = await fetchRowIdPage(
                        table,
                        rowId,
                        pageSize,
                        dataSource,
                        config
                    )
                } catch {
                    usesRowId = false
                    offset = 0
                    continue
                }
            } else {
                page = await fetchOffsetPage(
                    table,
                    offset,
                    pageSize,
                    dataSource,
                    config
                )
            }

            if (page.length === 0) break

            let output = ''

            for (const sourceRow of page) {
                const row = { ...sourceRow }
                const currentRowId = row.__starbase_rowid__
                delete row.__starbase_rowid__

                const columns = Object.keys(row).map(quoteIdentifier).join(', ')
                const values = Object.values(row).map(sqlLiteral).join(', ')

                output += `INSERT INTO ${quoteIdentifier(table)} (${columns}) VALUES (${values});\n`

                if (usesRowId) rowId = Number(currentRowId)
            }

            yield output

            if (usesRowId) {
                if (page.length < pageSize) break
            } else {
                offset += page.length
                if (page.length < pageSize) break
            }

            await breathe()
        }
    }

    yield 'COMMIT;\n'
}

export async function dumpDatabaseRoute(
    dataSource: DataSource,
    config: StarbaseDBConfiguration,
    pageSize = DEFAULT_PAGE_SIZE
): Promise<Response> {
    if (!Number.isInteger(pageSize) || pageSize < 1 || pageSize > 10_000) {
        return createResponse(undefined, 'Invalid export page size', 400)
    }

    try {
        // Resolve table metadata before committing an HTTP 200 stream.
        const tables = await getTables(dataSource, config)
        const iterator = dumpChunks(tables, dataSource, config, pageSize)

        const body = new ReadableStream<Uint8Array>({
            async pull(controller) {
                try {
                    const next = await iterator.next()

                    if (next.done) {
                        controller.close()
                        return
                    }

                    controller.enqueue(encoder.encode(next.value))
                } catch (error) {
                    controller.error(error)
                }
            },
            async cancel() {
                await iterator.return?.()
            },
        })

        return new Response(body, {
            headers: {
                'Content-Type': 'application/sql; charset=utf-8',
                'Content-Disposition':
                    'attachment; filename="database_dump.sql"',
                'Cache-Control': 'no-store',
            },
        })
    } catch (error) {
        console.error('Database Dump Error:', error)
        return createResponse(undefined, 'Failed to create database dump', 500)
    }
}

Comprehensive Unit Test Suite & Verification Harness:

Test Suite (`src/export/dump.test.ts`)

import { describe, expect, it, vi } from 'vitest'

const executeOperation = vi.fn()

vi.mock('.', () => ({
    executeOperation,
}))

vi.mock('../utils', () => ({
    createResponse: (_data: unknown, message: string, status: number) =>
        new Response(message, { status }),
}))

import { dumpDatabaseRoute } from './dump'

async function responseText(response: Response): Promise<string> {
    return await response.text()
}

describe('dumpDatabaseRoute', () => {
    it('streams a valid SQL dump without constructing a Blob', async () => {
        executeOperation.mockImplementation(async (queries: any[]) => {
            const { sql, params } = queries[0]

            if (sql.includes("FROM sqlite_master") && sql.includes("type = 'table'")) {
                return [{ name: 'users' }]
            }

            if (sql.includes('WHERE type = \'table\' AND name = ?')) {
                expect(params).toEqual(['users'])
                return [{ sql: 'CREATE TABLE users (id INTEGER, name TEXT)' }]
            }

            if (sql.includes('SELECT _rowid_')) {
                const after = params[0]

                if (after === 0) {
                    return [
                        {
                            __starbase_rowid__: 1,
                            id: 1,
                            name: "O'Reilly",
                        },
                    ]
                }

                return []
            }

            throw new Error(`Unexpected SQL: ${sql}`)
        })

        const response = await dumpDatabaseRoute(
            {} as any,
            { role: 'admin' },
            1
        )

        expect(response.status).toBe(200)
        expect(response.headers.get('Content-Type')).toContain('application/sql')
        expect(response.headers.get('Content-Disposition')).toContain(
            'database_dump.sql'
        )

        expect(await responseText(response)).toBe(
            [
                'BEGIN TRANSACTION;',
                '',
                '-- Table: "users"',
                'CREATE TABLE users (id INTEGER, name TEXT);',
                `INSERT INTO "users" ("id", "name") VALUES (1, 'O''Reilly');`,
                'COMMIT;',
                '',
            ].join('\n')
        )
    })

    it('uses keyset pagination and never emits OFFSET for rowid tables', async () => {
        const observedSql: string[] = []

        executeOperation.mockImplementation(async (queries: any[]) => {
            const { sql, params } = queries[0]
            observedSql.push(sql)

            if (sql.includes("FROM sqlite_master") && sql.includes("type = 'table'")) {
                return [{ name: 'events' }]
            }

            if (sql.includes('WHERE type = \'table\' AND name = ?')) {
                return [{ sql: 'CREATE TABLE events (id INTEGER)' }]
            }

            if (sql.includes('SELECT _rowid_')) {
                const after = params[0]

                if (after === 0) {
                    return [
                        { __starbase_rowid__: 10, id: 10 },
                        { __starbase_rowid__: 20, id: 20 },
                    ]
                }

                if (after === 20) {
                    return [{ __starbase_rowid__: 30, id: 30 }]
                }

                return []
            }

            throw new Error(`Unexpected SQL: ${sql}`)
        })

        const response = await dumpDatabaseRoute(
            {} as any,
            { role: 'admin' },
            2
        )

        const output = await responseText(response)

        expect(output.match(/INSERT INTO "events"/g)).toHaveLength(3)
        expect(observedSql.some((sql) => sql.includes('_rowid_ > ?'))).toBe(true)
        expect(observedSql.some((sql) => sql.includes('OFFSET'))).toBe(false)
    })

    it('falls back to bounded OFFSET pagination for WITHOUT ROWID tables', async () => {
        executeOperation.mockImplementation(async (queries: any[]) => {
            const { sql, params } = queries[0]

            if (sql.includes("FROM sqlite_master") && sql.includes("type = 'table'")) {
                return [{ name: 'lookup' }]
            }

            if (sql.includes('WHERE type = \'table\' AND name = ?')) {
                return [
                    {
                        sql: 'CREATE TABLE lookup (key TEXT PRIMARY KEY) WITHOUT ROWID',
                    },
                ]
            }

            if (sql.includes('SELECT _rowid_')) {
                throw new Error('no such column: _rowid_')
            }

            if (sql.includes('LIMIT ? OFFSET ?')) {
                return params[1] === 0 ? [{ key: 'first' }] : []
            }

            throw new Error(`Unexpected SQL: ${sql}`)
        })

        const response = await dumpDatabaseRoute(
            {} as any,
            { role: 'admin' },
            1
        )

        const output = await responseText(response)

        expect(output).toContain(
            `INSERT INTO "lookup" ("key") VALUES ('first');`
        )
    })

    it('serializes nulls, booleans, blobs, numbers, and quoted strings correctly', async () => {
        executeOperation.mockImplementation(async (queries: any[]) => {
            const { sql, params } = queries[0]

            if (sql.includes("FROM sqlite_master") && sql.includes("type = 'table'")) {
                return [{ name: 'values' }]
            }

            if (sql.includes('WHERE type = \'table\' AND name = ?')) {
                return [{ sql: 'CREATE TABLE values (a, b, c, d, e)' }]
            }

            if (sql.includes('SELECT _rowid_')) {
                return params[0] === 0
                    ? [
                          {
                              __starbase_rowid__: 1,
                              a: null,
                              b: true,
                              c: 42.5,
                              d: new Uint8Array([0, 15, 255]),
                              e: "it's safe",
                          },
                      ]
                    : []
            }

            throw new Error(`Unexpected SQL: ${sql}`)
        })

        const response = await dumpDatabaseRoute(
            {} as any,
            { role: 'admin' },
            10
        )

        expect(await responseText(response)).toContain(
            `VALUES (NULL, 1, 42.5, X'000fff', 'it''s safe');`
        )
    })

    it('returns a normal 500 response when metadata resolution fails', async () => {
        executeOperation.mockRejectedValueOnce(new Error('storage unavailable'))

        const response = await dumpDatabaseRoute(
            {} as any,
            { role: 'admin' }
        )

        expect(response.status).toBe(500)
        expect(await response.text()).toBe('Failed to create database dump')
    })

    it('rejects invalid page sizes before querying the Durable Object', async () => {
        const response = await dumpDatabaseRoute(
            {} as any,
            { role: 'admin' },
            0
        )

        expect(response.status).toBe(400)
        expect(executeOperation).not.toHaveBeenCalled()
    })
})

Run:

pnpm test --run src/export/dump.test.ts

Merge Readiness & Settlement Rails:

This implementation removes the full-database allocation path, maintains bounded export memory, protects SQL correctness, and includes a runnable regression suite covering pagination, WITHOUT ROWID, serialization, pre-stream failures, and invalid configuration. It is ready for immediate branch PR / merge after the stated test command is run in the repository checkout.

Settlement coordinates:

DOOM L1 Address: doom14ac0e785bffabe0357cb522608cf88c80a33cd7f3f925757
Solana (USDC / USDT / SOL): 2qi849TBq72XMpsp8PjR4YfegDEzc7BRFZaarwQ2tKgL
Base Mainnet (USDC / wDOOM) & Invoicing: isearch.xsolve@gmail.com

/attempt #59

Submitted PR #317 with incremental streaming via ReadableStream and constant-memory chunked pagination (CHUNK_SIZE = 1000). All 9/9 Vitest unit tests passing.

Navigation Menu

Repository navigation

Database dumps do not work on large databases #59

82 remaining items

🛠️ Production Implementation & Technical Verification

Production Source (`src/export/dump.ts`)

Test Suite (`src/export/dump.test.ts`)

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Database dumps do not work on large databases #59

Description

Activity

Brayden commented on Jan 24, 2025

b4s36t4 commented on Jan 24, 2025

Brayden commented on Jan 24, 2025

onyedikachi-david commented on Jan 25, 2025

algora-pbc commented on Jan 25, 2025

BenraouaneSoufiane commented on Jan 27, 2025

BenraouaneSoufiane commented on Feb 2, 2025

Kunal-Darekar commented on Mar 6, 2025

algora-pbc commented on Mar 7, 2025

Kunal-Darekar commented on Mar 16, 2025

ezzeddinp commented on Jan 30, 2026

whynice724-cell commented on Feb 25, 2026

82 remaining items

OP-88 commented on Jun 11, 2026

singhaditya21 commented on Jun 13, 2026

ThaiTrevor commented on Jun 14, 2026

Shaidyk commented on Jun 15, 2026

Shaidyk commented on Jun 15, 2026

hiSandog commented on Jul 3, 2026

Furox-Art commented on Sep 19, 2026

Furox-Art commented on Oct 7, 2026

Ollie520 commented on Oct 11, 2026

Yuvi1192p commented on Oct 24, 2026

isearchxsolve commented on Oct 24, 2026

🛠️ Production Implementation & Technical Verification

Production Source (src/export/dump.ts)

Test Suite (src/export/dump.test.ts)

MilindChatrabhuji commented on Oct 25, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Production Source (`src/export/dump.ts`)

Test Suite (`src/export/dump.test.ts`)