Architecting Local-First Web Apps with PGLite and ElectricSQL

The architectural pendulum of web development is swinging back toward the client. For years, we have treated the browser as a thin, often fragile view layer that communicates with a remote source of truth via REST or GraphQL. While this model served us well during the transition from desktop to web, it introduced inherent latency and a dependency on constant connectivity that modern users increasingly find unacceptable.

Local-first software represents a paradigm shift where the primary data store lives on the user's device, and the cloud serves as a secondary layer for durability, backup, and multi-device synchronization. To achieve this at scale, we need more than just simple key-value stores like IndexedDB; we need the power of relational databases and sophisticated sync protocols. This is where the combination of PGLite and ElectricSQL becomes a game-changer.

The Local-First Imperative

Before diving into the implementation, it is important to clarify why we are moving away from traditional API-driven architectures. A local-first approach solves three fundamental problems:

1. Zero Latency: Interactions happen at the speed of the local CPU and disk. There is no waiting for a round-trip to a data center.
2. Offline Resilience: The application remains fully functional regardless of network status. Data is synchronized automatically when connectivity returns.
3. Simplified State Management: By treating the local database as the single source of truth for the UI, we eliminate the need for complex global state management libraries and manual optimistic UI updates.

PGLite: Bringing Postgres to the Browser

Historically, running a full relational database in the browser was a heavy lift. Developers often turned to SQLite via WebAssembly (Wasm), which is excellent but creates a mismatch if your backend is running PostgreSQL. PGLite solves this by providing a WASM-based build of Postgres that runs directly in the browser, or even in Node.js and Bun.

Why PGLite?

PGLite is not a re-implementation of Postgres; it is Postgres. It supports a significant subset of Postgres features, including triggers, views, and even extensions like pg_vector. Because it uses the Postgres wire protocol internally, the transition from local development to server-side logic is seamless.

One of the standout features of PGLite is its support for the Origin Private File System (OPFS). This allows for high-performance, persistent storage that bypasses many of the limitations and overhead associated with IndexedDB.

Basic PGLite Implementation

Integrating PGLite into a TypeScript application is straightforward:

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

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

// Basic query execution
await db.query("CREATE TABLE IF NOT EXISTS todos (id SERIAL PRIMARY KEY, task TEXT, done BOOLEAN);");
await db.query("INSERT INTO todos (task, done) VALUES ($1, $2);", ['Build local-first app', false]);

const result = await db.query("SELECT * FROM todos;");
console.log(result.rows);

While this gives us a powerful local database, it doesn't solve the problem of getting data to and from a central server. For that, we need a sync layer.

ElectricSQL: The Sync Engine

ElectricSQL is a synchronization layer that sits between your server-side PostgreSQL database and your local-first client. It handles the heavy lifting of conflict resolution, data filtering, and real-time updates.

The "Shape" Concept

One of ElectricSQL’s most powerful abstractions is the Shape. In a traditional application, you might fetch data via an endpoint like /api/projects/1/tasks. In a local-first application using Electric, you define a Shape—a subset of the database you want to keep in sync locally.

When a client subscribes to a Shape, ElectricSQL ensures that all relevant rows are synced to the local PGLite instance. Any changes made locally are captured and streamed back to the server, and vice versa.

Designing the Architecture

A production-ready local-first architecture using these tools typically follows this flow:

1. Primary Database: A standard PostgreSQL instance running on your server or a managed provider.
2. Electric Sync Service: An Elixir-based service that monitors the Postgres logical replication stream.
3. Client-side PGLite: The local database instance running in the user's browser.
4. Satellite (Electric Client): The client-side library that manages the connection between PGLite and the Electric Sync Service.

Setting Up the Sync Layer

To integrate ElectricSQL with PGLite, you use the Electric client to "electrify" your database connection. Here is a simplified example of how this looks in a React context:

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

// Initialize PGLite
const conn = new PGLite('idb://local-db');

// Electrify the connection
const electric = await electrify(conn, config);

// Sync a specific 'Shape' of data
const shape = await electric.db.todos.sync();

Once synchronized, you no longer use fetch or axios to update data. You simply write to your local PGLite instance using standard SQL or an ORM like Drizzle or Prisma. ElectricSQL detects the change in the local database and propagates it to the server.

Handling Conflicts and Consistency

In a distributed system where multiple users can edit the same data offline, conflicts are inevitable. ElectricSQL uses Causal Integrity and Conflict-free Replicated Data Types (CRDTs) under the hood to handle these scenarios.

Unlike simple "Last Write Wins" (LWW) strategies, which can lead to data loss, Electric ensures that the final state of the database is consistent across all nodes. For developers, this means you can focus on the business logic rather than writing complex retry mechanisms or manual merging scripts.

Practical Example: Collaborative Task Management

Imagine two users, Alice and Bob, editing the same task list while offline. Alice renames a task, and Bob marks it as complete. In a traditional REST setup, the last person to sync would likely overwrite the other's changes.

With ElectricSQL and PGLite:

1. Alice's change is saved to her local PGLite.
2. Bob's change is saved to his local PGLite.
3. When they reconnect, ElectricSQL merges the operations. The result is a task that is both renamed and marked as complete, preserving the intent of both users.

Performance and Storage Considerations

While PGLite and ElectricSQL offer massive benefits, senior engineers must be mindful of the constraints of the browser environment.

1. Initial Sync Time

When a user first loads the application, the "initial sync" can be heavy depending on the size of the Shapes defined. It is a best practice to keep Shapes granular. Instead of syncing the entire orders table, sync only the orders for the current user_id within the last 30 days.

2. WASM Overhead

Running Postgres in WASM adds a few megabytes to your initial bundle size. For many enterprise or SaaS applications, this is a negligible trade-off for the performance gains achieved after the initial load. However, for lightweight content sites, this may be overkill.

3. Browser Storage Quotas

While OPFS provides more breathing room, browsers still impose storage limits. It is critical to implement data eviction strategies for local data that is no longer needed, ensuring the local PGLite instance doesn't grow indefinitely.

Schema Migrations in a Local-First World

Migrations are arguably the most challenging aspect of local-first development. You have to manage schemas on the server and potentially thousands of heterogeneous client environments.

ElectricSQL handles this by acting as the authority. When you migrate your server-side Postgres schema, Electric generates the necessary client-side migrations. When a client connects, it checks its local schema version against the server. If they differ, Electric applies the necessary changes to the local PGLite instance before resuming sync. This ensures that the application code, which expects a certain schema, doesn't crash due to a mismatch.

Security and Permissions

In a local-first world, you cannot rely on middleware to check permissions on every request, because there is no request for local reads. Security must be baked into the sync layer.

ElectricSQL uses a declarative permissions model. You define rules in your server-side schema that dictate which rows a user is allowed to see and modify. The sync engine then ensures that only the authorized "Shape" of data ever reaches the client's local PGLite instance. This moves security from the API layer to the data layer, which is a more robust approach for distributed systems.

Conclusion: The Path Forward

Architecting with PGLite and ElectricSQL requires a mental shift. We are moving away from transient request-response cycles toward persistent, reactive data streams. While the initial learning curve involves understanding Wasm persistence and sync protocols, the result is an application that feels significantly faster and more reliable than traditional web apps.

To get started with this stack, I recommend the following actionable steps:

1. Audit your data access patterns: Identify which parts of your application would benefit most from zero-latency reads and offline writes.
2. Start small with PGLite: Replace a complex IndexedDB or Redux implementation with a local PGLite instance to get a feel for SQL-in-the-browser.
3. Define your Shapes: Carefully plan your ElectricSQL Shapes to ensure users only download the data they need, optimizing both battery life and bandwidth.
4. Embrace the Relational Model: Stop trying to flatten your data for the UI. With Postgres in the browser, you can use the full power of joins and relational integrity to manage your local state.

Local-first is no longer a niche requirement for collaborative tools like Figma or Notion; it is becoming the standard for high-quality web software. By leveraging PGLite and ElectricSQL, you are not just building an app that works offline—you are building a superior user experience.