Mobile Local-First: Offline Sync with PowerSync

Your users are on the subway, in a basement meeting room, or driving through a dead zone — and your app spins a spinner until the network returns. Every user has felt it, and every mobile developer has shipped the workaround: an offline cache, a sync flag, a “last synced 3 hours ago” label. The local-first movement exists precisely because this doesn’t have to be the default. In a local-first app, the device’s local database is the primary database — reads and writes are instant and offline by design, and sync with the server is a background process, not a UX gate.

In this tutorial, you will learn how to build a local-first mobile app with PowerSync: embed a SQLite database on the client, sync it from Postgres through PowerSync’s sync engine, handle offline writes with an upload queue, resolve conflicts, and stream real-time changes into a reactive Flutter UI.

Key technologies: PowerSync (Dart/Flutter SDK), embedded SQLite, Postgres, Sync Rules (buckets), CRUD + watched queries, conflict resolution.

Prerequisites

• Flutter SDK installed and a basic Flutter project created
• A Postgres database (local, hosted, or Supabase) — PowerSync connects to Postgres, MongoDB, MySQL, or SQL Server
• A PowerSync instance (Cloud free tier or self-hosted) connected to your Postgres source database
• Working knowledge of Dart, SQL, and Flutter’s StreamBuilder

Why Local-First? The Offline Problem

The classic architecture treats the network as a dependency of every interaction: the UI calls an API, waits, and renders. That works when connectivity is reliable, but mobile connectivity isn’t. Local-first inverts the dependency:

1. Reads are local. Data lives in an embedded SQLite database on the device, so rendering is instant with zero network latency.
2. Writes are local. A mutation is applied to the local database immediately and placed in an upload queue; the UI never blocks on the server.
3. Sync is continuous and backgrounded. The client streams changes down from the server and uploads queued writes, whether the device is online or offline.

The result is a UI that behaves identically online and offline — and, because the server eventually converges with the device, real-time collaboration for free.

PowerSync Architecture

PowerSync is a sync engine purpose-built for local-first apps. The architecture has three moving parts:

• Embedded SQLite client. The PowerSync Client SDK manages a local SQLite database your app reads from and writes to directly, online or offline. The SDK also exposes a client-defined schema applied to synced data via SQLite views — this is why schema changes usually need no client-side migration.
• PowerSync Service (the sync engine). It connects to your source database (Postgres here), replicates data per your Sync Rules, streams changes to clients in real time, and receives the upload queue from clients.
• Backend connector. Your app backend implements the interface between the PowerSync Service and your source database: it issues auth credentials and applies client writes to Postgres.

On the client, all database operations run asynchronously in the background so the UI thread stays responsive, and the SDK supports query subscriptions that push updates to the UI as data changes.

Setting Up the Client

Install the SDK. In 2026 the single powersync package covers Flutter, web, and standalone Dart:

dart pub add powersync

The SDK also needs path_provider to locate a writable database directory:

dart pub add path_provider

1. Define the client-side schema

The schema describes the managed SQLite tables your app reads and writes. It’s derived from your backend schema, but you declare it locally. PowerSync automatically creates the id column (type text) for every table — you don’t declare a primary key:

// lib/models/schema.dart
import 'package:powersync/powersync.dart';

const schema = Schema([
  Table('todos', [
    Column.text('list_id'),
    Column.text('created_at'),
    Column.text('completed_at'),
    Column.text('description'),
    Column.integer('completed'),
    Column.text('created_by'),
    Column.text('completed_by'),
  ], indexes: [
    Index('list', [IndexedColumn('list_id')]),
  ]),
  Table('lists', [
    Column.text('created_at'),
    Column.text('name'),
    Column.text('owner_id'),
  ]),
]);

Available column types are text, integer, and real; PowerSync casts backend values to match automatically.

2. Instantiate the database

Create the PowerSyncDatabase, inject the schema plus a file path, and call initialize(). Only instantiate one instance per file — it’s typically a global (or provider-managed) object:

// lib/powersync/powersync.dart
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:powersync/powersync.dart';
import '../models/schema.dart';

late PowerSyncDatabase db;

Future<void> openDatabase() async {
  final dir = await getApplicationSupportDirectory();
  final path = join(dir.path, 'powersync-dart.db');

  db = PowerSyncDatabase(schema: schema, path: path);
  await db.initialize();
}

3. Connect to the backend

connect() starts syncing. It takes a backend connector implementing fetchCredentials() (returns a PowerSyncCredentials with your instance endpoint and a JWT token) and uploadData() (sends queued client writes to your backend):

// lib/powersync/my_backend_connector.dart
import 'package:powersync/powersync.dart';

class MyBackendConnector extends PowerSyncBackendConnector {
  final PowerSyncDatabase database;

  MyBackendConnector(this.database);

  @override
  Future<PowerSyncCredentials?> fetchCredentials() async {
    // Obtain a JWT from your auth service; the SDK caches it and
    // re-calls this only when the token is near expiry.
    return PowerSyncCredentials(
      endpoint: 'https://xxxxxx.powersync.journeyapps.com',
      token: 'your-authentication-token',
    );
  }

  @override
  Future<void> uploadData(PowerSyncDatabase database) async {
    // Called whenever there are queued local writes, online or offline.
    // If it throws, it is retried periodically.
    final transaction = await database.getNextCrudTransaction();
    if (transaction == null) return;

    for (final op in transaction.crud) {
      switch (op.op) {
        case UpdateType.put:
          // Tell your backend API to CREATE a record
          break;
        case UpdateType.patch:
          // Tell your backend API to UPDATE a record
          break;
        case UpdateType.delete:
          // Tell your backend API to DELETE a record
          break;
      }
    }

    // Completes the transaction and moves to the next one.
    await transaction.complete();
  }
}

Wire it up in main.dart:

Future<void> main() async {
  WidgetsFlutterBinding.ensureInitialized();
  await openDatabase();
  runApp(const DemoApp());
}

class _DemoAppState extends State<DemoApp> {
  @override
  void initState() {
    super.initState();
    db.connect(connector: MyBackendConnector(db));
  }
  // ...
}

Any writes you make to the local SQLite database are placed into the upload queue automatically and uploaded when connected.

Selective Sync with Sync Rules

Syncing an entire production database to every phone is wasteful and a privacy risk. PowerSync’s Sync Rules control which subset of data each client receives, organized into buckets. Each bucket definition has a Parameter Query (selects the parameters for this client) and Data Queries (selects the data for the bucket, filtered by those parameters).

# sync rules (legacy YAML) — or use Sync Streams, the recommended successor
bucket_definitions:
  user_lists:
    # Parameter Query: derive a parameter from the JWT
    parameters: SELECT request.user_id() as user_id
    # Data Query: filter data using the parameter
    data:
      - SELECT * FROM lists WHERE owner_id = bucket.user_id

Authentication parameters come from claims in the user’s JWT (always including sub, the user ID). PowerSync precomputes buckets for every distinct parameter value in the source database — e.g. user_lists["1"], user_lists["2"], and so on — and each client syncs only the buckets its parameters select. You can also pull parameters from tables (e.g. a user’s primary_list_id) or accept client parameters passed at connect() time, though client parameters should never be used for access control because clients control them.

For new projects PowerSync recommends Sync Streams, which support the same partial sync plus JOINs, on-demand syncing, and a simpler developer experience. You can migrate legacy Sync Rules in the dashboard or with powersync migrate sync-rules.

CRUD on the Local Database

Once configured, your app talks to SQLite directly. The four core methods:

• db.get(sql, args) — fetch a single row (throws if absent; use getOptional for a nullable result)
• db.getAll(sql, args) — fetch many rows
• db.execute(sql, args) — run an INSERT/UPDATE/DELETE
• db.watch(sql, args) — run a read query every time its source tables change, returning a Stream

// lib/widgets/todos_widget.dart
Future<void> addTodo(String listId, String description) async {
  await db.execute(
    'INSERT INTO todos (id, list_id, description, created_at, completed) '
    'VALUES (uuid(), ?, ?, datetime(), 0)',
    [listId, description],
  );
}

Future<void> toggleTodo(String id, int completed) async {
  await db.execute(
    'UPDATE todos SET completed = ?, completed_at = CASE ? WHEN 1 THEN datetime() END WHERE id = ?',
    [completed, completed, id],
  );
}

Note uuid() and datetime() — PowerSync’s SQLite build includes these helpers so every write carries a UUID id and a timestamp without you generating them in Dart. Because the client schema is applied via views, all tables automatically expose their id column.

Watching Queries for a Reactive UI

watch is the killer feature: subscribe to a SQL query and get a new result set whenever any table it touches changes — whether the change came from the user, a background sync, or another user’s device streaming in. Combine it with Flutter’s StreamBuilder for a fully reactive list:

// lib/widgets/lists_widget.dart
import 'package:flutter/material.dart';
import '../powersync/powersync.dart';

class TodosWidget extends StatelessWidget {
  const TodosWidget({super.key, required this.listId});

  final String listId;

  @override
  Widget build(BuildContext context) {
    return StreamBuilder(
      stream: db.watch(
        'SELECT * FROM todos WHERE list_id = ? ORDER BY created_at DESC',
        [listId],
      ),
      builder: (context, snapshot) {
        if (!snapshot.hasData) {
          return const Center(child: CircularProgressIndicator());
        }
        final rows = snapshot.data!;
        if (rows.isEmpty) {
          return const Center(child: Text('No todos yet'));
        }
        return ListView.builder(
          itemCount: rows.length,
          itemBuilder: (context, index) {
            final row = rows[index];
            return CheckboxListTile(
              value: row['completed'] == 1,
              title: Text(row['description'] as String),
              onChanged: (_) => toggleTodo(row['id'] as String,
                  row['completed'] == 1 ? 0 : 1),
            );
          },
        );
      },
    );
  }
}

The list updates automatically when a todo is toggled locally, when a sync pulls in a change from Postgres, and when another user edits the same list in real time. No polling, no manual reload.

Conflict Resolution

With multiple clients editing the same rows offline, conflicts are inevitable. PowerSync’s model: the upload queue stores three operation types — PUT (create, full row), PATCH (update, changed columns only), and DELETE (by ID). Your backend applies these to Postgres. The recommended default policy:

1. Deletes always win. If one client deletes a row, later updates to it are ignored (it may be recreated with the same ID).
2. Last write wins per field. For concurrent updates, the last update received by the server for each individual field wins.

Two implementation details matter. First, operations must be idempotent — the backend may receive the same operation more than once, so ignore DELETEs on already-deleted rows and deduplicate with the per-client incrementing operation ID that ships with each op. Second, if “last write wins” isn’t right for your domain, you can implement custom conflict resolution in the backend — for example, rejecting updates to an order once it’s marked completed, or merging updates with CRDT data structures like Yjs stored and synced through PowerSync.

Putting It All Together

A minimal complete local-first app: initialize the database, connect to the PowerSync instance, sync only the current user’s lists, and render todos reactively from local SQLite with instant offline writes.

// lib/main.dart — complete example
import 'package:flutter/material.dart';
import 'package:powersync/powersync.dart';
import 'powersync/powersync.dart';
import 'widgets/todos_widget.dart';

Future<void> main() async {
  WidgetsFlutterBinding.ensureInitialized();
  await openDatabase();
  runApp(const DemoApp());
}

class DemoApp extends StatelessWidget {
  const DemoApp({super.key});

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      home: const HomeScreen(),
    );
  }
}

class HomeScreen extends StatefulWidget {
  const HomeScreen({super.key});

  @override
  State<HomeScreen> createState() => _HomeScreenState();
}

class _HomeScreenState extends State<HomeScreen> {
  @override
  void initState() {
    super.initState();
    // Start background sync: streams data down and uploads local writes.
    db.connect(connector: MyBackendConnector(db));
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: const Text('My Lists')),
      body: StreamBuilder(
        stream: db.watch('SELECT * FROM lists ORDER BY created_at DESC'),
        builder: (context, snapshot) {
          if (!snapshot.hasData) {
            return const Center(child: CircularProgressIndicator());
          }
          final lists = snapshot.data!;
          if (lists.isEmpty) {
            return const Center(child: Text('No lists — add one below'));
          }
          return ListView.builder(
            itemCount: lists.length,
            itemBuilder: (context, index) {
              final list = lists[index];
              return ListTile(
                title: Text(list['name'] as String),
                onTap: () {
                  Navigator.of(context).push(MaterialPageRoute(
                    builder: (_) =>
                        Scaffold(appBar: AppBar(title: Text(list['name'] as String)),
                            body: TodosWidget(listId: list['id'] as String)),
                  ));
                },
              );
            },
          );
        },
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: () async {
          await db.execute(
            'INSERT INTO lists (id, name, owner_id, created_at) '
            'VALUES (uuid(), ?, ?, datetime())',
            ['New list', 'user-123'],
          );
        },
        tooltip: 'Add list',
        child: const Icon(Icons.add),
      ),
    );
  }
}

Expected output: Launch the app and the lists screen appears instantly from local SQLite. Create a list — the INSERT applies immediately to the local database, the watch stream fires, and the new row renders with zero network wait. In the background the SDK streams any server-side changes down and uploads the queued INSERT via your connector. Toggle a device into airplane mode and the app still works: reads and writes go to embedded SQLite, and the queued writes sync once connectivity returns. Open the same list on a second device and edits stream in live.

Conclusion & Next Steps

Local-first with PowerSync changes the UX contract of a mobile app: the database is local, offline is the default state, and sync is a background detail. You now know the core loop — embed SQLite, define the client schema, connect a backend connector, scope sync with buckets, do CRUD against the local DB, render with watch, and handle conflicts at the backend with idempotent operations.

Next steps: wire real authentication into fetchCredentials() (PowerSync supports Supabase Auth and custom JWT issuers), adopt Sync Streams over legacy Sync Rules, add an ORM layer (PowerSync integrates with drift), and explore encrypted local databases with SQLCipher for sensitive data.

References / Sources

• PowerSync Documentation
• Dart/Flutter SDK — PowerSync Docs
• Sync Rules (Legacy) — PowerSync Docs
• Handling Update Conflicts — PowerSync Docs
• powersync package — pub.dev
• PowerSync Dart SDK — GitHub