ByteGuideHub
Back to Articles
Distributed Systems & Databases • • 9 min read

Building a Self-Hosted Local-First Database with CRDTs and SQLite: A Practical Guide

Learn to build a self-hosted local-first database using CRDTs and SQLite. Step-by-step guide covers data modeling, conflict resolution, and peer-to-peer sync without central servers.

Sponsored / Ad Space

Building a Self-Hosted Local-First Database with CRDTs and SQLite: A Practical Guide

Quick Summary (TL;DR)

  • Local-first apps keep data on the user’s device, sync peer-to-peer, and work offline by default.
  • CRDTs (Conflict-free Replicated Data Types) guarantee automatic, deterministic merges of concurrent edits.
  • SQLite provides a battle-tested, embeddable storage engine for persisting CRDT state.
  • You’ll design a CRDT-compatible schema, implement LWW registers and OR-Sets, persist ops locally, and sync peers over WebRTC/libp2p—no central server required.

Modern applications increasingly demand offline capabilities, instant responsiveness, and privacy by default. The local-first paradigm delivers all three: data lives on the user’s device, syncs directly between peers, and never depends on a central authority. The two ingredients that make this practical are CRDTs for automatic conflict resolution and SQLite for durable, embeddable storage. This guide walks you through building such a system from scratch.

Why Local-First with CRDTs and SQLite?

Traditional client-server architectures treat the server as the source of truth. That model breaks down when users go offline, when latency matters, or when you want to avoid operating infrastructure. Local-first inverts this: the device is the source of truth, and synchronization is a background concern.

CRDTs make this inversion safe. Because they are mathematically designed to converge regardless of update order, two devices that edit the same record concurrently will always arrive at the same final state—without a coordinator. SQLite, meanwhile, gives you ACID transactions, a tiny footprint, and the ability to run literally anywhere: mobile, desktop, browser (via WebAssembly), or edge.

Core Concepts: CRDTs and SQLite

What Are CRDTs?

A CRDT is a data structure that can be replicated across multiple nodes, updated independently and concurrently, and merged deterministically. There are two families:

  • State-based (CvRDTs): Replicas exchange full state; merge is a join over a semilattice.
  • Operation-based (CmRDTs): Replicas exchange operations; delivery order doesn’t matter as long as delivery is reliable.

Common CRDT types include LWW Registers, G-Counters, OR-Sets, and RGA/Logoot sequences for text editing.

Why SQLite?

SQLite is ideal for local-first because it is:

  • Embedded—no separate server process.
  • Transactional—atomic writes protect CRDT metadata integrity.
  • Portable—the same file format works on iOS, Android, Linux, and the browser.
  • Fast—sub-millisecond reads from local disk.

Step 1: Setting Up Your SQLite Schema for CRDTs

Designing CRDT-Compatible Tables

Every CRDT row needs metadata to support merging: a replica ID, a logical clock (Lamport timestamp or hybrid logical clock), and a tombstone flag for deletions. Avoid auto-incrementing integers as primary keys—use UUIDs or ULIDs so IDs are globally unique without coordination.

Example Schema

CREATE TABLE todos (
    id            TEXT PRIMARY KEY,          -- ULID
    title         TEXT NOT NULL,
    completed     INTEGER NOT NULL DEFAULT 0,
    -- CRDT metadata
    updated_at    INTEGER NOT NULL,          -- HLC timestamp
    replica_id    TEXT NOT NULL,             -- device identifier
    deleted       INTEGER NOT NULL DEFAULT 0 -- tombstone
);

CREATE TABLE todo_tags (
    todo_id       TEXT NOT NULL,
    tag           TEXT NOT NULL,
    added_at      INTEGER NOT NULL,
    replica_id    TEXT NOT NULL,
    deleted       INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (todo_id, tag, replica_id)
);

CREATE INDEX idx_todos_updated ON todos(updated_at);

The todo_tags table models an OR-Set: each tag carries the replica that added it, so removals can be scoped precisely.

Step 2: Implementing CRDT Data Types in SQLite

Last-Writer-Wins (LWW) Register

An LWW register stores a single value plus a timestamp. On merge, the value with the higher timestamp wins; ties break by replica ID.

def merge_lww(local, remote):
    if remote.updated_at > local.updated_at:
        return remote
    if remote.updated_at == local.updated_at and remote.replica_id > local.replica_id:
        return remote
    return local

The todos.title and todos.completed columns behave as LWW registers.

Observed-Remove Set (OR-Set)

An OR-Set tracks each element with a unique tag. An element is present if any tag remains; removing it deletes only the tags the remover has observed.

def add_tag(conn, todo_id, tag, replica_id, hlc):
    conn.execute(
        "INSERT OR REPLACE INTO todo_tags VALUES (?, ?, ?, ?, 0)",
        (todo_id, tag, hlc, replica_id)
    )

def remove_tag(conn, todo_id, tag):
    conn.execute(
        "UPDATE todo_tags SET deleted = 1 WHERE todo_id = ? AND tag = ?",
        (todo_id, tag)
    )

Conflict-Free Replicated JSON (CRJSON)

For nested structures, a CRJSON document treats every object key as an LWW register, every array as an RGA sequence, and every scalar as a register. You can store the serialized JSON in a BLOB column alongside its metadata, or flatten it into relational tables for queryability.

flowchart TD
    A[Local Edit] --> B{CRDT Type?}
    B -->|Register| C[Update value + HLC]
    B -->|OR-Set| D[Add/Remove tag]
    B -->|Sequence| E[Insert with position ID]
    C --> F[Persist to SQLite]
    D --> F
    E --> F
    F --> G[Enqueue op for sync]

Step 3: Local Operations and Persistence

Writing Data Locally

Every mutation is a transaction: update the row, bump the HLC, and append the operation to an outbox table for later sync.

def update_title(conn, todo_id, new_title, replica_id):
    hlc = next_hlc()
    with conn:  # atomic
        conn.execute(
            "UPDATE todos SET title = ?, updated_at = ?, replica_id = ? WHERE id = ?",
            (new_title, hlc, replica_id, todo_id)
        )
        conn.execute(
            "INSERT INTO outbox(op, payload, created_at) VALUES (?, ?, ?)",
            ("update_title", json.dumps({"id": todo_id, "title": new_title}), hlc)
        )

Reading Data

Reads filter tombstones and apply the same merge logic used during sync, so the local view is always consistent:

SELECT id, title, completed
FROM todos
WHERE deleted = 0
ORDER BY updated_at DESC;

Step 4: Peer-to-Peer Synchronization

Sync Protocol Overview

Peers exchange operations from their outbox, applying CRDT merge rules on receipt. Because CRDTs are commutative and idempotent, duplicates and out-of-order delivery are harmless.

sequenceDiagram
    participant A as Peer A
    participant B as Peer B
    A->>B: sync_request(last_seen_hlc)
    B->>A: ops since last_seen_hlc
    A->>A: apply_ops (merge)
    A->>B: ops since last_seen_hlc
    B->>B: apply_ops (merge)
    A->>B: ack
    B->>A: ack

Implementing Sync with WebRTC or libp2p

  • WebRTC is ideal for browser-to-browser sync. Use a signaling server only to bootstrap the connection; data flows directly.
  • libp2p works well for native apps, giving you peer discovery, NAT traversal, and encrypted transports out of the box.

A minimal WebRTC data channel handler:

channel.onmessage = (event) => {
  const ops = JSON.parse(event.data);
  for (const op of ops) applyOp(op); // CRDT merge
  persistAppliedOps(ops);
};

Handling Sync Conflicts

With CRDTs, “conflicts” are resolved by the data structure itself. Your job is to ensure:

  1. Every op carries an HLC and replica ID.
  2. Merge is applied inside a SQLite transaction.
  3. The outbox is truncated only after receiving an ack.

Step 5: Conflict Resolution Strategies

Automatic Merge with CRDTs

For most fields, no code is needed—LWW and OR-Set semantics handle it. The only requirement is deterministic tie-breaking (e.g., by replica ID).

Custom Conflict Handlers

Sometimes you want domain logic. For example, a shopping cart should union quantities rather than overwrite them. Wrap the CRDT in a custom merge:

def merge_cart(local, remote):
    # Sum quantities for the same SKU
    merged = defaultdict(int)
    for item in local + remote:
        merged[item.sku] += item.qty
    return merged

Store the merged result as a new op, then propagate it.

Comparison: CRDTs vs. Traditional Sync

AspectCRDT + SQLiteServer-Authoritative Sync
Offline supportNativeLimited
Conflict handlingAutomatic, deterministicManual / last-write-wins
InfrastructureNone (peers only)Central server required
LatencyLocal (sub-ms)Network-bound
Metadata overheadHigher (HLCs, tags)Lower
ComplexityFront-loaded in schemaFront-loaded in server logic
PrivacyData stays on deviceServer sees all data

Best Practices and Pitfalls

  • Plan for metadata growth. HLCs, replica IDs, and tombstones add up. Implement periodic compaction once all peers have acknowledged a version.
  • Never delete rows outright. Use tombstones; hard deletes break convergence.
  • Version your schema. Add a schema_version table and migrate CRDT metadata alongside application data.
  • Test network partitions. Simulate long offline periods and concurrent edits across three or more replicas.
  • Cap the outbox. If a peer stays offline for weeks, the outbox can balloon. Snapshot-based sync (CvRDT) can help.
  • Use a stable HLC. Wall clocks drift; hybrid logical clocks keep causality even when clocks disagree.
flowchart LR
    A[Edit] --> B[SQLite Write]
    B --> C[Outbox]
    C --> D{Peer Online?}
    D -->|Yes| E[Send Ops]
    D -->|No| C
    E --> F[Merge on Peer]
    F --> G[Ack]
    G --> H[Compact Outbox]

Conclusion

Building a self-hosted, local-first database with CRDTs and SQLite is entirely feasible for a small team. The key is to treat CRDT metadata as first-class data, design your schema around merge semantics, and let the math handle conflict resolution. You get offline-first UX, privacy by default, and zero server operations—three properties that traditional architectures struggle to deliver simultaneously.

Start small: implement an LWW register over a single table, get sync working between two devices, then layer in OR-Sets and sequences. The complexity is real, but it is bounded, well-understood, and pays off with every user who opens your app on a plane.

FAQ

What is a local-first database? A local-first database stores data primarily on the user’s device, enabling offline functionality and fast access. It syncs with other devices or peers when connectivity is available, often using CRDTs to resolve conflicts automatically.

Why use CRDTs with SQLite? CRDTs provide automatic conflict resolution for concurrent edits, while SQLite offers a lightweight, reliable, and self-contained storage engine. Together, they enable robust local-first applications without a central server.

How does peer-to-peer synchronization work with CRDTs? Peers exchange CRDT operations or state updates directly. Since CRDTs are commutative and idempotent, the order of updates doesn’t matter, and all peers eventually converge to the same state without a central coordinator.

Can I use SQLite with CRDTs in a web browser? Yes, you can use SQLite compiled to WebAssembly (e.g., sql.js) or IndexedDB as a backend. Libraries like Yjs or Automerge can be integrated with SQLite for persistence.

Do I need a server for local-first sync? No, you can sync directly between peers using WebRTC or libp2p. However, you may use a relay server for NAT traversal or as a backup, but it’s not required for core functionality.

Sponsored / Ad Space
Back to all articles