Design patterns
9 minutes readBuilding Reliable Offline-First Desktop Apps with Electron and SQLite
A practical architecture for secure IPC, local persistence, pagination, migrations, and backups in desktop software.
A desktop application is not simply a website packaged in a window. Electron runs a privileged main process and a less-trusted renderer, while SQLite stores business-critical data on the user’s machine. Reliability depends on keeping that boundary secure, making writes atomic, upgrading schemas safely, and proving that backups can be restored.
1. Understand the process architecture
The main process owns window creation, native integrations, database connections, filesystem access, and IPC handlers. The renderer displays React UI and should be treated like a web page that may contain compromised or malformed content. The preload script is a narrow bridge between them. Keep contextIsolation enabled, nodeIntegration disabled, and a restrictive Content Security Policy; do not load arbitrary remote content into a privileged window.
import { app, BrowserWindow, ipcMain } from "electron";
import path from "node:path";
function createWindow() {
const win = new BrowserWindow({
webPreferences: {
preload: path.join(__dirname, "preload.js"),
contextIsolation: true,
nodeIntegration: false,
sandbox: true,
},
});
if (app.isPackaged) void win.loadFile(path.join(__dirname, "../renderer/index.html"));
else void win.loadURL("http://localhost:5173");
}
app.whenReady().then(createWindow);
// Register narrow IPC handlers in this trusted process.2. Expose a small, typed IPC API
Expose intent-based functions such as products.list rather than a generic send(channel, payload) method. This limits what renderer code can ask the main process to do and makes the API easier to test. TypeScript types improve developer feedback but disappear at runtime, so the receiving handler must still validate every payload. Never expose raw ipcRenderer, SQL strings, or unrestricted fs operations.
import { contextBridge, ipcRenderer } from "electron";
contextBridge.exposeInMainWorld("desktopApi", {
products: {
list: (input: { page: number; pageSize: number; search?: string }) =>
ipcRenderer.invoke("products:list", input),
create: (input: { name: string; price: number }) =>
ipcRenderer.invoke("products:create", input),
},
});
// Never expose raw ipcRenderer or arbitrary filesystem/SQL access.3. Validate every IPC payload
Validate bounds, string lengths, enum values, and object shapes in the main process. Use parameterized SQL for values; never interpolate user input into query text. For sensitive mutations, also verify the current application session or permission inside the trusted process rather than trusting a button being hidden in React.
import { ipcMain } from "electron";
function parseListInput(value: unknown) {
if (!value || typeof value !== "object") throw new Error("Invalid input");
const input = value as Record<string, unknown>;
const page = Number(input.page);
const pageSize = Number(input.pageSize);
const search = typeof input.search === "string" ? input.search.trim().slice(0, 100) : "";
if (!Number.isInteger(page) || page < 1) throw new Error("Invalid page");
if (!Number.isInteger(pageSize) || pageSize < 1 || pageSize > 100) throw new Error("Invalid page size");
return { page, pageSize, search };
}
ipcMain.handle("products:list", async (_event, value: unknown) => {
const { page, pageSize, search } = parseListInput(value);
const offset = (page - 1) * pageSize;
return db.all(
`SELECT id, name, price FROM products
WHERE name LIKE ? ORDER BY name, id LIMIT ? OFFSET ?`,
[`%${search}%`, pageSize, offset],
);
});4. Configure SQLite deliberately
Enable foreign-key enforcement on each connection and create indexes for common filters, joins, and sort orders. WAL often helps a reader coexist with a writer, but it does not allow unlimited concurrent writers and creates -wal/-shm sidecar files while active. synchronous = NORMAL is a workload and durability trade-off, not a universal rule. Measure queries with realistic data and inspect query plans before adding indexes.
import sqlite3 from "sqlite3";
export const db = new sqlite3.Database(databasePath);
export function run(sql: string, params: unknown[] = []) {
return new Promise<{ lastID: number; changes: number }>((resolve, reject) => {
db.run(sql, params, function (error) {
if (error) reject(error);
else resolve({ lastID: this.lastID, changes: this.changes });
});
});
}
export function all<T>(sql: string, params: unknown[] = []) {
return new Promise<T[]>((resolve, reject) => {
db.all(sql, params, (error, rows) => error ? reject(error) : resolve(rows as T[]));
});
}
export async function initializeDatabase() {
await run("PRAGMA foreign_keys = ON");
await run("PRAGMA journal_mode = WAL");
await run("PRAGMA synchronous = NORMAL");
await run(`CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price INTEGER NOT NULL CHECK(price >= 0),
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
)`);
}5. Keep business writes atomic
A sale that creates an invoice, decrements stock, and inserts line items is one business operation. If any step fails, all earlier writes must roll back. The example is an outline: adapt transaction handling to the driver and ensure all statements use the same connection. A conditional stock update (stock >= quantity) plus checking affected rows prevents overselling; a database transaction keeps the related writes together.
export async function createSale(input: SaleInput) {
await run("BEGIN IMMEDIATE");
try {
const sale = await run(
"INSERT INTO sales (customer_id, total) VALUES (?, ?)",
[input.customerId, input.total],
);
for (const line of input.lines) {
const update = await run(
"UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?",
[line.quantity, line.productId, line.quantity],
);
if (update.changes !== 1) throw new Error("Insufficient stock");
await run(
"INSERT INTO sale_lines (sale_id, product_id, quantity, price) VALUES (?, ?, ?, ?)",
[sale.lastID, line.productId, line.quantity, line.price],
);
}
await run("COMMIT");
return sale.lastID;
} catch (error) {
await run("ROLLBACK");
throw error;
}
}Important: do not let unrelated async requests interleave statements inside the same connection-level transaction. Use a transaction queue or a dedicated connection per transaction, and ensure rollback errors are surfaced rather than silently ignored.
6. Paginate in SQL, not in React
Load a bounded page from SQLite rather than selecting thousands of rows and slicing them in memory. Always use deterministic ordering such as ORDER BY name, id; without it, records may move between pages. LIMIT/OFFSET is simple for small and medium tables, while keyset pagination can scale better for deep pages. Keep the total count query and filtered rows aligned to the same filter.
const pageSize = Math.min(Math.max(input.pageSize, 1), 100);
const offset = (input.page - 1) * pageSize;
const rows = await all<Product>(
`SELECT id, name, price FROM products
WHERE name LIKE ?
ORDER BY name ASC, id ASC
LIMIT ? OFFSET ?`,
[`%${input.search}%`, pageSize, offset],
);
const countRows = await all<{ count: number }>(
"SELECT COUNT(*) AS count FROM products WHERE name LIKE ?",
[`%${input.search}%`],
);
return { rows, total: countRows[0]?.count ?? 0, page: input.page, pageSize };7. Version the schema and migrate existing users
A released app has databases from several older versions, not only an empty database. Track applied migrations in a schema table and run pending migrations before mounting screens that query the changed schema. Back up before risky changes, make migration order explicit, and test upgrades from real previous versions. Do not simply run CREATE TABLE on every launch and assume that existing columns have changed.
type Migration = { version: number; sql: string };
const migrations: Migration[] = [
{ version: 1, sql: `CREATE TABLE IF NOT EXISTS schema_migrations (
version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
); CREATE TABLE IF NOT EXISTS customers (
id INTEGER PRIMARY KEY, name TEXT NOT NULL
);` },
{ version: 2, sql: `ALTER TABLE customers ADD COLUMN phone TEXT;` },
];
// Production code should apply each migration transactionally where supported,
// record its version only after success, and handle already-applied versions.8. Back up and prove that restore works
When WAL is active, committed data may still involve the WAL sidecar. Copying only the main .db file while the app is open can produce an incomplete backup. Prefer the driver’s SQLite online backup API or a documented safe procedure that coordinates writes and checkpoints. Verify the destination, keep backups outside the app install directory, and regularly test restoring to a separate database. Cloud backup also needs encryption, authentication, retention, and conflict handling.
// Pseudocode: adapt to the SQLite driver/API used by the project.
export async function createBackup(destination: string) {
// Pause or serialize app writes if your backup method requires it.
// Prefer SQLite online backup API when available, especially with WAL enabled.
await db.backup(destination);
await verifyDatabaseFile(destination); // e.g. open copy and run integrity_check
}
export async function verifyDatabaseFile(filePath: string) {
const copy = openReadOnlyDatabase(filePath);
const result = await copy.get("PRAGMA integrity_check");
if (result.integrity_check !== "ok") throw new Error("Backup integrity check failed");
await copy.close();
}9. Release engineering and recovery tests
- Store the database in Electron’s userData directory, not beside the executable.
- Test app restart during a write, disk-full errors, locked database, and failed migrations.
- Never overwrite or delete user data as an installer side effect.
- Test backup restore, schema upgrade, and rollback/recovery procedures on a copy.
- Log useful diagnostic context without writing passwords, tokens, or sensitive business data.