remult-sqlite-github

A Remult data provider that syncs SQLite to GitHub for serverless deployments.

Features

• Automatic sync: SQLite database is automatically synced to GitHub
• Recovery: Database is automatically recovered from GitHub if local copy is missing
• Conflict detection: Uses GitHub's SHA-based conflict detection
• Compression: Optional gzip compression for uploads
• GitHub App support: Supports both Personal Access Tokens and GitHub App authentication
• Rate limit handling: Built-in retry logic with exponential backoff

Installation

npm install remult-sqlite-github

Requirements

• Node.js >= 18.0.0
• better-sqlite3 >= 9.0.0
• remult >= 3.0.0

Quick Start

import { remultApi } from "remult/remult-sveltekit"; // or your framework
import { createGitHubDataProvider } from "remult-sqlite-github";

export const api = remultApi({
  dataProvider: createGitHubDataProvider({
    file: "./mydb.sqlite",
    github: {
      owner: "your-username",
      repo: "your-database-repo",
      token: process.env.GITHUB_TOKEN,
    },
  }),
  entities: [Task],
});

Configuration

Full Options

createGitHubDataProvider({
  // SQLite Configuration
  file: "./mydb.sqlite",       // Use "/tmp/mydb.sqlite" for serverless
  sqliteOptions: {
    foreignKeys: true,        // Enable foreign key constraints (default: true)
    busyTimeout: 5000,        // SQLite busy timeout in ms (default: 5000)
    pragmas: {                // Additional PRAGMA statements
      cache_size: -64000,
    },
  },

  // GitHub Configuration
  github: {
    owner: "your-username",   // Repository owner (required)
    repo: "your-database-repo", // Repository name (required)
    branch: "main",           // Branch to use (default: "main")
    path: "data",             // Path prefix in repo (default: "")

    // Authentication (one of these required)
    token: "ghp_xxxx",        // Personal Access Token

    // OR GitHub App authentication
    appId: 12345,
    privateKey: "-----BEGIN RSA PRIVATE KEY-----...",
    installationId: 67890,
  },

  // Serverless mode (recommended for Vercel, Netlify, AWS Lambda, etc.)
  serverless: true,           // Auto-configures syncOnWrite and disables interval sync

  // Sync Configuration (optional, serverless: true sets sensible defaults)
  sync: {
    snapshotInterval: 30000,  // Interval between snapshots in ms (default: 30 sec, 0 if serverless)
    snapshotOnChange: true,   // Only snapshot if changes detected (default: true)
    syncOnWrite: false,       // Sync immediately after writes (default: false, true if serverless)
    enableWal: false,         // Enable WAL mode segmentation (default: false)
    walThreshold: 1048576,    // WAL size threshold in bytes (default: 1MB)
    maxRetries: 3,            // Max retry attempts (default: 3)
    compression: false,       // Enable gzip compression (default: false)
  },

  // Event callback
  onEvent: (event) => {
    console.log("Sync event:", event);
  },

  verbose: false,             // Enable verbose logging (default: false)
});

Environment Variables Example

# GitHub Configuration
GITHUB_OWNER=your-username
GITHUB_REPO=your-database-repo
GITHUB_BRANCH=main
GITHUB_TOKEN=ghp_xxxx

# Or GitHub App (alternative to token)
# GITHUB_APP_ID=12345
# GITHUB_PRIVATE_KEY="-----BEGIN RSA PRIVATE KEY-----..."
# GITHUB_INSTALLATION_ID=67890

# Sync options
COMPRESSION=false
DEBUG=false

Events

The onEvent callback receives the following event types:

type GitHubSyncEvent =
  | { type: "snapshot_uploaded"; sha: string; size: number; compressed: boolean }
  | { type: "wal_uploaded"; index: number; size: number }
  | { type: "recovery_started"; branch: string }
  | { type: "recovery_completed"; sha: string }
  | { type: "commit_created"; sha: string; message: string }
  | { type: "sync_error"; error: Error; context: string; willRetry: boolean }
  | { type: "rate_limit_hit"; resetAt: Date; remaining: number }
  | { type: "initialized"; recovered: boolean };

GitHub Repository Structure

The database is stored in your GitHub repository with this structure:

[path/]
├── snapshot.db              # Database snapshot (or snapshot.db.gz if compressed)
└── snapshot.meta.json       # Snapshot metadata (timestamp, size, checksum)

Serverless Deployment

For serverless platforms (Vercel, Netlify, AWS Lambda, etc.), use the serverless: true option:

createGitHubDataProvider({
  file: "/tmp/mydb.sqlite",  // IMPORTANT: Use /tmp for serverless
  github: {
    owner: "your-username",
    repo: "your-database-repo",
    token: process.env.GITHUB_TOKEN,
  },
  serverless: true,  // Enables immediate sync after every write
});

What serverless: true does:

• Sets syncOnWrite: true - syncs to GitHub immediately after every write
• Sets snapshotInterval: 0 - disables interval-based sync (not useful for ephemeral instances)

Important for serverless:

• Use /tmp for the database path (only writable directory on most serverless platforms)
• The database is recovered from GitHub on each cold start
• Each write triggers an immediate sync to ensure data consistency across instances

Branching Strategy

Use different branches for different environments:

// Production
createGitHubDataProvider({
  github: { branch: "main", ... }
})

// Staging
createGitHubDataProvider({
  github: { branch: "staging", ... }
})

Advanced Usage

Manual Snapshot

import { BetterSqlite3GitHubDataProvider } from "remult-sqlite-github";

const provider = new BetterSqlite3GitHubDataProvider(options);
await provider.init();

// Force an immediate snapshot
await provider.snapshot();

// Or force sync (ignores snapshotOnChange setting)
await provider.forceSync();

Access Raw Database

const db = provider.rawDatabase;
if (db) {
  db.exec("VACUUM");
}

Graceful Shutdown

// Performs final snapshot if there are pending changes
await provider.close();

Limitations & Recommendations

Database Size

| Size | Status | Notes | |------|--------|-------| | < 50MB | Recommended | Optimal performance, fast uploads/downloads | | 50-100MB | Supported | Works but slower sync times | | > 100MB | Not supported | GitHub API rejects files over 100MB |

Recommendations for managing database size:

• Run VACUUM periodically to reclaim space: db.exec("VACUUM")
• Use appropriate data types (avoid storing large blobs)
• Consider archiving old data to separate tables/databases
• Monitor size with the onEvent callback (snapshot_uploaded event includes size)

Compression

Compression is off by default but recommended for databases over 10MB:

createGitHubDataProvider({
  sync: {
    compression: true,  // Enable gzip compression
  },
})

| Scenario | Compression | Rationale | |----------|-------------|-----------| | < 10MB database | Off | Overhead not worth it, fast enough uncompressed | | 10-50MB database | Recommended | 50-70% size reduction typical for SQLite | | 50-100MB database | Strongly recommended | Essential to stay within limits and reduce transfer time | | Text-heavy data | On | Excellent compression ratios (70-90%) | | Binary/blob data | Optional | May not compress well |

Compression trade-offs:

• Pros: Smaller uploads, faster transfers, lower bandwidth usage
• Cons: CPU overhead for compression/decompression, slightly slower snapshots

Sync Interval

The default snapshot interval is 30 seconds. Adjust based on your needs:

createGitHubDataProvider({
  sync: {
    snapshotInterval: 30 * 1000,       // 30 seconds (default)
    snapshotOnChange: true,            // Only sync if data changed (default)
  },
})

| Interval | Use Case | |----------|----------| | 30 seconds | General use (default) | | 1 minute | Slightly lower API usage | | 5 minutes | Low-frequency updates, cost-conscious | | 0 (disabled) | Manual sync only via forceSync() |

Note: With snapshotOnChange: true (default), snapshots only occur if data has changed, so a short interval won't cause unnecessary uploads.

Rate Limits

GitHub API has rate limits that this library handles automatically:

| Auth Type | Rate Limit | Notes | |-----------|------------|-------| | Personal Access Token | 5,000/hour | Sufficient for most use cases | | GitHub App | 5,000/hour per installation | Same limit, but can have multiple installations | | Unauthenticated | 60/hour | Not supported by this library |

Rate limit considerations:

• Each snapshot uses 2-4 API calls (get SHA, upload file, upload metadata, optional cleanup)
• Recovery uses 2-3 API calls
• With 30-second intervals: ~120 snapshots/hour max = ~480 API calls/hour (well under limit)
• With snapshotOnChange: true (default), actual API calls are much lower since only changes trigger uploads
• The library automatically waits and retries when rate limited

Data Durability

Important: This is an async-only sync strategy. Data written to SQLite is:

1. ✅ Immediately persisted to local SQLite file
2. ⏳ Uploaded to GitHub on next snapshot interval (or manual sync)

What this means:

• If your serverless instance terminates before the next snapshot, recent changes may be lost
• For critical writes, call forceSync() after important operations
• This is suitable for applications where occasional data loss is acceptable

// For critical operations
await taskRepo.insert({ title: "Important task" });
await provider.forceSync();  // Ensure it's uploaded immediately

Concurrent Access

This library is designed for single-writer scenarios:

• Multiple serverless instances reading is fine
• Multiple instances writing simultaneously may cause conflicts
• Conflicts are detected via SHA validation and will trigger retries
• For multi-writer scenarios, consider a traditional database

Other Limitations

• GitHub.com only: GitHub Enterprise Server is not supported
• Public/Private repos: Both work, but private repos require appropriate token scopes
• Branch protection: Ensure your token has permission to push to the configured branch
• Large files: Files over 100MB cannot be stored (GitHub API limit)
• Binary data: Large BLOBs in SQLite will increase database size significantly

Best Practices

1. Use a dedicated repository for your database (not your application repo)
2. Enable compression for databases over 10MB
3. Use branches for environments (main, staging, development)
4. Monitor events to track sync health and catch errors
5. Set appropriate intervals based on your data criticality
6. Run VACUUM periodically to keep database size manageable
7. Use GitHub App auth for production (more secure than PATs)

Demo

See the examples/sveltekit directory for a complete SvelteKit demo application.

cd examples/sveltekit
npm install
cp .env.example .env
# Edit .env with your GitHub credentials
npm run dev

License

MIT