Skip to main content

​
Overview

The SQLite persistence adapter provides distributed locks, circuit breaker state management, and caching using SQLite as the backend. Ideal for single-node deployments, testing, or embedded applications where a lightweight database is preferred.

​
Installation

npm install @go-go-scope/persistence-sqlite sqlite3

​
Features

  • In-Memory Locks: Fast in-memory locks with TTL tracking
  • Circuit Breaker State: Persistent failure tracking with SQLite storage
  • Caching: Database-backed cache with TTL support
  • Checkpoints: Full checkpoint support for task recovery
  • Idempotency: Prevent duplicate operations (via SQLiteIdempotencyAdapter)
  • Auto Schema: Automatic table creation on connect
  • Single File: All data in a single SQLite database file
  • Zero Dependencies: No external services required

​
Basic Usage

import sqlite3 from 'sqlite3'
import { SQLiteAdapter } from '@go-go-scope/persistence-sqlite'
import { scope } from 'go-go-scope'

const db = new sqlite3.Database('/tmp/app.db')
const persistence = new SQLiteAdapter(db, { keyPrefix: 'myapp:' })

// Initialize tables
await persistence.connect()

await using s = scope({ persistence })

​
Configuration

​
db
Database
required
sqlite3 Database instance
​
options
PersistenceAdapterOptions
Configuration options

​
Database Schema

The adapter automatically creates the following tables on connect():

​
go_goscope_circuit

CREATE TABLE IF NOT EXISTS go_goscope_circuit (
  key TEXT PRIMARY KEY,
  state TEXT NOT NULL,
  failure_count INTEGER NOT NULL DEFAULT 0,
  last_failure_time INTEGER,
  last_success_time INTEGER,
  updated_at INTEGER NOT NULL
);

​
go_goscope_cache

CREATE TABLE IF NOT EXISTS go_goscope_cache (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  expires_at INTEGER,
  created_at INTEGER DEFAULT (unixepoch())
);

CREATE INDEX IF NOT EXISTS idx_cache_expires ON go_goscope_cache(expires_at);

​
go_goscope_checkpoints

CREATE TABLE IF NOT EXISTS go_goscope_checkpoints (
  id TEXT PRIMARY KEY,
  task_id TEXT NOT NULL,
  sequence INTEGER NOT NULL,
  timestamp INTEGER NOT NULL,
  progress INTEGER NOT NULL DEFAULT 0,
  data TEXT NOT NULL,
  estimated_time_remaining INTEGER,
  created_at INTEGER DEFAULT (unixepoch())
);

CREATE INDEX IF NOT EXISTS idx_checkpoints_task ON go_goscope_checkpoints(task_id);
CREATE INDEX IF NOT EXISTS idx_checkpoints_sequence ON go_goscope_checkpoints(task_id, sequence);

​
Lock Provider Methods

​
acquire(key, ttl, owner?)

Acquires an in-memory lock with TTL tracking. Locks are not persisted to the database for performance.
​
key
string
required
Lock identifier
​
ttl
number
required
Time-to-live in milliseconds
​
owner
string
Optional lock owner identifier (auto-generated if not provided)
Returns: Promise<LockHandle | null> - Lock handle or null if already locked
Locks are stored in-memory for performance. They are not persisted across application restarts. For persistent locks, use PostgreSQL or MySQL adapters.

​
extend(key, ttl, owner)

Extends the TTL of an existing lock.
​
key
string
required
Lock identifier
​
ttl
number
required
New time-to-live in milliseconds
​
owner
string
required
Lock owner identifier
Returns: Promise<boolean> - True if extended successfully

​
forceRelease(key)

Forces release of a lock regardless of owner.
​
key
string
required
Lock identifier

​
Cache Provider Methods

​
get(key)

Retrieves a cached value from SQLite.
​
key
string
required
Cache key
Returns: Promise<T | null> - Cached value or null if not found/expired

​
set(key, value, ttl?)

Stores a value in the cache (JSON serialized).
​
key
string
required
Cache key
​
value
T
required
Value to cache (must be JSON-serializable)
​
ttl
number
Time-to-live in milliseconds (optional)

​
delete(key)

Removes a cached value.
​
key
string
required
Cache key

​
has(key)

Checks if a key exists and is not expired.
​
key
string
required
Cache key
Returns: Promise<boolean>

​
clear()

Clears all cached values (respects key prefix if set).

​
keys(pattern?)

Lists all cache keys matching an optional pattern.
​
pattern
string
Optional SQL LIKE pattern with wildcards (e.g., 'user:*' becomes 'user:%')
Returns: Promise<string[]> - Array of matching keys

​
Circuit Breaker Methods

​
getState(key)

Retrieves circuit breaker state from SQLite.
​
key
string
required
Circuit breaker identifier
Returns: Promise<CircuitBreakerPersistedState | null>

​
setState(key, state)

Updates circuit breaker state.
​
key
string
required
Circuit breaker identifier
​
state
CircuitBreakerPersistedState
required
New state (state, failureCount, lastFailureTime, lastSuccessTime)

​
recordFailure(key, maxFailures)

Records a failure and potentially opens the circuit.
​
key
string
required
Circuit breaker identifier
​
maxFailures
number
required
Maximum failures before opening circuit
Returns: Promise<number> - Current failure count

​
recordSuccess(key)

Records a success and closes the circuit.
​
key
string
required
Circuit breaker identifier

​
Connection Example

import sqlite3 from 'sqlite3'
import { SQLiteAdapter } from '@go-go-scope/persistence-sqlite'

// File-based database
const db = new sqlite3.Database('./data/app.db')

const persistence = new SQLiteAdapter(db, {
  keyPrefix: 'myapp:',
})

// Initialize tables
await persistence.connect()

// Use with scope
await using s = scope({ persistence })

// Cleanup
await persistence.disconnect()

​
In-Memory Database

For testing or temporary data: 
import sqlite3 from 'sqlite3'
import { SQLiteAdapter } from '@go-go-scope/persistence-sqlite'

const db = new sqlite3.Database(':memory:')
const persistence = new SQLiteAdapter(db)

await persistence.connect()

await using s = scope({ persistence })

​
Bun Runtime Support

For Bun runtime, use the specialized Bun adapter: 
import { Database } from 'bun:sqlite'
import { SQLiteAdapter } from '@go-go-scope/persistence-sqlite-bun'

const db = new Database('./app.db')
const persistence = new SQLiteAdapter(db)

await persistence.connect()

await using s = scope({ persistence })

​
Best Practices

Use file-based databases for persistence or in-memory databases for testing. In-memory databases are extremely fast but don’t survive restarts.
Locks are stored in-memory and will be lost on application restart. For distributed locking across multiple instances, use Redis or PostgreSQL.
SQLite is single-writer. For high-concurrency scenarios with multiple processes, consider PostgreSQL or MySQL.

​
Performance Considerations

  • Locks are in-memory for maximum performance
  • Circuit breaker state and cache are persisted to SQLite
  • Use WAL mode for better concurrency: PRAGMA journal_mode=WAL;
  • Enable foreign keys if needed: PRAGMA foreign_keys=ON;
  • Consider connection pooling for Node.js clusters
  • Use indexes for checkpoint queries on large datasets

​
WAL Mode Example

import sqlite3 from 'sqlite3'
import { SQLiteAdapter } from '@go-go-scope/persistence-sqlite'

const db = new sqlite3.Database('./app.db')

// Enable WAL mode for better concurrency
db.run('PRAGMA journal_mode=WAL;')
db.run('PRAGMA synchronous=NORMAL;')

const persistence = new SQLiteAdapter(db)
await persistence.connect()

​
Use Cases

  • Single-node applications: Desktop apps, CLI tools, serverless functions
  • Development and testing: Fast iteration with zero external dependencies
  • Embedded systems: IoT devices, edge computing
  • Local caching: Fast local cache with persistence
  • Prototyping: Quick proof-of-concept without infrastructure

PostgreSQL Adapter

PostgreSQL-based persistence

MySQL Adapter

MySQL-based persistence

Build docs developers (and LLMs) love

Get started for freeTalk to us