Testing

Testing ElectricSQL: Local-First Sync, Conflict Resolution, and Offline Mode

HelpMeTest

HelpMeTest

— 8 min read
Testing ElectricSQL: Local-First Sync, Conflict Resolution, and Offline Mode

ElectricSQL takes a fundamentally different approach to application data. Rather than fetching data on demand, it syncs a subset of your Postgres database into a local SQLite store and keeps it live. Your application reads from local SQLite—always fast, always available—while Electric handles bidirectional sync in the background. This architecture is excellent for resilience and user experience, but it introduces testing challenges that don't exist in traditional client-server systems.

This guide covers how to build a robust test suite for ElectricSQL applications, from unit-testing shape subscriptions to validating conflict resolution behavior when offline mutations collide with server-side changes.

Understanding What Needs Testing

ElectricSQL's sync pipeline has several stages, each of which can fail in distinct ways:

  1. Shape subscriptions — did the client subscribe to the correct shape? Is the initial data correct?
  2. Sync state transitions — does the UI correctly reflect loading, synced, and error states?
  3. Offline writes — are local mutations persisted correctly to SQLite while offline?
  4. Conflict resolution — when offline writes conflict with server changes, does the CRDT logic produce the correct merged state?
  5. Reconnection behavior — does the client correctly replay local mutations and converge with the server after reconnecting?

Setting Up the Test Environment

ElectricSQL applications use PGlite (a WebAssembly Postgres) for local storage in browser environments, or SQLite via the better-sqlite3 driver in Node.js. For testing, PGlite is the most portable option.

npm install --save-dev vitest @electric-sql/pglite @electric-sql/client
npm install --save-dev @testing-library/react @testing-library/jest-dom

Create a test database factory that mirrors your production schema:

// src/test/db.ts
import { PGlite } from "@electric-sql/pglite";
import { electricSync } from "@electric-sql/pglite/sync";

export async function createTestDatabase() {
  const db = await PGlite.create({
    extensions: { electric: electricSync() },
  });

  // Apply your schema migrations
  await db.exec(`
    CREATE TABLE IF NOT EXISTS items (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      title TEXT NOT NULL,
      completed BOOLEAN NOT NULL DEFAULT false,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
      owner_id TEXT NOT NULL
    );

    CREATE TABLE IF NOT EXISTS comments (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
      body TEXT NOT NULL,
      author_id TEXT NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT now()
    );
  `);

  return db;
}

Testing Shape Subscriptions

Shapes are the core primitive in ElectricSQL—they define which rows sync to the client. Testing that your shapes are correctly defined prevents data missing from the local store.

// src/sync/shapes.ts
import { Shape, ShapeStream } from "@electric-sql/client";

export interface Item {
  id: string;
  title: string;
  completed: boolean;
  updated_at: string;
  owner_id: string;
}

export function createItemsStream(ownerId: string): ShapeStream<Item> {
  return new ShapeStream<Item>({
    url: `${import.meta.env.VITE_ELECTRIC_URL}/v1/shape`,
    params: {
      table: "items",
      where: `owner_id = '${ownerId}'`,
    },
  });
}

export function subscribeToItems(
  ownerId: string,
  callback: (items: Item[]) => void
): () => void {
  const stream = createItemsStream(ownerId);
  const shape = new Shape(stream);

  const unsubscribe = shape.subscribe(({ rows }) => {
    callback(rows);
  });

  return () => {
    unsubscribe();
    shape.unsubscribeAll();
  };
}
// src/sync/shapes.test.ts
import { expect, test, vi, describe, beforeEach } from "vitest";
import { Shape, ShapeStream } from "@electric-sql/client";
import { createItemsStream, subscribeToItems } from "./shapes";

vi.mock("@electric-sql/client", () => ({
  ShapeStream: vi.fn(),
  Shape: vi.fn(),
}));

describe("shape subscriptions", () => {
  let mockShape: {
    subscribe: ReturnType<typeof vi.fn>;
    unsubscribeAll: ReturnType<typeof vi.fn>;
  };

  beforeEach(() => {
    mockShape = {
      subscribe: vi.fn().mockReturnValue(vi.fn()),
      unsubscribeAll: vi.fn(),
    };
    vi.mocked(Shape).mockImplementation(() => mockShape as any);
    vi.mocked(ShapeStream).mockImplementation(() => ({}) as any);
  });

  test("createItemsStream uses correct table and where clause", () => {
    const ownerId = "user-123";
    createItemsStream(ownerId);

    expect(ShapeStream).toHaveBeenCalledWith(
      expect.objectContaining({
        params: expect.objectContaining({
          table: "items",
          where: `owner_id = '${ownerId}'`,
        }),
      })
    );
  });

  test("subscribeToItems calls callback with rows on update", () => {
    const ownerId = "user-456";
    const callback = vi.fn();

    const mockItems = [
      { id: "1", title: "Test item", completed: false, owner_id: ownerId, updated_at: new Date().toISOString() },
    ];

    mockShape.subscribe.mockImplementation((fn: (data: any) => void) => {
      fn({ rows: mockItems });
      return vi.fn();
    });

    subscribeToItems(ownerId, callback);

    expect(callback).toHaveBeenCalledWith(mockItems);
  });

  test("subscribeToItems returns cleanup function that unsubscribes", () => {
    const mockUnsubscribe = vi.fn();
    mockShape.subscribe.mockReturnValue(mockUnsubscribe);

    const cleanup = subscribeToItems("user-789", vi.fn());
    cleanup();

    expect(mockUnsubscribe).toHaveBeenCalled();
    expect(mockShape.unsubscribeAll).toHaveBeenCalled();
  });
});

Testing Local SQLite Operations

The local database layer is where most of your application logic runs. Test it directly using PGlite:

// src/db/items.ts
import type { PGlite } from "@electric-sql/pglite";

export async function createItem(
  db: PGlite,
  title: string,
  ownerId: string
): Promise<string> {
  const result = await db.query<{ id: string }>(
    "INSERT INTO items (title, owner_id) VALUES ($1, $2) RETURNING id",
    [title, ownerId]
  );
  return result.rows[0].id;
}

export async function toggleItem(db: PGlite, id: string): Promise<void> {
  await db.query(
    "UPDATE items SET completed = NOT completed, updated_at = now() WHERE id = $1",
    [id]
  );
}

export async function getItemsByOwner(
  db: PGlite,
  ownerId: string
): Promise<any[]> {
  const result = await db.query(
    "SELECT * FROM items WHERE owner_id = $1 ORDER BY updated_at DESC",
    [ownerId]
  );
  return result.rows;
}
// src/db/items.test.ts
import { expect, test, describe, beforeEach } from "vitest";
import { createTestDatabase } from "../test/db";
import { createItem, toggleItem, getItemsByOwner } from "./items";
import type { PGlite } from "@electric-sql/pglite";

describe("item database operations", () => {
  let db: PGlite;

  beforeEach(async () => {
    db = await createTestDatabase();
  });

  test("createItem inserts and returns an id", async () => {
    const id = await createItem(db, "My first item", "owner-1");

    expect(id).toBeTruthy();
    expect(typeof id).toBe("string");
  });

  test("getItemsByOwner returns only items for the given owner", async () => {
    await createItem(db, "Item A", "owner-1");
    await createItem(db, "Item B", "owner-1");
    await createItem(db, "Item C", "owner-2");

    const owner1Items = await getItemsByOwner(db, "owner-1");
    expect(owner1Items).toHaveLength(2);

    const owner2Items = await getItemsByOwner(db, "owner-2");
    expect(owner2Items).toHaveLength(1);
  });

  test("toggleItem flips completed state", async () => {
    const id = await createItem(db, "Toggle me", "owner-1");

    // Initially not completed
    const [before] = await getItemsByOwner(db, "owner-1");
    expect(before.completed).toBe(false);

    await toggleItem(db, id);

    const [after] = await getItemsByOwner(db, "owner-1");
    expect(after.completed).toBe(true);

    // Toggle back
    await toggleItem(db, id);
    const [final] = await getItemsByOwner(db, "owner-1");
    expect(final.completed).toBe(false);
  });
});

Testing Offline Mode and Sync State

One of the core promises of ElectricSQL is that writes work offline and sync when connectivity returns. Test this by simulating offline conditions:

// src/sync/offlineQueue.ts
export interface PendingWrite {
  id: string;
  table: string;
  operation: "insert" | "update" | "delete";
  data: Record<string, unknown>;
  timestamp: number;
}

export class OfflineQueue {
  private queue: PendingWrite[] = [];

  enqueue(write: Omit<PendingWrite, "id" | "timestamp">): string {
    const id = crypto.randomUUID();
    this.queue.push({
      ...write,
      id,
      timestamp: Date.now(),
    });
    return id;
  }

  dequeue(): PendingWrite | undefined {
    return this.queue.shift();
  }

  peek(): PendingWrite | undefined {
    return this.queue[0];
  }

  get size(): number {
    return this.queue.length;
  }

  getAll(): PendingWrite[] {
    return [...this.queue];
  }

  clear(): void {
    this.queue = [];
  }
}
// src/sync/offlineQueue.test.ts
import { expect, test, describe } from "vitest";
import { OfflineQueue } from "./offlineQueue";

describe("OfflineQueue", () => {
  test("enqueues writes and returns an id", () => {
    const queue = new OfflineQueue();

    const id = queue.enqueue({
      table: "items",
      operation: "insert",
      data: { title: "Offline item", owner_id: "user-1" },
    });

    expect(id).toBeTruthy();
    expect(queue.size).toBe(1);
  });

  test("dequeues in FIFO order", () => {
    const queue = new OfflineQueue();

    queue.enqueue({ table: "items", operation: "insert", data: { title: "First" } });
    queue.enqueue({ table: "items", operation: "insert", data: { title: "Second" } });
    queue.enqueue({ table: "items", operation: "insert", data: { title: "Third" } });

    expect(queue.dequeue()?.data.title).toBe("First");
    expect(queue.dequeue()?.data.title).toBe("Second");
    expect(queue.size).toBe(1);
  });

  test("returns undefined when queue is empty", () => {
    const queue = new OfflineQueue();
    expect(queue.dequeue()).toBeUndefined();
  });
});

Testing Conflict Resolution

ElectricSQL uses PostgreSQL's last-write-wins semantics by default, but many applications need custom conflict resolution. Test your conflict handlers explicitly:

// src/sync/conflicts.ts
export interface ItemVersion {
  id: string;
  title: string;
  completed: boolean;
  updated_at: string;
}

export type ConflictResolution = "local" | "remote" | "merge";

export interface ConflictResolver {
  resolve(local: ItemVersion, remote: ItemVersion): ItemVersion;
}

// Strategy: remote wins for completed state, merge titles if both changed
export class ItemConflictResolver implements ConflictResolver {
  resolve(local: ItemVersion, remote: ItemVersion): ItemVersion {
    const localTime = new Date(local.updated_at).getTime();
    const remoteTime = new Date(remote.updated_at).getTime();

    return {
      id: local.id,
      // Remote wins for completion state (server is authoritative)
      completed: remote.completed,
      // Most recent title wins
      title: remoteTime > localTime ? remote.title : local.title,
      // Always use the most recent timestamp
      updated_at: remoteTime > localTime ? remote.updated_at : local.updated_at,
    };
  }
}
// src/sync/conflicts.test.ts
import { expect, test, describe } from "vitest";
import { ItemConflictResolver } from "./conflicts";

const makeItem = (overrides: Partial<{
  id: string;
  title: string;
  completed: boolean;
  updated_at: string;
}> = {}) => ({
  id: "item-1",
  title: "Default title",
  completed: false,
  updated_at: "2024-01-01T10:00:00Z",
  ...overrides,
});

describe("ItemConflictResolver", () => {
  const resolver = new ItemConflictResolver();

  test("remote completed state always wins", () => {
    const local = makeItem({ completed: false, updated_at: "2024-01-01T12:00:00Z" });
    const remote = makeItem({ completed: true, updated_at: "2024-01-01T10:00:00Z" });

    const resolved = resolver.resolve(local, remote);
    expect(resolved.completed).toBe(true);
  });

  test("most recent title wins when timestamps differ", () => {
    const local = makeItem({ title: "Local edit", updated_at: "2024-01-01T11:00:00Z" });
    const remote = makeItem({ title: "Remote edit", updated_at: "2024-01-01T12:00:00Z" });

    const resolved = resolver.resolve(local, remote);
    expect(resolved.title).toBe("Remote edit");
  });

  test("local title wins when local is newer", () => {
    const local = makeItem({ title: "Local newer", updated_at: "2024-01-01T13:00:00Z" });
    const remote = makeItem({ title: "Remote older", updated_at: "2024-01-01T10:00:00Z" });

    const resolved = resolver.resolve(local, remote);
    expect(resolved.title).toBe("Local newer");
  });

  test("resolved item retains the id from local", () => {
    const local = makeItem({ id: "canonical-id" });
    const remote = makeItem({ id: "canonical-id" });

    const resolved = resolver.resolve(local, remote);
    expect(resolved.id).toBe("canonical-id");
  });
});

Testing React Components with Electric Hooks

// src/components/ItemList.test.tsx
import { render, screen, waitFor } from "@testing-library/react";
import userEvent from "@testing-library/user-event";
import { vi, expect, test, describe } from "vitest";

vi.mock("../hooks/useItems", () => ({
  useItems: vi.fn(),
}));

import { useItems } from "../hooks/useItems";
import { ItemList } from "./ItemList";

describe("ItemList", () => {
  test("shows syncing indicator when sync is in progress", () => {
    vi.mocked(useItems).mockReturnValue({
      items: [],
      syncing: true,
      error: null,
    });

    render(<ItemList ownerId="user-1" />);
    expect(screen.getByText(/syncing/i)).toBeInTheDocument();
  });

  test("renders items once sync completes", () => {
    vi.mocked(useItems).mockReturnValue({
      items: [
        { id: "1", title: "Buy groceries", completed: false, owner_id: "user-1", updated_at: "" },
        { id: "2", title: "Write tests", completed: true, owner_id: "user-1", updated_at: "" },
      ],
      syncing: false,
      error: null,
    });

    render(<ItemList ownerId="user-1" />);

    expect(screen.getByText("Buy groceries")).toBeInTheDocument();
    expect(screen.getByText("Write tests")).toBeInTheDocument();
  });

  test("shows error state when sync fails", () => {
    vi.mocked(useItems).mockReturnValue({
      items: [],
      syncing: false,
      error: new Error("Sync failed: network error"),
    });

    render(<ItemList ownerId="user-1" />);
    expect(screen.getByText(/sync failed/i)).toBeInTheDocument();
  });
});

End-to-End Sync Validation

For true end-to-end tests that validate the full sync pipeline against a real ElectricSQL service, you need browser automation. HelpMeTest can run parallel browser sessions and verify that changes made by one session appear in another—the definitive test for sync correctness. Combined with its AI-powered test generation, you can describe sync scenarios in plain English and get automated tests that verify bidirectional sync, offline behavior, and reconnection convergence without writing browser automation code by hand.

A typical sync validation scenario looks like this in prose:

  1. Open two browser tabs connected to the same database
  2. In tab A, create a new item
  3. Disconnect tab B from the network
  4. In tab B (offline), create another item and edit the first item
  5. Reconnect tab B
  6. Verify both items appear in both tabs with correct merged state

HelpMeTest's continuous monitoring can run this scenario against your staging environment on every deployment, catching sync regressions before they reach production.

CI Pipeline for ElectricSQL

# .github/workflows/electric-tests.yml
name: ElectricSQL Tests

on:
  push:
    branches: [main]
  pull_request:

jobs:
  unit-tests:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: "20"
          cache: "npm"
      - run: npm ci
      - run: npx vitest run --reporter=verbose

  integration-tests:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: electricsql/postgres-with-logical:14
        env:
          POSTGRES_DB: electric_test
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
      electric:
        image: electricsql/electric:latest
        env:
          DATABASE_URL: postgresql://postgres:postgres@postgres:5432/electric_test
          ELECTRIC_WRITE_TO_PG_MODE: direct_writes
        ports:
          - 5133:5133
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: "20"
      - run: npm ci
      - name: Run integration tests
        env:
          ELECTRIC_URL: http://localhost:5133
        run: npx vitest run --config vitest.integration.config.ts

Key Takeaways

Testing ElectricSQL applications requires thinking across three distinct layers: the shape subscription layer (what data syncs), the local SQLite layer (how data is read and written locally), and the conflict resolution layer (how concurrent edits merge). Each layer has its own failure modes and warrants dedicated tests.

Use PGlite for fast, in-process tests of your local database logic. Mock shape streams for component tests. Reserve full integration tests—running against real Electric and Postgres services—for your most critical sync scenarios. And run your end-to-end sync validation continuously with HelpMeTest to catch regressions the moment they're introduced.

Read more