@sidequest/sqlite-backend

@sidequest/sqlite-backend

SQLite backend driver for the Sidequest job processing system. This is the default backend for development and testing due to its simplicity and zero-configuration setup. Not recommended for production use.

Summary

This package provides a SQLite implementation of the Sidequest backend interface, enabling you to use SQLite as the storage layer for your job queue. It offers all the features of Sidequest including job management, queue operations, statistics, and cleanup routines, while leveraging SQLite's simplicity, zero-configuration setup, and file-based storage.

The SQLite backend extends the SQLBackend base class and provides optimized SQL queries for job processing, including atomic job claiming operations using SQLite transactions and efficient local file-based storage perfect for development, testing, and single-server deployments.

Installation

Install the SQLite backend package:

npm install @sidequest/sqlite-backend

You'll also need to install the main Sidequest package if you haven't already:

npm install sidequest

Basic Usage with Sidequest

Sidequest Configuration

Configure Sidequest to use the SQLite backend:

import { Sidequest } from "sidequest";

await Sidequest.start({
  backend: {
    driver: "@sidequest/sqlite-backend",
    config: "./sidequest.sqlite",
  },
  queues: [{ name: "default", priority: 10, workers: 2 }],
});

Connection String Examples

Default SQLite File:

backend: {
  driver: '@sidequest/sqlite-backend',
  config: './sidequest.sqlite'
}

Custom Database Path:

backend: {
  driver: '@sidequest/sqlite-backend',
  config: '/var/data/myapp.sqlite'
}

In-Memory Database (Testing):

backend: {
  driver: '@sidequest/sqlite-backend',
  config: ':memory:'
}

Features

• Zero Configuration - No database server setup required, just specify a file path
• File-Based Storage - Self-contained database in a single file for easy deployment
• ACID Transactions - Full transaction support for data integrity and safe job claiming
• WAL Mode - Write-Ahead Logging enabled by default for better concurrent access
• Lightweight - Minimal resource footprint, perfect for development and small deployments
• Migration Support - Automatic database schema management with Knex.js migrations
• Portable - Database files can be easily backed up, moved, or shared

WAL Mode and Concurrency

The SQLite backend automatically enables WAL (Write-Ahead Logging) mode for improved concurrent access. This provides several benefits:

• Better Concurrency: Allows multiple readers and one writer simultaneously
• Reduced Lock Contention: Minimizes SQLITE_BUSY errors during concurrent job processing
• Improved Performance: Faster writes and better throughput for job queue operations
• Safer Transactions: More reliable atomic operations for job claiming

WAL Mode Files

When WAL mode is enabled, SQLite creates additional files alongside your main database:

• sidequest.sqlite - Main database file
• sidequest.sqlite-wal - Write-ahead log file
• sidequest.sqlite-shm - Shared memory index file

These files are managed automatically by SQLite and should not be manually edited or deleted.

Limitations

While WAL mode significantly improves concurrency, SQLite is still not recommended for high-concurrency production deployments. For production use with multiple workers or distributed systems, consider using:

• PostgreSQL (@sidequest/postgres-backend) - Best for production
• MySQL (@sidequest/mysql-backend) - Good for production
• MongoDB (@sidequest/mongo-backend) - Alternative for production

License

LGPL-3.0-or-later