Better-SQLite3: Fast SQLite3 Bindings for Node.js

Key concepts:
├── Database - Synchronous connection to SQLite file
├── Statement - Prepared SQL statements for efficient execution
├── Prepared - Pre-compiled queries (.prepare() method)
├── Transactions - Atomic blocks (.transaction())
├── Hooks - Callbacks for database events (rollback, commit)
└── Extensions - Load SQLite extensions (JSON1, FTS5, etc.)

Unlike promises/callbacks:
  db.prepare('SELECT * FROM users WHERE id = ?').get(1) // sync!
  → { id: 1, name: 'Alice' } or null
  → No await, no then, no callbacks

Architecture Overview:

┌───────────────────────────────────┐
│         Node.js Application      │
│  (JavaScript / TypeScript)       │
└───────┬───────────────────────────┘
        │ better-sqlite3 API
┌───────▼───────────────────────────┐
│     C++ Native Addon (Node-API)  │
│  - Synchronous wrapper           │
│  - Memory management             │
│  - Error handling                │
└───────┬───────────────────────────┘
        │ C API
┌───────▼───────────────────────────┐
│        SQLite3 Library           │
│  - Embedded SQL engine           │
│  - B-tree storage                │
│  - ACID transactions             │
└───────┬───────────────────────────┘
        │
      [database.db file]

All in one process, no external server needed!

# Standard installation
npm install better-sqlite3

# Build prerequisites (if needed)
# Linux (Ubuntu/Debian)
sudo apt-get install -y build-essential python3

# macOS
xcode-select --install

# Verify installation
node -e "const Database = require('better-sqlite3'); const db = new Database(':memory:'); console.log('better-sqlite3 loaded successfully'); db.close();"

# Test with a real database file
node -e "const Database = require('better-sqlite3'); const db = new Database('test.db'); db.exec('CREATE TABLE test (id INTEGER PRIMARY KEY)'); console.log('Database created'); db.close();"

const Database = require('better-sqlite3');

// Create a file-based database (persistent)
const db = new Database('mydatabase.db');
console.log('Database created/opened: mydatabase.db');

// Create an in-memory database (temporary, lost on close)
const memDb = new Database(':memory:');

// Close the database when done
db.close();

// In TypeScript:
// import Database from 'better-sqlite3';
// const db = new Database('mydatabase.db');

const Database = require('better-sqlite3');
const db = new Database(':memory:');

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

// INSERT using parameterized statement
const insertUser = db.prepare('INSERT INTO users (name, email) VALUES (?, ?)');
insertUser.run('Alice', 'alice@example.com');
insertUser.run('Bob', 'bob@example.com');

console.log('Inserted users:', insertUser.changes, 'rows');

// SELECT single row
const getUser = db.prepare('SELECT * FROM users WHERE id = ?');
const user = getUser.get(1);
console.log('User 1:', user);

// SELECT all rows
const getAllUsers = db.prepare('SELECT * FROM users');
const users = getAllUsers.all();
console.log('All users:', users);

db.close();

// Named parameters
const findByEmail = db.prepare('SELECT * FROM users WHERE email = :email');
const found = findByEmail.get({ email: 'alice@example.com' });
console.log('Found:', found);

const Database = require('better-sqlite3');
const db = new Database(':memory:');

db.exec('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER)');

// Insert initial accounts
const insert = db.prepare('INSERT INTO accounts (id, balance) VALUES (?, ?)');
insert.run(1, 1000);
insert.run(2, 500);

// Define a transaction function
const transfer = db.transaction(function(fromId, toId, amount) {
  this.run('UPDATE accounts SET balance = balance - ? WHERE id = ?', amount, fromId);
  this.run('UPDATE accounts SET balance = balance + ? WHERE id = ?', amount, toId);
});

// Execute transaction
transfer(1, 2, 100);

// Check balances
const getBalance = db.prepare('SELECT * FROM accounts WHERE id = ?');
console.log('Account 1:', getBalance.get(1)); // { id: 1, balance: 900 }
console.log('Account 2:', getBalance.get(2)); // { id: 2, balance: 600 }

// Transaction with rollback on error
const riskyTransfer = db.transaction(function(amount) {
  this.run('UPDATE accounts SET balance = balance - ? WHERE id = 1', amount);
  this.run('UPDATE accounts SET nonexistent = 1 WHERE id = 1'); // Throws
});

try {
  riskyTransfer(500);
} catch (err) {
  console.log('Transaction rolled back:', err.message);
}

db.close();

const Database = require('better-sqlite3');
const db = new Database(':memory:');

// PRAGMA settings for performance
db.pragma('journal_mode = WAL');
db.pragma('synchronous = NORMAL');
db.pragma('cache_size = -64000');
db.pragma('temp_store = MEMORY');

// Row hooks
db.hook('commit', () => console.log('Transaction committed'));
db.hook('rollback', () => console.log('Transaction rolled back'));

// Custom JavaScript function in SQL
db.function('uppercase', (str) => str.toUpperCase());
const result = db.prepare('SELECT uppercase(?)').get('hello');
console.log(result);

// Custom aggregate function
db.aggregate('sumsq', class {
  constructor() { this.sum = 0; }
  step(value) { this.sum += value * value; }
  finalize() { return this.sum; }
});

db.exec('CREATE TABLE numbers (value INTEGER)');
const insert = db.prepare('INSERT INTO numbers VALUES (?)');
insert.run(1); insert.run(2); insert.run(3);
const sumSq = db.prepare('SELECT sumsq(value) FROM numbers').get();
console.log('Sum of squares:', sumSq);

db.close();

// Express + better-sqlite3 REST API
const express = require('express');
const Database = require('better-sqlite3');
const app = express();
app.use(express.json());

const db = new Database('api.db');
db.exec(`
  CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// GET /posts
app.get('/posts', (req, res) => {
  const posts = db.prepare('SELECT * FROM posts ORDER BY created_at DESC').all();
  res.json(posts);
});

// POST /posts
app.post('/posts', (req, res) => {
  const { title, content } = req.body;
  const insert = db.prepare('INSERT INTO posts (title, content) VALUES (?, ?)');
  const result = insert.run(title, content);
  res.json({ id: result.lastInsertRowid, title, content });
});

// GET /posts/:id
app.get('/posts/:id', (req, res) => {
  const post = db.prepare('SELECT * FROM posts WHERE id = ?').get(req.params.id);
  if (!post) return res.status(404).json({ error: 'Not found' });
  res.json(post);
});

app.listen(3000, () => console.log('API server on http://localhost:3000'));

process.on('SIGINT', () => {
  db.close();
  process.exit(0);
});

Common Gotchas:

DON'T:
  - Block event loop in production HTTP servers
  - Forget to close databases (leaks)
  - Use string concatenation in queries (SQL injection!)
  - Open multiple writable connections to same file
  - Run long operations in transactions

DO:
  - Use prepared statements with parameter binding
  - Close databases with db.close() when done
  - Use worker threads for blocking I/O in servers
  - Implement proper error handling with try/catch
  - Enable WAL mode for better concurrency
  - Use IMMEDIATE transactions for writes
  - Backup databases (just copy the .db file)

Comparison:
  sqlite3 (npm):      Async/callback-based, slower
  sql.js:            WASM-based, browser compatible
  better-sqlite3:    Sync, fast, native, recommended

// TypeScript usage
import Database from 'better-sqlite3';

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

const db = new Database('example.db');

const getUserById = db.prepare<User>('SELECT * FROM users WHERE id = ?');
const user: User | undefined = getUserById.get(1);

const getAllUsers = db.prepare<User[]>('SELECT * FROM users');
const users: User[] = getAllUsers.all();

process.on('SIGINT', () => {
  db.close();
  process.exit(0);
});

Quick Links:

GitHub:       https://github.com/WiseLibs/better-sqlite3
npm:          https://npm.io/package/better-sqlite3
SQLite Docs:  https://www.sqlite.org/docs.html

Star Count: 33K+ stars
Maintained by: Jameson Miller (@WiseLibs)
Created: 2016