Architecting Local-First Apps with PGLite and ElectricSQL

For the past decade, the standard blueprint for web applications has been the request-response model. A client makes a request, a server processes it against a database, and the client waits for a response. While this works, it introduces inherent latency and fails completely when the network is unstable.

Local-first software represents a paradigm shift where the primary data source is on the user's device, and the cloud serves as a synchronization and backup layer. This article explores a modern stack for achieving this: PGLite for local storage and ElectricSQL for seamless data synchronization.

The Anatomy of a Local-First Architecture

In a traditional architecture, the 'source of truth' lives in a remote data center. In a local-first architecture, the source of truth is the local database. This architecture provides three major benefits:

1. Zero Latency: UI interactions happen at the speed of the local disk/memory, not the network.
2. Offline-Capability: The app remains fully functional without an internet connection.
3. Simplified State Management: Instead of juggling complex Redux or TanStack Query states, the UI simply reacts to the local database.

To build this, we need two components: a robust local database that can run in the browser and a sync engine that handles the heavy lifting of moving data between the client and the server.

PGLite: Postgres in the Browser

Until recently, local storage in the browser meant choosing between the limited API of IndexedDB or the relational power of SQLite via WASM. PGLite changes this by providing a WASM-compiled version of Postgres that can run directly in a browser tab or Node.js environment.

Why PGLite?

PGLite is not a 'Postgres-like' database; it is the actual Postgres engine. This means you get full support for features like:

• Complex JOINs and Window Functions.
• JSONB support.
• PostGIS (in supported builds).
• Full-text search.

It is lightweight (roughly 3MB compressed) and can persist data using the browser's Origin Private File System (OPFS) or IndexedDB.

Initializing PGLite

Setting up PGLite is straightforward. Here is how you initialize a persistent instance in a TypeScript environment:

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

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

// Executing a query
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);", ['Learn PGLite', false]);

ElectricSQL: The Synchronization Bridge

Storing data locally is only half the battle. The real challenge is synchronizing that data with a central server and other users' devices while handling conflicts. This is where ElectricSQL comes in.

ElectricSQL is a sync layer that sits between your central Postgres database and your local PGLite instances. It uses a "Shapes" protocol to allow clients to subscribe to specific subsets of the database.

How Syncing Works

ElectricSQL monitors the logical replication stream of your primary Postgres database. When data changes on the server, Electric pushes those changes to relevant clients. When a client makes a change to PGLite, Electric captures those changes and reconciles them back to the server.

One of the most powerful features of ElectricSQL is its approach to conflict resolution. It utilizes Causal Integrity and Conflict-free Replicated Data Types (CRDTs) principles behind the scenes to ensure that all clients eventually converge on the same state without requiring complex manual resolution logic.

Implementing the Sync Flow

To integrate PGLite with ElectricSQL, we use the electric-sql client library. The architecture follows a simple flow: Define a Shape -> Sync to PGLite -> Query PGLite.

Defining Shapes

A "Shape" is a set of related tables and filters that define what data a user needs. For example, in a project management app, a user only needs the projects they belong to and the tasks associated with those projects.

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

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

// Sync a 'Shape' of data
const shape = await electric.sync({
  table: 'projects',
  include: {
    tasks: true
  },
  where: { owner_id: currentUserId }
});

// Wait for initial sync
await shape.synced;

Once the shape is synced, the data is available in the local PGLite instance. You can now query it using standard SQL.

Reactivity and the UI Layer

In a local-first app, the UI should be a reflection of the local database. When the database changes (either via user input or background sync), the UI should update automatically.

Using PGLite with a React wrapper allows for highly efficient reactive queries. Instead of fetching data in a useEffect, you subscribe to a live query.

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

const TodoList = () => {
  const { data: todos, loading } = useQuery(
    "SELECT * FROM todos ORDER BY id DESC;"
  );

  if (loading) return <div>Loading...</div>;

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

Because the query is running against a local WASM database, the "loading" state only exists during the initial boot or when heavy sync is occurring. For the user, the app feels instantaneous.

Handling Migrations

One of the hardest parts of local-first development is schema evolution. Since the database lives on the user's device, you cannot simply run a migration on your central server and expect everything to work.

ElectricSQL handles this by acting as a gateway. You apply migrations to your central Postgres database, and Electric ensures that the schema changes are propagated to the clients. PGLite then updates its local schema to match. This "centralized migration, distributed application" model significantly reduces the operational overhead usually associated with distributed databases.

Performance Considerations and Best Practices

While PGLite and ElectricSQL provide a powerful abstraction, there are architectural considerations to keep in mind:

1. Storage Limits

Browsers impose limits on how much data can be stored (often a percentage of available disk space). While OPFS allows for gigabytes of data, you should still be mindful of what you sync. Use Electric's "Shapes" to sync only the data necessary for the current user's context.

2. Initial Sync Time

The first time a user opens your app, they must download the WASM binary and the initial data set. To optimize this:

• Use a CDN to serve the PGLite WASM files.
• Implement a loading progress bar for the initial sync.
• Minimize the initial shape size.

3. Connection Management

ElectricSQL is designed to handle intermittent connectivity. However, your UI should still provide visual cues when the app is offline or when a sync is in progress. The Electric client provides status hooks to monitor the connection state.

Security and Permissions

Security in local-first apps is often misunderstood. Some assume that because the database is local, it is insecure. In reality, the security model shifts to the sync layer.

ElectricSQL integrates with standard authentication providers (like Auth0, Supabase Auth, or custom JWTs). When a client requests a Shape, Electric verifies the JWT and ensures the user has permission to access the requested rows. This is essentially Row-Level Security (RLS) for the edge.

The Impact on Development Velocity

Perhaps the most surprising benefit of this stack is the increase in development velocity. In a traditional app, adding a new feature often involves:

1. Updating the DB schema.
2. Updating the API backend (Controller, DTO, Service).
3. Updating the Frontend API client.
4. Updating the Frontend state management.
5. Adding optimistic UI logic.

With PGLite and ElectricSQL, the process is compressed. You update the schema, and the data simply 'appears' in your local database, ready to be queried by the UI. The entire category of 'Optimistic UI' logic disappears because the local write is the UI update.

Conclusion: The Future of the Web

Local-first is not just a niche requirement for note-taking apps or offline tools; it is the next evolution of user experience on the web. By utilizing PGLite to bring the full power of Postgres to the browser and ElectricSQL to handle the complexities of synchronization, we can build applications that are faster, more resilient, and easier to maintain.

Actionable Next Steps:

• Audit your latency: Identify parts of your application where network round-trips degrade the UX.
• Prototype a Shape: Use the ElectricSQL CLI to generate a sync service against an existing Postgres database.
• Implement PGLite: Replace a small piece of your application's complex client-side state with a PGLite local table to experience the simplified data flow first-hand.