# PowerSync

> The data hooks and the repositories on a PowerSync SQLite database on the device.

Source: https://bettersupabase.com/docs/frontend/powersync

```ts title="src/lib/supabase/client.native.ts"
import { PowerSyncDatabase } from "@powersync/react-native";
import { createNativeClient } from "better-supabase/client/native";
import { powersyncEngine } from "better-supabase/powersync";
import { connector } from "../powersync/connector";
import { betterSupabase } from "./index";
import { schema } from "../powersync/schema";

export const powersync = new PowerSyncDatabase({
  schema,
  database: { dbFilename: "app.db" },
});

export const bsb = createNativeClient(betterSupabase, supabase, {
  engine: powersyncEngine(powersync, { connector, local: ["customers"] }),
});
```

`powersyncEngine(db)` runs `bsb.db` and the generated
[data hooks](/docs/frontend/data-hooks) on the PowerSync database: each
repository call compiles to SQLite and runs on the device, online or not.
Rows come back the way PostgREST returns them: in the definition's casing,
with booleans, JSON and Temporal values decoded the same way. `local` lists
the tables PowerSync syncs; `bsb.db` reads and writes every other table
through PostgREST. Leave it out when every table syncs.

```tsx title="src/components/customer-list.native.tsx"
import { useCustomers } from "@/lib/supabase/hooks";

export function CustomerList() {
  const customers = useCustomers({
    where: { status: "active" },
    orderBy: { name: "asc" },
  });
  // ...
}
```

`@powersync/common` is not a dependency; the engine only needs `getAll`,
`execute` and `writeTransaction`, `onChange` for live reads and
`registerListener` with `currentStatus` for the sync state, so
`@powersync/react-native`, `@powersync/web` and a test double all work. To
run the repositories without the client, use
`betterSupabase.connect(powersyncExecutor(powersync))`.

## What runs on SQLite [#what-runs-on-sqlite]

Filters, search, sorting, offset and cursor pages, counts, aggregates,
includes (nested ones too, with their own `where`, `orderBy`, `limit` and
`required`), related counts and related aggregates, and inserts, updates,
upserts and deletes all run. A read with includes stays one SQL statement:
each include is a correlated subquery that returns JSON, and the executor
decodes its rows the same way as the top-level ones. Some things have no
SQLite form:

| Feature                                       | On SQLite                                 |
| --------------------------------------------- | ----------------------------------------- |
| full-text search (`fts`)                      | `unsupported` error                       |
| `db.$rpc` and function sources (`db.$search`) | `unsupported` error                       |
| writes to a table without a primary key       | `unsupported` error                       |
| `ilike`                                       | SQLite `like`: case-insensitive for ASCII |
| `like`                                        | `glob`: case-sensitive, like Postgres     |
| timestamps                                    | compared as UTC ISO text                  |

The error has kind `unsupported` (HTTP 501), the feature in `details`, and
nothing runs. To catch it before a screen does, check a list definition, or
a single read from `betterSupabase.spec`, in a test:

```ts title="src/lists.test.ts"
import { checkSqlite } from "better-supabase/powersync";

expect(await checkSqlite(betterSupabase, customerList)).toEqual({
  ok: true,
  data: undefined,
});

const detail = betterSupabase.spec.customers.list({
  include: {
    notes: { orderBy: { createdAt: "desc" }, limit: 5 },
    _count: { notes: true },
  },
});
expect(await checkSqlite(betterSupabase, detail)).toMatchObject({ ok: true });
```

`checkSqlite` compiles every query the list runs (the page, its count and the
facet counts), or the one read a spec describes, without a database. It
returns the first error; pass
`{ all: true }` to get every one as an array, empty when the whole list runs
on SQLite.

## Server fallback [#server-fallback]

What SQLite can't run goes to the server: a read that compiles to
`unsupported` (full-text search, a function source) runs on the client's
PostgREST executor, and so does `db.$rpc`. Everything else stays on SQLite.
Writes never fall back, so PowerSync's upload queue stays the only write path
for a synced table, and an unsupported write still returns `unsupported`.
When the device is offline, the read returns the server's error, usually kind
`network`. Pass `fallback: false` to keep every call on the device, or
another executor as `fallback`.

A hook that reads from the server is read again when one of its local tables
changes, and every hook is read again when PowerSync connects after it was
offline, so a fallback read catches up once the device is back.

## Porting a list from PostgREST [#porting-a-list-from-postgrest]

Most lists run locally as they are: filters, `ilike` search, facets, facet
counts, sorting, pages, aggregates, includes and related counts all compile
to SQLite, as long as the included tables sync too. The reads that don't are
usually full-text search and database functions that aggregate. For each, pick one:

- Sync what the screen needs. A summary table or view that a sync rule
  publishes (note counts per customer, a denormalized name) turns an RPC
  aggregate into a local table the hooks read.
- Search a synced text column with `search: ["name", "kvk"]` (`ilike`)
  instead of `{ fts: ... }`.
- Keep it on the server through the fallback, and accept that it needs a
  connection. It reads again when PowerSync connects.

To find the server-only reads, list them in a test:

```ts title="src/lists.test.ts"
import { checkSqlite } from "better-supabase/powersync";

it("runs every list on the device", async () => {
  for (const list of [customerList, noteList])
    expect(await checkSqlite(betterSupabase, list, { all: true })).toEqual([]);
});
```

The errors that test reports are the reads that go to the server; each has
the feature in `details`.

## Live results [#live-results]

The hooks read once and then follow their table: PowerSync reports every
local write and every synced row, at most once every 30 ms (`throttleMs`),
and each hook that reads a changed table reads again. A hook named after
something other than a table, such as a list definition wrapped in
`defineDataHooks("customer-pages", ...)`, names its tables in
`tables: { "customer-pages": ["customers"] }`. Writes through a hook apply
to the cached lists first, run on the device and roll back on an error.

### Sync state [#sync-state]

`useSyncStatus()` and `useConflicts()` from `better-supabase/react` read the
provider's engine:

```tsx
import { useConflicts, useSyncStatus } from "better-supabase/react";

function SyncBanner() {
  const sync = useSyncStatus();
  const { changes, dismiss } = useConflicts();
  // ...
}
```

`useSyncStatus` returns `connected`, `connecting`, `hasSynced`, `uploading`,
`downloading` and the last sync `error`, re-renders only when one of them
changes, and returns `undefined` when the engine syncs nothing.
`useConflicts` reads the changes the engine's `connector` could not upload;
see [Offline-first](/docs/guides/offline-first#conflicts).

A read with a count (`page` with `count: "exact"`, a list query's total)
runs the rows and the count in one `readTransaction`, so the two agree while
PowerSync syncs.

## Table names and keys [#table-names-and-keys]

PowerSync names each view after the table without its schema, and that is
the default. Pass `tableName` to `powersyncEngine` (or `powersyncExecutor`)
when your PowerSync schema renames a table. PowerSync tables key on a text
`id` that the client creates: an insert without one gets
`crypto.randomUUID()` (or `newId` when you pass it).

Writes go to the local database and PowerSync uploads them.
`createUploadConnector` replays them through repositories on PostgREST (a
client without the engine), and
`uploadRoute(server.db.attachments, { put: "create" })` inserts the rows of a
write-once table, counting a conflict as already applied. `onError` reports a
failed session read and retried uploads. See
[Offline-first](/docs/guides/offline-first) for the routes, outcomes and
conflicts.

## Uploaded columns [#uploaded-columns]

A generated column is in the row you sync down, but Postgres rejects an
insert or update that sets it. `WritableColumnOf` lists the columns of a table
that accept writes, so a column list for the upload fails to compile when it
names a generated one:

```ts title="src/lib/powersync/connector.ts"
import type {
  GeneratedColumnOf,
  WritableColumnOf,
} from "../supabase/generated";

// With `casing: "snake"` the names are the database names PowerSync uses.
const uploadable = [
  "organization_id",
  "user_id",
  "role",
] as const satisfies readonly WritableColumnOf<"memberships">[];

// Dropped from the upload payload before it reaches PostgREST.
type Skipped = GeneratedColumnOf<"memberships">; // "tier_role"
```

Adding `"tier_role"` to `uploadable` is a type error.