Sync tables offline

Keep a copy of your tables on the device: reads are instant, writes work offline and sync later, with your row level security and a conflict rule per table.

Offline sync keeps a local copy of chosen tables in your app, in IndexedDB in the browser or in a store you choose on mobile. Reads come from that copy, so screens open at once and keep working without a connection. Writes go to the copy straight away and wait in a queue; when the device is online the SDK sends them, receives everything that changed on the server, and tells you about any conflict. Each user receives only the rows your row level security policies let them read, and every write is checked against them like a REST API call.

#Turn it on for a table

In Database › Offline sync (project owners and admins) choose a table and what happens when a row changed in two places. The table needs a primary key. Existing rows become available to devices at once; after that, a trigger records every insert, update and delete in the same transaction.

#Use it in your app

const db = stitchdb.offline({ tables: ['todos'] })
await db.ready                                    // loads the local copy, then syncs in the background

const todos = db.table('todos')
await todos.upsert({ id: crypto.randomUUID(), title: 'Buy milk', done: false })
await todos.delete(id)

todos.all()                                       // every local row
todos.get(id)                                     // one row by primary key
todos.where((t) => !t.done)                       // local filter
const stop = todos.subscribe((rows) => render(rows))

db.on('status', (s) => showBadge(s.online, s.pending))     // { online, syncing, pending, lastSyncAt, error }
db.on('conflict', (c) => console.log(c.table, c.status, 'server kept', c.server))
await db.sync()                                   // sync now (also runs every 30 s and on reconnect)

The Flutter, Swift and Kotlin SDKs have the same client as client.offline(...), with streams or flows for the events. The queue and the local rows survive an app restart.

#When a row changed in two places

A conflict is a device change to a row that someone else changed on the server after the device last received it. The rule is set per table:

create function public.resolve_todos(t text, server_row jsonb, client_row jsonb) returns jsonb
language sql as $$
  -- keep the device title, but once done on any device a todo stays done
  select client_row || jsonb_build_object('done', (server_row->>'done')::bool or (client_row->>'done')::bool)
$$;

When the server keeps its row, or refuses a write (a policy check fails, a constraint is broken), the SDK replaces the local row with the server's and emits conflict with status conflict or rejected, the local and server rows, and the error message. Sending the same change twice is safe: each change has an id and is applied once.

#Over HTTP

Other clients can use the same endpoints at https://<ref>.p.stitchdb.com/sync/v1, with the project API key and the user's access token as for the REST API:

#Good to know

• Deleted rows are remembered for 30 days. A device that has been offline longer downloads its tables again; its queued changes are still sent first.
• When a row is deleted or a user loses access to it, devices receive its primary key so they can drop their copy, nothing else.
• The change log lives in your database (schema _stitch_sync), about 100 bytes per row, and counts toward its size.
• With row level security off, every signed-in user receives every row of the table. Studio warns about it.
• Stopping sync for a table removes its trigger and change log; your table is not changed.