Local-First

PowerSync Testing Guide: Offline-First, SQLite Sync, and Schema Testing

HelpMeTest

19 May 2026 — 6 min read

PowerSync takes a different approach to local-first sync: it works with your existing Postgres (or MongoDB) backend and SQLite on the client, replicating rows automatically based on sync rules you define. Testing PowerSync means verifying that sync rules route the right data to the right users, upload queues handle mutations correctly, and your app behaves properly when the sync connection is lost.

This guide covers comprehensive PowerSync testing from schema validation to full offline simulation.

PowerSync's Testing Architecture

PowerSync has three testable layers:

Sync rules (sync-rules.yaml) — define which rows sync to which users
Client SDK — manages SQLite, upload queue, and sync state
App integration — your components and queries that use PowerSync data

Setting Up the Test Environment

npm install @powersync/web @powersync/common vitest jsdom

test-utils.ts:

import { PowerSyncDatabase, Schema, column, Table } from "@powersync/web";

export const AppSchema = new Schema([
  new Table({
    name: "todos",
    columns: [
      column.text("title"),
      column.integer("completed"),
      column.text("owner_id"),
      column.text("list_id"),
      column.integer("created_at"),
    ]
  }),
  new Table({
    name: "lists",
    columns: [
      column.text("name"),
      column.text("owner_id"),
      column.integer("created_at"),
    ]
  })
]);

export function createTestPowerSync() {
  return new PowerSyncDatabase({
    schema: AppSchema,
    database: { dbFilename: `:memory:` }
  });
}

export async function seedDatabase(db: PowerSyncDatabase, data: {
  todos?: Array<Record<string, any>>;
  lists?: Array<Record<string, any>>;
}) {
  for (const todo of data.todos ?? []) {
    await db.execute(
      `INSERT INTO todos (id, title, completed, owner_id, list_id, created_at)
       VALUES (?, ?, ?, ?, ?, ?)`,
      [todo.id, todo.title, todo.completed ? 1 : 0,
       todo.owner_id, todo.list_id, todo.created_at ?? Date.now()]
    );
  }
  for (const list of data.lists ?? []) {
    await db.execute(
      `INSERT INTO lists (id, name, owner_id, created_at) VALUES (?, ?, ?, ?)`,
      [list.id, list.name, list.owner_id, list.created_at ?? Date.now()]
    );
  }
}

Testing SQLite Queries

PowerSync uses raw SQLite, which means full SQL expressiveness:

describe("PowerSync SQLite Queries", () => {
  let db: PowerSyncDatabase;

  beforeEach(async () => {
    db = createTestPowerSync();
    await db.init();
    await seedDatabase(db, {
      lists: [
        { id: "list-1", name: "Shopping", owner_id: "user-1" },
        { id: "list-2", name: "Work", owner_id: "user-1" },
      ],
      todos: [
        { id: "t1", title: "Buy milk", completed: false, owner_id: "user-1", list_id: "list-1" },
        { id: "t2", title: "Buy eggs", completed: false, owner_id: "user-1", list_id: "list-1" },
        { id: "t3", title: "Write report", completed: true, owner_id: "user-1", list_id: "list-2" },
        { id: "t4", title: "Other user", completed: false, owner_id: "user-2", list_id: null },
      ]
    });
  });

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

  test("basic SELECT query", async () => {
    const result = await db.getAll("SELECT * FROM todos WHERE owner_id = ?", ["user-1"]);
    expect(result).toHaveLength(3);
  });

  test("filter by completed status", async () => {
    const completed = await db.getAll(
      "SELECT * FROM todos WHERE completed = 1 AND owner_id = ?", ["user-1"]
    );
    expect(completed).toHaveLength(1);
    expect(completed[0].title).toBe("Write report");
  });

  test("JOIN query across tables", async () => {
    const result = await db.getAll(`
      SELECT t.id, t.title, l.name as list_name
      FROM todos t
      JOIN lists l ON t.list_id = l.id
      WHERE t.owner_id = ?
      ORDER BY l.name, t.title
    `, ["user-1"]);

    expect(result).toHaveLength(3);
    const shoppingTodos = result.filter((r: any) => r.list_name === "Shopping");
    expect(shoppingTodos).toHaveLength(2);
  });

  test("aggregate — count per list", async () => {
    const counts = await db.getAll(`
      SELECT list_id, COUNT(*) as count
      FROM todos WHERE owner_id = ? GROUP BY list_id
    `, ["user-1"]);

    const list1Count = counts.find((c: any) => c.list_id === "list-1")?.count;
    expect(list1Count).toBe(2);
  });

  test("getOptional returns null for missing row", async () => {
    const result = await db.getOptional("SELECT * FROM todos WHERE id = ?", ["nonexistent"]);
    expect(result).toBeNull();
  });

  test("LIKE search works", async () => {
    const results = await db.getAll(
      "SELECT * FROM todos WHERE title LIKE ? AND owner_id = ?",
      ["%milk%", "user-1"]
    );
    expect(results).toHaveLength(1);
    expect(results[0].title).toBe("Buy milk");
  });
});

Testing PowerSync Schema

describe("Schema Validation", () => {
  test("schema defines all required tables", () => {
    const tableNames = AppSchema.tables.map(t => t.name);
    for (const name of ["todos", "lists"]) {
      expect(tableNames).toContain(name);
    }
  });

  test("todos table has required columns", () => {
    const todosTable = AppSchema.tables.find(t => t.name === "todos")!;
    const columnNames = todosTable.columns.map(c => c.name);
    for (const col of ["title", "completed", "owner_id", "list_id"]) {
      expect(columnNames).toContain(col);
    }
  });

  test("completed column is INTEGER (SQLite boolean)", () => {
    const todosTable = AppSchema.tables.find(t => t.name === "todos")!;
    const completedCol = todosTable.columns.find(c => c.name === "completed");
    expect(completedCol?.type).toBe("INTEGER");
  });
});

Testing Sync Rules Logic

Extract sync rule logic into testable TypeScript functions that mirror your sync-rules.yaml:

// sync-rule-logic.ts — mirrors sync-rules.yaml logic
interface SyncContext {
  userID: string;
  role?: "admin" | "user";
}

export function todosForUser(todo: Record<string, any>, ctx: SyncContext): boolean {
  return todo.owner_id === ctx.userID;
}

export function listsForUser(list: Record<string, any>, ctx: SyncContext): boolean {
  return list.owner_id === ctx.userID;
}

export function adminCanSeeAll(_row: Record<string, any>, ctx: SyncContext): boolean {
  return ctx.role === "admin";
}

describe("Sync Rules Logic", () => {
  const userCtx: SyncContext = { userID: "user-1", role: "user" };
  const adminCtx: SyncContext = { userID: "admin-1", role: "admin" };

  test("user sees only their own todos", () => {
    expect(todosForUser({ owner_id: "user-1" }, userCtx)).toBe(true);
    expect(todosForUser({ owner_id: "user-2" }, userCtx)).toBe(false);
  });

  test("user sees only their own lists", () => {
    expect(listsForUser({ owner_id: "user-1" }, userCtx)).toBe(true);
    expect(listsForUser({ owner_id: "user-2" }, userCtx)).toBe(false);
  });

  test("admin sees all rows", () => {
    expect(adminCanSeeAll({ owner_id: "user-99" }, adminCtx)).toBe(true);
    expect(adminCanSeeAll({ owner_id: "user-99" }, userCtx)).toBe(false);
  });

  test("null owner_id is not synced to any user", () => {
    expect(todosForUser({ owner_id: null }, userCtx)).toBe(false);
  });
});

Testing the Upload Queue

describe("Upload Queue", () => {
  test("inserts are added to upload queue", async () => {
    const db = createTestPowerSync();
    await db.init();

    await db.execute(
      `INSERT INTO todos (id, title, completed, owner_id) VALUES (?, ?, ?, ?)`,
      ["queue-1", "Test", 0, "user-1"]
    );

    const batch = await db.getCrudBatch(100);
    expect(batch).toBeDefined();
    if (batch && batch.crud.length > 0) {
      expect(batch.crud[0].op).toBe("PUT");
      expect(batch.crud[0].table).toBe("todos");
    }

    await db.close();
  });

  test("completing upload removes entry from queue", async () => {
    const db = createTestPowerSync();
    await db.init();

    await db.execute(
      `INSERT INTO todos (id, title, completed, owner_id) VALUES (?, ?, ?, ?)`,
      ["upload-1", "Upload test", 0, "user-1"]
    );

    const batch1 = await db.getCrudBatch(1);
    if (batch1 && batch1.crud.length > 0) {
      const entryId = batch1.crud[0].id;
      await batch1.complete(null);

      const batch2 = await db.getCrudBatch(100);
      const remaining = batch2?.crud.filter(e => e.id === entryId) ?? [];
      expect(remaining).toHaveLength(0);
    }

    await db.close();
  });

  test("delete creates DELETE entry in queue", async () => {
    const db = createTestPowerSync();
    await db.init();

    await db.execute(
      `INSERT INTO todos (id, title, completed, owner_id) VALUES (?, ?, ?, ?)`,
      ["delete-q", "Delete test", 0, "user-1"]
    );

    const insertBatch = await db.getCrudBatch(1);
    if (insertBatch) await insertBatch.complete(null);

    await db.execute(`DELETE FROM todos WHERE id = ?`, ["delete-q"]);

    const deleteBatch = await db.getCrudBatch(1);
    if (deleteBatch && deleteBatch.crud.length > 0) {
      expect(deleteBatch.crud[0].op).toBe("DELETE");
    }

    await db.close();
  });
});

Testing Offline Behavior

describe("Offline Behavior", () => {
  test("queries work without sync connection", async () => {
    const db = createTestPowerSync();
    await db.init();
    // No connect() — fully offline

    await db.execute(
      `INSERT INTO todos (id, title, completed, owner_id) VALUES (?, ?, ?, ?)`,
      ["offline-1", "Offline todo", 0, "user-1"]
    );

    const result = await db.getAll("SELECT * FROM todos WHERE id = ?", ["offline-1"]);
    expect(result).toHaveLength(1);

    await db.close();
  });

  test("disconnected status shows correctly", async () => {
    const db = createTestPowerSync();
    await db.init();

    expect(db.currentStatus.connected).toBe(false);
    expect(db.currentStatus.dataFlowStatus.downloading).toBe(false);

    await db.close();
  });

  test("transaction is atomic", async () => {
    const db = createTestPowerSync();
    await db.init();

    await db.writeTransaction(async (tx) => {
      await tx.execute(
        `INSERT INTO lists (id, name, owner_id) VALUES (?, ?, ?)`,
        ["txn-list", "Txn List", "user-1"]
      );
      await tx.execute(
        `INSERT INTO todos (id, title, completed, owner_id, list_id) VALUES (?, ?, ?, ?, ?)`,
        ["txn-todo", "Txn Todo", 0, "user-1", "txn-list"]
      );
    });

    const list = await db.getOptional("SELECT * FROM lists WHERE id = ?", ["txn-list"]);
    const todo = await db.getOptional("SELECT * FROM todos WHERE id = ?", ["txn-todo"]);
    expect(list).toBeDefined();
    expect(todo).toBeDefined();

    await db.close();
  });

  test("failed transaction rolls back", async () => {
    const db = createTestPowerSync();
    await db.init();

    try {
      await db.writeTransaction(async (tx) => {
        await tx.execute(
          `INSERT INTO todos (id, title, completed, owner_id) VALUES (?, ?, ?, ?)`,
          ["rollback-1", "Should rollback", 0, "user-1"]
        );
        throw new Error("Intentional failure");
      });
    } catch {}

    const result = await db.getOptional("SELECT * FROM todos WHERE id = ?", ["rollback-1"]);
    expect(result).toBeNull();

    await db.close();
  });
});

CI/CD Configuration

name: PowerSync Tests

on: [push, pull_request]

jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: "20"
          cache: "npm"
      - run: npm ci
      - name: Run schema and unit tests
        run: npx vitest run tests/

Monitoring PowerSync in Production

SQLite sync failures are often silent — users see stale data without any error. Common production issues include sync rules that don't match query patterns, upload queues stuck on server errors, and conflict resolution producing unexpected results.

HelpMeTest provides continuous end-to-end monitoring for PowerSync applications. Write a test once that verifies writes on one device appear on another within your SLA — HelpMeTest runs it every 5 minutes and alerts when sync breaks. Usage-based pricing includes 24/7 monitoring at 5-minute intervals with no base fee.

Summary

PowerSync testing has five areas:

SQL queries — filters, JOINs, aggregates, null handling
Schema validation — table completeness, column types
Sync rules — extract to TypeScript, test permission boundaries
Upload queue — mutation ordering, completion, delete handling
Offline behavior — query availability, transaction atomicity, rollback

Sync rules tests are the highest value — they catch permission bugs before they expose user data in production. Mirror your sync-rules.yaml logic in TypeScript and test every boundary condition.