P
Persist

SQLite Adapter

Embedded SQLite storage with WAL mode, zero configuration, and full offline support.

Overview

SQLite is the default adapter for Persist. It stores data in a single file on disk with no external process required. This makes it ideal for desktop apps, mobile apps, CLI tools, and local-first workflows where simplicity matters.

Setup

import { createStore } from "@cuitty/persist";

const store = await createStore("my-app", {
  adapter: "sqlite",
  path: "./data/my-app.db",
});

If the file does not exist, Persist creates it along with the required schema tables. If it already exists, Persist validates the schema version and runs migrations if needed.

Configuration options

const store = await createStore("my-app", {
  adapter: "sqlite",
  path: "./data/my-app.db",
  sqlite: {
    walMode: true,         // default: true
    journalSizeLimit: 64 * 1024 * 1024, // 64 MB
    busyTimeout: 5000,     // wait 5s for locked database
    foreignKeys: true,     // enforce FK constraints
  },
});

WAL mode

Write-Ahead Logging (WAL) is enabled by default. WAL allows concurrent readers while a write is in progress, which significantly improves performance for stores that are read-heavy. Persist sets PRAGMA journal_mode=WAL on first connection.

In WAL mode, SQLite creates two companion files (-wal and -shm) alongside the main database file. These are normal operation artifacts and should not be deleted manually.

Performance tips

  • Batch writes: Use store.records.putMany() to insert multiple records in a single transaction. This is dramatically faster than individual put() calls.
  • Prefix queries: store.records.list({ prefix: "user:" }) uses an index scan, not a table scan.
  • Vacuum: Call store.maintenance.vacuum() periodically to reclaim space from deleted records.
// Batch insert example
await store.records.putMany([
  { key: "user:1", value: { name: "Alice" } },
  { key: "user:2", value: { name: "Bob" } },
  { key: "user:3", value: { name: "Carol" } },
]);

File locking

SQLite uses file-level locking. Only one process can write at a time. If you need multiple processes to write concurrently, consider the Postgres adapter or run a single writer process with multiple reader processes.

Supported store classes

The SQLite adapter supports all four store classes: records, events, blobs, and kv. Blobs are stored as BLOBs in the database. For large binary objects (over 100 MB), consider the S3 adapter instead.