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