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 sqlite3Basic 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 tablesCreating 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 agoTransactions
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 exclusivelyAdvanced 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
VACUUMperiodically 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