Enabling WALWhat is wal?Write-Ahead Logging - a database journal mode that allows concurrent reads during writes and provides better crash safety. Ask AI for more mode

By default, SQLite uses a rollbackWhat is rollback?Undoing a database migration or deployment to restore the previous state when something goes wrong. Ask AI for more journal. This limits you to one writer at a time, and readers block writers. Write-Ahead Logging (WAL) changes the game:

await db.exec('PRAGMA journal_mode = WAL');

What WAL mode gives you:

• Concurrent reads during writes: Readers don't block writers anymore
• Better performance: 10-100x faster for write-heavy workloads
• Crash safety: No database corruption if your app crashes
• Automatic: SQLite manages everything

Side effects:

• Two additional files appear: database.db-wal (journal) and database.db-shm (shared memory)
• These are normal, don't delete them
• They're automatically cleaned up during checkpoints

Essential PRAGMA settings

PRAGMAs are SQLite's configuration commands. Set these on every connection:

async function configureDb(db) {
  // Enable foreign keys (disabled by default!)
  await db.exec('PRAGMA foreign_keys = ON');
  
  // Use WAL mode
  await db.exec('PRAGMA journal_mode = WAL');
  
  // Performance optimizations
  await db.exec('PRAGMA synchronous = NORMAL');  // Balance safety and speed
  await db.exec('PRAGMA cache_size = -64000');   // 64MB cache (negative = KB)
  await db.exec('PRAGMA temp_store = MEMORY');   // Temporary tables in RAM
  
  // Auto-checkpoint WAL every 1000 pages
  await db.exec('PRAGMA wal_autocheckpoint = 1000');
}

const db = await open({ filename: './app.db', driver: sqlite3.Database });
await configureDb(db);

Why foreign keys matter

By default, SQLite doesn't enforce foreign keyWhat is foreign key?A column in one database table that references a row in another table, creating a link between the two. Ask AI for more constraints. This means:

-- Without foreign keys enabled, this succeeds:
INSERT INTO posts (user_id, title) VALUES (99999, 'Hello');
-- Even if user 99999 doesn't exist!

Always enable foreign keys unless you have a specific reason not to.

Backup strategies

Backing up SQLite is simple because it's just a file. But you need to do it right.

Method 1: SQLite backup APIWhat is api?A set of rules that lets one program talk to another, usually over the internet, by sending requests and getting responses. Ask AI for more (recommended)

async function backupDatabase(sourceDb, destPath) {
  const backup = await sourceDb.backup(destPath);
  
  // Wait for backup to complete
  while (backup.remaining > 0) {
    await backup.step(100);  // Copy 100 pages at a time
    console.log(`Backup: ${(backup.pageCount - backup.remaining)}/${backup.pageCount} pages`);
  }
  
  await backup.finish();
  console.log('Backup complete!');
}

// Usage
await backupDatabase(db, './backups/app_backup_2024_01_15.db');

This method handles locking correctly and works even while the database is in use.

Method 2: Command line backup

# Create a consistent backup
sqlite3 app.db ".backup backup.db"

# Export to SQL (portable, but larger)
sqlite3 app.db ".dump" > backup.sql

# Import from SQL
sqlite3 new.db < backup.sql

Method 3: File copy (only when safe)

# ⚠️ Only copy when app isn't writing!
cp app.db backup.db

Optimizing with VACUUM

Over time, SQLite databases accumulate fragmentation. The VACUUM command rebuilds the database file, reclaiming unused space and optimizing layout:

// Rebuild the database (can take time for large DBs)
await db.exec('VACUUM');

When to VACUUM:

• After deleting large amounts of data
• Periodically (monthly) for maintenance
• When the database file is much larger than expected

Security considerations

SQLite has no user accounts or privileges. Security happens at the file system level:

# Set restrictive permissions (Unix/Linux)
chmod 600 database.db      # Owner read/write only
chmod 600 database.db-wal
chmod 600 database.db-shm

In your application:

// ❌ NEVER do this
app.get('/database.db', (req, res) => {
  res.sendFile('./database.db');  // Exposing your database!
});

// ✅ Keep database outside public folders
const db = await open({
  filename: '/var/data/app.db',  // Outside web root
  driver: sqlite3.Database
});

When to migrate to PostgreSQL

SQLite works great until it doesn't. Here are the signs it's time to upgrade:

The good news: migrating from SQLite to PostgreSQL is straightforward. Your SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. Ask AI for more queries are mostly compatible, and ORMs like Prisma make the switch seamless.

The hybrid approach

Many teams use both databases strategically:

Development: SQLite (fast setup, easy to reset)
      ↓
Testing: SQLite (in-memory or temp files)
      ↓
Production: PostgreSQL (scalability, features)

With an ORMWhat is orm?Object-Relational Mapping - a library that lets you interact with a database using your programming language's objects instead of writing raw SQL. Ask AI for more like Prisma, switching is just changing an environment variableWhat is environment variable?A value stored outside your code that configures behavior per deployment, commonly used for secrets like API keys and database URLs. Ask AI for more:

// schema.prisma
datasource db {
  provider = env("DB_PROVIDER")  // "sqlite" or "postgresql"
  url      = env("DATABASE_URL")
}

# Development
DB_PROVIDER=sqlite DATABASE_URL="file:./dev.db"

# Production
DB_PROVIDER=postgresql DATABASE_URL="postgresql://user:pass@host/db"

Quick reference: production checklist

SQLite in production isn't crazy, it's pragmatic. Start with SQLite, optimize it properly, and migrate to PostgreSQL only when you have clear evidence you need to. Your future self will appreciate the simplicity.