Core features

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.

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 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.

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.

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, 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) 
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 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 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 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 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 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. 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.
import-events.ts
import { Sirannon } from '@delali/sirannon-db'
import { Sirannon, SirannonError } 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))
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}`)
  }
}

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 .

Move the import to the client SDK

The 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 covers standing one up.

import-events.ts
import { Sirannon, SirannonError } 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')
import { SirannonClient } from '@delali/sirannon-db/client'
 
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)])
 
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}`)
  }
}
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()

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 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.