Back to Details

mobile-storage-sqlite-powersync

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

SQLite + PowerSync Patterns

SQLite + PowerSync 模式

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>
快速指南： 使用
@powersync/react-native
构建基于本地SQLite的优先离线应用。通过
Table
和
column.text/integer/real
定义schema（id列会自动创建）。使用
PowerSyncDatabase
进行读写操作，使用
@powersync/react
中的
useQuery
实现响应式监听查询。通过实现
fetchCredentials
+ 
uploadData
的连接器对接后端。冲突解决默认采用按字段最后写入获胜规则——可在
uploadData
中自定义。使用
@powersync/op-sqlite
实现SQLCipher加密。
<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>
所有代码必须遵循CLAUDE.md中的项目约定（短横线命名法、命名导出、导入顺序、
import type
、命名常量）
（必须使用
new Table({ ... })
结合
column.text
、
column.integer
、
column.real
定义schema——绝不能声明
id
列，PowerSync会自动创建）
（必须在
init()
后调用
powersync.connect(connector)
以启动同步——否则数据库仅为本地模式，无法同步）
（必须在后端连接器中同时实现
fetchCredentials()
和
uploadData()
——缺少任意一个都会破坏同步循环）
（必须使用
@powersync/react
中的
useQuery
实现响应式查询——原生
getAll()
不会在数据变化时重新渲染）
</critical_requirements>
自动检测关键词： 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
适用场景：
  • 构建可与云数据库同步的优先离线React Native应用
  • 在本地SQLite中存储关系型数据并实现自动云同步
  • 实现可随同步数据变化而更新的响应式UI
  • 处理可离线运行、重新连接后自动同步的CRUD操作
  • 定义同步规则（bucket定义）以实现部分数据复制
  • 通过离线上传/下载队列管理文件附件
涵盖的核心模式：
  • 使用
    Table
    、
    column
    类型、索引和本地专属表定义schema
  • 使用默认或OP-SQLite适配器设置
    PowerSyncDatabase
  • React钩子：
    useQuery
    、
    useSuspenseQuery
    、
    useStatus
    、
    usePowerSync
  • 后端连接器：
    fetchCredentials()
    + 
    uploadData()
    实现
  • 通过
    execute()
    、
    get()
    、
    getAll()
    、
    getOptional()
    执行CRUD操作
  • 使用bucket定义（YAML）实现按用户过滤数据的同步规则
  • 冲突解决策略（最后写入获胜、字段级、自定义）
  • 使用
    AttachmentTable
    和
    AttachmentQueue
    处理附件
  • 集成OP-SQLite实现SQLCipher加密
不适用场景：
  • 无需同步的简单键值存储（使用键值存储工具）
  • 始终在线、从不离线的应用
  • 无需关系型查询的数据（使用键值存储工具）
  • 仅存储文件、无结构化元数据的场景（使用文件系统）
详细资源：
  • examples/core.md - Schema、数据库设置、CRUD、监听查询、钩子
  • examples/sync.md - 后端连接器、同步规则、冲突解决
  • examples/attachments.md - 附件队列、上传/下载、存储适配器
  • reference.md - API参考、设置清单
<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>
PowerSync 是一款基于 SQLite 的优先离线同步引擎。核心思想：应用直接读写本地SQLite数据库（无需网络调用），PowerSync在后台处理与云数据库的双向同步。
核心原则：
  1. 本地优先——所有读写操作均针对本地SQLite，应用可立即响应并离线运行
  2. 同步透明——PowerSync自动从服务器流式获取变更，并上传本地修改
  3. Schema驱动——客户端schema定义本地表，服务器同步规则定义每个客户端接收的数据
  4. 冲突自主解决——默认采用最后写入获胜规则，但
    uploadData()
    赋予你完全控制权
  5. 监听查询实现响应式——
    useQuery
    会在关联表变化时重新执行查询，保持UI同步
架构概览：
客户端（React Native）              云端
+-------------------+         +-------------------+
| 本地SQLite数据库   | <-同步->| PowerSync服务     |<--- 源数据库（Postgres等）
| (PowerSyncDatabase)|        | （同步规则）      |
+-------------------+         +-------------------+
| @powersync/react  |         | Bucket定义        |
| （useQuery等）     |         | （YAML配置）      |
+-------------------+         +-------------------+
数据流：
  • 写入： 应用调用本地SQLite的
    execute(INSERT/UPDATE/DELETE)
    。PowerSync将变更加入队列，并调用你的
    uploadData()
    推送到后端。
  • 读取： 服务器端的同步规则决定每个客户端接收的数据。PowerSync服务将变更流式传输到客户端的本地SQLite。
    useQuery
    监听表变化并重新渲染。
列类型： 仅支持三种类型——
column.text
、
column.integer
、
column.real
。
id
列（文本类型，主键）会自动创建。如果同步值与声明类型不匹配，会自动转换。
</philosophy>
<patterns>

Core Patterns

核心模式

Pattern 1: Schema Definition

模式1：Schema定义

Define your client-side schema using 
Table
and 
column
types. The schema mirrors your server tables (minus the 
id
column, which is auto-created).
typescript
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.
使用
Table
和
column
类型定义客户端schema。schema需与服务器表对应（无需包含
id
列，会自动创建）。
typescript
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 });

// 从schema推导类型
export type Database = (typeof AppSchema)["types"];
export type TodoRecord = Database["todos"];
export type ListRecord = Database["lists"];
优势： Schema是类型的唯一来源，索引优化查询性能，无需手动定义
id
列
注意事项： Schema中的表名必须与同步规则中的表名一致。不匹配会导致数据无法同步且无提示。
查看examples/core.md了解本地专属表和索引配置。

Pattern 2: PowerSyncDatabase Setup

模式2：PowerSyncDatabase设置

Create the database instance at app startup. Choose between the default SQLite adapter or OP-SQLite for encryption.
typescript
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.
在应用启动时创建数据库实例。可选择默认SQLite适配器或OP-SQLite实现加密。
typescript
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 },
});

// 初始化并连接（通常在应用启动时执行）
async function initDatabase(connector: PowerSyncBackendConnector) {
  await powersync.init();
  await powersync.connect(connector);
}
优势： 应用内共享单一实例，
init()
根据schema创建SQLite表，
connect()
启动双向同步
注意事项： 若未调用
connect()
，数据库仅能本地使用——无法同步。
查看examples/core.md了解带加密的OP-SQLite设置和React上下文提供者模式。

Pattern 3: React Hooks for Reactive Queries

模式3：React钩子实现响应式查询

Use 
useQuery
from 
@powersync/react
for watched queries that re-execute when dependent tables change. Wrap your app in 
PowerSyncContext.Provider
.
tsx
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.
使用
@powersync/react
中的
useQuery
实现监听查询，当关联表变化时自动重新执行。将应用包裹在
PowerSyncContext.Provider
中。
tsx
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}
    />
  );
}
优势： 
useQuery
会在
todos
表发生变更（插入、更新、删除）时自动重新执行，
isLoading
和
error
处理加载/错误状态
查看examples/core.md了解
useSuspenseQuery
、
useStatus
、
usePowerSync
和
runQueryOnce
的用法。

Pattern 4: CRUD Operations

模式4：CRUD操作

All writes use 
execute()
with parameterized SQL. PowerSync queues changes and calls your 
uploadData()
to sync.
typescript
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.
所有写入操作使用带参数化SQL的
execute()
。PowerSync会将变更加入队列，并调用
uploadData()
进行同步。
typescript
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 };
}
优势： 写入操作直接作用于本地SQLite（无需等待网络），
uuid()
在客户端生成ID，参数化查询防止SQL注入
注意事项： 
execute()
返回
{ rowsAffected, insertId }
。使用视图时，
rowsAffected
可能返回0——使用
RETURNING
子句确认修改。
查看examples/core.md了解
get()
、
getAll()
、
getOptional()
和事务模式。

Pattern 5: Backend Connector

模式5：后端连接器

The connector bridges PowerSync with your backend. Implement 
fetchCredentials()
for auth and 
uploadData()
for pushing local changes.
typescript
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.
连接器用于衔接PowerSync与你的后端。实现
fetchCredentials()
进行认证，
uploadData()
推送本地变更。
typescript
import type {
  PowerSyncBackendConnector,
  PowerSyncCredentials,
} from "@powersync/react-native";
import type { AbstractPowerSyncDatabase } from "@powersync/react-native";

export const connector: PowerSyncBackendConnector = {
  fetchCredentials: async (): Promise<PowerSyncCredentials> => {
    // 返回PowerSync实例URL和有效的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) {
      // 将每个操作发送到后端API
      await applyOperation(op);
    }
    await transaction.complete();
  },
};
优势： 清晰分离认证和数据上传，基于事务的处理确保原子性，
complete()
标记批次已同步
查看examples/sync.md了解完整的Supabase连接器、自定义后端模式和带重试的错误处理。

Pattern 6: Sync Rules (Bucket Definitions)

模式6：同步规则（Bucket定义）

Sync rules (YAML) define which server data each client receives. Configured on the PowerSync Service, not in client code.
yaml
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.
同步规则（YAML）定义每个客户端从服务器接收的数据。在PowerSync服务端配置，而非客户端代码中。
yaml
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:
    # 无参数 = 全局bucket，同步到所有客户端
    data:
      - SELECT * FROM settings
优势： 在服务器端实现按用户过滤数据，全局bucket用于共享数据，基于SQL的规则易于理解
注意事项： 默认每个客户端最多支持1000个bucket。表名必须与客户端schema一致。
查看examples/sync.md了解参数化bucket、客户端参数和多租户模式。

Pattern 7: Conflict Resolution

模式7：冲突解决

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.
默认行为是按字段最后写入获胜。可在
uploadData()
实现中自定义。
核心要点：PowerSync在
uploadData()
中赋予你完全控制权。你可以选择如何处理每个
CrudEntry
操作——接受、拒绝、合并或记录冲突。
常见策略：
  • 最后写入获胜（默认）： 直接插入/更新每个操作
  • 基于时间戳： 比较客户端与服务器时间戳，拒绝过期写入
  • 字段级合并： 仅应用较新的字段值，保留其他值
  • 服务器端验证： 执行业务规则（例如，禁止修改已发货订单）
  • 记录冲突： 存储两个版本供用户手动解决
查看examples/sync.md了解完整的冲突解决实现。

Pattern 8: Attachment Handling

模式8：附件处理

Use 
AttachmentTable
in your schema and 
AttachmentQueue
for offline-capable file upload/download.
typescript
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>
在schema中使用
AttachmentTable
，并通过
AttachmentQueue
实现支持离线的文件上传/下载。
typescript
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, // 关联附件ID
});

export const AppSchema = new Schema({
  users,
  attachments: new AttachmentTable(),
});
AttachmentQueue
管理生命周期：本地保存、队列上传、同步状态、其他设备自动下载、失败重试。
查看examples/attachments.md了解队列设置、上传/下载处理器和存储适配器模式。
</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
你要存储的数据类型是什么？
|
+-> 需要离线+云同步的关系型数据？
|   +-> 是 -> 使用PowerSync + SQLite（本技能）
|   +-> 否 -> 仅键值对？
|       +-> 是 -> 使用键值存储工具（非本技能）
|       +-> 否 -> 仅文件/媒体？
|           +-> 是 -> 使用文件系统
|
+-> 需要在React中实现响应式查询？
|   +-> 是 -> 使用@powersync/react中的useQuery
|   +-> 否 -> 直接使用powersync.getAll() / get()
|
+-> 需要设备端加密？
|   +-> 是 -> 使用带SQLCipher的@powersync/op-sqlite
|   +-> 否 -> 使用默认SQLite适配器
|
+-> 是否有文件附件？
|   +-> 是 -> 使用AttachmentTable + AttachmentQueue
|   +-> 否 -> 标准schema即可
|
+-> 如何解决冲突？
    +-> 简单应用 -> 最后写入获胜（默认）
    +-> 协作编辑 -> 字段级合并或CRDT
    +-> 业务关键 -> 服务器端验证 + 记录冲突

When to Use Each Query API

各查询API的适用场景

ScenarioAPI
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>
场景API
响应式组件数据
useQuery()
from 
@powersync/react
结合Suspense的响应式查询
useSuspenseQuery()
from 
@powersync/react
一次性获取（无响应式）
useQuery()
with 
runQueryOnce: true
服务/工具类读取
powersync.getAll()
/ 
get()
/ 
getOptional()
写入操作
powersync.execute()
连接状态
useStatus()
from 
@powersync/react
数据库实例访问
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>
高优先级问题：
  • 在schema中声明
    id
    列——PowerSync会自动创建
    text
    类型的主键
    id
    。手动声明会导致冲突。
  • 使用
    execute()
    执行读取（SELECT）而非
    getAll()
    / 
    useQuery()
    ——
    execute()
    返回的查询结果格式无法直接使用
  • 忘记调用
    powersync.connect(connector)
    ——数据库可本地使用但无法同步，开发中容易遗漏
  • 在React组件中使用
    getAll()
    并期望响应式——原生读取不会监听变化，需使用
    useQuery()
  • 在
    uploadData()
    中遗漏
    transaction.complete()
    ——未确认的事务会无限重试，导致重复上传
中优先级问题：
  • Schema表名与同步规则表名不匹配——数据无法同步且无提示
  • 未处理
    fetchCredentials()
    返回null的情况——当认证会话过期时会发生，必须重新认证
  • 在SQLite列中存储大二进制对象——使用
    AttachmentTable
    存储文件，SQLite仅存储元数据
  • 未在频繁查询的列上创建索引——大数据集时同步查询会变慢
  • 使用
    column.integer
    存储布尔值但未保持0/1的一致性——SQLite无原生布尔类型
注意事项与边缘情况：
  • uuid()
    是PowerSync的SQL函数，而非JavaScript函数——在SQL字符串中使用，不要在JS中使用
  • column.real
    存储IEEE 754双精度浮点数——注意货币的浮点精度问题（改用整数存储分）
  • 同步规则YAML使用
    request.user_id()
    从JWT获取认证用户ID——这不是自定义函数
  • getNextCrudTransaction()
    在上传队列为空时返回
    null
    ——迭代前务必检查
  • 使用视图时
    execute()
    可能返回
    rowsAffected: 0
    即使操作成功——使用
    RETURNING
    子句确认
  • PowerSync支持WebSocket（v1.11.0起默认）和HTTP流式同步——推荐使用WebSocket
  • 基于Rust的同步客户端自v1.29.0起默认启用——传递
    clientImplementation: SyncClientImplementation.JAVASCRIPT
    可使用旧版JS客户端
  • disconnectAndClear()
    会删除所有本地数据——使用
    disconnect()
    可停止同步并保留本地数据
  • 本地专属表（在Table选项中设置
    localOnly: true
    ）永远不会同步——适用于草稿数据或应用状态
</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>
所有代码必须遵循CLAUDE.md中的项目约定
（必须使用
new Table({ ... })
结合
column.text
、
column.integer
、
column.real
定义schema——绝不能声明
id
列，PowerSync会自动创建）
（必须在
init()
后调用
powersync.connect(connector)
以启动同步——否则数据库仅为本地模式，无法同步）
（必须在后端连接器中同时实现
fetchCredentials()
和
uploadData()
——缺少任意一个都会破坏同步循环）
（必须使用
@powersync/react
中的
useQuery
实现响应式查询——原生
getAll()
不会在数据变化时重新渲染）
不遵循这些规则会导致同步静默失败、数据丢失和非响应式UI。
</critical_reminders>