Local-First Sync with ElectricSQL and PGLite

The traditional web application architecture—request-response over a REST or GraphQL API—is hitting a wall. As user expectations for responsiveness and offline capability grow, the latency inherent in round-tripping every state change to a central server is becoming a liability. We have spent a decade trying to mask this latency with complex loading states, optimistic UI updates, and brittle caching layers.

A better approach is emerging: Local-first software. In this paradigm, the primary data source for the application is a database living directly within the client's environment. The cloud becomes a synchronization and backup service rather than the gatekeeper of every interaction.

Two technologies have recently converged to make this architecture not only viable but highly ergonomic for Postgres-centric stacks: ElectricSQL and PGLite. In this article, we will explore how to use these tools to build reactive, offline-capable applications that treat the browser as a first-class Postgres node.

The Architecture of Local-First Sync

To understand why ElectricSQL and PGLite are significant, we must first define the core challenge of local-first: state synchronization. It is relatively easy to run a database in a browser; the difficulty lies in ensuring that the local state eventually matches the global state without creating a nightmare of merge conflicts.

The Local Engine: PGLite

Historically, web developers were forced to use IndexedDB—a low-level, non-relational, and notoriously difficult API—or wrappers like Dexie or PouchDB. While SQLite via WASM became an option, it often felt like a foreign body in a Postgres-dominated ecosystem.

PGLite changes this. It is a build of Postgres packaged as a WASM module that runs entirely in the browser (or Node.js). Unlike other solutions, it is not a shim; it is the actual Postgres engine. This means you get full SQL support, JSONB, and even some extensions, all with a footprint small enough to load in a web page. Most importantly, it allows for true reactive queries locally.

The Sync Layer: ElectricSQL

Running a database locally is only half the battle. You need a way to stream data between the client and the server. ElectricSQL provides this sync layer. It uses Postgres's logical replication features to capture changes on the server and stream them to the client (and vice versa).

ElectricSQL doesn't just dump the entire database into the browser. It uses a concept called Shapes—sets of related data defined by SQL queries—to allow clients to subscribe to specific subsets of the database. This ensures that a mobile user doesn't download your entire multi-terabyte production database just to view their own profile.

Setting Up the Reactive Stack

Let’s look at how these pieces fit together in a practical implementation. We’ll assume a standard React-based frontend and a Postgres backend.

1. Initializing PGLite

First, we instantiate the local database. PGLite can persist data to IndexedDB, ensuring that when a user refreshes their browser, their data remains intact.

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

const db = new PGLite('idb://my-app-db');

// Basic SQL execution locally
await db.exec(`
  CREATE TABLE IF NOT EXISTS todos (
    id UUID PRIMARY KEY,
    task TEXT,
    completed BOOLEAN DEFAULT false
  );
`);

2. Configuring ElectricSQL

ElectricSQL sits between your primary Postgres instance and your PGLite instances. You run an Electric sync service (usually as a Docker container) that connects to your Postgres database via logical replication.

On the client, you initialize the Electric client and point it toward your local PGLite instance:

import { electrize } from '@electric-sql/pglite-sync';

const electric = await electrize(db, {
  url: 'https://your-electric-service-url.com',
  appName: 'my-todo-app'
});

3. Defining and Syncing Shapes

This is where the magic happens. You define a "Shape" to tell Electric which data should be synced to the local PGLite instance. This is a declarative process.

const shape = await electric.sync({
  table: 'todos',
  where: "user_id = 'user-123'" 
});

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

Once the shape is synced, any changes made to the todos table in the master Postgres database that match the criteria will be automatically pushed to the local PGLite instance. Conversely, any INSERT or UPDATE performed on the local PGLite instance will be captured and sent to the server when the user is online.

Reactive Queries: The User Experience Advantage

The most compelling reason to use PGLite with ElectricSQL is reactivity. In a traditional app, after an API call, you must manually update the local cache or refetch the data. In a reactive Postgres setup, you subscribe to the database itself.

Using a hook-based approach (common in React), your UI stays in sync with the underlying data store automatically:

function TodoList() {
  // This hook rerenders the component whenever the underlying 
  // local PGLite table changes, whether from a local user action 
  // or a background sync from the server.
  const { rows } = useQuery(db, 'SELECT * FROM todos ORDER BY id');

  return (
    <ul>
      {rows.map(todo => (
        <li key={todo.id}>{todo.task}</li>
      ))}
    </ul>
  );
}

This creates a "zero-latency" feel. When a user clicks "Complete," you execute a local SQL command. The UI updates instantly because the query hook detects the change in PGLite. The sync engine handles the heavy lifting of sending that update to the server in the background.

Handling Conflicts and Consistency

In any distributed system, conflicts are inevitable. What happens if two users edit the same record while offline?

ElectricSQL uses a Causal-Integrity model. By default, it employs a "Last Write Wins" (LWW) resolution strategy at the column level, which is sufficient for many CRUD applications. However, because it is built on Postgres, it maintains relational integrity. Unlike some NoSQL sync solutions, you won't end up with "orphaned" records where a child exists but its parent was deleted elsewhere; the sync engine understands foreign key constraints.

For more complex scenarios, you can leverage Postgres's built-in features or structure your data to be additive (e.g., using an events table) to avoid destructive conflicts entirely.

Security and Row-Level Security (RLS)

A common concern with local-first is security. If the client has a database, how do we prevent them from seeing data they shouldn't?

ElectricSQL leverages Postgres's native Row-Level Security (RLS). When a client connects and authenticates (usually via a JWT), the Electric sync service respects the RLS policies defined in your central Postgres database. If a user doesn't have permission to see a row, that row is never sent to their local PGLite instance. This allows you to centralize your security logic in the database schema where it belongs, rather than duplicating it across API endpoints.

Practical Considerations for Technical Leaders

Before migrating your entire stack to ElectricSQL and PGLite, consider the following trade-offs:

1. Bundle Size: PGLite is remarkably small for what it is, but it still adds a few hundred kilobytes of WASM to your bundle. For a complex SaaS tool, this is negligible. For a simple landing page, it might be overkill.
2. Initial Sync Latency: The first time a user opens the app, they must download their initial "Shape." You need to design your UX to handle this first-load state gracefully.
3. Migration Strategy: Moving to local-first is a paradigm shift. It requires thinking in terms of "Syncing Shapes" rather than "Fetching Endpoints." It is often best to start with a specific feature (e.g., a real-time notification center or an offline-capable form) rather than a full-app rewrite.
4. Storage Limits: While IndexedDB can store gigabytes of data, browsers do have eviction policies. Your application must be prepared for the local database to be cleared by the OS, necessitating a re-sync from the server.

The Future: Postgres Everywhere

The combination of ElectricSQL and PGLite represents a significant step toward the "Postgres Everywhere" vision. By extending the reach of Postgres into the browser, we eliminate the translation layer between the relational server and the non-relational client.

We no longer need to map SQL rows to JSON objects, then to Redux stores, then back to API requests. Instead, we have a unified data model that flows from the server to the edge. This reduces the surface area for bugs, simplifies state management, and—most importantly—provides an incredibly fast experience for the end user.

Conclusion: Actionable Next Steps

If you are looking to improve the responsiveness and reliability of your web applications, the local-first approach is no longer a theoretical exercise. To get started:

• Prototype with PGLite: Replace a small part of your application's local state (like a complex filter configuration) with a PGLite instance to get a feel for SQL in the browser.
• Audit your Data Shapes: Look at your existing API responses. Could they be represented as SQL queries? This is the first step in defining your ElectricSQL shapes.
• Evaluate Sync Needs: Identify features where latency is a deal-breaker (e.g., text editors, project management boards, or data-entry intensive tools). These are the primary candidates for an ElectricSQL implementation.

Local-first isn't just about working without internet; it's about making the internet feel invisible. By leveraging the power of Postgres on both sides of the wire, we can finally build web applications that are as robust and snappy as their native counterparts.