SQLite - The Database Everywhere¶

npm install better-sqlite3

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

// Create or open a database file
const db = new Database('myapp.db');

// In-memory database (fast, no persistence - great for testing)
const testDb = new Database(':memory:');

// Enable WAL mode for better concurrent read performance
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');  // SQLite doesn't enforce FK by default

// Create table
db.exec(`
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        email TEXT NOT NULL UNIQUE,
        username TEXT NOT NULL,
        password_hash TEXT NOT NULL,
        role TEXT NOT NULL DEFAULT 'user' CHECK(role IN ('user', 'admin')),
        created_at TEXT NOT NULL DEFAULT (datetime('now'))
    )
`);

// Insert with prepared statement (parameterized - prevents SQL injection)
const insert = db.prepare(`
    INSERT INTO users (email, username, password_hash, role)
    VALUES (?, ?, ?, ?)
`);

// Single insert
insert.run('omar@example.com', 'omar_hacker', '$2b$12$hash...', 'user');

// Multiple inserts in a transaction (much faster than individual inserts)
const insertMany = db.transaction((users) => {
    for (const user of users) {
        insert.run(user.email, user.username, user.passwordHash, user.role);
    }
});

insertMany([
    { email: 'ali@example.com', username: 'ali_sec', passwordHash: 'hash1', role: 'admin' },
    { email: 'khaled@example.com', username: 'khaled_pwn', passwordHash: 'hash2', role: 'user' }
]);

// Get a single row
const user = db.prepare('SELECT * FROM users WHERE email = ?').get('omar@example.com');
// Returns undefined if not found

// Get all rows
const allUsers = db.prepare('SELECT * FROM users ORDER BY created_at DESC').all();

// Get first row or null
const admin = db.prepare('SELECT * FROM users WHERE role = ?').get('admin');

// Iterator (memory efficient for large result sets)
const iterator = db.prepare('SELECT * FROM users').iterate();
for (const user of iterator) {
    console.log(user.email);
}

// Pagination
const page = db.prepare('SELECT * FROM users ORDER BY id LIMIT ? OFFSET ?').all(20, 0);

// Enable WAL mode
db.pragma('journal_mode = WAL');

// Check current mode
const mode = db.pragma('journal_mode');
// ['wal'] - if it worked

// WAL checkpoint (force WAL content back to main database)
db.pragma('wal_checkpoint(TRUNCATE)');

// Multiple processes trying to write simultaneously:
// Process 1 starts a write transaction
// Process 2 tries to write - SQLITE_BUSY error

// Handling busy errors with retry
function withRetry(fn, maxRetries = 5) {
    for (let i = 0; i < maxRetries; i++) {
        try {
            return fn();
        } catch (err) {
            if (err.code === 'SQLITE_BUSY' && i < maxRetries - 1) {
                // Exponential backoff - wait longer between retries
                const waitMs = Math.min(10 * Math.pow(2, i), 1000);
                Atomics.wait(new Int32Array(new SharedArrayBuffer(4)), 0, 0, waitMs);
                continue;
            }
            throw err;
        }
    }
}

// With better-sqlite3's WAL mode:
// Use explicit transactions for atomic multi-statement operations
const transfer = db.transaction((fromId, toId, amount) => {
    const from = db.prepare('SELECT balance FROM accounts WHERE id = ?').get(fromId);
    if (from.balance < amount) throw new Error('Insufficient funds');
    db.prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?').run(amount, fromId);
    db.prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?').run(amount, toId);
});

// Security best practices for SQLite:

// 1. Prevent access via file permissions
// chmod 600 myapp.db (only owner can read/write)
// chown appuser:appgroup myapp.db

// 2. Parameterize ALL queries (SQL injection still applies)
const stmt = db.prepare('SELECT * FROM users WHERE email = ?');
stmt.get(userInput);  // Safe - parameterized

// 3. Don't use SQLite for sensitive production data
// SQLite has no encryption at rest (unless using SEE extension)
// Anyone who copies the .db file has all your data

// 4. Enable WAL mode for concurrent read safety
// 5. Use backup API for safe backups
db.backup('backup.db');  // Creates a consistent snapshot while database is running

// 6. Attach database caution
// ATTACH DATABASE 'other.db' AS other - avoid this with untrusted files