Building Offline-First Applications with SQLite Sync Queues

Designing Reliable Sync Queues, Conflict Resolution, and Offline Data Consistency with SQLite

Offline-first applications are designed to work without a constant internet connection. Data is written locally and synchronized later when connectivity is available.

SQLite is a natural fit for offline-first systems because it runs locally, is reliable, and provides transactional guarantees.

The core challenge is not storage. It is synchronization.

This article shows how to implement:

• Local write queues

• Outbound sync pipelines

• Inbound data application

• Conflict resolution strategies

The goal is predictable and recoverable sync behavior.

What Offline-First Actually Means

In a traditional app:

• Client writes - Server

• Server stores - Returns response

In an offline-first app:

• Client writes - SQLite

• Changes stored locally

• Sync happens later

SQLite becomes the source of truth on the device.

Core Architecture

A practical offline sync system has four parts:

1. Local data tables

2. Sync queue table

3. Sync worker

4. Conflict resolution logic

Step 1: Local Data Table

Example table for tasks:

CREATE TABLE tasks (
  task_id     TEXT PRIMARY KEY,
  title       TEXT NOT NULL,
  completed   INTEGER NOT NULL,
  updated_at  TEXT NOT NULL
);

This table represents current state.

Step 2: Sync Queue Table

Every change is recorded in a queue.

CREATE TABLE sync_queue (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  operation     TEXT NOT NULL,
  table_name    TEXT NOT NULL,
  record_id     TEXT NOT NULL,
  payload       TEXT NOT NULL,
  created_at    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  synced        INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX idx_sync_unsynced
ON sync_queue(synced);

This is the backbone of offline synchronization.

Step 3: Capturing Local Changes

Every write should also insert into the sync queue.

Example:

INSERT INTO tasks (task_id, title, completed, updated_at)
VALUES (’t1’, ‘Buy groceries’, 0, datetime(’now’));

INSERT INTO sync_queue (operation, table_name, record_id, payload)
VALUES (
  ‘INSERT’,
  ‘tasks’,
  ‘t1’,
  json_object(
    ‘task_id’,’t1’,
    ‘title’,’Buy groceries’,
    ‘completed’,0,
    ‘updated_at’,datetime(’now’)
  )
);

This ensures changes are queued for sync.

Step 4: Sync Worker (Outbound)

The sync worker pushes local changes to the server.

Python example:

import sqlite3
import json
import requests

DB = “app.db”
API_URL = “https://api.example.com/sync”

def sync_outbound():
    conn = sqlite3.connect(DB)
    conn.row_factory = sqlite3.Row

    rows = conn.execute(”“”
        SELECT id, operation, table_name, payload
        FROM sync_queue
        WHERE synced = 0
        ORDER BY id
        LIMIT 50
    “”“).fetchall()

    for row in rows:
        data = {
            “operation”: row[”operation”],
            “table”: row[”table_name”],
            “payload”: json.loads(row[”payload”])
        }

        response = requests.post(API_URL, json=data)

        if response.status_code == 200:
            conn.execute(
                “UPDATE sync_queue SET synced = 1 WHERE id = ?”,
                (row[”id”],)
            )

    conn.commit()
    conn.close()

Install dependency:

pip install requests

Step 5: Applying Incoming Changes

The server sends updates back to the device.

Example:

def apply_inbound(changes):
    conn = sqlite3.connect(DB)

    for change in changes:
        if change[”operation”] == “INSERT”:
            conn.execute(”“”
                INSERT OR REPLACE INTO tasks (task_id, title, completed, updated_at)
                VALUES (?, ?, ?, ?)
            “”“, (
                change[”payload”][”task_id”],
                change[”payload”][”title”],
                change[”payload”][”completed”],
                change[”payload”][”updated_at”]
            ))

    conn.commit()
    conn.close()

Inbound sync updates local state.

Conflict Resolution Strategies

Conflicts occur when:

• Same record is modified on multiple devices

• Sync happens later
  

Strategy 1: Last Write Wins

Compare timestamps:

WHERE incoming.updated_at > local.updated_at

Simple, but may overwrite data.

Strategy 2: Version-Based Conflict Detection

Add version column:

ALTER TABLE tasks ADD COLUMN version INTEGER DEFAULT 1;

Only update if versions match:

WHERE version = expected_version

If not, trigger conflict handling.

Strategy 3: Field-Level Merge

Merge fields instead of overwriting:

• Keep latest title

• Preserve completion status

Requires custom logic per table.

Tracking Sync State

Instead of a boolean flag, track position:

SELECT * FROM sync_queue
WHERE id > ?
ORDER BY id;

Store last synced ID.

This reduces write amplification.

Handling Network Failures

The system must tolerate failure.

Best practices:

• Retry with backoff

• Do not delete unsynced data

• Make operations idempotent
  

Example idempotent pattern:

• Use unique IDs

• Server ignores duplicates
  

Performance Considerations

Enable WAL mode

PRAGMA journal_mode = WAL;

For basic information on how to handle concurrent transactions, check out Mastering Transactions and Concurrency.

Batch operations

Send multiple changes in one request.

Index critical columns

• sync_queue.synced

• tasks.updated_at
  

When This Pattern Works

Use SQLite sync queues when:

• Devices operate offline frequently

• Local writes must be fast

• Sync can be asynchronous

Common use cases:

• Mobile apps

• Field data collection

• Edge devices

• Note-taking apps
  

When It Does Not Work

Avoid this pattern when:

• Strict real-time consistency is required

• Global transactions are needed

• Conflict resolution is too complex

Closing Notes

Offline-first systems shift complexity from the server to the client.

SQLite provides a reliable local store, but synchronization must be designed carefully.

A well-structured sync queue ensures:

• durability

• recoverability

• predictable behavior

The system works because it is explicit, not automatic.

Subscribe Now

If you want real-world SQLite architecture patterns, subscribe to SQLite Forum.

Upcoming topics include:

• SQLite replication strategies

• distributed data ownership patterns

• SQLite B-tree internals

• building event-driven systems with SQLite

Subscribe to receive new articles directly.