Local-First Sync with ElectricSQL and PGlite: A Technical Guide

Building applications that remain responsive under poor network conditions or fully offline has historically been one of the most difficult challenges in software engineering. The traditional request-response model, while simple to reason about, forces a dependency on the network that inevitably degrades user experience.

Local-first software represents a paradigm shift where the primary data source is on the user's device, and synchronization with a server happens asynchronously in the background. By leveraging ElectricSQL and PGlite, we can now build these systems using the familiar power of PostgreSQL, extended to the browser via WebAssembly (Wasm) and synchronized via Conflict-free Replicated Data Types (CRDTs).

The Shift to Local-First Architecture

In a standard web application, the 'source of truth' is the server-side database. The UI is merely a temporary view of that data. If the network drops, the UI freezes or errors out.

Local-first flips this. The local device holds the source of truth for the current user, and the server acts as a relay and durability layer. This approach offers three major benefits:

1. Zero Latency: Every interaction is a local database write. There is no 'loading' state for mutations.
2. Offline Capability: The app works by default in a tunnel, on a plane, or in a basement.
3. Simplified State Management: Instead of managing complex Redux or TanStack Query caches, you simply query a local database.

Understanding the Stack: ElectricSQL and PGlite

To implement this effectively, we need two components: a robust local database and a reliable synchronization engine.

PGlite: Postgres in the Browser

PGlite is a groundbreaking project that packages the full PostgreSQL engine into a Wasm module. Unlike previous attempts to bridge SQL to the browser (like SQL.js), PGlite is a direct build of Postgres. It supports transactions, complex joins, and extensions, all running inside the browser's main thread or a Web Worker. It persists data to IndexedDB, providing a permanent, relational store on the client.

ElectricSQL: The Sync Layer

ElectricSQL acts as the bridge between your central Postgres database and your fleet of PGlite instances. It uses Postgres's logical replication to stream data changes. Crucially, ElectricSQL handles the heavy lifting of conflict resolution. It transforms standard relational data into a format that can be merged without conflicts, effectively treating your rows as CRDTs (Conflict-free Replicated Data Types).

How CRDTs Solve the Conflict Problem

When two users edit the same record while offline, a conflict is inevitable when they reconnect. Traditional systems use 'Last Write Wins' (LWW), which results in data loss.

ElectricSQL uses a causal-consistency model. It tracks the history and dependencies of operations. When a conflict occurs—for example, two users updating the same field—ElectricSQL uses deterministic resolution rules (often based on LWW but enriched with causal metadata) to ensure that every node eventually reaches the same state without manual intervention.

By embedding this logic at the sync layer, developers are freed from writing 'reconciliation' code. You simply write SQL; the system ensures the data converges.

Implementing a Local-First Sync Flow

Let's look at how to set up a basic synchronization flow. We will assume you have a central Postgres instance and an ElectricSQL sync service running.

1. Defining the Sync Shape

In ElectricSQL, you don't sync the entire database at once. You define 'Shapes'—subsets of the database schema and data that a specific user needs.

// Client-side setup with ElectricSQL and PGlite
import { PGlite } from '@electric-sql/pglite';
import { electrify } from 'electric-sql/pglite';

const pg = new PGlite('idb://my-database');
const electric = await electrify(pg, config);

// Define a shape to sync
const shape = await electric.db.items.sync({
  where: { 
    user_id: currentUserId 
  }
});

await shape.isReady();

2. Performing Local Mutations

Once the shape is synced, you interact with the database using standard SQL or a type-safe client. The write happens immediately to PGlite.

// This returns instantly, even if offline
await electric.db.items.create({
  data: {
    title: 'New Task',
    completed: false,
    created_at: new Date()
  }
});

ElectricSQL's background process detects this change in PGlite, packages it with the necessary metadata, and attempts to push it to the central Postgres server. If the user is offline, the change is queued in an outbox within PGlite and sent upon reconnection.

Data Modeling for Convergence

While ElectricSQL handles conflict resolution, your schema design should still favor local-first patterns.

Use UUIDs, Not Serials

In a distributed system, integer auto-increments are a recipe for primary key collisions. Always use UUIDs (specifically UUIDv4 or UUIDv7) generated on the client. This ensures that every record created locally is globally unique.

Soft Deletes

Instead of DELETE operations, consider using a deleted_at timestamp or an is_deleted boolean. This preserves the causal history of the record, making it easier for the sync engine to handle cases where one user updates a record while another deletes it.

Handling Migrations in a Distributed Environment

Migrations are the 'final boss' of local-first development. When you change the schema in your central Postgres DB, that change must propagate to all client PGlite instances.

ElectricSQL handles this by versioning the sync protocol. When the central schema changes, Electric generates new client-side types and migration scripts. The client-side library detects version mismatches and applies the necessary DDL changes to the local PGlite instance before resuming synchronization. This ensures that the local database structure always matches the expectations of the sync service.

Real-World Use Case: Collaborative Field Service App

Imagine an application for utility engineers inspecting equipment in remote areas with spotty 5G coverage.

1. Morning Sync: Before heading out, the engineer's device fetches the 'Shape' of all work orders assigned to them for the day.
2. Offline Execution: Throughout the day, the engineer updates inspection logs, attaches photos (stored as references), and marks tasks as complete. Every action is a local SQL transaction in PGlite.
3. Background Sync: As the engineer drives between sites and hits pockets of connectivity, ElectricSQL transparently pushes updates to the central office and pulls any new high-priority tickets assigned to them.
4. Conflict Resolution: If a dispatcher in the office updates a work order note at the same time the engineer does, the CRDT logic ensures both notes are preserved or merged according to the defined policy, rather than one overwriting the other.

Performance and Security Considerations

Bundle Size and PGlite

While PGlite is lightweight (~3MB compressed), it is still a significant addition to a web bundle. Use dynamic imports to load the database engine only when needed, or offload it to a Web Worker to keep the main thread responsive.

Row-Level Security (RLS)

ElectricSQL integrates with Postgres Row-Level Security. This is critical. Since the client is 'pulling' data, the sync service must ensure that a user can only subscribe to shapes they are authorized to see. By defining RLS policies in your central Postgres instance, ElectricSQL automatically filters the data stream for each authenticated client.

Conclusion: The Actionable Path Forward

Local-first is no longer a niche requirement for 'Google Docs clones'; it is becoming the standard for high-quality UX in professional SaaS. The combination of PGlite's local relational power and ElectricSQL's seamless sync layer provides a production-ready path to building these applications today.

To get started:

1. Audit your current state management: Identify areas where network latency or offline failures frustrate users.
2. Prototype with PGlite: Replace a small piece of IndexedDB or LocalStorage logic with PGlite to experience the power of SQL in the browser.
3. Implement a Sync Shape: Use ElectricSQL to connect that local storage to your backend, starting with a simple, non-critical table.
4. Design for Conflict: Move away from serial IDs and destructive deletes toward UUIDs and soft-delete patterns.

By moving the data closer to the user, you don't just fix offline bugs—you eliminate the very concept of 'loading' from your application's core vocabulary.