Local-First Apps with Electric SQL and PGLite: A Technical Guide

For years, the standard architecture for web applications has been the 'Thin Client, Thick Server' model. We treat the browser as a glorified terminal, fetching data over REST or GraphQL and showing a loading spinner while waiting for the round-trip. When the connection drops, the application breaks.

Local-first software challenges this status quo. By moving the primary data store to the client and synchronizing with the server in the background, we can build applications that are instantly responsive, work offline by default, and simplify state management. Two technologies are currently leading this charge in the PostgreSQL ecosystem: Electric SQL and PGLite.

The Architecture of Local-First

In a traditional setup, your frontend state is a cache of the server's database. In a local-first setup, the frontend state is a database.

This shift requires three specific components to work in harmony:

1. A Client-Side Database: A persistent store on the user's device that supports relational queries.
2. A Sync Engine: A service that sits between the client and the server to handle data movement, conflict resolution, and consistency.
3. A Server-Side Database: The 'Source of Truth' for long-term storage and cross-user collaboration.

By using PGLite as the client-side database and Electric SQL as the sync engine, we can use PostgreSQL across the entire stack.

Why PGLite? The Power of Postgres in WASM

Until recently, if you wanted a database in the browser, you were limited to IndexedDB or a WASM-compiled version of SQLite. While SQLite is excellent, it creates a 'dialect gap'—your server speaks Postgres, but your client speaks SQLite. This leads to friction in schema management and query logic.

PGLite is a breakthrough. It is a build of the actual Postgres source code compiled to WebAssembly, packaged as a lightweight TypeScript library. It allows you to run a full Postgres instance inside a browser tab, a service worker, or a Node.js environment.

Key Advantages of PGLite:

• Feature Parity: You get access to Postgres features like JSONB, Full-Text Search, and complex Common Table Expressions (CTEs) directly in the client.
• No Installation: It doesn't require a background process; it's just a library you import.
• Small Footprint: Despite being full Postgres, it is optimized to be around 3MB gzipped.
• Persistence: It can persist data to IndexedDB, ensuring that data survives page refreshes.

The Role of Electric SQL

Running a database locally is only half the battle. The harder half is synchronization. How do you handle multiple users editing the same record while offline? How do you ensure a mobile client doesn't try to download a multi-terabyte production database?

Electric SQL solves this. It provides a synchronization layer that streams data between your central Postgres database and local PGLite instances.

How Electric Works:

• Shapes: Electric uses a concept called 'Shapes.' A shape is a subset of your database—specific tables and filtered rows—that a client subscribes to. This prevents the client from being overwhelmed by data it doesn't need.
• Causal Consistency: Electric ensures that updates are applied in the correct order across all clients, maintaining the integrity of your relational data.
• Conflict Handling: It uses Last-Write-Wins (LWW) by default but is built on a foundation that supports more complex conflict resolution patterns.

Implementing the Stack: A Practical Example

Let’s look at how we would implement a collaborative project management tool. We want users to be able to create tasks, assign them, and update statuses, even if their Wi-Fi is spotty.

1. Setting up the Server

First, you need a standard Postgres database. Electric SQL connects to this database via logical replication. You define your schema as usual:

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

CREATE TABLE tasks (
  id UUID PRIMARY KEY,
  project_id UUID REFERENCES projects(id),
  title TEXT NOT NULL,
  status TEXT DEFAULT 'todo',
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Enable replication for Electric
ALTER TABLE projects REPLICA IDENTITY FULL;
ALTER TABLE tasks REPLICA IDENTITY FULL;

2. Initializing PGLite in the Client

In your frontend application (e.g., a React or Vue app), you initialize PGLite. We want to ensure the data persists in IndexedDB.

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

// Initialize PGLite with IndexedDB persistence
const db = new PGLite('idb://my-app-db');

async function setup() {
  await db.exec(`
    CREATE TABLE IF NOT EXISTS projects (id UUID PRIMARY KEY, name TEXT);
    CREATE TABLE IF NOT EXISTS tasks (id UUID PRIMARY KEY, project_id UUID, title TEXT, status TEXT);
  `);
}

3. Connecting the Sync Engine

Now, we use the Electric SDK to bridge our local PGLite instance with the remote server. We define a 'shape' to sync only the tasks for a specific project.

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

const projectShape = {
  table: 'tasks',
  where: { project_id: 'some-uuid-123' }
};

// Start the sync process
const { stop } = await syncShape(db, {
  url: 'https://your-electric-service.com',
  shape: projectShape
});

4. Reactive UI Queries

Because the database is local, your UI components can query it directly. Instead of a useEffect that fetches from an API, you use a reactive hook that listens to the local database.

// Example of a reactive hook logic
function useTasks(projectId: string) {
  const [tasks, setTasks] = useState([]);

  useEffect(() => {
    const liveQuery = db.live.query(
      'SELECT * FROM tasks WHERE project_id = $1 ORDER BY updated_at DESC',
      [projectId],
      (results) => setTasks(results.rows)
    );
    return () => liveQuery.unsubscribe();
  }, [projectId]);

  return tasks;
}

Addressing Conflict Resolution and Consistency

One of the biggest hurdles in local-first is conflict resolution. If User A and User B both edit a task title while offline, what happens when they reconnect?

Electric SQL uses Causal Sequence Consistency. It tracks the causal dependencies of operations. If User B's change happened after they had seen User A's change, User B's change prevails. If they happened concurrently, Electric uses a deterministic conflict resolution strategy (typically Last-Write-Wins based on a hybrid logical clock).

For developers, this means you don't have to write complex merge logic for every table. You get a 'distributed' database experience that feels like working with a single local instance.

Performance and User Experience Benefits

When you move the database to the client, the 'Perceived Performance' of your app skyrockets:

1. Zero Latency UI: Every 'Save' button click results in an immediate local SQL INSERT or UPDATE. The UI updates instantly because it's reading from the local disk/memory, not waiting for a 200ms round-trip to a data center.
2. Offline Productivity: Users can keep working in a tunnel, on a plane, or in a basement. When the connection returns, Electric silently pushes the local changes and pulls new ones.
3. Reduced Server Load: Your primary database no longer handles every single 'Read' request for every UI component. The server becomes a synchronization hub rather than a bottleneck for every interaction.

Security Considerations

A common question is: "If the whole database is in the browser, isn't that a security risk?"

Local-first doesn't mean all data is in the browser. It means the user's data is in the browser. Electric SQL integrates with Postgres Row Level Security (RLS). When a client requests a 'shape', the Electric sync service authenticates the user (typically via JWT) and ensures they only receive the rows they are permitted to see. The local PGLite instance only ever contains data the user is authorized to access.

The Trade-offs

No architecture is a silver bullet. Local-first introduces new challenges:

• Storage Limits: Browsers impose limits on IndexedDB (though they are usually generous, often up to 80% of disk space). You must be mindful of how much data you sync.
• Schema Migrations: Migrating a schema across thousands of disconnected client databases is harder than migrating a single central server. Electric provides tooling to help manage this, but it requires more planning than a traditional setup.
• Initial Sync: The first time a user opens the app, they may need to download a significant amount of data to populate the local database.

Conclusion and Actionable Steps

The combination of Electric SQL and PGLite represents a massive leap forward for the web. We are moving away from the 'Request/Response' cycle and toward a 'Synchronized State' model. This results in apps that feel 'solid'—they don't break when the network flutters, and they respond to user input with zero lag.

To get started with this stack:

1. Audit your data: Identify which parts of your application would benefit most from offline access or zero-latency interactions.
2. Experiment with PGLite: Replace a complex Redux or TanStack Query cache with a local PGLite instance to see how it simplifies your frontend logic.
3. Deploy an Electric instance: Use the Electric SQL Docker image to connect to an existing Postgres DB and start experimenting with 'Shapes' to sync data to your PGLite client.

Local-first is no longer a niche requirement for 'collaborative docs'—it is becoming the standard for high-quality, professional web applications.