Local-First Apps with ElectricSQL and PGLite: A Practical Guide

For the last decade, web development has been dominated by the request-response model. We build APIs, manage complex loading states, and implement optimistic UI updates to mask the inherent latency of the network. However, a shift is occurring. Developers are increasingly looking toward the "Local-First" paradigm—an architectural approach where the primary data source for the application is a local database, and synchronization with a server happens asynchronously in the background.

Two technologies have recently emerged as frontrunners in making this vision a reality for Postgres users: ElectricSQL and PGLite. By combining a WASM-based Postgres build in the browser with a high-performance synchronization layer, we can now build applications that are instantly responsive, work offline by default, and drastically simplify state management.

Moving Beyond the Request-Response Cycle

In a traditional SPA, the browser is a thin client. Every interaction—checking a checkbox, adding a comment, or updating a profile—requires a round-trip to the server. Even with modern tools like React Query or SWR, the developer is still responsible for managing the "in-between" states: isLoading, isError, and the manual cache invalidation that follows a mutation.

Local-first flips this. Instead of fetching data from an API endpoint, the UI queries a local database. When the user makes a change, it is written to that local database immediately. The UI updates instantly because the data is already there. A synchronization engine then handles the heavy lifting of sending that change to the server and pulling down changes from other clients.

The Core Stack: PGLite and Electric

To implement this effectively with Postgres, we need two components: a local database that speaks SQL and a sync service that understands Postgres logical replication.

PGLite: Postgres in the Browser

PGLite is a groundbreaking project that packages the full Postgres engine into a WASM (WebAssembly) binary. Unlike previous attempts to run Postgres in the browser, PGLite is lightweight (around 3MB compressed) and can be used as a reactive store.

Why use PGLite instead of IndexedDB or SQLite?

1. Feature Parity: You get the same types, constraints, and query capabilities in the browser as you have on your server.
2. Performance: For complex relational queries, Postgres is highly optimized.
3. No Impedance Mismatch: You can share schema definitions and even some business logic between your backend and frontend.

ElectricSQL: The Synchronization Engine

ElectricSQL acts as the bridge. It sits between your main Postgres database and your fleet of PGLite instances. Electric uses Postgres logical replication to track changes in the database and stream them to clients.

Recently, Electric evolved its architecture to focus on "Shapes." A Shape is a subset of your database—a set of tables and related records defined by a query—that a client subscribes to. This allows you to replicate only the data the user needs, rather than the entire multi-terabyte production database.

Implementation: From Postgres to the User's Screen

Let’s look at how to implement a reactive task management application using this stack.

1. Defining the Backend Schema

Your server-side Postgres schema remains your source of truth. You define your tables as usual:

CREATE TABLE projects (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TABLE todo_items (
  id UUID PRIMARY KEY,
  project_id UUID REFERENCES projects(id),
  content TEXT NOT NULL,
  completed BOOLEAN DEFAULT FALSE
);

2. Setting up PGLite on the Client

In your frontend application, you initialize PGLite. You can choose to persist the data in IndexedDB so that it survives page refreshes.

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

const db = new PGLite({
  dataDir: 'idb://my-app-db',
});

3. Syncing Data via Electric Shapes

Instead of calling GET /api/todos, you define a Shape and sync it into PGLite. Electric provides a sync service that handles the streaming of these records.

import { ShapeStream } from '@electric-sql/client';

// Define the shape of data you want to sync
const stream = new ShapeStream({
  url: 'https://api.electric-sql.com/v1/shape/todo_items',
  params: {
    where: "project_id = 'some-uuid'"
  }
});

// As data arrives, upsert it into PGLite
stream.subscribe((messages) => {
  for (const msg of messages) {
    const { value, headers } = msg;
    // Handle INSERT, UPDATE, DELETE logic in PGLite
    await db.query(
      "INSERT INTO todo_items (id, content, completed) VALUES ($1, $2, $3) ON CONFLICT (id) DO UPDATE...",
      [value.id, value.content, value.completed]
    );
  }
});

The Power of Reactivity

One of the most compelling reasons to use PGLite is its support for "Live Queries." Rather than manually re-fetching data when you think it has changed, you can observe a query.

In a React component, this looks like a dream:

function TodoList({ projectId }) {
  // This hook automatically rerenders when the local DB changes
  const { rows: todos } = useLiveQuery(
    "SELECT * FROM todo_items WHERE project_id = $1 ORDER BY created_at DESC",
    [projectId]
  );

  const toggleTodo = async (id, status) => {
    // Immediate local update. The UI reflects this instantly.
    await db.query("UPDATE todo_items SET completed = $1 WHERE id = $2", [!status, id]);
    
    // Electric handles pushing this change back to the server in the background.
  };

  return (
    <ul>
      {todos.map(todo => (
        <li key={todo.id} onClick={() => toggleTodo(todo.id, todo.completed)}>
          {todo.content} {todo.completed ? '✅' : '⬜'}
        </li>
      ))}
    </ul>
  );
}

Handling Conflicts and Consistency

In any distributed system, conflicts are inevitable. User A and User B might edit the same todo item while offline.

ElectricSQL handles this using Causal Integrity. It ensures that the order of operations is preserved and that the final state of the database is consistent across all nodes. By default, it often employs a "last-write-wins" approach at the column level, but because it's built on Postgres, you can leverage standard relational techniques to manage complex state.

Furthermore, because PGLite is a real database, you can use transactions. This ensures that multi-table updates (like moving an item from one list to another) are atomic. If the sync fails or the browser crashes mid-update, your local data remains consistent.

Architectural Trade-offs

While the benefits of local-first are significant, as senior engineers, we must acknowledge the trade-offs.

1. Initial Sync Latency

When a user first logs in, the application needs to pull down the initial "Shape." Depending on the volume of data, this can take longer than a single REST request. Strategy: Keep Shapes granular and only sync what is visible on the current screen.

2. Storage Limits

Browsers impose limits on IndexedDB storage (usually a percentage of available disk space). While PGLite is efficient, you cannot replicate a 100GB dataset to a mobile browser. Local-first is best suited for user-specific data or frequently accessed subsets of global data.

3. Schema Migrations

Migrations become more complex when you have a distributed fleet of databases. You must ensure that your sync layer can handle version mismatches between the server schema and the local PGLite schema. Electric helps by providing tooling to manage these transitions, but it requires more forethought than a traditional centralized migration.

Why This Matters for the Business

From a technical decision-maker's perspective, the move to ElectricSQL and PGLite isn't just about "cool tech." It impacts the bottom line:

• Reduced Server Costs: By offloading query execution to the client's CPU and reducing the number of API calls, you can scale your backend with fewer resources.
• Improved User Retention: Performance is a feature. Apps that feel instantaneous and work on a plane or in a subway tunnel have a significant competitive advantage.
• Developer Productivity: Eliminating the need to write boilerplate API endpoints and complex cache management logic allows teams to focus on building features.

Actionable Conclusion

Implementing local-first with ElectricSQL and PGLite represents a fundamental shift in how we build for the web. It moves us away from the "loading spinner" culture toward applications that feel like native software.

To get started:

1. Identify a subset of your app that would benefit most from offline access or high reactivity (e.g., a settings panel, a task list, or a draft editor).
2. Experiment with PGLite in a standalone environment to understand the performance characteristics of Postgres-in-WASM.
3. Deploy an Electric sync service (using their open-source Docker image) and define your first "Shape."
4. Refactor your state management to treat the local DB as the source of truth, removing redundant API calls.

The tools are now mature enough that local-first is no longer a niche architectural pattern for collaborative whiteboards; it is a viable, robust choice for general-purpose web applications.