Local-First Mastery: Syncing Data with Electric SQL and PGLite

Building web applications that feel instantaneous is no longer a luxury; it is a baseline expectation. However, as developers, we are constantly battling the physics of the network. Traditional request-response architectures—where every interaction requires a round-trip to a server—introduce latency, fragility in poor network conditions, and a complex web of loading states.

Local-first software architecture flips this model on its head. Instead of the browser being a thin view of a remote database, the browser is the database. By utilizing tools like Electric SQL and PGLite, we can bring the power of PostgreSQL directly into the client, enabling reactive, offline-capable applications that synchronize seamlessly with a central source of truth.

The Evolution of the Local-First Paradigm

For years, we have tried to simulate local-first behavior using caching layers like TanStack Query or SWR. While these tools are excellent for performance, they are fundamentally "cloud-first." They treat the local state as a temporary mirror of the server. If the user goes offline, the experience degrades quickly, and handling complex write operations while disconnected often results in a nightmare of manual reconciliation.

True local-first development means the application reads from and writes to a local database first. The synchronization with the server happens asynchronously in the background. This approach offers three primary advantages:

1. Zero Latency: UI updates happen at the speed of the local CPU/memory.
2. Offline Resilience: The app remains fully functional without an internet connection.
3. Simplified State Management: The database becomes the single source of truth for the UI, eliminating the need for complex global state stores.

Introducing the Power Couple: PGLite and Electric SQL

To achieve a local-first architecture using the SQL ecosystem, we need two components: a robust local database and a sophisticated synchronization engine. This is where PGLite and Electric SQL come in.

PGLite: Postgres in the Browser

Historically, if you wanted a database in the browser, you were limited to IndexedDB or SQLite via WASM. While SQLite is powerful, it creates a "dialect gap" if your backend is running PostgreSQL. You end up maintaining two different schema formats and query styles.

PGLite is a breakthrough. It is a build of Postgres compiled to WebAssembly (WASM), packaged into a client-side library. It allows you to run a real Postgres instance inside your browser tab, a worker, or a Node.js environment. It supports many Postgres extensions and, most importantly, allows you to use the same SQL syntax on the client that you use on the server.

Electric SQL: The Sync Engine

Having a database in the browser is only half the battle. You need a way to get data from your central Postgres server into PGLite and vice versa. Electric SQL provides this synchronization layer.

Electric SQL uses Postgres's logical replication to capture changes (CDC) and stream them to clients. It handles the complexities of partial replication (only syncing the data the user needs), conflict resolution, and active-active synchronization. Unlike manual sync logic, Electric is transparent; you write to your local database, and Electric ensures those changes eventually reach the server.

The Architectural Blueprint

A typical stack using these technologies looks like this:

1. Backend Database: A standard PostgreSQL instance.
2. Electric Sync Service: An intermediary service that connects to Postgres via logical replication.
3. Client-Side Database (PGLite): The WASM-based Postgres running in the user's browser.
4. Electric Client SDK: A library that manages the connection between PGLite and the Electric Sync Service.

Implementing Local-First: A Practical Guide

Let's walk through how to set up a basic reactive application using this stack.

1. Preparing the Backend

First, your Postgres server must have logical replication enabled. You'll need to set wal_level = logical. Electric SQL works by creating a "Publication" on your tables.

-- Create a table on the server
CREATE TABLE items (
  id UUID PRIMARY KEY,
  content TEXT NOT NULL,
  completed BOOLEAN NOT NULL DEFAULT false,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

-- Enable Electric for this table
ALTER TABLE items ENABLE ELECTRIC;

2. Initializing PGLite and Electric on the Client

On the frontend, you'll need to initialize the PGLite database and connect it to the Electric sync engine. Using the Electric SDK, this process is streamlined.

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

// Initialize PGLite
const pg = new PGLite();

// Connect Electric to the local PGLite instance
const config = {
  url: 'http://localhost:5133', // URL of your Electric Sync Service
  appName: 'my-app'
};

const electric = await electrize(pg, config);

3. Defining Shapes and Syncing Data

One of the most powerful features of Electric SQL is "Shapes." You don't want to sync your entire 500GB production database to a mobile browser. Shapes allow you to define a subset of data to sync based on queries.

// Sync only the items relevant to the current user
const shape = await electric.db.items.sync({
  where: {
    user_id: currentUserId
  }
});

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

4. Reactive Queries

Because we are using PGLite, we can perform standard SQL queries. Electric extends this by providing reactivity. When data changes in the local database (either via user input or background sync), your UI can automatically update.

// A reactive hook example (pseudo-code)
const { results } = useLiveQuery(
  electric.db.items.liveMany({ 
    orderBy: { updated_at: 'desc' } 
  })
);

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

Handling Conflicts and Security

In a distributed system where multiple users can edit the same data offline, conflicts are inevitable. Electric SQL uses a Casual Consistency model. By default, it employs "Last Write Wins" (LWW) based on timestamps, but it also supports more sophisticated conflict-free replicated data types (CRDTs) logic internally to ensure that all nodes eventually converge to the same state.

Security is handled via Permissions. You can define rules in your schema that determine which users can see or modify which rows. Electric enforces these rules during the sync process, ensuring that a client cannot request a "Shape" of data they aren't authorized to access.

Performance and Operational Considerations

While the local-first approach is powerful, it isn't a free lunch. As a senior engineer, you must consider the trade-offs:

• Bundle Size: PGLite is a WASM binary. While highly optimized, it still adds roughly 2-3MB to your initial load. For many enterprise or productivity apps, this is a non-issue, but it's something to monitor for consumer-facing sites.
• Initial Sync: The first time a user opens the app, they must download their initial data set. Designing your "Shapes" effectively is crucial to ensure this initial sync is fast.
• Storage Limits: Browsers impose limits on how much data can be stored in persistent storage (IndexedDB). While PGLite can handle significant amounts of data, it is not meant to replace a data warehouse.

Real-World Use Cases

When should you choose this stack over a traditional REST API?

1. Collaborative Tools: Applications like Notion, Linear, or Figma where users need instant feedback and the ability to work offline.
2. Field Service Applications: Apps used by technicians in basements or remote areas where connectivity is intermittent. They can perform their work in PGLite, and the data will sync once they reach a 4G/5G zone.
3. Data-Intensive Dashboards: When users need to perform complex filtering and sorting on thousands of rows, doing it via local SQL queries is orders of magnitude faster than hitting a server API for every filter change.

Conclusion: The Actionable Path Forward

Local-first architecture is shifting from a niche experiment to a viable production strategy. By leveraging the familiarity of PostgreSQL through PGLite and the robust sync capabilities of Electric SQL, you can bypass the traditional hurdles of offline state management.

If you're looking to implement this in your next project, start with these steps:

1. Audit your data requirements: Identify which parts of your application would benefit most from zero-latency interactions.
2. Prototype with PGLite: Replace a small piece of your client-side state management with a local PGLite instance to get a feel for the SQL-in-browser experience.
3. Integrate Electric: Set up an Electric Sync Service container and experiment with syncing a single table. Observe how the "Shape" API simplifies data fetching.

The future of the web is distributed, and bringing the database to the edge is the most effective way to build the high-performance applications users demand.