I didn't need PowerSync, so I built sync on top of SQLite in React Native

Alexander Oskin — Mon, 13 Jul 2026 07:44:08 +0000

Ask how to add offline sync to a React Native app and the same names usually come up: PowerSync, ElectricSQL, WatermelonDB, and RxDB. I tried to map all four onto Skulpt. Each one solved a larger problem than the one in front of me.

I built Skulpt, an open-source workout tracker for iOS and Android. The app keeps its data in an on-device SQLite database and works without a server. Sync sits on top as an optional layer. I wrote it in TypeScript without a third-party sync library or CRDTs, and it doesn't depend on Postgres replication slots.

Why I didn't use a sync library

The tradeoff was different in each case. PowerSync adds a server-side service, either managed or self-hosted. ElectricSQL assumes Postgres-backed sync infrastructure. WatermelonDB has its own database and expects the backend to follow its sync protocol. RxDB worked, but its bundle cost was hard to justify in this app.

I kept coming back to a shorter question: what does Skulpt need?

A user can change data while a device is offline.
Local changes eventually need to reach the server.
Changes made on another device need to come back down.
If two devices edit the same record, the latest write wins. Workouts aren't collaborative documents. Nobody is co-authoring a bench press set in real time.

Once I wrote that list down, the design got pretty small: a sync queue and a small HTTP API. A general-purpose framework would spend much of its complexity budget on cases Skulpt doesn't have.

The outbox pattern, adapted for SQLite

Each local write path that participates in sync also adds a row to sync_queue. When sync runs, the client reads the pending rows, compacts them, sends one batch to the server, and pulls everything that changed since the last successful pull. Incoming records are applied with last-write-wins. That's the basic loop.

The queue schema is deliberately boring:

export const syncQueue = sqliteTable('sync_queue', {
    id: text('id', { length: 21 }).primaryKey(),
    tableName: text('table_name').notNull(),
    recordId: text('record_id', { length: 21 }).notNull(),
    operation: text('operation', {
        enum: ['create', 'update', 'delete']
    }).notNull(),
    data: text('data', { mode: 'json' })
        .$type<Record<string, unknown>>(),
    timestamp: integer('timestamp', { mode: 'timestamp_ms' }).notNull(),
    synced: integer('synced').notNull().default(0),
});

Synchronized write paths call queueSyncOperation after the local database operation succeeds:

export const queueSyncOperation = async (
    operation: Omit<SyncQueueInsert, 'id' | 'synced'>
) => {
    if (!isSyncEnabled()) return;

    const syncOperation: SyncQueueInsert = { id: nanoid(), ...operation };
    await db.insert(syncQueue).values(syncOperation);
};

If EXPO_PUBLIC_SYNC_HOST isn't set, the helper returns immediately and adds no new queue rows. The sync provider remains disabled. Creates usually store a complete record in data; updates can store only the fields that changed. The compaction pass merges those patches before sending them.

Making sync optional

// src/sync/config.ts
export const isSyncEnabled = (): boolean =>
    typeof process.env.EXPO_PUBLIC_SYNC_HOST === 'string' &&
    process.env.EXPO_PUBLIC_SYNC_HOST.length > 0;

One environment variable gates both queueing and the sync provider. Leave it unset and the app remains a local SQLite app. It doesn't poll for changes, register with a server, or make sync network calls.

This let me ship the local app first, then add sync without replacing the data layer.

Push: compact first, then send

My first version sent queued operations to the server one at a time. That became wasteful during a workout, when the same exercise record might be updated a dozen times. Sending twelve updates for one row also makes their order part of the protocol.

Before a push, I now compact all pending operations into one final operation per record:

const groupOperationsByTable = (operations: SyncQueueSelect[]) => {
    const compacted = new Map<string, CompactedSyncOperation>();

    for (const operation of operations) {
        const key = `${operation.tableName}:${operation.recordId}`;
        const current = compacted.get(key);

        switch (operation.operation) {
            case 'create':
                if (current?.operation === 'delete') break;

                compacted.set(key, {
                    operation: 'create',
                    data: mergeSyncData(
                        operation.recordId,
                        operation.data,
                        current?.operation === 'create' ? current.data : null,
                    ),
                });
                break;

            case 'update': {
                if (current?.operation === 'delete') break;

                const nextOperation =
                    current?.operation === 'create' ? 'create' : 'update';
                const existingData =
                    current?.operation === 'create' || current?.operation === 'update'
                        ? current.data
                        : null;

                compacted.set(key, {
                    operation: nextOperation,
                    data: mergeSyncData(
                        operation.recordId,
                        operation.data,
                        existingData,
                    ),
                });
                break;
            }

            case 'delete':
                compacted.set(key, { operation: 'delete', data: null });
                break;
        }
    }

    // Group the result into { tableName: { created[], updated[], deleted[] } }
};

The create → update case is easy to get wrong. If a record is created and then edited before the next sync, the compacted result must still be a create, but with the final merged data. The server hasn't seen the row yet, so sending an update would produce a missing-record conflict.

The compacted batch goes out in one request:

POST /sync

{
  "workout": {
    "created": [...],
    "updated": [...],
    "deleted": ["id1", "id2"]
  },
  "exercise_set": {
    "created": [...],
    "updated": [],
    "deleted": []
  }
}

Pull: incremental, last-write-wins

The client stores a lastSyncTimestamp and sends it on the next pull:

GET /sync?since=1744123456789&userId=user_123&type=user

The response groups records and deleted IDs by table. Each table also has a server timestamp. Existing records are updated only when the incoming updatedAt is newer:

const upsert = async (table, rows, key, dateKeys) => {
    for (const row of rows) {
        const found = await tx.select().from(table)
            .where(eq(key, row.id)).limit(1);

        if (found.length === 0) {
            await tx.insert(table).values(row);
            continue;
        }

        const serverTime = parseServerDate(row.updatedAt).getTime();
        const localTime = parseServerDate(found[0].updatedAt).getTime();

        if (serverTime > localTime) {
            await tx.update(table).set(row).where(eq(key, row.id));
        }
    }
};

One bug here took longer than it should have. The server is written in Rust and can return a NaiveDateTime without a timezone suffix. JavaScript treats that string as local time, not UTC, so the comparison can be wrong without throwing an error. parseServerDate checks for a timezone indicator and appends Z when it is missing.

After a successful pull, the client stores the largest per-table timestamp from the server response as lastSyncTimestamp.

Conflict resolution

Last-write-wins covers ordinary update conflicts, but it doesn't cover an update to a record that another device has already deleted.

Suppose device A deletes a workout. Device B is offline and still has that workout, so it queues an update. When device B reconnects and pushes, the server responds with the record that caused the conflict:

409 { type: "conflict", code: "missing_record", table: "workout", id: "..." }

The client marks the stale update for that record as done, pulls the deletion, and retries the remaining push:

let pushResult = await pushLocalChanges();

if (!pushResult.success && pushResult.conflictMissingRecord) {
    const pullResult = await pullServerChanges();
    if (!pullResult.success) return false;

    pushResult = await pushLocalChanges();
}

After the pull, the local copy is gone as well. I don't hit this conflict often. It needs an offline edit to overlap a deletion on another device, but sooner or later it happens.

The backfill problem

I almost missed backfill. A user can run Skulpt in local-only mode for three months, then enable sync. Their database may contain hundreds of records, but the queue is empty because queueSyncOperation returned early while sync was disabled.

Before the first sync of each app session, the client scans every synchronized table and queues records modified after lastSyncTimestamp:

let backfillDone = false;

export const backfillSyncQueue = async (): Promise<void> => {
    if (backfillDone) return;

    const lastSync = await getLastSyncTimestamp();

    const existingEntries = await db
        .select({ tableName: syncQueue.tableName, recordId: syncQueue.recordId })
        .from(syncQueue)
        .where(eq(syncQueue.synced, 0));

    const alreadyQueued = new Set(
        existingEntries.map(e => `${e.tableName}:${e.recordId}`)
    );

    for (const { name, table, updatedAtCol } of tables) {
        const rows = await db.select().from(table)
            .where(gt(updatedAtCol, lastSync));

        const toQueue = rows.filter(
            row => !alreadyQueued.has(`${name}:${row.id}`)
        );

        await queueSyncOperations(
            toQueue.map(row => ({
                tableName: name,
                recordId: row.id,
                operation: row.createdAt > lastSync ? 'create' : 'update',
                timestamp: row.updatedAt,
                data: row,
            }))
        );
    }

    backfillDone = true;
};

On the first sync, lastSyncTimestamp is new Date(0), so every eligible record is queued. If sync is re-enabled later, only records modified during the gap qualify. backfillDone keeps the scan to once per session.

Queue cleanup

The client marks completed operations with synced = 1 instead of deleting them immediately. Once the entire sync cycle succeeds, it removes those rows in batches:

const SYNC_QUEUE_CLEANUP_BATCH_SIZE = 1000;

export const cleanupSyncedOperations = async (): Promise<number> => {
    let deletedCount = 0;

    while (true) {
        const batch = await db
            .select({ id: syncQueue.id })
            .from(syncQueue)
            .where(eq(syncQueue.synced, 1))
            .limit(SYNC_QUEUE_CLEANUP_BATCH_SIZE);

        if (batch.length === 0) break;

        await db.delete(syncQueue).where(inArray(syncQueue.id, batch.map(r => r.id)));
        deletedCount += batch.length;

        if (batch.length < SYNC_QUEUE_CLEANUP_BATCH_SIZE) break;
    }

    return deletedCount;
};

A single delete over a large queue can hold SQLite's write lock long enough to affect the UI. Deleting at most 1,000 rows per statement puts a bound on that pause.

The full cycle

export const performSync = async (): Promise<boolean> => {
    await backfillSyncQueue();

    let pushResult = await pushLocalChanges();

    if (!pushResult.success && pushResult.conflictMissingRecord) {
        const pullResult = await pullServerChanges();
        if (!pullResult.success) return false;

        pushResult = await pushLocalChanges();
    }

    if (!pushResult.success) return false;

    const pullResult = await pullServerChanges();
    if (!pullResult.success) return false;

    await cleanupSyncedOperations();
    return true;
};

If the app stops during a sync, the next cycle reads the queue again. Operations are marked complete only after the server accepts the push.

What the server has to implement

The client uses one route with two HTTP methods. Most of the bookkeeping remains on the client.

POST /sync accepts one batch of creates, updates, and deletes grouped by table. It applies the changes and either confirms them or returns a conflict with the missing table and record ID.

GET /sync accepts a since timestamp and user ID. It returns records modified after that timestamp, deleted IDs, and a timestamp for each table in the response.

The server doesn't maintain a cursor for every client or calculate record diffs. It stores a batch and returns whatever changed after T.

Where this falls short

There is no real-time push channel. Skulpt polls while it is online and starts an initial sync when the app is in the foreground. A workout tracker can live with that delay. A feature that depends on live updates cannot.

Last-write-wins also fails when individual fields need to merge instead of one version replacing another. Skulpt's data belongs to one user and isn't edited collaboratively, so a record-level winner is acceptable here. It won't be acceptable for every data model.

Pulls aren't paginated. The client fetches all changes for a user since the previous timestamp in one response. That works for the amount of personal data Skulpt handles. A larger dataset or multi-tenant system would need scoped or paginated pulls.

PowerSync and ElectricSQL make sense when an app needs their broader replication model, especially for shared data or live updates. Skulpt doesn't.

Skulpt's records belong to one person, and record-level last-write-wins matches what an edit means in the app. In that setting I haven't needed more than the queue, compaction pass, and GET/POST route described above. The implementation is in src/sync/, and Skulpt is available under GPL-3.0.

Skulpt is an open-source workout tracker for iOS and Android. It is free and has no subscription.

DEV Community: Alexander Oskin