Architecting Local-First Web Apps with PGLite and ElectricSQL

Building web applications that feel instantaneous and remain functional without an internet connection is no longer a niche requirement; it is becoming the gold standard for high-quality user experiences. For years, developers have struggled with the 'offline-sync' problem, often implementing fragile, custom-built solutions on top of WebSockets or polling.

However, the emergence of the Local-First movement—paired with powerful tools like PGLite and ElectricSQL—is fundamentally changing how we approach data architecture. By bringing the full power of PostgreSQL into the browser and pairing it with a robust synchronization layer, we can build applications that are faster, more resilient, and significantly easier to reason about.

The Shift to Local-First Architecture

Traditional web apps follow a 'thin client' model: the browser requests data, the server fetches it from a database, and the browser renders it. Every interaction requires a round-trip to the server. If the network is slow or non-existent, the app breaks.

Local-First architecture flips this. The primary data source is a local database residing within the client's memory or persistent storage (like IndexedDB or OPFS). The UI reads from and writes to this local database. Background synchronization then handles the heavy lifting of propagating those changes to a central server and fetching updates from other clients.

This approach provides three primary benefits:

1. Zero Latency: UI updates happen at the speed of local memory.
2. Offline Resilience: The app remains fully functional without a connection.
3. Simplified State Management: The database becomes the single source of truth, eliminating the need for complex global state libraries like Redux for data fetching.

PGLite: Postgres in the Browser

Until recently, if you wanted a relational database in the browser, your best bet was SQLite compiled to WebAssembly (WASM). While excellent, it introduced a 'dialect gap'—you'd use SQLite on the frontend and Postgres on the backend, leading to friction in schema design and query logic.

PGLite changes this. It is a build of Postgres 15+ compiled to WASM, packaged into a client-side library. It is remarkably small (~3MB gzipped) and provides a full Postgres environment including support for extensions like pgvector.

Why PGLite Matters

Unlike other WASM database ports, PGLite is designed to be embedded. It supports multiple persistence backends, including:

• Memory: For transient sessions or testing.
• IndexedDB: For standard browser persistence.
• Origin Private File System (OPFS): For high-performance, file-system-like storage in modern browsers.

Using PGLite means you can use the same SQL syntax, data types, and even some constraints on both ends of your stack.

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

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

await db.query(`
  CREATE TABLE IF NOT EXISTS tasks (
    id UUID PRIMARY KEY,
    title TEXT,
    completed BOOLEAN DEFAULT false
  );
`);

await db.query("INSERT INTO tasks (id, title) VALUES ($1, $2)", [
  crypto.randomUUID(),
  'Master Local-First Architecture'
]);

ElectricSQL: The Sync Engine

Having a local database is only half the battle. The real challenge is synchronization: how do you keep the local PGLite instance in sync with a central Postgres production database while handling conflicts and permissions?

ElectricSQL is an open-source synchronization layer designed specifically for this purpose. It sits between your central Postgres database and your local PGLite instances. It uses logical replication to stream changes back and forth in real-time.

The Concept of Shapes

One of ElectricSQL's most powerful features is the concept of 'Shapes.' You don't want to sync your entire multi-terabyte production database to every user's phone. A Shape is a subset of the database schema and data that a specific client needs.

For example, in a project management app, a user only needs the projects they are assigned to. You define a Shape, and ElectricSQL ensures that only that data—and any subsequent changes to it—is synced to the client's PGLite instance.

Implementing the Sync Flow

To architect a system using these tools, you generally follow a three-tier structure:

1. The Backend: A standard PostgreSQL instance. This remains your 'source of truth' for long-term storage and cross-user data.
2. The Electric Sync Service: A middle-tier service (often run as a Docker container) that connects to your Postgres instance via logical replication.
3. The Client: Your frontend application running PGLite and the Electric client SDK.

Practical Implementation Steps

1. Database Schema and Permissions

First, you define your schema on the server. ElectricSQL uses standard Postgres tables. You then 'electrify' these tables to enable sync.

-- On your server-side Postgres
ALTER TABLE projects ENABLE ELECTRIC;
ALTER TABLE tasks ENABLE ELECTRIC;

2. Client-Side Initialization

On the frontend, you initialize the Electric client and point it to your PGLite instance. The SDK handles the connection to the sync service.

import { electrify } from 'electric-sql/pglite';
import { PGLite } from '@electric-sql/pglite';
import { schema } from './generated/client'; // Generated from your SQL schema

const pg = new PGLite('idb://local-db');
const config = {
  url: 'https://your-electric-service.com'
};

const electric = await electrify(pg, schema, config);
const { db } = electric;

// Sync a specific shape
const shape = await db.projects.sync({
  where: { owner_id: currentUser.id },
  include: { tasks: true }
});

await shape.synced; // Wait for initial data load

3. Reactive UI

Because the data is local, your UI can react to database changes instantly. Electric provides hooks (for React and other frameworks) that re-render components whenever the underlying PGLite data changes—whether that change came from a local user action or a sync update from another user.

// In a React component
const { results } = useLiveQuery(db.tasks.liveMany());

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

Solving Conflict Resolution

In a distributed system, conflicts are inevitable. User A edits a task while offline; User B deletes the same task. Traditional systems often rely on 'last write wins,' which can lead to data loss.

ElectricSQL utilizes Causally Adjacent Conflict-free Replicated Data Types (CRDTs) under the hood. When you electrify a table, Electric adds metadata to track the history of changes. This allows the system to resolve conflicts deterministically across all nodes without requiring a central coordinator to mediate every single write. For developers, this means you can focus on building features rather than writing complex 'reconciliation' logic.

Performance and Storage Considerations

While PGLite and ElectricSQL are powerful, architecting for the browser requires mindfulness regarding resources.

Persistence Strategies

IndexedDB is the most compatible storage backend, but it has performance overhead due to the way it handles transactions. If your application involves heavy write operations, move to OPFS (Origin Private File System). OPFS provides much lower latency and is designed for file-system-like access, making it ideal for a database engine like Postgres.

Data Pruning

Mobile devices have limited storage. You shouldn't sync a user's entire 10-year history if they only need the last 30 days. Use ElectricSQL Shapes to define strict boundaries on what data is synced and implement local cleanup logic for PGLite if the database size grows beyond a certain threshold.

Security and Authentication

Security in a local-first world moves from the API layer to the data layer. Instead of securing REST endpoints, you secure the 'Sync Stream.'

ElectricSQL integrates with standard JWT-based authentication. When a client connects, the sync service validates the JWT. You then use Postgres Row-Level Security (RLS) or Electric's internal permission system to ensure that a user can only subscribe to Shapes they are authorized to see. This ensures that even though the database is local, the data privacy remains server-controlled.

Conclusion: The Actionable Path Forward

Transitioning to a local-first architecture using PGLite and ElectricSQL is a strategic move that pays dividends in user satisfaction and system robustness. If you are starting a new project or refactoring a data-heavy application, follow these steps:

1. Evaluate your data model: Identify which parts of your application benefit most from zero-latency (e.g., editors, dashboards, task lists).
2. Prototype with PGLite: Replace your existing client-side state management with PGLite to experience the power of relational queries in the browser.
3. Implement ElectricSQL for Sync: Start with a single 'electrified' table to handle the bi-directional sync between your server and client.
4. Optimize for OPFS: Ensure your production environment uses the most performant storage backend available to the browser.

By moving the database to the edge, you aren't just building an 'offline mode'—you are building a faster, more reliable, and more scalable future for web applications.