agents-inc/mobile-storage-sqlite-powersync

SQLite + PowerSync Patterns

Quick Guide: Use @powersync/react-native for offline-first apps backed by local SQLite. Define schemas with Table and column.text/integer/real (id column is auto-created). Use PowerSyncDatabase for reads/writes, useQuery from @powersync/react for reactive watched queries. Connect to your backend via a connector implementing fetchCredentials + uploadData. Conflict resolution defaults to last-write-wins per field -- customize in uploadData. Use @powersync/op-sqlite for SQLCipher encryption.

---

<critical_requirements>

CRITICAL: Before Using This Skill

All code must follow project conventions in CLAUDE.md (kebab-case, named exports, import ordering, import type, named constants)

(You MUST define schemas with new Table({ ... }) using column.text, column.integer, column.real -- NEVER declare an id column, PowerSync creates it automatically)

(You MUST call powersync.connect(connector) after init() to start syncing -- without it the database is local-only with no sync)

(You MUST implement both fetchCredentials() and uploadData() in your backend connector -- missing either breaks the sync loop)

(You MUST use useQuery from @powersync/react for reactive queries -- raw getAll() does NOT re-render on data changes)

</critical_requirements>

---

Auto-detection: PowerSync, powersync, @powersync/react-native, @powersync/react, @powersync/op-sqlite, PowerSyncDatabase, useQuery, usePowerSync, useStatus, useSuspenseQuery, PowerSyncBackendConnector, fetchCredentials, uploadData, column.text, column.integer, column.real, Schema, Table, sync rules, bucket_definitions, offline-first SQLite, watched query, CrudEntry, CrudTransaction, AttachmentQueue, AttachmentTable, local-only table

When to use:

• Building offline-first React Native apps that sync with a cloud database
• Storing relational data locally in SQLite with automatic cloud sync
• Implementing reactive UIs that update when synced data changes
• Handling CRUD operations that work offline and sync when reconnected
• Defining sync rules (bucket definitions) for partial data replication
• Managing file attachments with offline upload/download queues

Key patterns covered:

• Schema definition with Table, column types, indexes, and local-only tables
• PowerSyncDatabase setup with default or OP-SQLite adapter
• React hooks: useQuery, useSuspenseQuery, useStatus, usePowerSync
• Backend connector: fetchCredentials() + uploadData() implementation
• CRUD operations via execute(), get(), getAll(), getOptional()
• Sync rules with bucket definitions (YAML) for per-user data filtering
• Conflict resolution strategies (last-write-wins, field-level, custom)
• Attachment handling with AttachmentTable and AttachmentQueue
• OP-SQLite integration for SQLCipher encryption

When NOT to use:

• Simple key-value storage without sync (use a key-value store)
• Apps that never go offline and always have connectivity
• Data that does not need relational queries (use a key-value store)
• File-only storage without structured metadata (use the filesystem)

Detailed Resources:

• examples/core.md - Schema, database setup, CRUD, watched queries, hooks
• examples/sync.md - Backend connector, sync rules, conflict resolution
• examples/attachments.md - Attachment queue, upload/download, storage adapters
• reference.md - API reference, setup checklist

---

<philosophy>

Philosophy

PowerSync is an offline-first sync engine that sits on top of SQLite. The core idea: your app reads and writes to a local SQLite database instantly (no network calls), and PowerSync handles bidirectional sync with your cloud database in the background.

Core principles:

1. Local-first -- all reads and writes hit local SQLite, so the app works instantly and offline
2. Sync is transparent -- PowerSync streams changes from the server and uploads local mutations automatically
3. Schema drives everything -- the client schema defines local tables, the server sync rules define what data each client receives
4. Conflict resolution is yours -- defaults to last-write-wins, but uploadData() gives you full control
5. Watched queries for reactivity -- useQuery re-executes queries when dependent tables change, keeping UI in sync

Architecture overview:

Client (React Native)              Cloud
+-------------------+         +-------------------+
| Local SQLite DB   | <-sync->| PowerSync Service |<--- Source DB (Postgres, etc.)
| (PowerSyncDatabase)|        | (Sync Rules)      |
+-------------------+         +-------------------+
| @powersync/react  |         | Bucket Defs       |
| (useQuery, etc.)  |         | (YAML config)     |
+-------------------+         +-------------------+

Data flow:

• Writes: App calls execute(INSERT/UPDATE/DELETE) on local SQLite. PowerSync queues the change and calls your uploadData() to push it to the backend.
• Reads: Sync rules on the server determine which data each client receives. The PowerSync Service streams changes to the client's local SQLite. useQuery watches for table changes and re-renders.

Column types: Only three types exist -- column.text, column.integer, column.real. The id column (text, primary key) is auto-created. If a synced value doesn't match the declared type, it is cast automatically.

</philosophy>

---

<patterns>

Core Patterns

Pattern 1: Schema Definition

Define your client-side schema using Table and column types. The schema mirrors your server tables (minus the id column, which is auto-created).

import { column, Schema, Table } from "@powersync/react-native";

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

const todos = new Table(
  {
    list_id: column.text,
    created_at: column.text,
    completed_at: column.text,
    description: column.text,
    completed: column.integer,
  },
  { indexes: { list: ["list_id"] } },
);

export const AppSchema = new Schema({ todos, lists });

// Derive types from schema
export type Database = (typeof AppSchema)["types"];
export type TodoRecord = Database["todos"];
export type ListRecord = Database["lists"];

Why good: Schema is source of truth for types, indexes optimize query performance, no manual id column needed

Gotcha: Table names in the schema must match table names in your sync rules. Mismatches cause data to silently not sync.

See examples/core.md for local-only tables and index configuration.

---

Pattern 2: PowerSyncDatabase Setup

Create the database instance at app startup. Choose between the default SQLite adapter or OP-SQLite for encryption.

import { PowerSyncDatabase } from "@powersync/react-native";
import { AppSchema } from "./schema";

const DB_FILENAME = "app.db";

export const powersync = new PowerSyncDatabase({
  schema: AppSchema,
  database: { dbFilename: DB_FILENAME },
});

// Initialize and connect (typically in app bootstrap)
async function initDatabase(connector: PowerSyncBackendConnector) {
  await powersync.init();
  await powersync.connect(connector);
}

Why good: Single instance shared across app, init() creates SQLite tables from schema, connect() starts bidirectional sync

Gotcha: Without connect(), the database works but is purely local -- no sync occurs.

See examples/core.md for OP-SQLite setup with encryption and the React context provider pattern.

---

Pattern 3: React Hooks for Reactive Queries

Use useQuery from @powersync/react for watched queries that re-execute when dependent tables change. Wrap your app in PowerSyncContext.Provider.

import { useQuery, useStatus, usePowerSync } from "@powersync/react";

function TodoList({ listId }: { listId: string }) {
  const { data: todos, isLoading, error } = useQuery<TodoRecord>(
    "SELECT * FROM todos WHERE list_id = ? ORDER BY created_at DESC",
    [listId],
  );

  if (isLoading) return <ActivityIndicator />;
  if (error) return <Text>Error: {error.message}</Text>;

  return (
    <FlatList
      data={todos}
      renderItem={({ item }) => <TodoItem todo={item} />}
      keyExtractor={(item) => item.id}
    />
  );
}

Why good: useQuery automatically re-runs when the todos table changes (insert, update, delete), isLoading and error handle loading/error states

See examples/core.md for useSuspenseQuery, useStatus, usePowerSync, and runQueryOnce usage.

---

Pattern 4: CRUD Operations

All writes use execute() with parameterized SQL. PowerSync queues changes and calls your uploadData() to sync.

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

function useTodos(listId: string) {
  const powersync = usePowerSync();

  const addTodo = async (description: string) => {
    await powersync.execute(
      "INSERT INTO todos (id, list_id, description, created_at, completed) VALUES (uuid(), ?, ?, datetime(), 0)",
      [listId, description],
    );
  };

  const toggleTodo = async (id: string, completed: boolean) => {
    const completedAt = completed ? new Date().toISOString() : null;
    await powersync.execute(
      "UPDATE todos SET completed = ?, completed_at = ? WHERE id = ?",
      [completed ? 1 : 0, completedAt, id],
    );
  };

  const deleteTodo = async (id: string) => {
    await powersync.execute("DELETE FROM todos WHERE id = ?", [id]);
  };

  return { addTodo, toggleTodo, deleteTodo };
}

Why good: Writes hit local SQLite instantly (no network wait), uuid() generates IDs client-side, parameterized queries prevent SQL injection

Gotcha: execute() returns { rowsAffected, insertId }. When using views, rowsAffected may return 0 -- use RETURNING clauses to confirm mutations.

See examples/core.md for get(), getAll(), getOptional(), and transaction patterns.

---

Pattern 5: Backend Connector

The connector bridges PowerSync with your backend. Implement fetchCredentials() for auth and uploadData() for pushing local changes.

import type { PowerSyncBackendConnector, PowerSyncCredentials } from "@powersync/react-native";
import type { AbstractPowerSyncDatabase } from "@powersync/react-native";

export const connector: PowerSyncBackendConnector = {
  fetchCredentials: async (): Promise<PowerSyncCredentials> => {
    // Return your PowerSync instance URL and a valid JWT
    const session = await getAuthSession();
    return {
      endpoint: POWERSYNC_URL,
      token: session.accessToken,
      expiresAt: session.expiresAt,
    };
  },

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

    for (const op of transaction.crud) {
      // Send each operation to your backend API
      await applyOperation(op);
    }
    await transaction.complete();
  },
};

Why good: Clean separation of auth and data upload, transaction-based processing ensures atomicity, complete() marks the batch as synced

See examples/sync.md for the full Supabase connector, custom backend patterns, and error handling with retries.

---

Pattern 6: Sync Rules (Bucket Definitions)

Sync rules (YAML) define which server data each client receives. Configured on the PowerSync Service, not in client code.

bucket_definitions:
  user_lists:
    parameters: SELECT request.user_id() as user_id
    data:
      - SELECT * FROM lists WHERE owner_id = bucket.user_id
      - SELECT * FROM todos WHERE list_id IN (
          SELECT id FROM lists WHERE owner_id = bucket.user_id
        )

  global_settings:
    # No parameters = global bucket, synced to all clients
    data:
      - SELECT * FROM settings

Why good: Per-user data filtering at the server, global buckets for shared data, SQL-based rules are familiar

Gotcha: Maximum 1,000 buckets per client (default). Table names must match client schema.

See examples/sync.md for parameterized buckets, client parameters, and multi-tenant patterns.

---

Pattern 7: Conflict Resolution

Default behavior is last-write-wins per field. Customize in your uploadData() implementation.

The key insight: PowerSync gives you full control in uploadData(). You choose how to handle each CrudEntry operation -- accept, reject, merge, or record conflicts.

Common strategies:

• Last-write-wins (default): Simply upsert each operation
• Timestamp-based: Compare client vs server timestamps, reject stale writes
• Field-level merge: Apply only newer field values, keep others
• Server-side validation: Enforce business rules (e.g., prevent modifying shipped orders)
• Conflict recording: Store both versions for manual user resolution

See examples/sync.md for complete conflict resolution implementations.

---

Pattern 8: Attachment Handling

Use AttachmentTable in your schema and AttachmentQueue for offline-capable file upload/download.

import { AttachmentTable } from "@powersync/react-native";
import { column, Schema, Table } from "@powersync/react-native";

const users = new Table({
  name: column.text,
  photo_id: column.text, // References attachment ID
});

export const AppSchema = new Schema({
  users,
  attachments: new AttachmentTable(),
});

The AttachmentQueue manages the lifecycle: local save, queued upload, synced state, automatic download on other devices, retry on failure.

See examples/attachments.md for queue setup, upload/download handlers, and storage adapter patterns.

</patterns>

---

<decision_framework>

Decision Framework

What kind of data are you storing?
|
+-> Relational data that needs offline + cloud sync?
|   +-> YES -> PowerSync + SQLite (this skill)
|   +-> NO  -> Key-value pairs only?
|       +-> YES -> Use a key-value store (not this skill)
|       +-> NO  -> Files/media only?
|           +-> YES -> Use the filesystem
|
+-> Do you need reactive queries in React?
|   +-> YES -> Use useQuery from @powersync/react
|   +-> NO  -> Use powersync.getAll() / get() directly
|
+-> Do you need on-device encryption?
|   +-> YES -> Use @powersync/op-sqlite with SQLCipher
|   +-> NO  -> Use the default SQLite adapter
|
+-> Do you have file attachments?
|   +-> YES -> Use AttachmentTable + AttachmentQueue
|   +-> NO  -> Standard schema is sufficient
|
+-> How should conflicts be resolved?
    +-> Simple apps -> Last-write-wins (default)
    +-> Collaborative editing -> Field-level merge or CRDTs
    +-> Business-critical -> Server-side validation + conflict recording

When to Use Each Query API

| Scenario | API | |-----------------------------------|--------------------------------------------| | Reactive component data | useQuery() from @powersync/react | | Reactive with Suspense | useSuspenseQuery() from @powersync/react | | One-time fetch (no reactivity) | useQuery() with runQueryOnce: true | | Service/utility reads | powersync.getAll() / get() / getOptional() | | Write operations | powersync.execute() | | Connection status | useStatus() from @powersync/react | | Database instance access | usePowerSync() from @powersync/react |

</decision_framework>

---

<red_flags>

RED FLAGS

High Priority Issues:

• Declaring an id column in schema -- PowerSync auto-creates id as text primary key. Declaring it causes conflicts.
• Calling execute() for reads (SELECT) instead of getAll() / useQuery() -- execute() does not return query results in a usable format
• Forgetting powersync.connect(connector) -- database works locally but nothing syncs, easy to miss in development
• Using getAll() in React components expecting reactivity -- raw reads do not watch for changes, use useQuery() instead
• Missing transaction.complete() in uploadData() -- unacknowledged transactions retry indefinitely, causing duplicate uploads

Medium Priority Issues:

• Schema table names not matching sync rule table names -- data silently fails to sync
• Not handling fetchCredentials() returning null -- happens when auth session expires, must re-authenticate
• Storing large blobs in SQLite columns -- use AttachmentTable for files, keep SQLite for metadata
• Missing indexes on frequently queried columns -- sync queries can be slow with large datasets
• Using column.integer for booleans without consistent 0/1 values -- SQLite has no native boolean type

Gotchas & Edge Cases:

• uuid() is a PowerSync SQL function, not a JavaScript function -- use it in SQL strings, not in JS
• column.real stores IEEE 754 doubles -- be aware of floating-point precision for currency (use integer cents instead)
• Sync rules YAML uses request.user_id() to access the authenticated user ID from the JWT -- not a custom function
• getNextCrudTransaction() returns null when the upload queue is empty -- always check before iterating
• execute() with views may return rowsAffected: 0 even on success -- use RETURNING clause for confirmation
• PowerSync supports WebSocket (default since v1.11.0) and HTTP streaming for sync -- WebSocket is recommended
• The Rust-based sync client is enabled by default since v1.29.0 -- pass clientImplementation: SyncClientImplementation.JAVASCRIPT to use the legacy JS client
• disconnectAndClear() removes all local data -- use disconnect() to stop sync while preserving local data
• Local-only tables (set localOnly: true on Table options) are never synced -- useful for draft data or app state

</red_flags>

---

<critical_reminders>

CRITICAL REMINDERS

All code must follow project conventions in CLAUDE.md

(You MUST define schemas with new Table({ ... }) using column.text, column.integer, column.real -- NEVER declare an id column, PowerSync creates it automatically)

(You MUST call powersync.connect(connector) after init() to start syncing -- without it the database is local-only with no sync)

(You MUST implement both fetchCredentials() and uploadData() in your backend connector -- missing either breaks the sync loop)

(You MUST use useQuery from @powersync/react for reactive queries -- raw getAll() does NOT re-render on data changes)

Failure to follow these rules will cause silent sync failures, missing data, and non-reactive UIs.

</critical_reminders>