Time Crisis SQLite Storage Adapter


SQLite storage adapter for Time Crisis job scheduler. Provides a production-ready, durable storage backend using SQLite.

Installation

``bash
npm i @timecrisis/timecrisis @timecrisis/timecrisis-sqlite better-sqlite3
`

Features

- 💾 Production-ready SQLite storage implementation
- 🔄 Automatic schema migrations
- 🔒 Transaction support
- 🚀 Efficient job querying and locking
- 🧹 Built-in cleanup for old jobs

Usage

$3

`typescript
import Database from 'better-sqlite3';
import { JobScheduler } from 'timecrisis';
import { SQLiteJobStorage } from 'timecrisis-sqlite';

// Create SQLite connection
const db = new Database('jobs.sqlite');

// Enable WAL mode for better concurrency
db.pragma('journal_mode = WAL');
db.pragma('busy_timeout = 5000');

// Create storage instance
const storage = new SQLiteJobStorage(db);
await storage.init(); // This runs migrations

// Create scheduler
const scheduler = new JobScheduler({
storage,
node: 'worker-1',
pollInterval: 1000,
maxConcurrentJobs: 5,
});

// Start scheduler
await scheduler.start();
`

$3

For production use, configure SQLite for reliability:

`typescript
const db = new Database('jobs.sqlite', {
// Verbose error messages
verbose: process.env.NODE_ENV === 'development',

// File system synchronization level
fileMustExist: false,
});

// Performance and reliability settings
db.pragma('journal_mode = WAL');
db.pragma('synchronous = NORMAL');
db.pragma('busy_timeout = 5000');
db.pragma('foreign_keys = ON');
db.pragma('temp_store = MEMORY');
`

$3

Configure job retention periods:

`typescript
// Cleanup old jobs periodically
await storage.cleanup({
// Keep completed jobs for 7 days
jobRetention: 7,

// Keep failed jobs for 14 days
failedJobRetention: 14,

// Keep dead letter jobs for 30 days
deadLetterRetention: 30,
});
`

$3

Get storage-specific metrics:

`typescript
const metrics = await storage.getMetrics();
console.log(metrics);
// {
// jobs: {
// total: 1000,
// pending: 5,
// completed: 900,
// failed: 95,
// deadLetter: 10,
// scheduled: 50
// },
// averageDurationByType: {
// 'send-email': 245,
// 'process-video': 15000
// },
// failureRateByType: {
// 'send-email': 0.02,
// 'process-video': 0.15
// }
// }
`

$3

The storage adapter supports multiple workers accessing the same database through SQLite's WAL mode and distributed locking:

`typescript
// Worker 1
const worker1 = new JobScheduler({
storage: new SQLiteJobStorage(db1),
node: 'worker-1',
// Keep lock TTL short for faster failover
jobLockTTL: 30000,
leaderLockTTL: 30000,
});

// Worker 2
const worker2 = new JobScheduler({
storage: new SQLiteJobStorage(db2),
node: 'worker-2',
jobLockTTL: 30000,
leaderLockTTL: 30000,
});
`

$3

Regular maintenance tasks:

`typescript
// Vacuum database to reclaim space
db.pragma('vacuum');

// Analyze tables for query optimization
db.pragma('analyze');

// Optimize WAL file
db.pragma('wal_checkpoint(TRUNCATE)');

// Backup database
const backup = new Database('backup.sqlite');
db.backup(backup).then(() => {
backup.close();
});
`

$3

The adapter provides detailed error types:

`typescript
try {
await storage.createJob(job);
} catch (error) {
if (error instanceof JobNotFoundError) {
// Handle missing job
} else if (error instanceof JobRunNotFoundError) {
// Handle missing job run
} else if (error instanceof ScheduledJobNotFoundError) {
// Handle missing scheduled job
}
}
`

Performance Tips

WAL Configuration:

`typescript
// Larger WAL file for better write performance
db.pragma('page_size = 4096');
db.pragma('wal_autocheckpoint = 1000');
``

Contributing

See the main repository for contribution guidelines.