Testing

Testing PowerSync: Offline-First Apps and SQLite Sync Validation

HelpMeTest

HelpMeTest

— 8 min read
Testing PowerSync: Offline-First Apps and SQLite Sync Validation

PowerSync connects your Postgres (or MongoDB, MySQL) backend to a local SQLite database on the client, enabling offline-first applications that work seamlessly without a network connection. The sync engine handles bidirectional data flow, conflict resolution, and state management—but none of that is useful if you can't verify it works correctly under the conditions your users actually encounter.

This guide builds a comprehensive test strategy for PowerSync applications, covering the SQLite local layer, sync state machine verification, conflict resolution testing, and React Native integration.

The PowerSync Testing Stack

PowerSync applications have three layers worth testing independently:

  1. Local SQLite layer — your schema, queries, and write operations
  2. Sync state machine — transitions between disconnected, connecting, connected, downloading, and uploading states
  3. Upload queue — local mutations queued for sync when connectivity returns

Each layer can be tested in isolation, with integration tests covering the interactions between them.

# Install test dependencies
npm install --save-dev vitest @testing-library/react-native jest-environment-node
npm install --save-dev @powersync/react-native  <span class="hljs-comment"># or @powersync/web for web apps

Setting Up a Test PowerSync Database

PowerSync exposes a AbstractPowerSyncDatabase base class that you can instantiate with an in-memory SQLite database for testing:

// src/test/testDb.ts
import { PowerSyncDatabase } from "@powersync/web";
import { WASQLiteOpenFactory } from "@powersync/web";
import { AppSchema } from "../schema";

export async function createTestDatabase() {
  const db = new PowerSyncDatabase({
    schema: AppSchema,
    database: {
      dbFilename: ":memory:",
    },
  });

  await db.init();
  return db;
}

Define your schema clearly so tests can validate it:

// src/schema.ts
import { column, Schema, Table } from "@powersync/web";

const todos = new Table({
  list_id: column.text,
  created_at: column.text,
  completed_at: column.text,
  description: column.text,
  completed: column.integer,
  created_by: column.text,
  completed_by: column.text,
});

const lists = new Table({
  created_at: column.text,
  name: column.text,
  owner_id: column.text,
});

export const AppSchema = new Schema({ todos, lists });
export type Database = (typeof AppSchema)["types"];

Testing Local SQLite Queries

PowerSync's local SQLite layer supports standard SQL queries. Test your query functions directly against an in-memory database:

// src/db/todos.ts
import type { PowerSyncDatabase } from "@powersync/web";

export interface Todo {
  id: string;
  list_id: string;
  description: string;
  completed: number;
  created_at: string;
  created_by: string;
  completed_at?: string;
  completed_by?: string;
}

export async function getTodosByList(
  db: PowerSyncDatabase,
  listId: string
): Promise<Todo[]> {
  return db.getAll<Todo>(
    "SELECT * FROM todos WHERE list_id = ? ORDER BY created_at DESC",
    [listId]
  );
}

export async function getIncompleteTodos(
  db: PowerSyncDatabase,
  listId: string
): Promise<Todo[]> {
  return db.getAll<Todo>(
    "SELECT * FROM todos WHERE list_id = ? AND completed = 0",
    [listId]
  );
}

export async function getTodoCount(
  db: PowerSyncDatabase,
  listId: string
): Promise<{ total: number; completed: number }> {
  const result = await db.get<{ total: number; completed: number }>(
    `SELECT
       COUNT(*) as total,
       SUM(CASE WHEN completed = 1 THEN 1 ELSE 0 END) as completed
     FROM todos
     WHERE list_id = ?`,
    [listId]
  );
  return result ?? { total: 0, completed: 0 };
}
// src/db/todos.test.ts
import { describe, test, expect, beforeEach, afterEach } from "vitest";
import { createTestDatabase } from "../test/testDb";
import { getTodosByList, getIncompleteTodos, getTodoCount } from "./todos";
import type { PowerSyncDatabase } from "@powersync/web";

describe("todos queries", () => {
  let db: PowerSyncDatabase;

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

  afterEach(async () => {
    await db.disconnectAndClear();
  });

  async function seedTodo(overrides: Partial<{
    id: string;
    list_id: string;
    description: string;
    completed: number;
    created_at: string;
    created_by: string;
  }> = {}) {
    const todo = {
      id: crypto.randomUUID(),
      list_id: "list-1",
      description: "Test todo",
      completed: 0,
      created_at: new Date().toISOString(),
      created_by: "user-1",
      ...overrides,
    };
    await db.execute(
      "INSERT INTO todos (id, list_id, description, completed, created_at, created_by) VALUES (?, ?, ?, ?, ?, ?)",
      [todo.id, todo.list_id, todo.description, todo.completed, todo.created_at, todo.created_by]
    );
    return todo;
  }

  test("getTodosByList returns todos for the given list", async () => {
    await seedTodo({ list_id: "list-1", description: "Task A" });
    await seedTodo({ list_id: "list-1", description: "Task B" });
    await seedTodo({ list_id: "list-2", description: "Task C" });

    const todos = await getTodosByList(db, "list-1");
    expect(todos).toHaveLength(2);
    expect(todos.map((t) => t.description)).toContain("Task A");
    expect(todos.map((t) => t.description)).toContain("Task B");
  });

  test("getIncompleteTodos filters out completed items", async () => {
    await seedTodo({ completed: 0, description: "Pending" });
    await seedTodo({ completed: 1, description: "Done" });

    const incomplete = await getIncompleteTodos(db, "list-1");
    expect(incomplete).toHaveLength(1);
    expect(incomplete[0].description).toBe("Pending");
  });

  test("getTodoCount returns correct totals", async () => {
    await seedTodo({ completed: 0 });
    await seedTodo({ completed: 0 });
    await seedTodo({ completed: 1 });

    const counts = await getTodoCount(db, "list-1");
    expect(counts.total).toBe(3);
    expect(counts.completed).toBe(1);
  });

  test("getTodosByList returns empty array for unknown list", async () => {
    const todos = await getTodosByList(db, "nonexistent-list");
    expect(todos).toHaveLength(0);
  });
});

Testing the Upload Queue (Conflict Resolution)

PowerSync's upload queue is where your conflict resolution logic lives. When local mutations sync to the server, the uploadData callback in your PowerSyncBackendConnector implementation decides how to handle conflicts:

// src/sync/connector.ts
import type { AbstractPowerSyncDatabase, PowerSyncBackendConnector, UploadQueueStats } from "@powersync/web";

export interface SyncOperationResult {
  success: boolean;
  conflictResolution?: "local_wins" | "remote_wins" | "merged";
}

export class AppConnector implements PowerSyncBackendConnector {
  async fetchCredentials() {
    const response = await fetch("/api/auth/powersync-token");
    const { token, endpoint } = await response.json();
    return { token, endpoint };
  }

  async uploadData(database: AbstractPowerSyncDatabase): Promise<void> {
    const transaction = await database.getNextCrudTransaction();
    if (!transaction) return;

    try {
      const operations = transaction.crud;

      for (const op of operations) {
        await this.applyOperation(op);
      }

      await transaction.complete();
    } catch (error) {
      // Leave transaction in queue for retry
      throw error;
    }
  }

  private async applyOperation(op: any): Promise<void> {
    const response = await fetch(`/api/data/${op.table}`, {
      method: op.op === "PUT" ? "PUT" : op.op === "PATCH" ? "PATCH" : "DELETE",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify({
        id: op.id,
        data: op.opData,
      }),
    });

    if (response.status === 409) {
      // Conflict — fetch server version and overwrite local
      const serverVersion = await response.json();
      await this.reconcileConflict(op, serverVersion);
      return;
    }

    if (!response.ok) {
      throw new Error(`Upload failed: ${response.status}`);
    }
  }

  private async reconcileConflict(localOp: any, serverData: any): Promise<void> {
    // Application-specific conflict resolution
    // In this case, server wins for concurrent edits to the same field
    const response = await fetch(`/api/data/${localOp.table}/${localOp.id}`, {
      method: "PUT",
      headers: { "Content-Type": "application/json" },
      body: JSON.stringify(serverData),
    });

    if (!response.ok) {
      throw new Error("Conflict resolution failed");
    }
  }
}
// src/sync/connector.test.ts
import { describe, test, expect, vi, beforeEach } from "vitest";
import { AppConnector } from "./connector";

describe("AppConnector.uploadData", () => {
  let connector: AppConnector;

  beforeEach(() => {
    connector = new AppConnector();
    global.fetch = vi.fn();
  });

  function makeMockDatabase(operations: any[]) {
    const transaction = {
      crud: operations,
      complete: vi.fn().mockResolvedValue(undefined),
    };

    return {
      getNextCrudTransaction: vi.fn().mockResolvedValue(transaction),
      _transaction: transaction,
    };
  }

  test("completes transaction after successful upload", async () => {
    vi.mocked(fetch).mockResolvedValue(
      new Response(JSON.stringify({ id: "1" }), { status: 200 })
    );

    const db = makeMockDatabase([
      { table: "todos", id: "todo-1", op: "PATCH", opData: { completed: true } },
    ]);

    await connector.uploadData(db as any);

    expect(db._transaction.complete).toHaveBeenCalled();
  });

  test("does nothing when transaction queue is empty", async () => {
    const db = {
      getNextCrudTransaction: vi.fn().mockResolvedValue(null),
    };

    await connector.uploadData(db as any);

    expect(fetch).not.toHaveBeenCalled();
  });

  test("throws on non-conflict server errors, leaving transaction for retry", async () => {
    vi.mocked(fetch).mockResolvedValue(
      new Response("Internal Server Error", { status: 500 })
    );

    const db = makeMockDatabase([
      { table: "todos", id: "todo-1", op: "PATCH", opData: { description: "Updated" } },
    ]);

    await expect(connector.uploadData(db as any)).rejects.toThrow("Upload failed: 500");
    expect(db._transaction.complete).not.toHaveBeenCalled();
  });
});

Testing Sync State Transitions

PowerSync exposes its sync state through a currentStatus observable. Test that your UI responds correctly to each state:

// src/hooks/useSyncStatus.ts
import { usePowerSync } from "@powersync/react";
import { SyncStatusType } from "@powersync/web";

export interface SyncStatus {
  isConnected: boolean;
  isDownloading: boolean;
  isUploading: boolean;
  hasPendingUploads: boolean;
  lastSyncedAt: Date | null;
}

export function useSyncStatus(): SyncStatus {
  const db = usePowerSync();
  const status = db.currentStatus;

  return {
    isConnected: status.connected,
    isDownloading: status.dataFlowStatus.downloading,
    isUploading: status.dataFlowStatus.uploading,
    hasPendingUploads: (status.stats?.uploadQueueCount ?? 0) > 0,
    lastSyncedAt: status.lastSyncedAt ?? null,
  };
}
// src/components/SyncStatusBar.test.tsx
import { render, screen } from "@testing-library/react";
import { vi, test, expect, describe } from "vitest";

vi.mock("@powersync/react", () => ({
  usePowerSync: vi.fn(),
}));

import { usePowerSync } from "@powersync/react";
import { SyncStatusBar } from "./SyncStatusBar";

function mockPowerSyncStatus(overrides: Partial<{
  connected: boolean;
  downloading: boolean;
  uploading: boolean;
  uploadQueueCount: number;
}> = {}) {
  const config = {
    connected: true,
    downloading: false,
    uploading: false,
    uploadQueueCount: 0,
    ...overrides,
  };

  vi.mocked(usePowerSync).mockReturnValue({
    currentStatus: {
      connected: config.connected,
      dataFlowStatus: {
        downloading: config.downloading,
        uploading: config.uploading,
      },
      stats: { uploadQueueCount: config.uploadQueueCount },
      lastSyncedAt: new Date(),
    },
  } as any);
}

describe("SyncStatusBar", () => {
  test("shows connected status when synced", () => {
    mockPowerSyncStatus({ connected: true });
    render(<SyncStatusBar />);
    expect(screen.getByText(/connected/i)).toBeInTheDocument();
  });

  test("shows offline indicator when disconnected", () => {
    mockPowerSyncStatus({ connected: false });
    render(<SyncStatusBar />);
    expect(screen.getByText(/offline/i)).toBeInTheDocument();
  });

  test("shows download progress when syncing", () => {
    mockPowerSyncStatus({ connected: true, downloading: true });
    render(<SyncStatusBar />);
    expect(screen.getByText(/syncing/i)).toBeInTheDocument();
  });

  test("shows pending uploads count when queue is non-empty", () => {
    mockPowerSyncStatus({ uploading: true, uploadQueueCount: 3 });
    render(<SyncStatusBar />);
    expect(screen.getByText(/3 pending/i)).toBeInTheDocument();
  });
});

React Native Testing with PowerSync

React Native adds extra complexity due to the SQLite native module. Use jest-expo or a manual mock:

// __mocks__/@powersync/react-native.ts
export const PowerSyncDatabase = vi.fn().mockImplementation(() => ({
  init: vi.fn().mockResolvedValue(undefined),
  execute: vi.fn().mockResolvedValue({ rowsAffected: 1 }),
  getAll: vi.fn().mockResolvedValue([]),
  get: vi.fn().mockResolvedValue(null),
  connect: vi.fn().mockResolvedValue(undefined),
  disconnect: vi.fn().mockResolvedValue(undefined),
  disconnectAndClear: vi.fn().mockResolvedValue(undefined),
  currentStatus: {
    connected: false,
    dataFlowStatus: { downloading: false, uploading: false },
    stats: { uploadQueueCount: 0 },
    lastSyncedAt: null,
  },
  watch: vi.fn().mockImplementation(async function* () {}),
}));

export const usePowerSync = vi.fn();
export const PowerSyncContext = { Provider: ({ children }: any) => children };

Testing the Watch API for Reactive Queries

PowerSync's watch API re-runs queries when dependent tables change. Test this reactive behavior:

// src/db/reactive.test.ts
import { describe, test, expect, beforeEach, afterEach } from "vitest";
import { createTestDatabase } from "../test/testDb";
import type { PowerSyncDatabase } from "@powersync/web";

describe("reactive watch queries", () => {
  let db: PowerSyncDatabase;

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

  afterEach(async () => {
    await db.disconnectAndClear();
  });

  test("watch emits updated results after an insert", async () => {
    const results: any[][] = [];

    const abortController = new AbortController();
    const watchPromise = (async () => {
      for await (const update of db.watch(
        "SELECT * FROM todos WHERE list_id = ?",
        ["list-1"],
        { signal: abortController.signal }
      )) {
        results.push(update.rows._array);
        if (results.length >= 2) break;
      }
    })();

    // Wait for initial emission (empty)
    await new Promise((resolve) => setTimeout(resolve, 50));

    // Insert a row
    await db.execute(
      "INSERT INTO todos (id, list_id, description, completed, created_at, created_by) VALUES (?, ?, ?, ?, ?, ?)",
      ["todo-1", "list-1", "Watch me", 0, new Date().toISOString(), "user-1"]
    );

    await watchPromise;
    abortController.abort();

    // First result: empty array
    expect(results[0]).toHaveLength(0);
    // Second result: contains the new todo
    expect(results[1]).toHaveLength(1);
    expect(results[1][0].description).toBe("Watch me");
  });
});

CI Pipeline Setup

# .github/workflows/powersync-tests.yml
name: PowerSync Tests

on:
  push:
    branches: [main, develop]
  pull_request:

jobs:
  unit-and-integration:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - uses: actions/setup-node@v4
        with:
          node-version: "20"
          cache: "npm"

      - name: Install dependencies
        run: npm ci

      - name: Run unit tests
        run: npx vitest run --reporter=verbose --coverage

      - name: Upload coverage to Codecov
        uses: codecov/codecov-action@v4
        with:
          files: ./coverage/lcov.info

  e2e-tests:
    runs-on: ubuntu-latest
    needs: unit-and-integration
    if: github.ref == 'refs/heads/main'
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: "20"
      - run: npm ci
      - name: Run E2E tests against staging
        env:
          POWERSYNC_URL: ${{ secrets.STAGING_POWERSYNC_URL }}
          SUPABASE_URL: ${{ secrets.STAGING_SUPABASE_URL }}
          SUPABASE_ANON_KEY: ${{ secrets.STAGING_SUPABASE_ANON_KEY }}
        run: npx playwright test --project=chromium

Monitoring Sync Health in Production

Beyond CI, sync applications need ongoing monitoring because sync failures are often silent—the app works, but data is stale or queued mutations never reach the server. HelpMeTest's continuous monitoring can validate sync health on your production or staging environment by running automated tests that:

  • Create a record in one session and verify it appears in another session within an acceptable time window
  • Simulate offline behavior and confirm the upload queue drains after reconnection
  • Verify that the sync status indicator accurately reflects the actual sync state

At $100/month for unlimited parallel test runs, HelpMeTest makes it economical to run these sync health checks every few minutes rather than waiting for user complaints. Its AI-powered test generation can analyze your PowerSync application and propose monitoring scenarios based on the sync patterns it detects.

Summary

PowerSync testing breaks cleanly into layers: use PGlite or an in-memory database for SQL query tests, mock the PowerSync hooks for component tests, and write explicit tests for your upload queue and conflict resolution logic. The reactive watch API requires async generator tests that observe multiple state transitions. End-to-end sync validation—verifying data propagates correctly between clients—is best handled by continuous monitoring tools running against a live environment.

Read more