Every Ionic app we are asked to rescue has the same bug report somewhere in its backlog: "the app is useless on the warehouse floor / in the basement / on the plane." Teams then bolt a cache onto their HTTP layer, discover that a cache is not a database, and end up with duplicated writes and rows that silently disappear.
Offline-first is an architecture decision, not a plugin. This tutorial builds
the version we ship for clients: a local SQLite database as the single source
of truth, an outbox table for pending writes, a sync loop driven by
@capacitor/network, and a conflict policy you can explain to your product
owner. Examples are Ionic Angular with Capacitor 8; the data layer is plain
TypeScript and drops into React or Vue unchanged.
The rule that makes it work
The UI never talks to the network. The UI talks to SQLite. A background sync process reconciles SQLite with the server.
If you keep that rule, offline behaviour stops being a special case: the app that works on a plane is the same code path as the app on office Wi-Fi, just with a longer queue. If you break it — one screen that fetches directly because it was quicker — that screen becomes the one that fails in the field.
1. Install and initialise
npm i @capacitor-community/sqlite
npm i jeep-sqlite sql.js # browser/dev fallback only
npx cap sync
On iOS add a NSFaceIDUsageDescription if you plan to use biometric-backed
encryption. On Android nothing extra is required.
A single initialiser, called once from main.ts before the app bootstraps:
import { CapacitorSQLite, SQLiteConnection, SQLiteDBConnection } from '@capacitor-community/sqlite';
import { Capacitor } from '@capacitor/core';
const sqlite = new SQLiteConnection(CapacitorSQLite);
let db: SQLiteDBConnection;
export async function initDb(): Promise<SQLiteDBConnection> {
if (db) return db;
if (Capacitor.getPlatform() === 'web') {
// jeep-sqlite stores in IndexedDB; fine for dev and PWA builds
await customElements.whenDefined('jeep-sqlite');
await sqlite.initWebStore();
}
db = await sqlite.createConnection('app', false, 'no-encryption', 1, false);
await db.open();
await migrate(db);
return db;
}
Two things people get wrong here. First, createConnection throws if a
connection with that name already exists after a hot reload — wrap it with
sqlite.isConnection('app', false) and reuse. Second, on web you must save
to the IndexedDB store explicitly (sqlite.saveToStore('app')) after writes,
or your dev data vanishes on reload.
2. Schema, with sync columns
Every synced table carries four extra columns. They are the whole trick.
async function migrate(db: SQLiteDBConnection) {
await db.execute(`
CREATE TABLE IF NOT EXISTS job (
id TEXT PRIMARY KEY, -- UUID generated on the client
title TEXT NOT NULL,
status TEXT NOT NULL,
notes TEXT,
updated_at TEXT NOT NULL, -- ISO 8601, set locally on write
server_rev TEXT, -- last revision the server confirmed
dirty INTEGER NOT NULL DEFAULT 0,
deleted INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS outbox (
seq INTEGER PRIMARY KEY AUTOINCREMENT,
entity TEXT NOT NULL,
entity_id TEXT NOT NULL,
op TEXT NOT NULL, -- insert | update | delete
payload TEXT NOT NULL, -- JSON snapshot
attempts INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS sync_state (
entity TEXT PRIMARY KEY,
cursor TEXT -- server watermark for pull
);
`);
}
Client-generated UUIDs are non-negotiable. If IDs come from the server, an offline create has no identity, and every foreign key you write while offline has to be rewritten later. Generate the UUID on the device and let the server accept it.
deleted is a tombstone, not a DELETE. You cannot sync a row that no longer
exists.
3. Writes go to SQLite and the outbox, in one transaction
export async function saveJob(job: Job): Promise<void> {
const now = new Date().toISOString();
const db = await initDb();
await db.executeTransaction([
{
statement: `INSERT INTO job (id,title,status,notes,updated_at,dirty)
VALUES (?,?,?,?,?,1)
ON CONFLICT(id) DO UPDATE SET
title=excluded.title, status=excluded.status,
notes=excluded.notes, updated_at=excluded.updated_at, dirty=1`,
values: [job.id, job.title, job.status, job.notes ?? null, now],
},
{
statement: `INSERT INTO outbox (entity,entity_id,op,payload,created_at)
VALUES ('job',?,?,?,?)`,
values: [job.id, job.serverRev ? 'update' : 'insert', JSON.stringify({ ...job, updated_at: now }), now],
},
]);
scheduleSync(); // fire and forget; safe to call constantly
}
The row and its outbox entry are committed together. If the app is killed mid-write, you never get a visible change with no queued sync, or the reverse.
Reads are boring, which is the point:
export async function listJobs(): Promise<Job[]> {
const db = await initDb();
const res = await db.query(`SELECT * FROM job WHERE deleted = 0 ORDER BY updated_at DESC`);
return (res.values ?? []).map(rowToJob);
}
4. The sync loop
import { Network } from '@capacitor/network';
import { App } from '@capacitor/app';
let running = false;
let pending = false;
export function scheduleSync() {
if (running) { pending = true; return; }
void runSync();
}
async function runSync() {
running = true;
try {
const { connected } = await Network.getStatus();
if (!connected) return;
await push();
await pull();
} catch (e) {
console.warn('[sync] failed, will retry', e);
} finally {
running = false;
if (pending) { pending = false; void runSync(); }
}
}
Network.addListener('networkStatusChange', s => { if (s.connected) scheduleSync(); });
App.addListener('appStateChange', s => { if (s.isActive) scheduleSync(); });
A single-flight guard plus a pending flag is enough. Do not run one sync per
screen; concurrent syncs are how you get duplicate rows.
Push
async function push() {
const db = await initDb();
const res = await db.query(`SELECT * FROM outbox ORDER BY seq ASC LIMIT 50`);
for (const row of res.values ?? []) {
try {
const result = await api.apply(row.entity, row.op, JSON.parse(row.payload));
await db.executeTransaction([
{ statement: `UPDATE job SET server_rev=?, dirty=0 WHERE id=? AND updated_at<=?`,
values: [result.rev, row.entity_id, result.updated_at] },
{ statement: `DELETE FROM outbox WHERE seq=?`, values: [row.seq] },
]);
} catch (e: any) {
if (e.status === 409) { await resolveConflict(row, e.serverRow); continue; }
if (e.status >= 400 && e.status < 500) { await park(row, e); continue; }
throw e; // 5xx / offline: stop, keep order, retry later
}
}
}
Three failure classes, three behaviours: conflict → resolve, permanent client error → park the item in a dead-letter table and surface it in the UI (a silently dropped write is worse than an error), transient error → stop and retry, preserving order.
Make the server endpoint idempotent on the client UUID plus an operation id. Mobile networks time out after the server has committed; without idempotency that retry creates a second record.
Pull
Pull by watermark, not by "download everything":
async function pull() {
const db = await initDb();
const state = await db.query(`SELECT cursor FROM sync_state WHERE entity='job'`);
let cursor = state.values?.[0]?.cursor ?? null;
for (;;) {
const page = await api.changes('job', cursor); // { rows, nextCursor, hasMore }
const stmts = page.rows.map(r => ({
statement: `INSERT INTO job (id,title,status,notes,updated_at,server_rev,dirty,deleted)
VALUES (?,?,?,?,?,?,0,?)
ON CONFLICT(id) DO UPDATE SET
title=excluded.title, status=excluded.status, notes=excluded.notes,
updated_at=excluded.updated_at, server_rev=excluded.server_rev,
deleted=excluded.deleted
WHERE job.dirty = 0`, -- never clobber unsynced local edits
values: [r.id, r.title, r.status, r.notes, r.updated_at, r.rev, r.deleted ? 1 : 0],
}));
stmts.push({ statement: `INSERT INTO sync_state (entity,cursor) VALUES ('job',?)
ON CONFLICT(entity) DO UPDATE SET cursor=excluded.cursor`,
values: [page.nextCursor] });
await db.executeTransaction(stmts);
cursor = page.nextCursor;
if (!page.hasMore) break;
}
}
The WHERE job.dirty = 0 clause is the single most important line in the
file. Without it, a pull that lands while a local edit is queued wipes the
user's work.
5. Conflict policy
Pick one, write it down, and tell the users which one you picked.
- Last-write-wins by server clock. Cheapest. Acceptable for status flags and checkboxes. Device clocks lie, so compare server revisions, not local timestamps.
- Field-level merge. Keep a per-field
updated_atand merge column by column. Good for forms where two people edit different fields of the same record. - Ask the user. Store both versions, flag the row, and show a "resolve" screen. The only honest option for free-text fields like inspection notes.
Whatever you choose, never resolve a conflict by discarding data with no
trace. Copy the losing version into a conflict_log table; support will thank
you.
6. Things that bite in production
- Encryption.
@capacitor-community/sqlitesupports SQLCipher via theencryptionconnection mode. Turn it on before your first release; adding it later means writing a migration that re-encrypts on device. - Migrations. Ship
upgradestatements with version numbers from day one (sqlite.addUpgradeStatement). Users skip versions; your migration path must handle 1 → 4, not just 3 → 4. - iOS backup. By default the database goes into a directory that iCloud backs up. If it holds cached server data, exclude it or Apple review may ask why your app backs up 400 MB.
- Big lists. SQLite is fast; the DOM is not. Use
ion-virtual-scroll/@ionic/angularvirtual scrolling andLIMIT/OFFSET, and index the columns you sort on. - Storage pressure. Prune synced, closed records older than N days on startup, or your app grows until iOS evicts it.
- Testing. Run the sync layer in Node against
better-sqlite3with the same SQL, and test the four cases that matter: offline create, offline edit of a server row, conflicting edit, and delete-while-offline.
7. Telling the user what is going on
An offline app that gives no feedback feels broken. Two small components pay
for themselves: a header chip bound to Network status plus the outbox count,
and a per-row "pending" indicator driven by dirty = 1. Users forgive a delay
they can see.
Building this once is a couple of weeks; building it twice, correctly, is where our clients call us. If you are adding offline support to an existing Ionic app — or your current sync layer has started losing writes — our Ionic consultants and Capacitor consultants can review the data layer and give you a plan. Get in touch.