🚀 Drop-in Replacement for node:sqlite

This package provides 100% API compatibility with Node.js v24 built-in SQLite module (node:sqlite). You can seamlessly switch between this package and the built-in module without changing any code.

// Using Node.js built-in SQLite (requires Node.js 22.5.0+ and --experimental-sqlite flag)
const { DatabaseSync } = require("node:sqlite");

// Using @photostructure/sqlite (works on Node.js 20+ without any flags)
const { DatabaseSync } = require("@photostructure/sqlite");

// The API is identical - no code changes needed!

Overview

Node.js has an experimental built-in SQLite module that provides synchronous database operations with excellent performance. However, it's only available in the newest Node.js versions, and requires the --experimental-sqlite flag.

This package extracts that implementation into a standalone library that:

• Works everywhere: Compatible with Node.js 20+ without experimental flags
• Drop-in replacement: 100% API compatible with node:sqlite - no code changes needed
• Full-featured: Includes all SQLite extensions (FTS, JSON, math functions, etc.)
• High performance: Direct SQLite C library integration with minimal overhead
• Type-safe: Complete TypeScript definitions matching Node.js exactly
• Worker thread support: Full support for Node.js worker threads with proper isolation
• Future-proof: When node:sqlite becomes stable, switching back requires zero code changes

Installation

npm install @photostructure/sqlite

Quick Start

As a Drop-in Replacement

// If you have code using node:sqlite:
const { DatabaseSync } = require("node:sqlite");

// Simply replace with:
const { DatabaseSync } = require("@photostructure/sqlite");
// That's it! No other changes needed.

Basic Example

import { DatabaseSync } from "@photostructure/sqlite";

// Create an in-memory database
const db = new DatabaseSync(":memory:");

// Create a table
db.exec(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE
  )
`);

// Insert data
const insertStmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");
const result = insertStmt.run("Alice Johnson", "[email protected]");
console.log("Inserted user with ID:", result.lastInsertRowid);

// Query data
const selectStmt = db.prepare("SELECT * FROM users WHERE id = ?");
const user = selectStmt.get(result.lastInsertRowid);
console.log("User:", user);

// Clean up
db.close();

API Reference

Database Configuration Options

Custom Functions

// Register a simple custom SQL function
db.function("multiply", (a, b) => a * b);

// With options
db.function(
  "hash",
  {
    deterministic: true, // Same inputs always produce same output
    directOnly: true, // Cannot be called from triggers/views
  },
  (value) => {
    return crypto.createHash("sha256").update(String(value)).digest("hex");
  },
);

// Aggregate function
db.aggregate("custom_sum", {
  start: 0,
  step: (sum, value) => sum + value,
  result: (sum) => sum,
});

// Use in SQL
const result = db
  .prepare("SELECT custom_sum(price) as total FROM products")
  .get();
console.log(result.total);

Database Backup

// Simple backup
await db.backup("./backup.db");

// Backup with progress monitoring
await db.backup("./backup.db", {
  rate: 10, // Copy 10 pages per iteration
  progress: ({ totalPages, remainingPages }) => {
    const percent = (
      ((totalPages - remainingPages) / totalPages) *
      100
    ).toFixed(1);
    console.log(`Backup progress: ${percent}%`);
  },
});

// Backup specific attached database
db.exec("ATTACH DATABASE 'other.db' AS other");
await db.backup("./other-backup.db", {
  source: "other", // Backup the attached database instead of main
});

Database Restoration

// Restore from a backup file
import * as fs from "fs";

// Close the current database
db.close();

// Copy the backup file over the current database file
fs.copyFileSync("./backup.db", "./mydata.db");

// Reopen the database with the restored data
const restoredDb = new DatabaseSync("./mydata.db");

// Verify restoration
const count = restoredDb.prepare("SELECT COUNT(*) as count FROM users").get();
console.log(`Restored database has ${count.count} users`);

Worker Thread Support

This package has full support for Node.js worker threads. Each worker thread gets its own isolated SQLite environment.

// main.js
const { Worker } = require("worker_threads");

// Spawn multiple workers to handle database operations
const worker1 = new Worker("./db-worker.js");
const worker2 = new Worker("./db-worker.js");

// Send queries to workers
worker1.postMessage({
  sql: "SELECT * FROM users WHERE active = ?",
  params: [true],
});

// db-worker.js
const { parentPort } = require("worker_threads");
const { DatabaseSync } = require("@photostructure/sqlite");

// Each worker creates its own database connection
const db = new DatabaseSync("./app.db");

parentPort.on("message", ({ sql, params }) => {
  try {
    const stmt = db.prepare(sql);
    const results = stmt.all(...params);
    stmt.finalize();
    parentPort.postMessage({ success: true, results });
  } catch (error) {
    parentPort.postMessage({ success: false, error: error.message });
  }
});

Key points:

• Each worker thread must create its own DatabaseSync instance
• Database connections cannot be shared between threads
• SQLite's built-in thread safety (multi-thread mode) ensures data integrity
• No special initialization required - just use normally in each worker

Extension Loading

SQLite extensions can be loaded to add custom functionality. Extension loading requires explicit permission for security.

// Enable extension loading at database creation
const db = new DatabaseSync("./mydb.sqlite", {
  allowExtension: true,
});

// Enable extension loading (required before loading)
db.enableLoadExtension(true);

// Load an extension
db.loadExtension("./extensions/vector.so");

// Optionally specify an entry point
db.loadExtension("./extensions/custom.so", "sqlite3_custom_init");

// Disable extension loading when done for security
db.enableLoadExtension(false);

Session-based Change Tracking

SQLite's session extension allows you to record changes and apply them to other databases - perfect for synchronization, replication, or undo/redo functionality. This feature is available in both node:sqlite and @photostructure/sqlite, but not in better-sqlite3.

// Create a session to track changes
const session = db.createSession({ table: "users" });

// Make some changes
db.prepare("UPDATE users SET name = ? WHERE id = ?").run("Alice Smith", 1);
db.prepare("INSERT INTO users (name, email) VALUES (?, ?)").run(
  "Bob",
  "[email protected]",
);

// Get the changes as a changeset
const changeset = session.changeset();
session.close();

// Apply changes to another database
const otherDb = new DatabaseSync("./replica.db");
const applied = otherDb.applyChangeset(changeset, {
  onConflict: (conflict) => {
    console.log(`Conflict on table ${conflict.table}`);
    return constants.SQLITE_CHANGESET_REPLACE; // Resolve by replacing
  },
});

Parameter Binding

const stmt = db.prepare("SELECT * FROM users WHERE name = ? AND age > ?");

// Positional parameters
const users1 = stmt.all("Alice", 25);

// Named parameters (with object)
const stmt2 = db.prepare(
  "SELECT * FROM users WHERE name = $name AND age > $age",
);
const users2 = stmt2.all({ name: "Alice", age: 25 });

Using with TypeScript

interface User {
  id: number;
  name: string;
  email: string;
  created_at: string;
}

const db = new DatabaseSync("users.db");
const stmt = db.prepare("SELECT * FROM users WHERE id = ?");

const user = stmt.get(1) as User;
console.log(`User: ${user.name} <${user.email}>`);

Performance

This package provides performance comparable to Node.js's built-in SQLite and better-sqlite3, with:

• Synchronous operations - No async/await overhead
• Direct C library access - Minimal JavaScript ↔ native boundary crossings
• Full SQLite features - FTS5, JSON functions, R*Tree indexes, math functions, session extension (including changesets)

Performance is quite similar to node:sqlite and better-sqlite3, while significantly faster than async sqlite3 due to synchronous operations.

Platform Support

Platform	x64	ARM64	Notes
Linux	✅	✅	GLIBC 2.31+ (Ubuntu 20.04+, Debian 11+, RHEL 8+)
Alpine	✅	✅	Alpine 3.21+ (musl libc)
macOS	✅	✅	macOS 10.15+
Windows	✅	✅	Windows 10+

Linux Distribution Requirements

Supported distributions (with prebuilt binaries):

• Ubuntu 20.04 LTS and newer
• Debian 11 (Bullseye) and newer
• RHEL/CentOS/Rocky/Alma Linux 8 and newer
• Fedora 32 and newer
• Alpine Linux 3.21 and newer (musl libc)
• Any distribution with GLIBC 2.31 or newer

Not supported (GLIBC too old):

• Debian 10 (Buster) - GLIBC 2.28
• Ubuntu 18.04 LTS - GLIBC 2.27
• CentOS 7 - GLIBC 2.17
• Amazon Linux 2 - GLIBC 2.26

Note: While Node.js 20 itself supports these older distributions, our prebuilt binaries require GLIBC 2.31+ due to toolchain requirements. Users on older distributions can still compile from source if they have a compatible compiler (GCC 10+ with C++20 support).

Prebuilt binaries are provided for all supported platforms. If a prebuilt binary isn't available, the package will compile from source using node-gyp.

Development Requirements

• Node.js: v20 or higher
• Build tools (if compiling from source):
  • Linux: build-essential, python3 (3.8+), GCC 10+ or Clang 10+
  • macOS: Xcode command line tools
  • Windows: Visual Studio Build Tools 2019 or newer
• Python: 3.8 or higher (required by node-gyp v11)

Alternatives

When choosing a SQLite library for Node.js, you have several excellent options. Here's how @photostructure/sqlite compares to the alternatives:

🏷️ node:sqlite — Node.js Built-in Module

The official SQLite module included with Node.js 22.5.0+ (experimental)

✨ Pros:

• Zero dependencies — Built directly into Node.js
• Official support — Maintained by the Node.js core team
• Clean synchronous API — Simple, predictable blocking operations
• Full SQLite power — FTS5, JSON functions, R*Tree, sessions/changesets, and more

⚠️ Cons:

• Experimental status — Not yet stable for production use
• Requires Node.js 22.5.0+ — Won't work on older versions
• Flag required — Must use --experimental-sqlite to enable
• API may change — Breaking changes possible before stable release
• Limited real-world usage — Few production deployments to learn from

🎯 Best for: Experimental projects, early adopters, and preparing for the future when it becomes stable.

---

🚀 better-sqlite3 — The Performance Champion

The most popular high-performance synchronous SQLite library

✨ Pros:

• Blazing fast — 2-15x faster than async alternatives
• Rock-solid stability — Battle-tested in thousands of production apps
• Rich feature set — User functions, aggregates, virtual tables, extensions
• Extensive community — Large ecosystem with many resources

⚠️ Cons:

• Different API — Not compatible with Node.js built-in SQLite
• V8-specific — Requires separate builds for each Node.js version
• Synchronous only — No async operations (usually a feature, not a bug)
• Migration effort — Switching from other libraries requires code changes
• No session support — Doesn't expose SQLite's session/changeset functionality

🎯 Best for: High-performance applications where you want maximum speed and control over the API.

---

📦 sqlite3 — The Async Classic

The original asynchronous SQLite binding for Node.js

✨ Pros:

• Battle-tested legacy — 10+ years of production use
• Massive ecosystem — 4000+ dependent packages
• Truly asynchronous — Non-blocking operations won't freeze your app
• Extensive resources — Countless tutorials and Stack Overflow answers
• Extension support — Works with SQLCipher for encryption
• Node-API stable — One build works across Node.js versions

⚠️ Cons:

• Significantly slower — 2-15x performance penalty vs synchronous libs
• Callback complexity — Prone to callback hell without careful design
• Unnecessary overhead — SQLite is inherently synchronous anyway
• Memory management quirks — Exposes low-level C concerns to JavaScript
• Concurrency issues — Mutex contention under heavy load

🎯 Best for: Legacy codebases, apps requiring true async operations, or when you need SQLCipher encryption.

---

🎯 Quick Decision Guide

Choose @photostructure/sqlite when you want:

• ✅ Future-proof code that works with both this package AND node:sqlite
• ✅ Node.js API compatibility without waiting for stable release
• ✅ Broad Node.js support (v20+) without experimental flags
• ✅ Synchronous performance with a clean, official API
• ✅ Node-API stability — one build works across Node.js versions
• ✅ Zero migration path when node:sqlite becomes stable
• ✅ Session/changeset support for replication and synchronization

Choose better-sqlite3 when you want:

• ✅ The most mature and feature-rich synchronous SQLite library
• ✅ Maximum performance above all else
• ✅ A specific API design that differs from Node.js

Choose sqlite3 when you have:

• ✅ Legacy code using async/callback patterns
• ✅ Hard requirement for non-blocking operations
• ✅ Need for SQLCipher encryption

Choose node:sqlite when you're:

• ✅ Experimenting with bleeding-edge Node.js features
• ✅ Building proof-of-concepts for future migration
• ✅ Working in environments where you control the Node.js version

Contributing

Contributions are welcome! This project maintains 100% API compatibility with Node.js's built-in SQLite module. Please run tests with npm test and ensure code passes linting with npm run lint before submitting changes. When adding new features, include corresponding tests and ensure they match Node.js SQLite behavior exactly.

The project includes automated sync scripts to keep up-to-date with:

• Node.js SQLite implementation via npm run sync:node
• SQLite library updates via npm run sync:sqlite

Security

This project takes security seriously and employs multiple layers of protection:

• Automated scanning: npm audit, Snyk, OSV Scanner, CodeQL (JS/TS and C++), and TruffleHog
• Weekly security scans: Automated checks for new vulnerabilities
• Rapid patching: Security fixes are prioritized and released quickly
• Memory safety: Validated through ASAN, valgrind, and comprehensive testing

Running Security Scans Locally

# Install security tools (OSV Scanner, etc.)
./scripts/setup-security-tools.sh

# Run all security scans
npm run security

For details, see our Security Policy. To report vulnerabilities, please email [email protected].

License

MIT License - see LICENSE for details.

This package includes SQLite, which is in the public domain, as well as code from the Node.js project, which is MIT licensed.

Support

• 🐛 Bug reports: GitHub Issues
• 💬 Questions: GitHub Discussions
• 📧 Security issues: see SECURITY.md

Current Implementation Status

✅ Fully Supported:

• Core SQLite operations (all database and statement methods)
• User-defined functions and aggregate functions
• SQLite sessions/changesets for change tracking
• Database backup and restoration
• Worker thread support with proper isolation
• Extension loading with security controls
• All SQLite data types including BigInt
• Statement iterators
• Transaction control

All features of Node.js's built-in SQLite module are now fully implemented!

For concurrent access within the same process, multiple database connections work well with WAL mode enabled.

---

Development

This project was built with substantial assistance from Claude Code, an AI coding assistant.

Note that all changes are human-reviewed before merging.

Project Timeline

• 900+ lines of C++

  `find . -name "*.cpp" -o -name "*.h" -not -path "./node_modules/*" -not -path "./vendored/*" -not -path "*/upstream/*" -exec wc -l {} +`
• 17,000 lines of comprehensive TypeScript tests

  `find . -name "*.ts" -not -path "./node_modules/*" -not -path "./vendored/*" -not -path "*/upstream/*" -exec wc -l {} +`
• 400+ tests with full API compliance running in both ESM and CJS modes
• Multi-platform CI/CD with automated builds
• Security scanning and memory leak detection
• Automated sync from Node.js and SQLite upstream
• Robust benchmarking suite including all popular Node.js SQLite libraries

Development Cost

• API usage: ~$650 in Claude tokens
• Actual cost: $200/month MAX 20x plan subscription
• Time saved: Weeks of manual porting work

This project demonstrates how AI-assisted development can accelerate complex system programming while maintaining high code quality through comprehensive testing and human oversight.

---

Note: This package is not affiliated with the Node.js project. It extracts and redistributes Node.js's SQLite implementation under the MIT license.