sqlite

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

```bash

# 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

```

Initialize Database

```typescript

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

```typescript

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

```

Basic Table Creation

```typescript

// 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

```typescript

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);

`);

```

Prepared Statements

```typescript

// 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

```typescript

// 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

```typescript

// 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

```typescript

// 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

```

Basic Transactions

```typescript

// 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

```typescript

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

```typescript

// 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

```

Joins

```typescript

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

```typescript

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)

```typescript

// 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+)

```typescript

// 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();

```

Type-Safe Queries

```typescript

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

```typescript

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();

}

}

```

Manual Migrations

```typescript

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);

```

Indexes

```typescript

// 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

```typescript

// 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

```typescript

// 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 Database

```typescript

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);

}

```

```typescript

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);

}

}

}

```

```typescript

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' });

});

```

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

Repository Pattern

```typescript

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 {

return this.insertStmt.get(user.id, user.name, user.email) as User;

}

update(id: string, data: Partial): 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;

}

}

```

• 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