Local-First Sync with ElectricSQL: Postgres to SQLite at the Edge

Building responsive web applications has traditionally meant a constant battle against network latency. We’ve spent a decade perfecting loading spinners, optimistic UI updates, and complex cache invalidation logic. But no matter how much we optimize our REST or GraphQL endpoints, we are still tethered to the 'request-response' cycle. If the network drops, the app breaks. If the server is 200ms away, the user feels it.

Local-first software represents a paradigm shift. Instead of treating the server as the primary data source and the client as a temporary cache, local-first treats the local database as the primary source of truth. ElectricSQL is emerging as a frontrunner in this space, providing a bridge between the reliability of Postgres and the ubiquity of SQLite at the edge.

The Architecture of Local-First

In a traditional cloud-centric model, the UI waits for the server to acknowledge a write before updating. In a local-first model, the UI writes directly to a local database (usually SQLite via Wasm in the browser). This write is instant. A background synchronization service then handles the heavy lifting of replicating that change to the server and other clients.

ElectricSQL facilitates this by sitting between your Postgres database and your client-side SQLite database. It leverages Postgres's logical replication to stream changes in real-time.

The Core Components

1. Postgres: Your central authority and long-term storage.
2. Electric Sync Service: An Elixir-based middleware that manages the replication protocol, handles permissions, and keeps track of which client needs what data.
3. Electric Client SDK: A library that integrates with your frontend (React, Vue, etc.) to manage the local SQLite instance and the sync connection.

Why ElectricSQL? The Power of Active Replication

Many developers attempt to build 'offline mode' using manual synchronization logic or complex Redux-persist setups. This almost always leads to edge cases where data becomes inconsistent. ElectricSQL solves this through Active Cloud-to-Edge Replication.

Unlike traditional caching, where you fetch specific resources, ElectricSQL allows you to define "Shapes." A Shape is a subset of your database—specific tables and related records—that should be kept in sync on the client. Once a Shape is defined, the Electric Sync Service ensures that every insert, update, or delete in Postgres that matches that Shape is automatically pushed to the client's local SQLite database.

Practical Implementation: A Collaborative Task Manager

To understand how this works in practice, let’s look at a simplified implementation of a collaborative task manager.

1. Database Schema (Postgres)

First, we define our schema in Postgres. ElectricSQL requires a few standard practices, such as having primary keys on all tables and enabling logical replication.

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

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

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

2. Setting up the Client

On the frontend, we initialize the Electric client. In a web environment, this will typically use a Wasm-based SQLite driver like wa-sqlite or Electric's own PGLite for a lightweight Postgres-in-the-browser experience.

import { electrify } from 'electric-sql/wa-sqlite';
import { schema } from './generated/client'; // Generated from your DB schema

const config = {
  url: 'proxy://localhost:5133', // Electric Sync Service URL
};

const db = await electrify(conn, schema, config);

3. Syncing Data with Shapes

This is where the magic happens. Instead of calling fetch('/api/todos'), we tell Electric which data we want to keep locally.

const { stop } = await db.sync({
  tables: {
    projects: true,
    todos: {
      where: 'project_id = \'some-uuid\''
    }
  }
});

Once this call is made, the local SQLite database begins populating. From this point forward, your application logic interacts exclusively with the local db.

4. Querying and Mutating

Because the data is local, queries are instantaneous. You can use standard SQL or the Electric type-safe client.

// This query runs against the local SQLite DB
const todos = await db.todos.findMany({
  where: { completed: false }
});

// This write is also local and instant
await db.todos.create({
  data: {
    id: genUUID(),
    content: 'Learn ElectricSQL',
    project_id: 'some-uuid'
  }
});

The Electric background process detects the change in the local SQLite file and asynchronously pushes it to the Electric Sync Service, which then applies it to the global Postgres instance.

Conflict Resolution: The Elephant in the Room

When multiple users edit the same data offline, conflicts are inevitable. ElectricSQL handles this using Last-Write-Wins (LWW) at the column level by default.

If User A updates a task's title while offline, and User B updates the same task's description, ElectricSQL is smart enough to merge these changes because they affect different columns. If they both update the title, the one with the later timestamp wins. While LWW is sufficient for many use cases, more complex scenarios can be handled by designing your schema with CRDT (Conflict-free Replicated Data Type) principles in mind, such as using append-only event logs.

Beyond Performance: The UX of Reliability

Local-first isn't just about speed; it's about reliability. Consider a field service technician using an app in a basement with spotty LTE. In a traditional app, every 'Save' button click is a gamble. In a local-first app built with ElectricSQL, the technician has the full context of their work available locally, and their updates are saved to disk immediately. The app handles the 'syncing' indicator in the status bar, freeing the user from worrying about connectivity.

Security and Permissions

One common concern with replicating data to the edge is security. You don't want a user's local SQLite database to contain data they aren't authorized to see.

ElectricSQL addresses this through a robust permissions system that integrates with your existing authentication provider (like Auth0, Clerk, or custom JWTs). You define rules in a permissions.sql file or via the Electric dashboard that dictate which rows a user is allowed to sync based on their JWT claims. This ensures that the "Shape" of data sent to the client is always filtered by the user's authorization level.

Challenges and Considerations

While ElectricSQL significantly simplifies local-first development, it is not a silver bullet. Engineers should be aware of the following:

• Initial Sync Latency: The first time a user opens the app, the initial sync of the required Shapes can take time depending on the data volume. Efficient Shape definition is critical.
• Storage Limits: Browsers have limits on how much data can be stored in IndexedDB (which SQLite usually uses as a backing store). Large datasets may require aggressive cleanup or more granular Shapes.
• Schema Evolutions: Migrating a local-first schema requires care. You need to ensure that the server-side Postgres migrations and the client-side SQLite expectations remain in sync.

Comparison with Alternatives

How does ElectricSQL compare to other players in the space?

• Replicache: Excellent for high-performance UI but requires you to write custom backend 'mutators' and 'push' endpoints. ElectricSQL automates more of the backend plumbing by tapping directly into Postgres.
• RxDB: A great NoSQL option that works with various backends. ElectricSQL is better suited for teams that want to stay within the relational/SQL ecosystem.
• PowerSync: Very similar to ElectricSQL in philosophy. The choice often comes down to specific feature sets (like PowerSync's support for more backend types) versus Electric's deep Postgres integration and open-source Elixir core.

Conclusion: The Path Forward

The transition to local-first architecture is a natural evolution of the web. As we push more processing power to the edge, the bottleneck remains the network. ElectricSQL provides a professional-grade bridge to cross that gap without discarding the Postgres tools we already know and trust.

To get started, I recommend the following steps:

1. Identify a high-latency feature: Find a part of your app where loading spinners are hurting the UX (e.g., a search filter or a complex form).
2. Prototype a Shape: Use the ElectricSQL CLI to generate a client for that specific subset of your data.
3. Benchmark the difference: Measure the interaction latency between a traditional API call and a local SQLite query. The results are usually an order of magnitude faster.

Local-first is no longer a niche requirement for 'offline' apps; it is the new standard for 'fast' apps.