SQLite for Desktop/Local Apps

better-sqlite3 setup, WAL mode, concurrent access, and performance tuning

What You’ll Build

After following this guide, you will have a working implementation of sqlite for desktop/local apps in your project. SQLite is the best database for local-first apps, CLI tools, and Electron apps. Zero configuration, no server process, and incredibly fast for read-heavy workloads. This guide covers optimal setup with better-sqlite3 (synchronous, 5-10x faster than the async driver), WAL mode for concurrent reads, and common patterns.

Use Cases & Problems Solved

• Set up a production-ready database layer with type-safe queries
• Manage schema changes through migrations without data loss
• Avoid raw SQL injection risks and inconsistent data access patterns

Prerequisites

• Node.js 18+

Step-by-Step Implementation

Install better-sqlite3

The following snippet shows how to install better-sqlite3. Copy this into your project and adjust the values for your environment.

npm install better-sqlite3

Database setup with WAL mode

The following snippet shows how to database setup with wal mode. Copy this into your project and adjust the values for your environment.

const Database = require('better-sqlite3');

const db = new Database('app.db');

// Enable WAL mode for concurrent reads + single writer
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

// Create tables
db.exec(`
  CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    content TEXT DEFAULT '',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

Prepared statements for performance

The following snippet shows how to prepared statements for performance. Copy this into your project and adjust the values for your environment.

// Prepared statements — parse SQL once, execute many times
const insert = db.prepare('INSERT INTO notes (title, content) VALUES (?, ?)');
const getById = db.prepare('SELECT * FROM notes WHERE id = ?');
const getAll = db.prepare('SELECT * FROM notes ORDER BY updated_at DESC');
const search = db.prepare('SELECT * FROM notes WHERE title LIKE ?');

// Use transactions for batch operations (100x faster)
const insertMany = db.transaction((notes) => {
  for (const note of notes) insert.run(note.title, note.content);
});

insertMany([
  { title: 'Note 1', content: 'Hello' },
  { title: 'Note 2', content: 'World' },
]);

// Query
const note = getById.get(1);  // { id: 1, title: 'Note 1', ... }
const allNotes = getAll.all(); // Array of all notes

⚠️ Don’t Do This

// Extremely slow — each insert is a separate transaction
for (const item of thousandItems) {
  db.prepare('INSERT INTO items (name) VALUES (?)').run(item.name);
}
// Takes ~4 seconds for 1000 rows!

const insertMany = db.transaction((items) => {
  const stmt = db.prepare('INSERT INTO items (name) VALUES (?)');
  for (const item of items) stmt.run(item.name);
});
insertMany(thousandItems);
// Takes ~20ms for 1000 rows!

Testing

Verify your implementation with these tests:

// __tests__/sqlite-for-desktop-local-apps.test.ts
import { describe, it, expect } from 'vitest';

describe('SQLite for Desktop/Local Apps', () => {
  it('should initialize without errors', () => {
    // Test that the setup completes successfully
    expect(() => setup()).not.toThrow();
  });

  it('should handle the primary use case', async () => {
    const result = await execute();
    expect(result).toBeDefined();
    expect(result.success).toBe(true);
  });

  it('should handle edge cases', async () => {
    // Test with empty/null input
    const result = await execute(null);
    expect(result.error).toBeDefined();
  });
});

Verification

node -e "
const Database = require('better-sqlite3');
const db = new Database(':memory:');
db.exec('CREATE TABLE test (id INTEGER PRIMARY KEY, val TEXT)');
db.prepare('INSERT INTO test (val) VALUES (?)').run('hello');
console.log(db.prepare('SELECT * FROM test').all());
"