tessl-labs/sqlite-node-best-practices

SQLite best practices for Node.js with better-sqlite3 — WAL mode, pragmas, foreign keys, STRICT tables, transactions, migrations, graceful shutdown, and query patterns

1.65x

Quality

98%

Does it follow best practices?

Impact

96%

1.65x

Average score across 5 eval scenarios

Securityby

Passed

No known issues

name:: sqlite-node-best-practices
description:: SQLite best practices for Node.js with better-sqlite3 — WAL mode, pragma configuration, foreign keys, transactions, migrations, STRICT tables, prepared statements, graceful shutdown, and performance patterns. Use when building or reviewing a Node.js app with SQLite, when you see better-sqlite3 imports, when setting up a new database, when debugging slow queries or SQLITE_BUSY errors, or when migrating from in-memory storage to SQLite.
keywords:: sqlite, better-sqlite3, node.js, database, WAL mode, migrations, indexing, prepared statements, transactions, sqlite pragmas, sqlite performance, STRICT tables, busy_timeout, foreign_keys, graceful shutdown, INTEGER PRIMARY KEY, JSON1, database setup node
license:: MIT

SQLite Best Practices for Node.js

Name: tessl-labs/sqlite-node-best-practices
Rating: 97.2 (1 reviews)
Author: tessl-labs

Production-quality SQLite setup and patterns for Node.js applications using better-sqlite3.

Library Choice: better-sqlite3

Use better-sqlite3 (synchronous, C++ binding) not sqlite3 (callback-based async). better-sqlite3 is:

Faster — synchronous calls avoid event-loop overhead for a local file database
Simpler — no callback/promise wrapping needed
Safer — transactions are straightforward (no async interleaving)

npm install better-sqlite3
npm install -D @types/better-sqlite3   # for TypeScript

Connection Setup — Get This Right First

Every SQLite connection needs these pragmas. Set them immediately when the database opens, before any queries:

import Database from 'better-sqlite3';

const db = new Database('./data.db');

// === Required pragmas — set these before any queries ===
db.pragma('journal_mode = WAL');          // Write-Ahead Logging — concurrent reads during writes
db.pragma('foreign_keys = ON');           // Enforce FK constraints (OFF by default!)
db.pragma('busy_timeout = 5000');         // Wait 5s instead of throwing SQLITE_BUSY on lock
db.pragma('synchronous = NORMAL');        // Safe with WAL, better performance than FULL
db.pragma('cache_size = -20000');         // 20MB cache (negative = KB)
db.pragma('temp_store = MEMORY');         // Keep temp tables/indexes in memory

export default db;

Why each pragma matters

Pragma	Default	Recommended	Why
`journal_mode`	DELETE	WAL	Allows concurrent reads while writing. Without this, readers block writers and vice versa.
`foreign_keys`	OFF	ON	SQLite silently ignores FK constraints by default. You will get orphaned rows and broken relationships. Must be set per-connection, not once.
`busy_timeout`	0	5000+	Without this, any concurrent access throws SQLITE_BUSY immediately instead of retrying.
`synchronous`	FULL	NORMAL	Safe with WAL mode. FULL forces an extra fsync per transaction for no benefit under WAL.
`cache_size`	-2000 (2MB)	-20000 (20MB)	Larger cache reduces disk I/O for read-heavy workloads.
`temp_store`	DEFAULT	MEMORY	Temp tables and indexes stay in RAM instead of hitting disk.

Critical: foreign_keys must be set per connection

PRAGMA foreign_keys = ON is not persisted in the database file. It must be set on every new connection. If you forget, INSERTs and DELETEs will silently ignore foreign key constraints.

For test environments

const DB_PATH = process.env.NODE_ENV === 'test' ? ':memory:' : './data.db';
const db = new Database(DB_PATH);
// Pragmas still needed for :memory: databases (especially foreign_keys)
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

Graceful Shutdown

Always close the database on process exit. Without this, WAL checkpointing may not complete and you risk data in the WAL file not being merged back:

process.on('SIGTERM', () => {
  db.close();
  process.exit(0);
});

process.on('SIGINT', () => {
  db.close();
  process.exit(0);
});

If running an HTTP server (Express, Fastify, etc.), close the database after the server stops accepting connections:

process.on('SIGTERM', () => {
  server.close(() => {
    db.close();
    process.exit(0);
  });
});

Schema Design

STRICT tables

Use STRICT tables to enforce type checking. Without STRICT, SQLite's type affinity system allows inserting a string into an INTEGER column silently:

CREATE TABLE menu_items (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  category TEXT NOT NULL,
  price_cents INTEGER NOT NULL,
  available INTEGER NOT NULL DEFAULT 1,
  created_at TEXT DEFAULT (datetime('now')) NOT NULL,
  updated_at TEXT DEFAULT (datetime('now')) NOT NULL
) STRICT;

STRICT tables only allow: INTEGER, REAL, TEXT, BLOB, ANY.

INTEGER PRIMARY KEY (rowid alias)

INTEGER PRIMARY KEY makes the column an alias for SQLite's internal rowid — this is the fastest possible lookup:

-- GOOD: id is an alias for rowid (fast)
id INTEGER PRIMARY KEY

-- With AUTOINCREMENT: prevents rowid reuse after deletion
-- Only use if you need guaranteed-monotonic IDs (slightly slower)
id INTEGER PRIMARY KEY AUTOINCREMENT

When to use AUTOINCREMENT: Only when you need to guarantee that a deleted row's ID is never reused (e.g., external systems reference your IDs). For most cases, plain INTEGER PRIMARY KEY is sufficient and slightly faster.

Money as integers

Store monetary values in cents (INTEGER), never as REAL/FLOAT:

-- BAD: floating point — 19.99 might become 19.990000000000002
price REAL

-- GOOD: store cents as integer — 1999 = $19.99
price_cents INTEGER NOT NULL

Dates as TEXT in ISO 8601

SQLite has no native date type. Store dates as ISO 8601 TEXT strings:

created_at TEXT DEFAULT (datetime('now')) NOT NULL

Query with SQLite date functions:

SELECT * FROM orders WHERE created_at > datetime('now', '-1 hour');
SELECT * FROM orders WHERE date(created_at) = date('now');

CHECK constraints for enums

SQLite has no enum type. Use CHECK constraints:

status TEXT NOT NULL DEFAULT 'received'
  CHECK (status IN ('received', 'preparing', 'ready', 'picked_up', 'cancelled'))

JSON columns with JSON1

SQLite has built-in JSON functions for semi-structured data:

-- Store as TEXT, query with json_extract
customizations TEXT NOT NULL DEFAULT '{}';

-- Query JSON fields
SELECT * FROM order_items
WHERE json_extract(customizations, '$.milk') = 'oat';

-- Aggregate with json_group_array / json_object
SELECT o.*, json_group_array(json_object(
  'id', oi.id,
  'name', mi.name,
  'quantity', oi.quantity
)) AS items
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN menu_items mi ON mi.id = oi.menu_item_id
WHERE o.id = ?
GROUP BY o.id;

Migrations

Use a sequential migration pattern. Never scatter CREATE TABLE IF NOT EXISTS inline throughout your app:

// src/db.ts
import Database from 'better-sqlite3';

const db = new Database('./data.db');

// Pragmas first — always
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');
db.pragma('busy_timeout = 5000');
db.pragma('synchronous = NORMAL');
db.pragma('cache_size = -20000');
db.pragma('temp_store = MEMORY');

// Migration tracking table
db.exec(`
  CREATE TABLE IF NOT EXISTS _migrations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    applied_at TEXT DEFAULT (datetime('now'))
  )
`);

function migrate(name: string, sql: string) {
  const exists = db.prepare('SELECT 1 FROM _migrations WHERE name = ?').get(name);
  if (!exists) {
    db.exec(sql);
    db.prepare('INSERT INTO _migrations (name) VALUES (?)').run(name);
    console.log(`Migration applied: ${name}`);
  }
}

// Migrations in order
migrate('001_menu_items', `
  CREATE TABLE menu_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    category TEXT NOT NULL,
    price_cents INTEGER NOT NULL,
    created_at TEXT DEFAULT (datetime('now')) NOT NULL,
    updated_at TEXT DEFAULT (datetime('now')) NOT NULL
  ) STRICT;

  CREATE INDEX idx_menu_items_category ON menu_items(category);
`);

migrate('002_orders', `
  CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'received'
      CHECK (status IN ('received', 'preparing', 'ready', 'picked_up', 'cancelled')),
    total_cents INTEGER NOT NULL,
    created_at TEXT DEFAULT (datetime('now')) NOT NULL,
    updated_at TEXT DEFAULT (datetime('now')) NOT NULL
  ) STRICT;

  CREATE TABLE order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    menu_item_id INTEGER NOT NULL REFERENCES menu_items(id),
    quantity INTEGER NOT NULL DEFAULT 1,
    unit_price_cents INTEGER NOT NULL,
    created_at TEXT DEFAULT (datetime('now')) NOT NULL
  ) STRICT;

  CREATE INDEX idx_orders_status ON orders(status);
  CREATE INDEX idx_orders_created_at ON orders(created_at);
  CREATE INDEX idx_order_items_order_id ON order_items(order_id);
  CREATE INDEX idx_order_items_menu_item_id ON order_items(menu_item_id);
`);

export default db;

Wrap each migration in a transaction

For safety, wrap each migration's SQL execution in a transaction so partial failures don't leave the schema in a broken state:

function migrate(name: string, sql: string) {
  const exists = db.prepare('SELECT 1 FROM _migrations WHERE name = ?').get(name);
  if (!exists) {
    const run = db.transaction(() => {
      db.exec(sql);
      db.prepare('INSERT INTO _migrations (name) VALUES (?)').run(name);
    });
    run();
    console.log(`Migration applied: ${name}`);
  }
}

Indexing

Always index

Foreign key columns — SQLite does NOT auto-index foreign keys (unlike PostgreSQL). Without an index, every DELETE on the parent table triggers a full table scan on the child.
Columns in WHERE clauses
Columns in ORDER BY
Columns in JOIN conditions

-- Foreign key indexes — REQUIRED
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_menu_item_id ON order_items(menu_item_id);

-- Frequently filtered columns
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Composite index for common query patterns (leftmost column first)
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

Don't over-index

Tables with fewer than ~1000 rows (full scan is faster)
Columns with very low cardinality on small tables (e.g., boolean flags)
Columns that are rarely queried
Every index slows down writes — only index what you query

Transactions

Wrap related writes in transactions. Without an explicit transaction, each statement is its own auto-committed transaction (very slow for bulk operations):

// BAD — 100 separate transactions, 100 fsyncs
for (const item of items) {
  db.prepare('INSERT INTO order_items ...').run(item);
}

// GOOD — single transaction, single fsync
const insertItems = db.transaction((items: CartItem[]) => {
  const stmt = db.prepare(
    'INSERT INTO order_items (order_id, menu_item_id, quantity, unit_price_cents) VALUES (?, ?, ?, ?)'
  );
  for (const item of items) {
    stmt.run(item.orderId, item.menuItemId, item.quantity, item.priceCents);
  }
});

insertItems(items);

Transaction with return value

const createOrder = db.transaction((customerName: string, items: CartItem[]) => {
  const order = db.prepare(
    'INSERT INTO orders (customer_name, total_cents) VALUES (?, ?)'
  ).run(customerName, calculateTotal(items));

  const orderId = order.lastInsertRowid;

  const stmt = db.prepare(
    'INSERT INTO order_items (order_id, menu_item_id, quantity, unit_price_cents) VALUES (?, ?, ?, ?)'
  );
  for (const item of items) {
    stmt.run(orderId, item.menuItemId, item.quantity, item.priceCents);
  }

  return getOrderById(Number(orderId));
});

Transactions are automatic rollback on throw

better-sqlite3 transactions automatically roll back if an exception is thrown. No manual ROLLBACK needed:

const transfer = db.transaction((fromId: number, toId: number, amount: number) => {
  const from = db.prepare('SELECT balance FROM accounts WHERE id = ?').get(fromId);
  if (from.balance < amount) {
    throw new Error('Insufficient funds'); // Automatically rolls back
  }
  db.prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?').run(amount, fromId);
  db.prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?').run(amount, toId);
});

Prepared Statements

Always use prepared statements. Never interpolate values into SQL strings:

// BAD — SQL injection risk, no query plan caching
db.exec(`SELECT * FROM orders WHERE id = ${id}`);

// GOOD — parameterized, safe, cached query plan
db.prepare('SELECT * FROM orders WHERE id = ?').get(id);

Cache frequently used statements

Prepare once at module level, reuse across calls:

// Prepare once
const getOrderById = db.prepare(`
  SELECT o.*, json_group_array(json_object(
    'id', oi.id,
    'menu_item_id', oi.menu_item_id,
    'quantity', oi.quantity,
    'unit_price_cents', oi.unit_price_cents
  )) AS items
  FROM orders o
  LEFT JOIN order_items oi ON oi.order_id = o.id
  WHERE o.id = ?
  GROUP BY o.id
`);

const listOrdersByStatus = db.prepare(
  'SELECT * FROM orders WHERE status = ? ORDER BY created_at DESC'
);

// Use many times
function findOrder(id: number) {
  return getOrderById.get(id);
}

function findOrdersByStatus(status: string) {
  return listOrdersByStatus.all(status);
}

Connection Management

Single shared connection

better-sqlite3 is synchronous and SQLite allows only one writer at a time. Use a single shared database instance across your application — do NOT create a connection per request:

// GOOD — single shared instance in db.ts, imported everywhere
// src/db.ts
const db = new Database('./data.db');
export default db;

// src/routes/orders.ts
import db from '../db';

// BAD — new connection per request (leaks file handles, misses pragma setup)
app.get('/orders', (req, res) => {
  const db = new Database('./data.db'); // Don't do this
  // ...
});

Testing Patterns

In-memory database for tests

export function createTestDb() {
  const db = new Database(':memory:');
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');
  // Run migrations...
  return db;
}

Reset between tests

export function resetDatabase(db: Database.Database) {
  // Temporarily disable FK checks for clean truncation
  db.pragma('foreign_keys = OFF');
  // Delete in reverse dependency order
  db.exec('DELETE FROM order_items');
  db.exec('DELETE FROM orders');
  db.exec('DELETE FROM menu_items');
  db.pragma('foreign_keys = ON');
}

Checklist

Connection setup:

WAL mode enabled (journal_mode = WAL)
Foreign keys ON (foreign_keys = ON)
Busy timeout set (busy_timeout = 5000)
synchronous = NORMAL (safe with WAL)
Graceful shutdown — db.close() on SIGTERM/SIGINT
Single shared connection (not per-request)
:memory: for test environments

Schema:

Migrations pattern (sequential, not inline CREATE TABLE)
STRICT tables where possible
INTEGER PRIMARY KEY (rowid alias) for primary keys
All foreign key columns indexed
CHECK constraints for enum-like columns
Money stored as INTEGER (cents)
Dates stored as TEXT (ISO 8601)
JSON columns with json_extract for semi-structured data

Queries:

All queries use prepared statements (never string interpolation)
Frequently used statements prepared once and reused
Related writes wrapped in transactions
Bulk inserts wrapped in a single transaction

Verifiers

node-sqlite-setup — SQLite connection setup with all required pragmas, graceful shutdown, and schema best practices
node-sqlite-queries — Query patterns: prepared statements, transactions, and bulk operations
node-sqlite-schema — Schema design: STRICT tables, indexes on FKs, migrations, type choices

Workspace: tessl-labs
Visibility: Public
Created: 14 days ago
Last updated: 10 days ago
Publish Source: CLI
Badge