Local-First Sync with ElectricSQL and PGlite: A Modern Approach

The architecture of web applications is undergoing a fundamental shift. For years, we have built applications around a request-response cycle where the browser is a thin client and the server is the source of truth. While this model is simple, it introduces inherent latency, complex state management for 'optimistic updates,' and a complete failure mode when the user loses connectivity.

Local-first software aims to flip this script. In a local-first application, the primary data source is a database living directly on the user's device. The network moves to the background, acting as a synchronization layer rather than a gatekeeper. This article explores how to implement this pattern using two powerful tools: ElectricSQL and PGlite.

The Core Challenge: Synchronization and Conflict Resolution

Building local-first apps isn't just about 'offline mode.' It’s about ensuring that when multiple users edit the same data simultaneously—offline or online—the system converges to a consistent state without losing data or requiring manual intervention.

Traditionally, this meant implementing complex versioning or 'last-write-wins' strategies, which often resulted in data loss. The modern solution is Conflict-free Replicated Data Types (CRDTs). CRDTs are data structures designed so that concurrent updates can be merged automatically and deterministically. Whether a change happens in New York or London, once the two nodes sync, they are guaranteed to arrive at the exact same state.

PGlite: Bringing Postgres to the Browser

Until recently, if you wanted a database in the browser, you were largely stuck with IndexedDB or wrappers like Dexie. While functional, they lack the power, relational integrity, and ecosystem of a full SQL database.

PGlite changes the game. It is a WASM-based build of Postgres that runs entirely within the browser (or Node.js) with no external dependencies. It allows you to use real PostgreSQL—complete with foreign keys, triggers, and complex queries—directly in your client-side code.

Why PGlite matters for Local-First:

1. Full SQL Support: Use the same queries on the frontend and backend.
2. Persistence: It can persist data to IndexedDB, ensuring data survives page refreshes.
3. Performance: Because the data is local, reads and writes happen in microseconds, not milliseconds.
4. Reactivity: PGlite supports 'live queries,' allowing your UI to update instantly when the underlying data changes.

ElectricSQL: The Sync Engine

If PGlite is the local storage, ElectricSQL is the nervous system that connects it to your central Postgres authority. ElectricSQL is a sync layer that sits between your primary Postgres database and your local PGlite instances.

It handles the heavy lifting of:

• Replication: Streaming changes from the server to the client and vice versa.
• Conflict Resolution: Using CRDT-based logic to ensure all clients converge.
• Partial Sync (Shapes): Allowing clients to subscribe to only the subset of data they need, rather than downloading the entire multi-terabyte production database.

Building a Local-First Feature: A Practical Example

Let’s walk through the conceptual implementation of a collaborative task manager. In this scenario, users can create tasks, mark them as complete, and assign them to team members, even while on a plane without Wi-Fi.

1. Defining the Schema

First, we define our schema in our central Postgres database. ElectricSQL works by augmenting your existing tables.

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  title TEXT NOT NULL,
  completed BOOLEAN DEFAULT false,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Inform ElectricSQL to manage this table
ALTER TABLE tasks ENABLE ELECTRIC;

2. Initializing the Client-Side Database

In our frontend application, we initialize PGlite and connect it to the ElectricSQL sync service.

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

// Initialize PGlite with IndexedDB persistence
const pg = new PGlite('idb://my-app-db');

// Connect it to the Electric sync service
const electric = await electrify(pg, {
  url: 'http://localhost:5133',
  appName: 'my-task-app'
});

3. Syncing Data with Shapes

One of the most powerful features of ElectricSQL is Shapes. You don't want to sync every task from every user to every device. You only want the data relevant to the current user.

// Sync only the tasks that belong to the current user's project
const shape = await electric.db.tasks.sync({
  where: {
    project_id: currentProjectId
  }
});

// Wait for the initial sync to complete
await shape.isReady();

4. Reactive UI Updates

Because we are using PGlite, we can treat our database as the primary state manager for our UI. When a user clicks 'Complete,' we write directly to the local database. The UI updates instantly, and ElectricSQL handles the background sync.

// A React example of a live query
const { results } = useLiveQuery(
  electric.db.tasks.liveMany({ 
    where: { completed: false } 
  })
);

const toggleTask = async (id: string) => {
  await electric.db.tasks.update({
    data: { completed: true },
    where: { id }
  });
};

Handling Conflicts with CRDTs

Consider a scenario where User A and User B are both offline. User A renames a task to "Refactor API," and User B renames the same task to "Update Endpoints."

In a traditional system, whoever syncs last wins, and the other's work is deleted. With ElectricSQL's CRDT implementation, the system treats these as concurrent updates. Electric uses a Last-Write-Wins (LWW) Register approach at the column level or Causal Ordering depending on the configuration. Because the updates are tracked with metadata, the system can merge them deterministically. If the changes are on different columns (e.g., one changes the title, one changes the status), both changes are preserved. If they change the same column, the CRDT ensures every node arrives at the same final value without a merge conflict error.

Architectural Considerations

When moving to a local-first architecture with ElectricSQL and PGlite, there are several shifts in mindset required for senior engineers:

Permissions and Security

In a local-first world, you cannot rely on server-side middleware to check permissions on every write. Instead, you define Sync Rules. These are policies that dictate what data a user is allowed to see (the 'Read' side) and what changes the server will accept (the 'Write' side). ElectricSQL allows you to define these rules in a way that aligns with your existing Postgres roles and RLS (Row Level Security).

Data Migrations

Schema migrations are notoriously difficult in distributed systems. ElectricSQL manages this by versioning the schema and ensuring that clients can only sync if their local schema is compatible with the server's expected shape. You must plan for "backward compatibility" in your application code, as some users may still be running an older version of your app with an older schema while offline.

Performance and Storage

While PGlite is highly efficient, browsers have storage limits for IndexedDB. You must be intentional about what data you sync. Use the Shapes API to strictly limit the local footprint. For massive datasets, consider a hybrid approach where historical data remains on the server and only active data is synced locally.

The Business Value of Local-First

Why should technical decision-makers invest in this?

1. Unmatched UX: Zero-latency interactions make apps feel "native." No more loading spinners on every button click.
2. Reliability: Your app works in subways, elevators, and remote areas. This is a massive competitive advantage for field-service or productivity tools.
3. Reduced Server Load: Many read operations are moved to the client's device, potentially reducing the scale required for your API layer.

Conclusion

The combination of PGlite and ElectricSQL represents a significant leap forward for web development. By bringing a full Postgres instance to the browser and backing it with a robust, CRDT-based synchronization engine, we can finally build applications that are as resilient as they are fast.

Actionable Next Steps:

• Audit your current state management: Identify areas where network latency or offline failures are hurting your user experience.
• Experiment with PGlite: Swap out a small portion of your client-side state (like a cached list) with PGlite to feel the power of SQL in the browser.
• Prototype a Shape: Use ElectricSQL to sync a single table to a local PGlite instance and observe how the system handles concurrent edits.

Local-first is no longer a niche requirement for document editors; it is the future of high-performance web applications.