> Complete content index: https://sirannon.sondelali.com/llms.txt # Bulk load > Import a large dataset in one transaction under relaxed durability, then restore the configured writer durability, so a big load crosses one durability barrier for the whole dataset. URL: https://sirannon.sondelali.com/docs/bulk-load Section: Core features Version: 0.3 (@delali/sirannon-db@0.3.0, latest) Loading a large dataset through many small committed transactions is slow, and it can stall the whole server. At `synchronous = 'full'`, every commit calls `fsync`, and because the engine is synchronous, each `fsync` blocks the event loop until the disk confirms. Tens of thousands of blocking `fsync` calls back to back stop the server from answering anything else. `db.bulkLoad` runs the whole dataset in one transaction under a relaxed durability level, then restores the configured level before it resolves. This page builds one file, `import-events.ts`, step by step. You will load a batch of rows, read the summary, choose a durability level, handle the errors a load can raise, and then move the same import to the client SDK so that it runs over a server. Sirannon writes the database file into a directory you create yourself, so make one before you run any of the code below. ```bash mkdir -p data ``` ## Load a dataset `bulkLoad(sql, paramsBatch, options?)` runs one statement across many parameter sets. Pass `paramsBatch` as an array of parameter arrays, one per row, the same shape `executeBatch` accepts. Start `import-events.ts` with an open database and a dataset of event rows. The example generates 250,000 rows with `Array.from` so that you can run it end to end; swap the generator for your own data. ```ts title="import-events.ts" const driver = betterSqlite3() const sirannon = new Sirannon({ driver }) const db = await sirannon.open('analytics', './data/analytics.db') await db.execute('CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, payload TEXT NOT NULL)') const events = Array.from({ length: 250_000 }, (_, index) => ({ id: index + 1, payload: { type: 'page_view', path: '/products', sessionId: index % 5000 }, })) const paramsBatch = events.map((event) => [event.id, JSON.stringify(event.payload)]) const summary = await db.bulkLoad('INSERT INTO events (id, payload) VALUES (?, ?)', paramsBatch) console.log(JSON.stringify(summary, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2)) ``` ```json result { "rowsLoaded": 250000, "changes": 250000 } ``` The summary sums the row count and the changes, so a load of millions of rows never holds millions of result objects in memory. `rowsLoaded` counts the parameter sets you passed, and `changes` counts the rows the statement touched. ## Choose a durability level The `durability` option sets the writer durability the load runs under, and it takes `'off'` or `'normal'`. It defaults to `'off'`. Add the option to make the choice explicit. ```ts title="import-events.ts" const driver = betterSqlite3() const sirannon = new Sirannon({ driver }) const db = await sirannon.open('analytics', './data/analytics.db') await db.execute('CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, payload TEXT NOT NULL)') const events = Array.from({ length: 250_000 }, (_, index) => ({ id: index + 1, payload: { type: 'page_view', path: '/products', sessionId: index % 5000 }, })) const paramsBatch = events.map((event) => [event.id, JSON.stringify(event.payload)]) const summary = await db.bulkLoad('INSERT INTO events (id, payload) VALUES (?, ?)', paramsBatch) // [!code --] // [!code ++:5] const summary = await db.bulkLoad( 'INSERT INTO events (id, payload) VALUES (?, ?)', paramsBatch, { durability: 'off' }, ) console.log(JSON.stringify(summary, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2)) ``` `'off'` is the fastest and the least safe. It suits a load that starts from an empty database and that you can re-run after a power loss. A crash during an `'off'` load can corrupt the file, so recovery means running the load again from scratch. Use `'normal'` for a load into a database that already holds data you cannot afford to lose, because it keeps WAL corruption safety while it still drops the per-commit `fsync`. ## What the load restores The load holds the single writer for its whole duration, so no other write commits under the relaxed level and no two loads race on the durability setting. On success Sirannon checkpoints the WAL at the restored level, so it writes the loaded rows into the main database file before the call resolves. That checkpoint runs synchronously and blocks the event loop for the length of the WAL flush, which grows with the size of the load. A database opened with the [`backups`](/docs/backup-chains) option is the one exception, because it checkpoints nothing here whatever `checkpoint` says. A checkpoint at the end of the load would let SQLite overwrite the frames that load wrote before anything had captured them, so the backup cycle checkpoints once it holds those frames. The rows are in the database either way, and the only difference is when they reach the main database file. Whichever level you pick, the load restores the configured `synchronous` level when it finishes. That configured level comes from `DatabaseOptions` and defaults to `'normal'`. A crash mid-load leaves the configured level in force on the next open, because `PRAGMA synchronous` is connection state that SQLite never stores in the database file. Set the level a load returns to by passing `synchronous: 'full'` to `open`, which the [queries and transactions guide](/docs/queries-and-transactions) covers alongside the rest of `DatabaseOptions`. ## Load over the server The server exposes the same path. `POST /db/:id/load` runs a bulk load over HTTP, and the `load` WebSocket message runs it over a WebSocket connection. Both take the SQL, the parameter batch, and an optional `durability`, and both reply with `{ rowsLoaded, changes }`. One load must fit under the server's `maxBodyBytes` cap, so send a larger dataset as several sequential loads, each of which restores durability on its own. The [server guide](/docs/server) documents the routes, the message shapes, and the body-size cap. Load is one of three write shapes the server offers, alongside transaction and batch. The [server guide](/docs/server) sets them side by side so that you can pick the right one for the write in front of you. ## Handle errors A bulk load adds a few codes on top of the [core error codes](/docs/queries-and-transactions). Every one extends `SirannonError` and includes a machine-readable `code`. - The load raises `INVALID_DURABILITY` when `durability` is neither `'off'` nor `'normal'`. - The load raises `DURABILITY_RESTORE_FAILED` when it committed but the writer connection failed before it could restore that durability. Read this code as "the load succeeded, do not run it again". - The server returns `BULK_LOAD_UNSUPPORTED` when the database's execution target does not support bulk load. - The server returns `PAYLOAD_TOO_LARGE` when a load sent over the server exceeds `maxBodyBytes`. ```ts title="import-events.ts" import { Sirannon } from '@delali/sirannon-db' // [!code --] import { Sirannon, SirannonError } from '@delali/sirannon-db' // [!code ++] const driver = betterSqlite3() const sirannon = new Sirannon({ driver }) const db = await sirannon.open('analytics', './data/analytics.db') await db.execute('CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, payload TEXT NOT NULL)') const events = Array.from({ length: 250_000 }, (_, index) => ({ id: index + 1, payload: { type: 'page_view', path: '/products', sessionId: index % 5000 }, })) const paramsBatch = events.map((event) => [event.id, JSON.stringify(event.payload)]) // [!code --:7] const summary = await db.bulkLoad( 'INSERT INTO events (id, payload) VALUES (?, ?)', paramsBatch, { durability: 'off' }, ) console.log(JSON.stringify(summary, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2)) // [!code ++:13] try { const summary = await db.bulkLoad( 'INSERT INTO events (id, payload) VALUES (?, ?)', paramsBatch, { durability: 'off' }, ) console.log(JSON.stringify(summary, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2)) } catch (err) { if (err instanceof SirannonError) { console.error(`Load failed [${err.code}]: ${err.message}`) } } ``` ```json result { "rowsLoaded": 250000, "changes": 250000 } ``` `DURABILITY_RESTORE_FAILED` is the one code you do not retry. The rows are already committed, so running the load again would double them unless the statement is idempotent. ## Move the import to the client SDK The [client SDK](/docs/client-sdk) runs the same import against a remote server and does the splitting for you. `db.loadAll(sql, rows, options?)` takes any iterable or async iterable of parameter sets, batches it into requests, and sends each batch through the server's load route so that rows stream from a file or the network with one batch in memory at a time. It returns the summed `{ rowsLoaded, changes }` across every batch. Swap the embedded open for a client connection and the `bulkLoad` call for `loadAll`; the server must already expose the `analytics` database with a `maxBodyBytes` large enough for one batch, and the [server guide](/docs/server) covers standing one up. ```ts title="import-events.ts" // [!code --:7] const driver = betterSqlite3() const sirannon = new Sirannon({ driver }) const db = await sirannon.open('analytics', './data/analytics.db') // [!code ++:8] const client = new SirannonClient('http://localhost:9876', { transport: 'websocket', requestTimeout: 0, }) const db = client.database('analytics') await db.execute('CREATE TABLE IF NOT EXISTS events (id INTEGER PRIMARY KEY, payload TEXT NOT NULL)') const events = Array.from({ length: 250_000 }, (_, index) => ({ id: index + 1, payload: { type: 'page_view', path: '/products', sessionId: index % 5000 }, })) const paramsBatch = events.map((event) => [event.id, JSON.stringify(event.payload)]) // [!code --:13] try { const summary = await db.bulkLoad( 'INSERT INTO events (id, payload) VALUES (?, ?)', paramsBatch, { durability: 'off' }, ) console.log(JSON.stringify(summary, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2)) } catch (err) { if (err instanceof SirannonError) { console.error(`Load failed [${err.code}]: ${err.message}`) } } // [!code ++:9] const summary = await db.loadAll( 'INSERT INTO events (id, payload) VALUES (?, ?)', paramsBatch, { batchSize: 5000, durability: 'off' }, ) console.log(JSON.stringify(summary, (k, v) => (typeof v === 'number' ? Number(v.toFixed(2)) : v), 2)) client.close() ``` ```json result { "rowsLoaded": 250000, "changes": 250000 } ``` The summary matches the embedded run, because the server executes the same bulk load underneath; the client only splits the dataset into batches. `batchSize` sets the rows per request and defaults to `1000`. The client sends each batch as one request, so it must fit under the server's `maxBodyBytes`; widen that cap or lower the batch size for wide rows, and a non-positive or non-integer value throws `INVALID_ARGUMENT` before the client sends anything. `durability` passes through to each server-side load and takes the same `'off'` or `'normal'` levels chosen above. The `requestTimeout: 0` makes the WebSocket transport wait indefinitely; its default of 30 seconds fits ordinary queries, and one batch of a huge import can legitimately run longer. `loadAll` also manages the checkpoint for you. It marks only the final batch as the checkpointing one, so the fsyncing WAL checkpoint runs once at the end, while every batch still restores the configured durability on the server. An import that stops partway never leaves the writer at the relaxed level. When you need per-batch control, `db.load(sql, paramsBatch, durability?, checkpoint?)` sends a single batch and exposes the `checkpoint` flag directly; pass `checkpoint: false` on every call but the last, exactly as you would when splitting loads by hand. That flag reaches a server database opened with the [`backups`](/docs/backup-chains) option and changes nothing there, because such a database leaves every checkpoint to its own backup cycle. Send the flag as you would anywhere else, and the server applies whichever rule its own configuration sets.