SQLite

SQLite with better-sqlite3

Expert assistance with SQLite database operations using the better-sqlite3 Node.js driver.

Overview

SQLite is a lightweight, embedded SQL database engine:

• Zero Configuration: No server setup required, single file database
• ACID Compliant: Full transaction support with rollback
• High Performance: Excellent for read-heavy workloads
• Portable: Single file, easy backup and distribution
• better-sqlite3: Synchronous Node.js driver, faster than async alternatives

Installation

# Install better-sqlite3
npm install better-sqlite3
npm install --save-dev @types/better-sqlite3

# Optional: SQLite CLI tools
# Ubuntu/Debian
sudo apt-get install sqlite3

# macOS
brew install sqlite3

Basic Setup

Initialize Database

import Database from 'better-sqlite3';

// Create or open database
const db = new Database('mydb.sqlite');

// In-memory database (for testing)
const memDb = new Database(':memory:');

// Read-only mode
const readDb = new Database('mydb.sqlite', { readonly: true });

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

// Close database
db.close();

Database Configuration

import Database from 'better-sqlite3';

const db = new Database('mydb.sqlite', {
  verbose: console.log, // Log every SQL statement
  fileMustExist: false, // Create if doesn't exist
});

// Recommended pragmas
db.pragma('journal_mode = WAL'); // Write-Ahead Logging
db.pragma('synchronous = NORMAL'); // Balance safety/performance
db.pragma('foreign_keys = ON'); // Enable foreign keys
db.pragma('temp_store = MEMORY'); // Use memory for temp tables

Creating Tables

Basic Table Creation

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

// Create index
db.exec(`
  CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)
`);

Complex Schema

db.exec(`
  CREATE TABLE IF NOT EXISTS certificate_authorities (
    id TEXT PRIMARY KEY,
    subject_dn TEXT NOT NULL,
    serial_number TEXT NOT NULL UNIQUE,
    not_before INTEGER NOT NULL,
    not_after INTEGER NOT NULL,
    kms_key_id TEXT NOT NULL,
    certificate_pem TEXT NOT NULL,
    is_root BOOLEAN NOT NULL DEFAULT 0,
    parent_ca_id TEXT REFERENCES certificate_authorities(id),
    status TEXT NOT NULL CHECK(status IN ('active', 'revoked', 'expired')),
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
  );

  CREATE TABLE IF NOT EXISTS certificates (
    id TEXT PRIMARY KEY,
    ca_id TEXT NOT NULL REFERENCES certificate_authorities(id) ON DELETE CASCADE,
    subject_dn TEXT NOT NULL,
    serial_number TEXT NOT NULL UNIQUE,
    not_before INTEGER NOT NULL,
    not_after INTEGER NOT NULL,
    certificate_pem TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('active', 'revoked', 'expired')),
    revocation_date INTEGER,
    revocation_reason TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
  );

  CREATE INDEX IF NOT EXISTS idx_certificates_ca_id ON certificates(ca_id);
  CREATE INDEX IF NOT EXISTS idx_certificates_status ON certificates(status);
  CREATE INDEX IF NOT EXISTS idx_certificates_serial ON certificates(serial_number);
`);

Queries

Prepared Statements

// SELECT query
const getUser = db.prepare('SELECT * FROM users WHERE id = ?');
const user = getUser.get('user-123');

// SELECT all
const getAllUsers = db.prepare('SELECT * FROM users');
const users = getAllUsers.all();

// SELECT with multiple parameters
const findUsers = db.prepare('SELECT * FROM users WHERE name LIKE ? AND created_at > ?');
const results = findUsers.all('%John%', 1640000000);

// Named parameters
const getUserByEmail = db.prepare('SELECT * FROM users WHERE email = @email');
const user = getUserByEmail.get({ email: 'john@example.com' });

Insert Operations

// Single insert
const insertUser = db.prepare(`
  INSERT INTO users (id, name, email)
  VALUES (?, ?, ?)
`);

const info = insertUser.run('user-123', 'John Doe', 'john@example.com');
console.log(`Inserted ${info.changes} rows, last ID: ${info.lastInsertRowid}`);

// Insert with RETURNING (SQLite 3.35+)
const insertUserReturning = db.prepare(`
  INSERT INTO users (id, name, email)
  VALUES (?, ?, ?)
  RETURNING *
`);

const newUser = insertUserReturning.get('user-456', 'Jane Doe', 'jane@example.com');
console.log('Created user:', newUser);

// Bulk insert (fast)
const insert = db.prepare('INSERT INTO users (id, name, email) VALUES (?, ?, ?)');
const insertMany = db.transaction((users) => {
  for (const user of users) {
    insert.run(user.id, user.name, user.email);
  }
});

insertMany([
  { id: '1', name: 'Alice', email: 'alice@example.com' },
  { id: '2', name: 'Bob', email: 'bob@example.com' },
  { id: '3', name: 'Charlie', email: 'charlie@example.com' },
]);

Update Operations

// Update
const updateUser = db.prepare(`
  UPDATE users
  SET name = ?, email = ?
  WHERE id = ?
`);

const info = updateUser.run('John Smith', 'john.smith@example.com', 'user-123');
console.log(`Updated ${info.changes} rows`);

// Update with RETURNING
const updateReturning = db.prepare(`
  UPDATE users
  SET name = ?
  WHERE id = ?
  RETURNING *
`);

const updatedUser = updateReturning.get('New Name', 'user-123');

Delete Operations

// Delete
const deleteUser = db.prepare('DELETE FROM users WHERE id = ?');
const info = deleteUser.run('user-123');
console.log(`Deleted ${info.changes} rows`);

// Delete with condition
const deleteOldUsers = db.prepare(`
  DELETE FROM users
  WHERE created_at < ?
`);

const info = deleteOldUsers.run(Date.now() - 86400000); // 24 hours ago

Transactions

Basic Transactions

// Define transaction
const transferFunds = db.transaction((fromId, toId, amount) => {
  const debit = db.prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?');
  const credit = db.prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?');

  debit.run(amount, fromId);
  credit.run(amount, toId);
});

// Execute transaction (atomic)
transferFunds('account-1', 'account-2', 100);

Complex Transactions

const createOrder = db.transaction((order, items) => {
  // Insert order
  const insertOrder = db.prepare(`
    INSERT INTO orders (id, user_id, total)
    VALUES (?, ?, ?)
    RETURNING *
  `);

  const newOrder = insertOrder.get(order.id, order.userId, order.total);

  // Insert order items
  const insertItem = db.prepare(`
    INSERT INTO order_items (order_id, product_id, quantity, price)
    VALUES (?, ?, ?, ?)
  `);

  for (const item of items) {
    insertItem.run(newOrder.id, item.productId, item.quantity, item.price);
  }

  // Update inventory
  const updateInventory = db.prepare(`
    UPDATE products
    SET stock = stock - ?
    WHERE id = ?
  `);

  for (const item of items) {
    updateInventory.run(item.quantity, item.productId);
  }

  return newOrder;
});

// Use transaction
const order = createOrder(
  { id: 'order-1', userId: 'user-1', total: 150.00 },
  [
    { productId: 'prod-1', quantity: 2, price: 50.00 },
    { productId: 'prod-2', quantity: 1, price: 50.00 },
  ]
);

Transaction Options

// Immediate transaction (lock immediately)
const immediateTransaction = db.transaction((data) => {
  // Operations
});
immediateTransaction.immediate(); // Optional: make it immediate

// Deferred transaction (default)
const deferredTransaction = db.transaction((data) => {
  // Operations
});
deferredTransaction.deferred(); // Optional: make it deferred

// Exclusive transaction
const exclusiveTransaction = db.transaction((data) => {
  // Operations
});
exclusiveTransaction.exclusive(); // Lock database exclusively

Advanced Queries

Joins

const getUsersWithOrders = db.prepare(`
  SELECT
    u.id, u.name, u.email,
    COUNT(o.id) as order_count,
    SUM(o.total) as total_spent
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id
`);

const results = getUsersWithOrders.all();

Subqueries

const getTopCustomers = db.prepare(`
  SELECT *
  FROM users
  WHERE id IN (
    SELECT user_id
    FROM orders
    GROUP BY user_id
    HAVING SUM(total) > ?
  )
`);

const topCustomers = getTopCustomers.all(1000);

Full-Text Search (FTS5)

// Create FTS table
db.exec(`
  CREATE VIRTUAL TABLE documents_fts USING fts5(
    title,
    content,
    content=documents,
    content_rowid=id
  );

  -- Populate FTS index
  INSERT INTO documents_fts(rowid, title, content)
  SELECT id, title, content FROM documents;
`);

// Search
const search = db.prepare(`
  SELECT *
  FROM documents d
  JOIN documents_fts fts ON d.id = fts.rowid
  WHERE documents_fts MATCH ?
  ORDER BY rank
`);

const results = search.all('security AND encryption');

JSON Operations (SQLite 3.38+)

// Store JSON
const insertWithJson = db.prepare(`
  INSERT INTO users (id, name, metadata)
  VALUES (?, ?, json(?))
`);

insertWithJson.run('user-1', 'John', JSON.stringify({ role: 'admin', age: 30 }));

// Query JSON
const getAdmins = db.prepare(`
  SELECT *
  FROM users
  WHERE json_extract(metadata, '$.role') = 'admin'
`);

const admins = getAdmins.all();

TypeScript Integration

Type-Safe Queries

import Database from 'better-sqlite3';

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

const db = new Database('mydb.sqlite');

// Type-safe prepared statements
const getUserById = db.prepare<[string], User>('SELECT * FROM users WHERE id = ?');
const user: User | undefined = getUserById.get('user-123');

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

// Insert with types
interface InsertUser {
  id: string;
  name: string;
  email: string;
}

const insertUser = db.prepare<[string, string, string]>(`
  INSERT INTO users (id, name, email)
  VALUES (?, ?, ?)
`);

function createUser(user: InsertUser) {
  return insertUser.run(user.id, user.name, user.email);
}

Database Class Wrapper

import Database from 'better-sqlite3';

export class DatabaseClient {
  private db: Database.Database;

  constructor(filename: string) {
    this.db = new Database(filename);
    this.db.pragma('journal_mode = WAL');
    this.db.pragma('foreign_keys = ON');
  }

  getUserById(id: string): User | undefined {
    const stmt = this.db.prepare<[string], User>('SELECT * FROM users WHERE id = ?');
    return stmt.get(id);
  }

  createUser(user: InsertUser): User {
    const stmt = this.db.prepare<[string, string, string]>(`
      INSERT INTO users (id, name, email)
      VALUES (?, ?, ?)
      RETURNING *
    `);
    return stmt.get(user.id, user.name, user.email)!;
  }

  close() {
    this.db.close();
  }
}

Migrations

Manual Migrations

const migrations = [
  // Migration 1
  `CREATE TABLE users (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
  )`,

  // Migration 2
  `ALTER TABLE users ADD COLUMN created_at INTEGER DEFAULT (unixepoch())`,

  // Migration 3
  `CREATE INDEX idx_users_email ON users(email)`,
];

function migrate(db: Database.Database) {
  // Create migrations table
  db.exec(`
    CREATE TABLE IF NOT EXISTS migrations (
      id INTEGER PRIMARY KEY,
      applied_at INTEGER NOT NULL DEFAULT (unixepoch())
    )
  `);

  const getCurrentVersion = db.prepare('SELECT MAX(id) as version FROM migrations');
  const currentVersion = (getCurrentVersion.get() as any).version || 0;

  const insertMigration = db.prepare('INSERT INTO migrations (id) VALUES (?)');

  // Run pending migrations
  const runMigrations = db.transaction(() => {
    for (let i = currentVersion; i < migrations.length; i++) {
      console.log(`Running migration ${i + 1}`);
      db.exec(migrations[i]);
      insertMigration.run(i + 1);
    }
  });

  runMigrations();
}

// Run migrations
migrate(db);

Performance Optimization

Indexes

// Create indexes for frequently queried columns
db.exec(`
  CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
  CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
  CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);

  -- Composite index
  CREATE INDEX IF NOT EXISTS idx_orders_user_status
    ON orders(user_id, status);

  -- Partial index (SQLite 3.8+)
  CREATE INDEX IF NOT EXISTS idx_active_users
    ON users(email) WHERE status = 'active';
`);

// Analyze query performance
db.exec('ANALYZE');

Query Optimization

// Use EXPLAIN QUERY PLAN
const plan = db.prepare('EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = ?');
console.log(plan.all('john@example.com'));

// Batch operations in transactions
const insertMany = db.transaction((users) => {
  const insert = db.prepare('INSERT INTO users (id, name, email) VALUES (?, ?, ?)');
  for (const user of users) {
    insert.run(user.id, user.name, user.email);
  }
});

// This is ~1000x faster than individual inserts
insertMany(largeUserArray);

Connection Settings

// Optimize for performance
db.pragma('cache_size = -64000'); // 64MB cache
db.pragma('temp_store = MEMORY');
db.pragma('mmap_size = 30000000000'); // 30GB memory-mapped I/O
db.pragma('page_size = 4096'); // Match OS page size

// Check settings
console.log(db.pragma('cache_size', { simple: true }));
console.log(db.pragma('page_size', { simple: true }));

Backup and Restore

Backup Database

import fs from 'fs';

// Simple file copy (database must be closed or in WAL mode)
function backupDatabase(source: string, dest: string) {
  fs.copyFileSync(source, dest);
  // Also copy WAL and SHM files if they exist
  if (fs.existsSync(`${source}-wal`)) {
    fs.copyFileSync(`${source}-wal`, `${dest}-wal`);
  }
  if (fs.existsSync(`${source}-shm`)) {
    fs.copyFileSync(`${source}-shm`, `${dest}-shm`);
  }
}

// Online backup using VACUUM INTO (SQLite 3.27+)
function vacuumBackup(db: Database.Database, dest: string) {
  db.prepare(`VACUUM INTO ?`).run(dest);
}

// Export to SQL
function exportToSql(db: Database.Database, filename: string) {
  const tables = db.prepare(`
    SELECT name FROM sqlite_master
    WHERE type='table' AND name NOT LIKE 'sqlite_%'
  `).all() as { name: string }[];

  let sql = '';
  for (const { name } of tables) {
    // Get CREATE statement
    const createStmt = db.prepare(`
      SELECT sql FROM sqlite_master WHERE name = ?
    `).get(name) as { sql: string };
    sql += createStmt.sql + ';\n\n';

    // Get data
    const rows = db.prepare(`SELECT * FROM ${name}`).all();
    for (const row of rows) {
      const values = Object.values(row).map(v =>
        typeof v === 'string' ? `'${v.replace(/'/g, "''")}'` : v
      ).join(', ');
      sql += `INSERT INTO ${name} VALUES (${values});\n`;
    }
    sql += '\n';
  }

  fs.writeFileSync(filename, sql);
}

Error Handling

import Database from 'better-sqlite3';

try {
  const result = db.prepare('INSERT INTO users (id, email) VALUES (?, ?)').run('1', 'test@example.com');
} catch (error) {
  if (error instanceof Database.SqliteError) {
    switch (error.code) {
      case 'SQLITE_CONSTRAINT_UNIQUE':
        console.error('Unique constraint violation');
        break;
      case 'SQLITE_CONSTRAINT_FOREIGNKEY':
        console.error('Foreign key constraint violation');
        break;
      default:
        console.error('Database error:', error.message);
    }
  }
}

Testing

import Database from 'better-sqlite3';

// Use in-memory database for tests
let testDb: Database.Database;

beforeEach(() => {
  testDb = new Database(':memory:');
  testDb.pragma('foreign_keys = ON');

  // Setup schema
  testDb.exec(`
    CREATE TABLE users (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      email TEXT NOT NULL UNIQUE
    )
  `);
});

afterEach(() => {
  testDb.close();
});

test('creates user', () => {
  const insert = testDb.prepare('INSERT INTO users VALUES (?, ?, ?)');
  const info = insert.run('1', 'John', 'john@example.com');

  expect(info.changes).toBe(1);

  const user = testDb.prepare('SELECT * FROM users WHERE id = ?').get('1');
  expect(user).toEqual({ id: '1', name: 'John', email: 'john@example.com' });
});

Best Practices

1. Use WAL Mode: Better concurrency with journal_mode = WAL
2. Enable Foreign Keys: Always set foreign_keys = ON
3. Use Transactions: Batch operations in transactions for performance
4. Prepared Statements: Reuse prepared statements for frequently executed queries
5. Index Strategically: Index columns used in WHERE, JOIN, ORDER BY
6. Regular VACUUM: Run VACUUM periodically to defragment
7. Backup Regularly: Implement automated backup strategy
8. Monitor Size: SQLite works best under 1TB
9. Connection Pooling: Use single connection per process (better-sqlite3 is synchronous)
10. Error Handling: Handle constraint violations gracefully

Common Patterns

Repository Pattern

export class UserRepository {
  private db: Database.Database;

  private getByIdStmt: Database.Statement<[string]>;
  private getAllStmt: Database.Statement<[]>;
  private insertStmt: Database.Statement<[string, string, string]>;
  private updateStmt: Database.Statement<[string, string, string]>;
  private deleteStmt: Database.Statement<[string]>;

  constructor(db: Database.Database) {
    this.db = db;

    // Prepare statements once
    this.getByIdStmt = db.prepare('SELECT * FROM users WHERE id = ?');
    this.getAllStmt = db.prepare('SELECT * FROM users');
    this.insertStmt = db.prepare('INSERT INTO users (id, name, email) VALUES (?, ?, ?) RETURNING *');
    this.updateStmt = db.prepare('UPDATE users SET name = ?, email = ? WHERE id = ? RETURNING *');
    this.deleteStmt = db.prepare('DELETE FROM users WHERE id = ?');
  }

  findById(id: string): User | undefined {
    return this.getByIdStmt.get(id) as User | undefined;
  }

  findAll(): User[] {
    return this.getAllStmt.all() as User[];
  }

  create(user: Omit<User, 'created_at'>): User {
    return this.insertStmt.get(user.id, user.name, user.email) as User;
  }

  update(id: string, data: Partial<User>): User | undefined {
    return this.updateStmt.get(data.name, data.email, id) as User | undefined;
  }

  delete(id: string): boolean {
    const info = this.deleteStmt.run(id);
    return info.changes > 0;
  }
}

Resources

• SQLite Documentation: https://www.sqlite.org/docs.html
• better-sqlite3 Documentation: https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md
• SQLite Tutorial: https://www.sqlitetutorial.net/
• Performance Tips: https://www.sqlite.org/performance.html