Local-First Sync: Building Collaborative Apps with ElectricSQL and PGlite

The standard architectural pattern for web applications has remained largely unchanged for two decades: a thin client communicates with a central server via an API (REST, GraphQL, or gRPC), and the server manages a central database. While this model is easy to reason about, it introduces a significant bottleneck: the network. Every interaction that requires data persistence or retrieval is subject to latency, jitter, and the binary state of connectivity.

Local-first software aims to invert this model. By treating the local device as the primary data source and the cloud as a secondary synchronization and backup layer, we can build applications that are instantly responsive, work perfectly offline, and support seamless multi-user collaboration. In this article, we will explore the implementation of a local-first stack using two powerful tools: PGlite (Postgres in the browser) and ElectricSQL (the synchronization layer).

The Local-First Paradigm Shift

In a traditional "cloud-first" application, the UI is a projection of the server's state. When a user clicks "Save," the application shows a loading spinner, sends a network request, waits for a 200 OK, and then updates the local state.

In a local-first application, the UI is a projection of a local database. When a user clicks "Save," the application writes directly to the local database. The UI updates immediately (zero-latency). A background process then handles the synchronization of that local change to other devices and the server.

This shift introduces three primary challenges:

1. Data Persistence on the Client: How do we store complex, relational data efficiently in a browser or mobile environment?
2. Synchronization: How do we move data between the client and server without writing thousands of lines of bespoke sync logic?
3. Conflict Resolution: How do we handle two users editing the same record simultaneously while offline?

PGlite: Bringing Postgres to the Edge

Historically, client-side storage was limited to simple Key-Value stores (LocalStorage) or the somewhat clunky IndexedDB. While libraries like Dexie.js or RxDB improved the developer experience, they didn't offer the full power of a relational engine.

PGlite changes this. It is a WASM-compiled version of Postgres packaged into a client-side library. It allows you to run a full Postgres instance inside a browser tab, a Node.js worker, or a mobile environment.

Why PGlite?

• SQL on the Client: You can use the same schemas, constraints, and queries on the frontend as you do on the backend.
• Performance: Because it runs in WASM, it is significantly faster than many JavaScript-native storage solutions for complex queries.
• Small Footprint: Despite being a full Postgres build, it is optimized for the browser, with a bundle size that is manageable for modern web apps.

import { PGlite } from "@electric-sql/pglite";

const db = new PGlite();
await db.exec(`
  CREATE TABLE IF NOT EXISTS todos (
    id UUID PRIMARY KEY,
    task TEXT,
    done BOOLEAN DEFAULT false
  );
`);

await db.query("INSERT INTO todos (id, task) VALUES ($1, $2)", [
  crypto.randomUUID(),
  "Implement local-first sync",
]);

ElectricSQL: The Sync Engine

Having a database in the browser is only half the battle. The real complexity lies in keeping that local database in sync with a central authority and other clients. ElectricSQL is a synchronization layer for Postgres that provides an "active-replication" protocol.

ElectricSQL sits between your main Postgres database and your local PGlite instances. It consumes the Postgres logical replication stream and broadcasts changes to clients, while also accepting incoming changes from clients and applying them to the server.

The Concept of "Shapes"

One of the most powerful features of ElectricSQL is the concept of Shapes. In a local-first app, you rarely want to sync the entire database to every device—a smartphone doesn't have the storage for a multi-terabyte production DB.

A Shape is a subset of the database defined by a query. When a client subscribes to a Shape, ElectricSQL ensures that all data matching that query (and any related data defined in the shape) is synced to the local PGlite instance.

// Example of subscribing to a shape
const shape = await electric.syncTable('todos', {
  where: { user_id: currentUserId },
  include: { tags: true }
});

// The data is now physically present in the local PGlite instance
// and will stay updated in real-time.

Conflict Resolution and CRDTs

When multiple users edit data offline and then reconnect, conflicts are inevitable. Traditional databases use locking or "Last Write Wins" (LWW) at the row level, which often leads to data loss.

ElectricSQL utilizes Conflict-free Replicated Data Types (CRDTs) under the hood. CRDTs are data structures that are mathematically guaranteed to converge to the same state across all nodes, regardless of the order in which updates are received.

How ElectricSQL Handles Conflicts

ElectricSQL transforms standard Postgres tables into a causal-consistent state. It uses a combination of:

• LWW-Registers for primitive values: By default, if two users change the same column, the timestamp-based "last write" wins at the column level, not the row level. This prevents one user's change to a title from overwriting another user's change to a description.
• Causal Consistency: It ensures that if Operation B was performed after seeing the result of Operation A, Operation A will always be applied first on all nodes.
• Referential Integrity: Electric ensures that foreign key relationships are maintained even during concurrent operations (e.g., you can't delete a parent row if a concurrent operation added a child row to it).

Implementing a Real-World Sync Flow

Let's look at the lifecycle of a data change in an ElectricSQL + PGlite architecture:

1. Local Mutation: The user interacts with the UI. The application executes a standard SQL UPDATE or INSERT against the local PGlite instance.
2. Immediate UI Update: Since the database is local, the query returns in milliseconds. The UI reflects the change instantly using a reactive hook (like useQuery from the Electric SDK).
3. Background Sync: The Electric client identifies the local change. It packages the change with a causal metadata header and sends it to the Electric Sync Service via WebSockets.
4. Server Integration: The Sync Service validates the change against Postgres permissions and applies it to the central database.
5. Broadcast: The Sync Service pushes the change out to all other clients who have subscribed to a Shape that includes this data.
6. Convergence: Other clients receive the change and apply it to their local PGlite instances. Their UIs update automatically.

Architectural Considerations

Building with this stack requires a slight shift in how we think about security and data modeling.

Security and Permissions

In a local-first world, you cannot rely on an API layer to gate-keep every row. Instead, security is typically handled at the sync layer. ElectricSQL integrates with Postgres Row Level Security (RLS). When a client requests a Shape, Electric uses the user's JWT to verify which rows they are allowed to see and modify. If a user attempts to write to a row they don't own locally, the sync service will reject the change when it tries to hit the central Postgres instance.

Migrations

Schema migrations become more complex. You must ensure that the local PGlite schema remains compatible with the server-side Postgres schema. Electric handles this by versioning the sync protocol. When the server schema changes, Electric can notify clients that they need to update their local schema or re-sync their data.

Data Volume

While PGlite is efficient, browser storage (IndexedDB/OPFS) has limits. Engineers must be disciplined about what data is synced. Use Shapes to fetch only what is necessary for the current user context. For example, in a Slack-like app, you might sync the last 30 days of messages for the active channels, rather than the entire history of the workspace.

The Technical Trade-offs

No architecture is a silver bullet. Local-first introduces specific overheads:

• Initial Sync Latency: The first time a user opens the app, there is a "bootstrap" phase where the initial Shapes must be downloaded. This can be mitigated with clever UX and partial loading.
• Complexity of State: You are now managing a distributed system. Debugging "why did this row not sync?" requires better observability than debugging a simple 500 Internal Server Error.
• Storage Limits: Mobile browsers are aggressive about evicting data. You must implement robust logic to handle cases where the local database is wiped by the OS.

Conclusion: The Path Forward

Implementing local-first synchronization with ElectricSQL and PGlite represents a significant leap forward in how we build user-centric applications. By moving the database to the edge and using CRDT-based synchronization, we eliminate the "loading spinner" as a default UI state and provide a resilience to network conditions that was previously impossible.

To get started, follow these actionable steps:

1. Audit your existing UI: Identify areas where network latency causes friction. These are your best candidates for local-first features.
2. Experiment with PGlite: Replace a small piece of volatile React state or LocalStorage with a PGlite instance to get a feel for SQL in the browser.
3. Define your Shapes: Map out your data requirements. What subset of your central Postgres DB does a single user actually need to perform their job offline?
4. Leverage RLS: Ensure your Postgres Row Level Security is robust, as it becomes your primary firewall in a sync-based architecture.

The era of the thin client is ending. The future belongs to applications that are as reliable as a local text file but as collaborative as a shared spreadsheet.