Architecting Local-First Web Apps with PGLite and ElectricSQL

The traditional web application architecture—where the browser acts as a thin client for a remote server—is reaching its performance limits. As users demand instantaneous feedback and seamless offline capabilities, the 'spinner' has become a symbol of architectural debt. To solve this, a new paradigm has emerged: Local-First. By moving the primary data store into the client and treating the server as a synchronization hub, we can eliminate network latency from the critical path of user interaction.

In this article, we will explore how to architect a robust local-first system using two cutting-edge technologies: PGLite, a WASM-based Postgres for the browser, and ElectricSQL, a synchronization layer that bridges local data with a central Postgres database.

The Shift to Local-First Architecture

For decades, we have optimized the request-response cycle. We moved logic to the edge, implemented aggressive caching, and optimized payloads. However, as long as a user interaction requires a round-trip to a data center, the speed of light remains our ultimate bottleneck.

Local-first architecture flips the script. Instead of querying a remote API, the application queries a local database. Data is persisted locally first, and synchronization happens in the background. This approach provides three primary benefits:

1. Zero Latency: UI updates are optimistic and instantaneous because they interact with local memory or disk.
2. Offline Resilience: The app remains fully functional without an internet connection.
3. Simplified State Management: By using a real database (SQL) on the client, we move away from complex, ephemeral state management libraries like Redux or Pinia and back to a declarative data model.

PGLite: The Power of Postgres in the Browser

Historically, local storage meant using localStorage (limited and synchronous), IndexedDB (notorious for a clunky API), or SQLite via WASM. While SQLite is excellent, it introduces a 'dialect gap'—your client uses SQLite syntax while your server uses Postgres.

PGLite changes this. It is a build of Postgres 15+ compiled to WebAssembly, packaged into a client-side library. It allows you to run a full-featured Postgres instance inside your browser tab, a Worker, or a Node.js environment.

Why PGLite?

• Relational Integrity: You get actual foreign keys, constraints, and triggers in the browser.
• Extensions: It supports various Postgres extensions, including pgvector for local AI/ML applications.
• Size: Despite being a full database engine, it is optimized for the web, weighing in at a few megabytes compressed.

Initializing PGLite

Setting up PGLite is straightforward. You can choose to persist data in IndexedDB so that the database survives page refreshes:

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

// Initialize the database with IndexedDB persistence
const db = new PGLite('idb://my-application-db');

// Execute a query just like you would on the server
await db.query(`
  CREATE TABLE IF NOT EXISTS todo (
    id UUID PRIMARY KEY,
    task TEXT,
    completed BOOLEAN DEFAULT false
  );
`);

ElectricSQL: Solving the Sync Problem

Running a database on the client is only half the battle. The real challenge is synchronization. How do you ensure that data changed on one client is reflected on others without creating messy conflicts or overloading the network?

ElectricSQL is a sync layer for Postgres. It sits between your central Postgres database and your local PGLite instances. It uses Active Replication to stream data changes bidirectionally. Unlike traditional REST APIs, ElectricSQL doesn't require you to write manual sync logic or conflict resolution code for every endpoint.

The Concept of Shapes

ElectricSQL introduces the concept of 'Shapes.' A Shape is a subset of your central database—defined by tables and filters—that a client 'subscribes' to. This ensures the client doesn't download the entire multi-terabyte production database, but only the data relevant to the current user.

// Example of syncing a 'Shape' of data
const shape = await electric.syncTable('todo', {
  where: { user_id: currentUserId }
});

Once a shape is synced, ElectricSQL handles the heavy lifting of keeping the local PGLite instance in sync with the server using a conflict-free mechanism.

Architecting the Data Flow

A production-grade local-first architecture using this stack typically follows this flow:

1. Local Write: The user performs an action (e.g., marks a task as complete). The application executes a standard UPDATE query against the local PGLite instance.
2. Optimistic UI: The UI reacts immediately because the local query returns in microseconds.
3. Background Sync: ElectricSQL's satellite process detects the change in the PGLite WAL (Write Ahead Log) and pushes the change to the Electric sync service.
4. Global Propagation: The sync service applies the change to the central Postgres DB and broadcasts it to all other clients subscribed to that data shape.
5. Conflict Resolution: If two users edit the same row simultaneously, ElectricSQL uses LWW (Last Write Wins) or causal consistency models to ensure all clients eventually converge on the same state.

Practical Implementation: A Step-by-Step Guide

To implement this stack, you need to configure your backend to support logical replication.

Step 1: Postgres Configuration

Your central Postgres instance must have wal_level set to logical. This allows ElectricSQL to listen to changes via the Postgres replication protocol.

ALTER SYSTEM SET wal_level = 'logical';

Step 2: The Sync Service

You deploy the Electric Sync Service (usually as a Docker container). This service connects to your Postgres DB and exposes a stream for the clients.

Step 3: Integrating the Client

In your frontend, you wrap your PGLite instance with the Electric client. This creates the bridge.

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

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

// Electrify the PGLite instance
const electric = await electrify(baseDb, config);

// Now, PGLite is automatically synced with the server

Handling Complexities: Migrations and Security

While the local-first approach simplifies many aspects of development, it introduces new challenges in schema evolution and security.

Schema Migrations

In a traditional app, you migrate the server and the app follows. In a local-first app, you have thousands of databases living on users' devices. ElectricSQL manages this by versioning the shapes. When the server schema changes, Electric ensures that clients receive the necessary migrations to keep their local PGLite schema in sync with the expected shape.

Security and Row-Level Security (RLS)

Security in local-first apps is paramount because the user has direct access to the local database. You must use Postgres Row Level Security (RLS) on the central server. ElectricSQL respects these RLS policies. When a client requests a shape, ElectricSQL ensures they only receive the rows they are authorized to see based on their authentication token.

Performance Considerations

When architecting with PGLite, keep these performance tips in mind:

• Web Workers: Run PGLite in a Web Worker to keep the main UI thread free from database processing overhead. This is especially important for complex analytical queries.
• Index Strategically: Just like on the server, local queries need indexes. Don't neglect CREATE INDEX statements in your local setup logic.
• Partial Sync: Use ElectricSQL's filtering capabilities to keep the local database footprint small. Avoid syncing large blobs or historical data that isn't immediately necessary.

The Death of the Spinner

The most profound impact of this architecture is the disappearance of loading states. Because the data is always 'there,' your UI code becomes significantly simpler. You no longer need to manage isLoading, isError, and data states for every single component. You simply query the local DB, and the UI stays reactive to changes in that DB.

Conclusion: Actionable Next Steps

Architecting for local-first is a strategic decision that pays dividends in user experience and developer productivity. If you are starting a new project or refactoring a data-heavy application, follow these steps:

1. Evaluate the Dialect: If you are already using Postgres on the backend, PGLite is the logical choice to minimize cognitive load and code duplication.
2. Start with a Single Module: Don't migrate your entire app to local-first at once. Pick a high-interaction feature (like a dashboard or a task manager) and implement it using PGLite and ElectricSQL.
3. Master SQL: Re-invest in your SQL skills. The better you understand relational modeling, the more power you can leverage from PGLite's engine.
4. Monitor Storage: Keep an eye on IndexedDB storage limits, especially on mobile browsers, and implement data retention policies for your local shapes.

Local-first isn't just about offline support; it's about building applications that feel instantaneous. By leveraging PGLite and ElectricSQL, we are finally bringing the full power of the relational database to where the user is: the browser.