Architecture Guide: Handling SQLite Locks & WAL Mode Across Multiple Electron Windows

 You have likely encountered it: the dreaded SQLITE_BUSY: database is locked error.

In a standard Node.js server environment, SQLite behaves predictably. But inside Electron, things get complicated. You launch a second window, attempt a write operation, and suddenly your application crashes or hangs. Even enabling Write-Ahead Logging (WAL) doesn't seem to solve the concurrency issues completely.

This is a structural problem, not a library bug. This guide details the architectural root cause of SQLite locking in Electron and provides a production-grade implementation using the IPC Broker Pattern to solve it permanently.

The Root Cause: Process Isolation vs. File System

To solve the locking issue, you must understand the mismatch between Electron's process model and SQLite's concurrency model.

1. The Electron Process Model

Electron uses a multi-process architecture based on Chromium.

• The Main Process: Runs the application lifecycle and Node.js primitives. There is only one.
• The Renderer Processes: Every visible window (or hidden worker) runs in its own isolated process.

2. The SQLite Concurrency Model

SQLite is serverless. It coordinates access via file system locks.

• Rollback Journal (Default): Requires an exclusive lock on the file for writing. No other process can read or write during this time.
• WAL Mode (Write-Ahead Logging): Allows simultaneous readers and one writer.

3. The Collision

When you import better-sqlite3 or sqlite3 directly inside a Renderer window (e.g., inside a React component or a Vue store), you create a distinct database connection per window.

If you have three windows open, you have three separate processes fighting for file system locks on db.sqlite. While WAL mode handles concurrency better than the default journaling mode, it is not immune to race conditions when multiple distinct processes attempt heavy writes simultaneously, especially when encryption (SQLCipher) adds CPU overhead to the I/O operations.

The Solution: You must treat the Main Process as the database server and the Renderers as clients.

The Architecture: The IPC Broker Pattern

We will implement a singleton database manager in the Main process. All Renderers will request data via Inter-Process Communication (IPC). This serializes database access through Node.js's single-threaded event loop in the Main process, effectively creating a "queue" that prevents locking errors naturally.

Tech Stack

• Electron (Latest)
• better-sqlite3: The fastest synchronous driver (essential for Electron).
• TypeScript: For strict typing across IPC bridges.
• SQLCipher: Implicitly handled via connection pragmas.

---

Step 1: The Database Manager (Main Process)

First, we create a resilient database class in the Main process. This class handles the connection, encryption, and performance tuning.

Create src/main/database.ts:

import Database from 'better-sqlite3';
import path from 'path';
import { app } from 'electron';

interface QueryResult {
  changes: number;
  lastInsertRowid: number | bigint;
  data: any[];
}

export class DatabaseManager {
  private db: Database.Database;
  private static instance: DatabaseManager;

  private constructor() {
    // Locate the DB file in the user data folder to avoid permission issues
    const dbPath = path.join(app.getPath('userData'), 'app_encrypted.db');
    
    // Open the connection
    this.db = new Database(dbPath, { verbose: console.log });
    
    this.initializeSecurity();
    this.optimizePerformance();
    this.runMigrations();
  }

  public static getInstance(): DatabaseManager {
    if (!DatabaseManager.instance) {
      DatabaseManager.instance = new DatabaseManager();
    }
    return DatabaseManager.instance;
  }

  private initializeSecurity() {
    // SQLCipher / Encryption handling
    // ALWAYS set the key before performing any other operations
    const secret = process.env.DB_SECRET || 'development-secret-key';
    this.db.pragma(`key = '${secret}'`);
  }

  private optimizePerformance() {
    // Critical for concurrency and performance
    this.db.pragma('journal_mode = WAL');
    
    // Loosens constraints on OS disk flushing. 
    // Small risk of corruption on power loss, but massive speed gain.
    this.db.pragma('synchronous = NORMAL'); 
  }

  private runMigrations() {
    // Example schema
    this.db.exec(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL UNIQUE,
        email TEXT NOT NULL,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
      )
    `);
  }

  // Generic handler for fetching data (SELECT)
  public query(sql: string, params: any[] = []): any[] {
    try {
      const stmt = this.db.prepare(sql);
      return stmt.all(...params);
    } catch (err) {
      console.error('Database Read Error:', err);
      throw err;
    }
  }

  // Generic handler for writing data (INSERT, UPDATE, DELETE)
  public execute(sql: string, params: any[] = []): QueryResult {
    try {
      const stmt = this.db.prepare(sql);
      const info = stmt.run(...params);
      return {
        changes: info.changes,
        lastInsertRowid: info.lastInsertRowid,
        data: [],
      };
    } catch (err) {
      console.error('Database Write Error:', err);
      throw err;
    }
  }
}

Step 2: Setting up IPC Handlers (Main Process)

We expose the database methods to the renderer via ipcMain. This acts as our API layer.

Modify src/main/index.ts (or your main entry point):

import { app, BrowserWindow, ipcMain } from 'electron';
import { DatabaseManager } from './database';

// Initialize DB immediately to catch startup errors
let dbManager: DatabaseManager;

app.whenReady().then(() => {
  dbManager = DatabaseManager.getInstance();
  createWindow();
});

/**
 * IPC HANDLERS
 * These function as the "Server Endpoints" for the Renderers.
 */

// Handle SELECT queries
ipcMain.handle('db-query', async (event, sql: string, params: any[]) => {
  // Wrap in try/catch to return structured errors to Renderer
  try {
    return dbManager.query(sql, params);
  } catch (error: any) {
    throw new Error(`DB_QUERY_ERROR: ${error.message}`);
  }
});

// Handle INSERT/UPDATE/DELETE
ipcMain.handle('db-execute', async (event, sql: string, params: any[]) => {
  try {
    return dbManager.execute(sql, params);
  } catch (error: any) {
    throw new Error(`DB_EXECUTE_ERROR: ${error.message}`);
  }
});

Step 3: The Context Bridge (Preload Script)

We never expose the ipcRenderer module directly to the frontend for security reasons. Instead, we expose a specific db API.

Create or modify src/preload/index.ts:

import { contextBridge, ipcRenderer } from 'electron';

contextBridge.exposeInMainWorld('db', {
  query: (sql: string, params: any[] = []) => 
    ipcRenderer.invoke('db-query', sql, params),
    
  execute: (sql: string, params: any[] = []) => 
    ipcRenderer.invoke('db-execute', sql, params),
});

Add TypeScript definition (e.g., src/types/renderer.d.ts):

export interface IElectronDB {
  query: <T = any>(sql: string, params?: any[]) => Promise<T[]>;
  execute: (sql: string, params?: any[]) => Promise<{
    changes: number;
    lastInsertRowid: number | bigint;
  }>;
}

declare global {
  interface Window {
    db: IElectronDB;
  }
}

Step 4: Consuming in the Renderer (React Example)

Now, your frontend components can call the database as if they were making an API request. The await handles the asynchronous nature of the IPC bridge, even though SQLite is running synchronously in the Main process.

Example Component src/renderer/UserList.tsx:

import React, { useEffect, useState } from 'react';

interface User {
  id: number;
  username: string;
  email: string;
}

export const UserList = () => {
  const [users, setUsers] = useState<User[]>([]);
  const [loading, setLoading] = useState(false);

  const fetchUsers = async () => {
    try {
      const result = await window.db.query<User>(
        'SELECT * FROM users ORDER BY created_at DESC'
      );
      setUsers(result);
    } catch (err) {
      console.error('Failed to fetch users', err);
    }
  };

  const addUser = async () => {
    setLoading(true);
    try {
      await window.db.execute(
        'INSERT INTO users (username, email) VALUES (?, ?)',
        [`User_${Date.now()}`, 'demo@example.com']
      );
      // Refresh list after write
      await fetchUsers(); 
    } catch (err) {
      alert('Error adding user');
    } finally {
      setLoading(false);
    }
  };

  useEffect(() => {
    fetchUsers();
  }, []);

  return (
    <div className="p-6">
      <div className="flex justify-between items-center mb-4">
        <h2 className="text-xl font-bold">Users</h2>
        <button 
          onClick={addUser} 
          disabled={loading}
          className="bg-blue-600 text-white px-4 py-2 rounded hover:bg-blue-700"
        >
          {loading ? 'Saving...' : 'Add User'}
        </button>
      </div>
      
      <ul className="space-y-2">
        {users.map((u) => (
          <li key={u.id} className="border p-2 rounded shadow-sm">
            {u.username} <span className="text-gray-500">({u.email})</span>
          </li>
        ))}
      </ul>
    </div>
  );
};

---

Deep Dive: Why This Fixes "Database Locked"

This architecture solves the concurrency problem through Singleton Serialization.

1. Single Writer Principle

By forcing all DB requests to go through ipcMain.handle, we ensure that only one JavaScript object (DatabaseManager instance) ever touches the physical .db file.

2. The Event Loop as a Queue

Node.js is single-threaded. When multiple windows send IPC messages simultaneously:

1. Electron receives the IPC events.
2. Electron pushes these events into the Main process's event loop.
3. The Main process picks them up one by one.
4. better-sqlite3 executes the SQL synchronously blocking the Main thread for microseconds.
5. The result is returned.

Because the execution is sequential within the Main process, two queries can never physically attempt to write to the file at the exact same nanosecond. The Node.js event loop acts as a natural, highly efficient mutex.

3. WAL Mode Optimization

By using journal_mode = WAL, we allow the database to move read operations out of the way of write operations. Even though we are serializing requests, WAL makes the individual operations significantly faster, preventing the UI from feeling "sluggish" during heavy database activity.

---

Common Pitfalls & Edge Cases

1. Blocking the Main Process

Since better-sqlite3 is synchronous, a massive query (e.g., selecting 50,000 rows) will freeze your entire app (all windows) until it completes.

• Fix: Use pagination (LIMIT / OFFSET) strictly. Never SELECT * on large tables without limits.

2. Large IPC Payloads

Passing huge JSON objects over IPC is expensive (serialization/deserialization CPU costs).

• Fix: If you need to export a massive CSV, do the file writing in the Main process and only send a "success" boolean to the Renderer, rather than sending the raw data to the Renderer to save.

3. Transactions

The example above handles single queries. For complex operations ensuring atomicity:

Update DatabaseManager:

public transaction(queries: { sql: string; params: any[] }[]) {
  const runMany = this.db.transaction((items) => {
    for (const item of items) {
      this.db.prepare(item.sql).run(...item.params);
    }
  });
  runMany(queries);
}

Conclusion

Handling SQLite in Electron requires respecting the boundaries of the process model. By centralizing database access in the Main process and utilizing IPC as a request broker, you eliminate lock contention entirely.

This approach not only resolves SQLITE_BUSY errors but also centralizes your security logic (SQLCipher keys) and schema management, making your application easier to maintain and scale.