Core features

Bulk load

Import a large dataset in one transaction under relaxed durability, then restore the configured writer durability, so a big load pays one durability barrier instead of one per row.

Table of Contents

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 load a batch of rows, read the summary, choose a durability level, and handle the errors a load can raise.

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 you can run it end to end; swap the generator for your own data.

import-events.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
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))

The summary sums the row count and the changes rather than returning one object per row, 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.

import-events.ts
import { Sirannon } from '@delali/sirannon-db'
import { betterSqlite3 } from '@delali/sirannon-db/driver/better-sqlite3'
 
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,
  { 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. SQLite sanctions it for 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.

Know 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 the WAL is checkpointed at the restored level, so the loaded rows are written 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.

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 when you open the database.

const db = await sirannon.open('analytics', './data/analytics.db', {
  synchronous: 'full',
})

The queries and transactions guide covers 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 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 sets them side by side so 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. Every one extends SirannonError and carries 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 its durability could be restored. 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.
import { SirannonError } from '@delali/sirannon-db'
 
try {
  await db.bulkLoad('INSERT INTO events (id, payload) VALUES (?, ?)', paramsBatch, { durability: 'normal' })
} catch (err) {
  if (err instanceof SirannonError) {
    console.error(`Load failed [${err.code}]: ${err.message}`)
  }
}

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.