# PowerSync

> The same repositories over a PowerSync SQLite database on the device.

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

```ts title="src/lib/powersync/database.ts"
import { PowerSyncDatabase } from "@powersync/react-native";
import { powersyncExecutor } from "better-supabase/powersync";
import { betterSupabase } from "../supabase";
import { schema } from "./schema";

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

export const local = betterSupabase.connect(powersyncExecutor(powersync));

const customers = await local.customers
  .findMany({ where: { status: "active" }, orderBy: { name: "asc" } })
  .orThrow();
```

`powersyncExecutor(db)` compiles each repository call to SQLite and runs it on
the PowerSync database. Rows come back the way PostgREST returns them: in the
definition's casing, with booleans, JSON and Temporal values decoded the same
way. `@powersync/common` is not a dependency; the executor only needs
`getAll`, `execute` and `writeTransaction`, and `onChange` for `watch`, so
`@powersync/react-native`, `@powersync/web` and a test double all work.

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

Filters, search, sorting, offset and cursor pages, counts, aggregates on
one table, and inserts, updates, upserts and deletes all run.
Some things have no SQLite form:

| Feature                                       | On SQLite                                 |
| --------------------------------------------- | ----------------------------------------- |
| `include` (embedding) and related counts      | `unsupported` error                       |
| 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 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,
});
```

`checkSqlite` compiles every query the list runs (the page, its count and the
facet counts) 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]

Pass `fallback` to send what SQLite can't run to the server instead of
failing:

```ts title="src/lib/powersync/database.ts"
import { postgrestExecutor } from "better-supabase";

export const local = betterSupabase.connect(
  powersyncExecutor(powersync, {
    fallback: postgrestExecutor(supabase, betterSupabase.executorOptions()),
  }),
);
```

A read that compiles to `unsupported` (an `include`, full-text search, a
function source) runs on the fallback, and so does `db.$rpc`. Everything
else stays on SQLite. Writes never fall back, so PowerSync's upload queue
stays the only write path, and an unsupported write still returns
`unsupported`. When the device is offline, the read returns the fallback's
error, usually kind `network`.

A fallback read inside `watch` or `useWatch` reruns only when one of the
watched local tables changes. A change on the server that hasn't synced to
those tables yet doesn't show until the next local change or the next
mount.

## 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 and one-table aggregates all compile to SQLite. The
reads that don't are usually embeddings, 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 or an `include` into a local table you read and `watch`.
* Search a synced text column with `search: ["name", "kvk"]` (`ilike`)
  instead of `{ fts: ... }`.
* Keep it on the server with `fallback`, and accept that it needs a
  connection and only reruns on local changes.

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([]);
});
```

With a fallback, the errors that test reports are the reads that go to the
server; each has the feature in `details`.

## Live results [#live-results]

`watch(db, query, options)` reruns a repository call whenever PowerSync
reports a change to one of `tables`, for local writes and synced rows alike:

```ts
import { sqliteTables, watch } from "better-supabase/powersync";

const stop = watch(powersync, () => customerList.run(local, query), {
  tables: sqliteTables(betterSupabase, ["customers"]),
  onResult: (result) => render(result),
});
```

`sqliteTables` maps table keys to the names PowerSync uses (`customerTags`
to `customer_tags`). The function `watch` returns stops it, and so does
`signal`; a signal that is already aborted never starts it.

Rows that didn't change keep their object identity between runs (matched by
`id`, else by position), so `memo` list items skip re-rendering, and a run
that returns the same result calls no `onResult`. `structuralSharing: false`
turns that off.

### In React [#in-react]

`better-supabase/powersync/react` wraps `watch` in a hook and adds the sync
state:

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

function Customers({ search }: { search: string }) {
  const { data, error, loading } = useWatch({
    db: powersync,
    query: () =>
      customerList.run(local, { ...customerList.defaults, q: search }),
    tables,
    deps: [search],
  });
  const { hasSynced, uploading } = useSyncStatus(powersync);
  const { changes } = useConflicts(connector);
  // ...
}
```

`useWatch` restarts when `deps` or `tables` change and reports `loading`
until the first result for them; `enabled: false` pauses it.
`useSyncStatus` returns `connected`, `connecting`, `hasSynced`, `uploading`,
`downloading` and the last sync `error`, and re-renders only when one of them
changes. `useConflicts` is described in
[Offline-first](/docs/guides/offline-first#conflicts).

A read with a count (`paginate` 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 `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. See
[Offline-first](/docs/guides/offline-first) for replaying them through the
repositories on the server side.