v0.3.0: Production Concurrency Utilities

🔒 Production Concurrency Utilities

Based on Jellyfin's real-world SQLite locking experience, v0.3.0 adds production-ready concurrency helpers for high-concurrency deployments.

New Features

Concurrency Utilities (opt-in)

• enableWAL(db, options?) - Configure Write-Ahead Logging mode

  • Better read concurrency (multiple readers during writes)
  • Optimal pragmas: synchronous=NORMAL, busy_timeout=5000ms, cache_size=64MB
  • Customizable options for advanced use cases

• withRetry(operation, options?) - Exponential backoff retry logic

  • Automatically retries on SQLITE_BUSY/database locked errors
  • Default 5 retries with exponential backoff: 10ms → 20ms → 40ms → 80ms → 160ms
  • Optional jitter to prevent thundering herd
  • Works with both sync and async operations

• WriteQueue - Pessimistic locking queue

  • FIFO queue serializes all write operations
  • Eliminates lock contention entirely
  • Predictable latency for high-concurrency scenarios
  • Handles both sync and async operations

• initializeConcurrency(db, options?) - Convenience function

  • Combines WAL mode setup with write queue initialization

Comprehensive Documentation (1,892 lines)

• CONCURRENCY-BEST-PRACTICES.md (500+ lines)

  • Three locking strategies: No-Lock, Optimistic (retry), Pessimistic (queue)
  • WAL mode configuration and best practices
  • Transaction batching patterns (20x speedup)
  • Multi-process architecture patterns
  • Production monitoring and debugging

• COMPETITIVE-ANALYSIS.md (400+ lines)

  • Comparison with Neo4j, ArangoDB, OrientDB, Memgraph, TinkerPop, gun.js, level-graph
  • Performance benchmarks: 500x smaller footprint, 3000x faster startup
  • Use case decision matrices

• LIMITATIONS.md (500+ lines)

  • Enhanced concurrency section with Jellyfin findings
  • WAL mode, retry logic, and write queue patterns
  • Mitigation strategies with code examples

Testing

• 32 comprehensive tests for concurrency utilities (all passing)
• Tests cover all three locking strategies
• Integration tests with full concurrency stack
• TDD approach with tests written first

Changed

• Exposed Database.db as public readonly for advanced usage
  • Allows direct access to better-sqlite3 instance
  • Enables pragma configuration (WAL mode, timeouts, etc.)
  • Maintains encapsulation with readonly modifier

Performance

• WAL mode enables concurrent reads during writes
• WriteQueue eliminates lock contention overhead
• 7.11x faster merge operations with proper indexing (from v0.2.0)
• 20x speedup with transaction batching

Use Cases

Perfect for:

• Web applications with concurrent users (>10 simultaneous writes)
• API servers with bursty write patterns
• Background job processing with multiple workers
• Desktop/mobile apps needing offline-first architecture
• Production deployments requiring reliability

Migration Guide

All concurrency utilities are opt-in. Existing code continues to work without changes.

Basic (Most Common):

import { GraphDatabase, enableWAL } from 'sqlite-graph';

const db = new GraphDatabase('./graph.db');
enableWAL(db); // Better read concurrency

With Retry Logic:

import { withRetry } from 'sqlite-graph';

await withRetry(() =>
  db.mergeNode('Company', { name: 'TechCorp' }, { industry: 'SaaS' })
);

High-Concurrency:

import { WriteQueue } from 'sqlite-graph';

const writeQueue = new WriteQueue();
await writeQueue.enqueue(() =>
  db.createNode('Job', { title: 'Engineer' })
);

Full Stack (Maximum Safety):

import { initializeConcurrency, withRetry } from 'sqlite-graph';

const { db, writeQueue } = initializeConcurrency(new GraphDatabase('./graph.db'));

await writeQueue.enqueue(() =>
  withRetry(() =>
    db.mergeNode('Job', { url }, { title: 'Engineer' })
  )
);

Full Changelog

See CHANGELOG.md for complete details.

v0.2.0 - MERGE Operations

🚀 Major Features

Cypher-like MERGE Operations

This release adds idempotent upsert operations similar to Neo4j's Cypher MERGE, solving the critical problem of handling duplicate data from external sources.

New Methods

• mergeNode() - Create if not exists, update if exists

  • Match on single or multiple properties
  • onCreate / onMatch semantics for conditional property setting
  • Returns { node, created } indicating whether node was created or matched
  • Automatic conflict detection when multiple nodes match

• mergeEdge() - Ensure unique relationships

  • Prevents duplicate edges between nodes
  • Property merging on match
  • ON CREATE / ON MATCH support

• Index Management

  • createPropertyIndex(nodeType, property, unique?) - Create JSON property indexes
  • listIndexes() - View all merge indexes
  • dropIndex(indexName) - Remove indexes
  • Performance warnings in dev mode when indexes are missing

Example Usage

// Daily job scraper - run multiple times safely
db.createPropertyIndex('Job', 'url', true);

const { node: job, created } = db.mergeNode(
  'Job',
  { url: 'https://example.com/job/123' },  // Match on URL
  { title: 'Engineer', status: 'active' },
  {
    onCreate: { discovered: Date.now(), applicationStatus: 'not_applied' },
    onMatch: { lastSeen: Date.now() }
  }
);

console.log(created ? 'New job discovered!' : 'Job updated');

📊 Performance

• 1.39x faster than manual SELECT-then-INSERT/UPDATE pattern
• Atomic transactions ensure data consistency
• JSON property indexes enable efficient lookups on large datasets

📚 Documentation

• MERGE-DESIGN.md - Complete design specification
• merge-patterns.ts - 7 comprehensive examples including:
  • Simple upserts
  • ON CREATE / ON MATCH tracking
  • Company deduplication
  • Bulk ETL imports
  • Conflict handling
  • Performance benchmarks

✅ Testing

• 33 new unit tests with 100% coverage of merge functionality
• Tests for creation, matching, conflicts, and edge cases
• Index management test suite

🎯 Use Cases

Perfect for:

• ETL pipelines that run repeatedly
• Job scrapers and data importers
• Distributed systems requiring retry-safe operations
• Data deduplication (companies, skills, tags)
• Tracking discovery vs. update timestamps

Breaking Changes

None - this is a backward-compatible feature addition.

---

Full Changelog: https://github.com/michaeloboyle/sqlite-graph/blob/main/CHANGELOG.md