name	SQLite
description	Expert guidance for SQLite database with better-sqlite3 Node.js driver including database setup, queries, transactions, migrations, performance optimization, and integration with TypeScript. Use this when working with embedded databases, better-sqlite3 driver, or SQLite operations.

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

Use WAL Mode: Better concurrency with journal_mode = WAL
Enable Foreign Keys: Always set foreign_keys = ON
Use Transactions: Batch operations in transactions for performance
Prepared Statements: Reuse prepared statements for frequently executed queries
Index Strategically: Index columns used in WHERE, JOIN, ORDER BY
Regular VACUUM: Run VACUUM periodically to defragment
Backup Regularly: Implement automated backup strategy
Monitor Size: SQLite works best under 1TB
Connection Pooling: Use single connection per process (better-sqlite3 is synchronous)
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

Install Skill

SKILL.md

SQLite with better-sqlite3

Overview

Installation

Basic Setup

Initialize Database

Database Configuration

Creating Tables

Basic Table Creation

Complex Schema

Queries

Prepared Statements

Insert Operations

Update Operations

Delete Operations

Transactions

Basic Transactions

Complex Transactions

Transaction Options

Advanced Queries

Joins

Subqueries

Full-Text Search (FTS5)

JSON Operations (SQLite 3.38+)

TypeScript Integration

Type-Safe Queries

Database Class Wrapper

Migrations

Manual Migrations

Performance Optimization

Indexes

Query Optimization

Connection Settings

Backup and Restore

Backup Database

Error Handling

Testing

Best Practices

Common Patterns

Repository Pattern

Resources