Serverpod + PowerSync

Add offline-capable data sync to Serverpod projects using PowerSync.

Used in conjunction with Serverpod, PowerSync enables developers to build local-first apps that are robust in poor network conditions and that have highly responsive frontends while relying on Serverpod for shared models in a full-stack Dart project. This guide walks you through configuring PowerSync within your Serverpod project.

Overview

PowerSync works by:

1. Automatically streaming changes from your Postgres backend source database into a SQLite database on the client.
2. Collecting local writes that users have performed on the SQLite database, and allowing you to upload those writes to your backend.

See Architecture Overview for a full overview.

To integrate PowerSync into a Serverpod project, a few aspects need to be considered:

1. Database setup

   Your Serverpod models need to be persisted into a Postgres database.

2. PowerSync configuration

   PowerSync needs access to your Postgres database to stream changes to users.

3. Authentication

   To ensure each user only has access to the data they’re supposed to see, Serverpod authenticates users against PowerSync.

4. Data sync

   After configuring your clients, your Serverpod projects are offline-ready!

This guide shows all steps in detail. Here, we assume you’re working with a fresh Serverpod project. You can follow along by creating a notes project using the Serverpod CLI:

# If you haven't already, dart pub global activate serverpod_cli

serverpod create notes

Of course, all steps and migrations also apply to established projects.

Database Setup

Begin by configuring your Postgres database for PowerSync. PowerSync requires logical replication to be enabled. With the docker-compose.yaml file generated by Serverpod, add a command to the postgres service to enable this option. This is also a good opportunity to add a health check, which helps PowerSync connect at the right time later:

services:
  # Development services
  postgres:
    image: pgvector/pgvector:pg16
    ports:
      - "8090:5432"
    command: ["postgres", "-c", "wal_level=logical"] # Added for PowerSync
    environment:
      POSTGRES_USER: postgres
      POSTGRES_DB: notes
      # ...
    healthcheck: # Added for PowerSync
      test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"]
      interval: 5s
      timeout: 5s
      retries: 5
    volumes:
      - notes_data:/var/lib/postgresql/data

You can also find sources for the completed demo in this repository. More information about setting up Postgres for PowerSync is available here.

Next, configure existing models to be persisted in the database. In the template created by Serverpod, edit notes_server/lib/src/greeting.spy.yaml:

### A greeting message which can be sent to or from the server.
class: Greeting

table: greeting # Added table key

fields:
  ### Important! Each model used with PowerSync needs to have a UUID id column.
  id: UuidValue,defaultModel=random,defaultPersist=random
  ### The user id owning this greeting, used for access control in PowerSync
  owner: String

  ### The greeting message.
  message: String
  ### The author of the greeting message.
  author: String
  ### The time when the message was created.
  timestamp: DateTime

PowerSync works best when ids are stable. And since clients can also create rows locally, using randomized ids reduces the chance of collisions. This is why we prefer UUIDs over the default incrementing key.

After making the changes, run serverpod generate and ignore the issues in greeting_endpoint.dart for now. Instead, run serverpod create-migration and note the generated path:

$ serverpod create-migration

✓ Creating migration (87ms)
 • Migration created: migrations/<migration id>
✅ Done.

We will use the migration adding the greeting table to also configure a replication that PowerSync will hook into. For that, edit notes_server/migrations/<migration id>/migration.sql At the end of that file, after COMMIT;, add this:

-- Create a role/user with replication privileges for PowerSync
CREATE ROLE powersync_role WITH REPLICATION BYPASSRLS LOGIN PASSWORD 'myhighlyrandompassword';
-- Set up permissions for the newly created role
-- Read-only (SELECT) access is required
GRANT SELECT ON ALL TABLES IN SCHEMA public TO powersync_role;  

-- Optionally, grant SELECT on all future tables (to cater for schema additions)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO powersync_role; 

To restrict read access to specific tables, explicitly list allowed tables for both the SELECT privilege, and for the publication mentioned in the next step (as well as for any other publications that may exist).

This is also a good place to set up a Postgres publication that a PowerSync Service will subscribe to:

-- Create a publication to replicate tables. The publication must be named "powersync"
CREATE PUBLICATION powersync FOR ALL TABLES;

Note that the PowerSync Service has to read all updates present in the publication, regardless of whether the table is referenced in your Sync Streams / Sync Rules definitions. This can cause large spikes in memory usage or introduce replication delays, so if you’re dealing with large data volumes, you’ll want to specify a comma-separated subset of tables to replicate instead of FOR ALL TABLES.

The snippet above replicates all tables and is the simplest way to get started in a dev environment.

After adding these statements to migration.sql, also add them to definition.sql. The reason is that Serverpod runs that file when instantiating the database from scratch, migration.sql would be ignored in that case.

PowerSync Configuration

PowerSync requires a service to process Postgres writes into a form that can be synced to clients. Additionally, your Serverpod backend will be responsible for generating JWTs to authenticate clients as they connect to this service.

To set that up, begin by generating an RSA key to sign these JWTs. In the server project, run dart pub add jose to add a package supporting JWTs in Dart. Then, create a tool/generate_keys.dart that prints a new key pair when run:

Show tool/generate_keys.dart

import 'dart:convert';
import 'dart:math';

import 'package:jose/jose.dart';

void main() {
  var generatedKey = JsonWebKey.generate('RS256').toJson();
  final kid = 'powersync-${generateRandomString(8)}';
  generatedKey = {...generatedKey, 'kid': kid};

  print('''
      JS_JWK_N: ${generatedKey['n']}
      PS_JWK_E: ${generatedKey['e']}
      PS_JWK_KID: $kid
''');

  final encodedKeys = base64Encode(utf8.encode(json.encode(generatedKey)));
  print('JWT signing keys for backend: $encodedKeys');
}

String generateRandomString(int length) {
  final random = Random.secure();
  final buffer = StringBuffer();

  for (var i = 0; i < length; i++) {
    const alphabet = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

    buffer.writeCharCode(alphabet.codeUnitAt(random.nextInt(alphabet.length)));
  }

  return buffer.toString();
}

Run dart run tool/generate_jwt.dart and save its output, it’s needed for the next step as well.

For development, you can add the PowerSync Service to the compose file. It needs access to the source database, a Postgres database to store intermediate data, and the public half of the generated signing key.

services:
  powersync:
    restart: unless-stopped
    image: journeyapps/powersync-service:latest
    depends_on:
      postgres:
        condition: service_healthy
    command: ["start", "-r", "unified"]
    volumes:
      - ./service.yaml:/config/service.yaml
    environment:
      POWERSYNC_CONFIG_PATH: /config/service.yaml
      # Use the credentials created in the previous step, the /notes is the datase name for Postgres
      PS_SOURCE_URI: "postgresql://powersync_role:myhighlyrandompassword@postgres:5432/notes"
      PS_STORAGE_URI: "postgresql://powersync_role:myhighlyrandompassword@postgres:5432/powersync_storage"
      JS_JWK_N: # output from generate_keys.dart
      PS_JWK_E: AQAB # output from generate_keys.dart
      PS_JWK_KID: # output from generate_keys.dart
    ports:
      - 8095:8080

To configure PowerSync, create a file called service.yaml next to the compose file. This file configures how PowerSync connects to the source database, how to authenticate users, and which data to sync:

replication:
  connections:
    - type: postgresql
      uri: !env PS_SOURCE_URI

      # SSL settings
      sslmode: disable # 'verify-full' (default) or 'verify-ca' or 'disable'

# Connection settings for bucket storage
storage:
  type: postgresql
  uri: !env PS_STORAGE_URI
  sslmode: disable # 'verify-full' (default) or 'verify-ca' or 'disable'

# The port which the PowerSync API server will listen on
port: 8080

sync_config:
  content: |
    config:
      edition: 3
    streams:
      todos:
        # For each user, sync all greeting they own.
        auto_subscribe: true # Sync by default
        query: SELECT * FROM greeting WHERE owner = auth.user_id()

client_auth:
  audience: [powersync]
  jwks:
    keys:
      - kty: RSA
        n: !env PS_JWK_N
        e: !env PS_JWK_E
        alg: RS256
        kid: !env PS_JWK_KID

More information on available options is available under Service Configuration

Authentication

PowerSync processes the entire source database into buckets, an efficient representation for sync. With the configuration shown here, there is one such bucket per user storing all greetings owned by that user. For security, it is crucial each user only has access to their own bucket. This is why PowerSync gives you full access control:

1. When a client connects to PowerSync, it fetches an authentication token from your Serverpod instance.
2. Your Dart backend logic returns a JWT describing what data the user should have access to.
3. In the sync_rules section, you reference properties of the created JWTs to control data visible to the connecting clients.

In this guide, we will use a single virtual user for everything. For real projects, follow Serverpod documentation on authentication.

PowerSync needs two endpoints, one to request a JWT and one to upload local writes from clients to the backend source database. In notes_server/lib/src/powersync_endpoint.dart, create those endpoints:

import 'dart:convert';
import 'dart:isolate';

import 'generated/protocol.dart';
import 'package:serverpod/serverpod.dart';
import 'package:jose/jose.dart';

class PowerSyncEndpoint extends Endpoint {
  Future<String> createJwt(Session session) async {
    // TODO: Throw if the session is unauthenticated.

    // TODO: Extract user-id from session outside
    final userId = 'global_user';
    final token = await Isolate.run(() => _createPowerSyncToken(userId));

    // Also create default greeting if none exist for this user.
    if (await Greeting.db.count(session) == 0) {
      await Greeting.db.insertRow(
        session,
        Greeting(
          owner: userId,
          message: 'Hello from Serverpod and PowerSync',
          author: 'admin',
          timestamp: DateTime.now(),
        ),
      );
    }

    return token;
  }

  Future<void> createGreeting(Session session, Greeting greeting) async {
    // TODO: Throw if the session is unauthenticated.
    await Greeting.db.insertRow(session, greeting);
  }

  Future<void> updateGreeting(Session session, UuidValue id,
      {String? message}) async {
    // TODO: Throw if the session is unauthenticated, or if the user should not
    // be able to update this greeting.
    await session.db.transaction((tx) async {
      final row = await Greeting.db.findById(session, id);
      await Greeting.db.updateRow(session, row!.copyWith(message: message));
    });
  }

  Future<void> deleteGreeting(Session session, UuidValue id) async {
    // TODO: Throw if the session is unauthenticated, or if the user should not
    // be able to delete this greeting.
    await Greeting.db.deleteWhere(session, where: (tbl) => tbl.id.equals(id));
  }
}

Future<String> _createPowerSyncToken(String userId) async {
  final decoded = _jsonUtf8.decode(base64.decode(_signingKey));
  final signingKey = JsonWebKey.fromJson(decoded as Map<String, Object?>);

  final now = DateTime.now();

  final builder = JsonWebSignatureBuilder()
    ..jsonContent = {
      'sub': userId,
      'iat': now.millisecondsSinceEpoch ~/ 1000,
      'exp': now.add(Duration(minutes: 10)).millisecondsSinceEpoch ~/ 1000,
      'aud': ['powersync'],
      'kid': _keyId,
    }
    ..addRecipient(signingKey, algorithm: 'RS256');

  final jwt = builder.build();
  return jwt.toCompactSerialization();
}

final _jsonUtf8 = JsonCodec().fuse(Utf8Codec());

const _signingKey = 'TODO'; // The "JWT signing keys for backend" bit from tool/generate_keys.dart
const _keyId = 'TODO'; // PS_JWK_KID from tool/generate_keys.dart

You can delete the existing greeting_endpoint.dart file, it’s not necessary since PowerSync is used to fetch data from your server. Also remove invocations related to future calls in lib/server.dart. Don’t forget to run serverpod generate afterwards.

Data Sync

With all services, configured, it’s time to spin up development services:

docker compose down
docker compose up --detach --scale powersync=0

# This creates the PowerSync role
dart run bin/main.dart --role maintenance --apply-migrations

# Create the PowerSync bucket storage database, use password from docker-compose.yaml
psql -h 127.0.0.1 -p 8090 -U postgres

Password from user postgres: <from compose>
postgres=# CREATE DATABASE powersync_storage WITH OWNER = powersync_role;
postgres=# \q

# Start PowerSync Service
docker compose up --detach

# Start backend
dart run bin/main.dart

With your Serverpod backend and PowerSync running, you can start connecting your clients. Go to the _flutter project generated by Serverpod and run dart pub add powersync path path_provider. Next, replace main.dart with this demo:

import 'package:flutter/foundation.dart';
import 'package:notes_client/notes_client.dart';
import 'package:flutter/material.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import 'package:powersync/powersync.dart' hide Column;
import 'package:powersync/powersync.dart' as ps;
import 'package:serverpod_flutter/serverpod_flutter.dart';

/// Sets up a global client object that can be used to talk to the server from
/// anywhere in our app. The client is generated from your server code
/// and is set up to connect to a Serverpod running on a local server on
/// the default port. You will need to modify this to connect to staging or
/// production servers.
/// In a larger app, you may want to use the dependency injection of your choice
/// instead of using a global client object. This is just a simple example.
late final Client client;

late final PowerSyncDatabase db;

late String serverUrl;

void main() async {
  // When you are running the app on a physical device, you need to set the
  // server URL to the IP address of your computer. You can find the IP
  // address by running `ipconfig` on Windows or `ifconfig` on Mac/Linux.
  // You can set the variable when running or building your app like this:
  // E.g. `flutter run --dart-define=SERVER_URL=https://api.example.com/`
  const serverUrlFromEnv = String.fromEnvironment('SERVER_URL');
  final serverUrl =
      serverUrlFromEnv.isEmpty ? 'http://$localhost:8080/' : serverUrlFromEnv;

  client = Client(serverUrl)
    ..connectivityMonitor = FlutterConnectivityMonitor();

  db = PowerSyncDatabase(
    // For more options on defining the schema, see https://docs.powersync.com/client-sdks/reference/flutter#1-define-the-client-side-schema
    schema: Schema([
      ps.Table('greeting', [
        ps.Column.text('owner'),
        ps.Column.text('message'),
        ps.Column.text('author'),
        ps.Column.text('timestamp'),
      ])
    ]),
    path: await getDatabasePath(),
    logger: attachedLogger,
  );
  await db.initialize();
  await db.connect(connector: ServerpodConnector(client.powerSync));

  Object? lastError;
  db.statusStream.listen((status) {
    final error = status.anyError;
    if (error != null && error != lastError) {
      debugPrint('PowerSync error: $error');
    }

    lastError = error;
  });

  runApp(const MyApp());
}

Future<String> getDatabasePath() async {
  const dbFilename = 'powersync-demo.db';
  // getApplicationSupportDirectory is not supported on Web
  if (kIsWeb) {
    return dbFilename;
  }
  final dir = await getApplicationSupportDirectory();
  return join(dir.path, dbFilename);
}

final class ServerpodConnector extends PowerSyncBackendConnector {
  final EndpointPowerSync _service;

  ServerpodConnector(this._service);

  @override
  Future<PowerSyncCredentials?> fetchCredentials() async {
    final token = await _service.createJwt();
    return PowerSyncCredentials(
      endpoint: 'http://localhost:8095',
      token: token,
    );
  }

  @override
  Future<void> uploadData(PowerSyncDatabase database) async {
    if (await database.getCrudBatch() case final pendingWrites?) {
      for (final write in pendingWrites.crud) {
        if (write.table != 'greeting') {
          throw 'TODO: handle other tables';
        }

        switch (write.op) {
          case UpdateType.put:
            await _service.createGreeting(Greeting.fromJson(write.opData!));
          case UpdateType.patch:
            await _service.updateGreeting(
              UuidValue.fromString(write.id),
              message: write.opData!['message'] as String?,
            );
          case UpdateType.delete:
            await _service.deleteGreeting(UuidValue.fromString(write.id));
        }
      }

      await pendingWrites.complete();
    }
  }
}

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

  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'Serverpod Demo',
      theme: ThemeData(primarySwatch: Colors.blue),
      home: const GreetingListPage(),
    );
  }
}

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

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text('PowerSync + Serverpod'),
        actions: const [_ConnectionState()],
      ),
      body: StreamBuilder(
        stream:
            db.watch('SELECT id, message, author FROM greeting ORDER BY id'),
        builder: (context, snapshot) {
          if (snapshot.hasData) {
            return ListView(
              children: [
                for (final row in snapshot.requireData)
                  _GreetingRow(
                    key: ValueKey(row['id']),
                    id: row['id'],
                    message: row['message'],
                    author: row['author'],
                  ),
              ],
            );
          } else if (snapshot.hasError) {
            return Text(snapshot.error.toString());
          } else {
            return const CircularProgressIndicator();
          }
        },
      ),
    );
  }
}

final class _GreetingRow extends StatelessWidget {
  final String id;
  final String message;
  final String author;

  const _GreetingRow(
      {super.key,
      required this.id,
      required this.message,
      required this.author});

  @override
  Widget build(BuildContext context) {
    return ListTile(
      title: Row(
        children: [
          Expanded(child: Text(message)),
          IconButton(
            onPressed: () async {
              await db.execute('DELETE FROM greeting WHERE id = ?', [id]);
            },
            icon: Icon(Icons.delete),
            color: Colors.red,
          ),
        ],
      ),
      subtitle: Text('Greeting from $author'),
    );
  }
}

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

  @override
  Widget build(BuildContext context) {
    return StreamBuilder(
      stream: db.statusStream,
      initialData: db.currentStatus,
      builder: (context, snapshot) {
        final data = snapshot.requireData;
        return Icon(data.connected ? Icons.wifi : Icons.cloud_off);
      },
    );
  }
}

Ensure containers are running (docker compose up), start your backend dart run bin/main.dart in notes_server and finally launch your app. When the app is loaded, you should see a greeting synced from the server. To verify PowerSync is working, here are some things to try:

1. Update in the source database: Connect to the Postgres database again (psql -h 127.0.0.1 -p 8090 -U postgres) and run a query like update greeting set message = upper(message);. Note how the app’s UI reflects these changes without you having to write any code for these updates.
2. Click on a delete icon to see local writes automatically being uploaded to the backend.
3. Add new items to the database and stop your backend to simulate being offline. Deleting items still updates the client immediately, changes will be written to Postgres as your backend comes back online.

Next Steps

This guide demonstrated a minimal setup with PowerSync and Serverpod. To expand on this, you could explore:

• Web support: PowerSync supports Flutter web, but needs additional assets.
• Authentication: If you already have an existing backend that is publicly-reachable, serving a JWKS URL would be safer than using pre-shared keys.
• Deploying: The easiest way to run PowerSync is to let us host it for you (you still have full control over your source database and backend). You can also explore self-hosting the PowerSync Service.