/mobile-storage-sqlite-powersync

name: mobile-storage-sqlite-powersync
description: PowerSync offline-first sync engine on SQLite for React Native - schema definition, watched queries, CRUD operations, backend connectors, sync rules, conflict resolution, attachments

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

**(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](examples/core.md) - Schema, database setup, CRUD, watched queries, hooks
• [examples/sync.md](examples/sync.md) - Backend connector, sync rules, conflict resolution
• [examples/attachments.md](examples/attachments.md) - Attachment queue, upload/download, storage adapters
• [reference.md](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: colum