Fix: PowerSync Not Working — Offline Sync Failing, Queries Returning Stale Data, or Backend Connection Errors

The Problem

PowerSync connects but local queries return empty:

const result = await db.getAll('SELECT * FROM todos');
// [] — empty even though the backend database has data

Or data changes locally but doesn’t sync to the server:

await db.execute('INSERT INTO todos (id, title) VALUES (?, ?)', [uuid(), 'New task']);
// Inserts locally but never appears on the server

Or the sync status stays “connecting” indefinitely:

PowerSync: connecting...
PowerSync: connecting...
// Never reaches "connected"

Why This Happens

PowerSync provides offline-first sync between a local SQLite database and a remote backend (Postgres, Supabase, MongoDB, etc.):

• Sync rules define what data syncs — PowerSync uses server-defined “sync rules” that control which rows sync to which users. If sync rules don’t match the data or the user’s parameters, no data arrives at the client.
• Writes go through a backend connector — PowerSync’s local SQLite is read-only from the sync perspective. Local writes are queued in an “upload queue” and sent to the server through your custom backend connector. Without a connector implementation, writes stay queued forever.
• The local database schema must match sync rules — the client defines its own SQLite schema that must align with what the sync rules send. Mismatched column names or types cause data to be dropped during sync.
• Authentication must be set up — PowerSync Cloud authenticates clients using JWTs. Without valid credentials, the sync connection fails.

Fix 1: Set Up PowerSync with React

npm install @powersync/react @powersync/web

// lib/powersync/schema.ts — local SQLite schema
import { column, Schema, Table } from '@powersync/web';

const todos = new Table({
  title: column.text,
  completed: column.integer,  // SQLite doesn't have boolean — use 0/1
  user_id: column.text,
  created_at: column.text,
});

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

export const schema = new Schema({ todos, projects });

// TypeScript types (optional but recommended)
export type Todo = {
  id: string;
  title: string;
  completed: number;
  user_id: string;
  created_at: string;
};

export type Project = {
  id: string;
  name: string;
  description: string;
  owner_id: string;
};

// lib/powersync/connector.ts — backend connector
import { AbstractPowerSyncDatabase, PowerSyncBackendConnector, UpdateType } from '@powersync/web';

export class BackendConnector implements PowerSyncBackendConnector {
  constructor(private apiUrl: string) {}

  // Fetch credentials for PowerSync Cloud
  async fetchCredentials() {
    const res = await fetch(`${this.apiUrl}/api/powersync/token`);
    const data = await res.json();

    return {
      endpoint: data.powersyncUrl,  // PowerSync Cloud endpoint
      token: data.token,             // JWT token
    };
  }

  // Upload local changes to your backend
  async uploadData(database: AbstractPowerSyncDatabase) {
    const transaction = await database.getNextCrudTransaction();
    if (!transaction) return;

    try {
      for (const op of transaction.crud) {
        switch (op.op) {
          case UpdateType.PUT:
            await fetch(`${this.apiUrl}/api/${op.table}`, {
              method: 'POST',
              headers: { 'Content-Type': 'application/json' },
              body: JSON.stringify({ id: op.id, ...op.opData }),
            });
            break;

          case UpdateType.PATCH:
            await fetch(`${this.apiUrl}/api/${op.table}/${op.id}`, {
              method: 'PATCH',
              headers: { 'Content-Type': 'application/json' },
              body: JSON.stringify(op.opData),
            });
            break;

          case UpdateType.DELETE:
            await fetch(`${this.apiUrl}/api/${op.table}/${op.id}`, {
              method: 'DELETE',
            });
            break;
        }
      }

      await transaction.complete();
    } catch (error) {
      console.error('Upload failed:', error);
      // Transaction will be retried on next sync
    }
  }
}

// lib/powersync/db.ts — initialize PowerSync
import { PowerSyncDatabase } from '@powersync/web';
import { schema } from './schema';
import { BackendConnector } from './connector';

let powerSyncInstance: PowerSyncDatabase | null = null;

export async function initPowerSync() {
  if (powerSyncInstance) return powerSyncInstance;

  const db = new PowerSyncDatabase({
    schema,
    database: { dbFilename: 'myapp.db' },
  });

  const connector = new BackendConnector(
    process.env.NEXT_PUBLIC_API_URL || 'http://localhost:3001'
  );

  await db.init();
  await db.connect(connector);

  powerSyncInstance = db;
  return db;
}

Fix 2: React Provider and Hooks

// app/providers.tsx
'use client';

import { PowerSyncContext } from '@powersync/react';
import { useEffect, useState } from 'react';
import { initPowerSync } from '@/lib/powersync/db';
import type { PowerSyncDatabase } from '@powersync/web';

export function PowerSyncProvider({ children }: { children: React.ReactNode }) {
  const [db, setDb] = useState<PowerSyncDatabase | null>(null);

  useEffect(() => {
    initPowerSync().then(setDb);
  }, []);

  if (!db) return <div>Initializing database...</div>;

  return (
    <PowerSyncContext.Provider value={db}>
      {children}
    </PowerSyncContext.Provider>
  );
}

// app/layout.tsx
import { PowerSyncProvider } from './providers';

export default function RootLayout({ children }) {
  return (
    <html>
      <body>
        <PowerSyncProvider>{children}</PowerSyncProvider>
      </body>
    </html>
  );
}

// components/TodoList.tsx — reactive queries
'use client';

import { useQuery, useStatus } from '@powersync/react';
import type { Todo } from '@/lib/powersync/schema';

function TodoList({ userId }: { userId: string }) {
  // Watched query — re-renders when data changes
  const { data: todos, isLoading } = useQuery<Todo>(
    'SELECT * FROM todos WHERE user_id = ? ORDER BY created_at DESC',
    [userId],
  );

  const status = useStatus();

  if (isLoading) return <div>Loading...</div>;

  return (
    <div>
      {/* Sync status indicator */}
      <div>
        {status.connected ? '🟢 Online' : '🔴 Offline'}
        {status.uploading && ' (uploading...)'}
        {status.downloading && ' (downloading...)'}
      </div>

      <ul>
        {todos.map(todo => (
          <li key={todo.id}>
            <input
              type="checkbox"
              checked={todo.completed === 1}
              onChange={() => toggleTodo(todo.id, todo.completed)}
            />
            {todo.title}
          </li>
        ))}
      </ul>
    </div>
  );
}

Fix 3: Local Writes (Offline-First)

// hooks/useTodos.ts
'use client';

import { usePowerSync, useQuery } from '@powersync/react';
import type { Todo } from '@/lib/powersync/schema';
import { v4 as uuid } from 'uuid';

export function useTodos(userId: string) {
  const db = usePowerSync();
  const { data: todos } = useQuery<Todo>(
    'SELECT * FROM todos WHERE user_id = ? ORDER BY created_at DESC',
    [userId],
  );

  async function addTodo(title: string) {
    // Write to local SQLite — instantly available
    await db.execute(
      'INSERT INTO todos (id, title, completed, user_id, created_at) VALUES (?, ?, ?, ?, ?)',
      [uuid(), title, 0, userId, new Date().toISOString()],
    );
    // PowerSync queues this write and uploads it via the connector
  }

  async function toggleTodo(id: string, currentCompleted: number) {
    await db.execute(
      'UPDATE todos SET completed = ? WHERE id = ?',
      [currentCompleted === 1 ? 0 : 1, id],
    );
  }

  async function deleteTodo(id: string) {
    await db.execute('DELETE FROM todos WHERE id = ?', [id]);
  }

  return { todos, addTodo, toggleTodo, deleteTodo };
}

// Usage in component
function TodoApp({ userId }: { userId: string }) {
  const { todos, addTodo, toggleTodo, deleteTodo } = useTodos(userId);

  return (
    <div>
      <button onClick={() => addTodo('New task')}>Add Todo</button>
      {todos.map(todo => (
        <div key={todo.id}>
          <input
            type="checkbox"
            checked={todo.completed === 1}
            onChange={() => toggleTodo(todo.id, todo.completed)}
          />
          {todo.title}
          <button onClick={() => deleteTodo(todo.id)}>Delete</button>
        </div>
      ))}
    </div>
  );
}

Fix 4: Sync Rules (Server-Side)

Sync rules define which data syncs to which users:

# sync-rules.yaml — PowerSync Cloud configuration
bucket_definitions:
  # User's own data
  user_data:
    parameters: SELECT token_parameters.user_id AS user_id
    data:
      - SELECT * FROM todos WHERE user_id = bucket.user_id
      - SELECT * FROM projects WHERE owner_id = bucket.user_id

  # Shared project data
  project_members:
    parameters:
      SELECT project_id FROM project_members
      WHERE user_id = token_parameters.user_id
    data:
      - SELECT * FROM projects WHERE id = bucket.project_id
      - SELECT * FROM todos WHERE project_id = bucket.project_id

Fix 5: Token Endpoint (Authentication)

// app/api/powersync/token/route.ts
import jwt from 'jsonwebtoken';
import { auth } from '@/auth';

export async function GET() {
  const session = await auth();
  if (!session?.user) {
    return Response.json({ error: 'Unauthorized' }, { status: 401 });
  }

  // Create a JWT for PowerSync with user-specific parameters
  const token = jwt.sign(
    {
      sub: session.user.id,
      iat: Math.floor(Date.now() / 1000),
      exp: Math.floor(Date.now() / 1000) + 300,  // 5 minutes
      parameters: {
        user_id: session.user.id,
      },
    },
    process.env.POWERSYNC_PRIVATE_KEY!,
    { algorithm: 'RS256' },
  );

  return Response.json({
    token,
    powersyncUrl: process.env.NEXT_PUBLIC_POWERSYNC_URL!,
  });
}

Fix 6: Conflict Resolution

// Backend connector — handle conflicts during upload
async uploadData(database: AbstractPowerSyncDatabase) {
  const transaction = await database.getNextCrudTransaction();
  if (!transaction) return;

  try {
    for (const op of transaction.crud) {
      if (op.op === UpdateType.PATCH) {
        // Send with last known server version for conflict detection
        const res = await fetch(`${this.apiUrl}/api/${op.table}/${op.id}`, {
          method: 'PATCH',
          headers: { 'Content-Type': 'application/json' },
          body: JSON.stringify({
            ...op.opData,
            _version: op.metadata?.version,  // For optimistic concurrency
          }),
        });

        if (res.status === 409) {
          // Conflict — server has a newer version
          // PowerSync will re-sync the latest server data
          console.warn('Conflict detected for', op.table, op.id);
          continue;  // Skip this op, let sync resolve it
        }
      }
    }
    await transaction.complete();
  } catch (error) {
    // Will retry on next sync cycle
  }
}

Still Not Working?

Queries return empty after connecting — sync rules might not match the user’s token parameters. Check that token_parameters.user_id in sync rules matches the parameters.user_id in the JWT. Also verify the table names in sync rules match the Postgres tables exactly.

Writes queue but never upload — the uploadData method in your connector is either not implemented, throwing errors silently, or not calling transaction.complete(). Add logging to the upload method. Also check that the backend API endpoints your connector calls are accessible and returning success responses.

Sync status stays “connecting” — the PowerSync Cloud endpoint URL or token is invalid. Check that NEXT_PUBLIC_POWERSYNC_URL is set correctly. Verify the JWT token endpoint returns valid tokens. Enable debug logging to see connection errors.

Data syncs down but local writes are lost on page reload — PowerSync persists the local SQLite database using IndexedDB (web) or the filesystem (React Native). If the browser clears IndexedDB, local data is lost. Ensure writes are uploaded before the user navigates away by checking status.uploading.

For related offline-first and database issues, see Fix: ElectricSQL Not Working and Fix: Turso Not Working.