Mastering Local-First Sync with ElectricSQL and Postgres

The paradigm of web and mobile application development is shifting. For years, we have conditioned ourselves to accept the 'request-response' cycle as an immutable law of the internet. We build a UI, it makes a fetch request to an API, the API queries a database, and the result travels back across the wire. When the connection is slow, the user sees a spinner. When the connection is gone, the app breaks.

Local-first software aims to change this by treating the local device—the phone, the laptop, the browser—as a primary data store rather than a mere cache. By leveraging ElectricSQL to synchronize Postgres with a local SQLite database, we can build applications that are instantly responsive, offline-capable by default, and architecturally simpler.

The Architecture of Local-First Sync

To understand ElectricSQL, we must first understand the architectural gap it fills. In a traditional setup, your state lives in a centralized Postgres instance. In a local-first setup, your state lives in an embedded SQLite database within the client application.

ElectricSQL acts as a synchronization layer that sits between your central Postgres database and these distributed SQLite instances. It utilizes Postgres's logical replication features to stream changes from the cloud to the edge and vice versa.

The Sync Service

The core component is the Electric Sync Service. It connects to your Postgres database and monitors the Write-Ahead Log (WAL). When a change occurs in Postgres, the Sync Service identifies which clients need that data and pushes the updates. Conversely, when a client writes to its local SQLite database, the Electric client library bundles those changes and sends them to the Sync Service, which then applies them to Postgres.

This architecture provides three major benefits:

1. Zero Latency: Reads and writes happen against the local SQLite file. There is no network round-trip on the critical path of a user action.
2. Built-in Offline Support: Because the app operates on a local database, it works perfectly without an internet connection. Syncing happens automatically in the background when connectivity returns.
3. Simplified State Management: You no longer need complex Redux or TanStack Query logic to manage server state. Your local database is your state.

Defining Data Shapes

One of the most powerful concepts in ElectricSQL is 'Shapes.' In a production environment, you cannot—and should not—replicate your entire multi-terabyte Postgres database to every user's mobile phone.

Shapes allow you to define a subset of your database schema and data that should be synced to a specific client. This is done using a declarative API. For example, in a project management tool, a user only needs the projects they are a member of and the tasks within those projects.

// Example of defining a shape in the client
const { data } = useShape({
  table: 'tasks',
  where: { project_id: currentProjectId },
  include: {
    comments: true,
    assignees: true
  }
});

This 'Shape' mechanism ensures that replication is efficient, preserving both bandwidth and local storage space. The Sync Service handles the heavy lifting of filtering the Postgres replication stream to match these shapes.

Setting Up the Infrastructure

Implementing ElectricSQL starts with your Postgres schema. Electric requires standard Postgres tables, but you must enable logical replication and create a publication for Electric to consume.

1. Postgres Configuration

Your Postgres instance needs wal_level = logical. Once configured, you define your tables as you normally would. Electric uses a tool called electric-sql to generate a type-safe client based on your schema.

-- A standard Postgres table
CREATE TABLE items (
  id UUID PRIMARY KEY,
  content TEXT NOT NULL,
  completed BOOLEAN DEFAULT false,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

-- Enable replication for Electric
ALTER TABLE items REPLICA IDENTITY FULL;

2. The Sync Service

You can run the Electric Sync Service via Docker. It requires a connection string to your Postgres database and a few configuration environment variables. It acts as the traffic controller, managing the subscriptions of thousands of concurrent SQLite clients.

3. Client-Side Integration

On the client side (React, Vue, or vanilla JS/TS), you initialize the Electric client. This client manages the local SQLite database (using Wasm in the browser or native drivers on mobile) and the WebSocket connection to the Sync Service.

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

const config = {
  url: 'http://localhost:5133',
};

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

Handling Conflict Resolution and Consistency

In a distributed system where multiple users can edit the same data offline, conflicts are inevitable. ElectricSQL handles this through a combination of Causal Integrity and Last-Write-Wins (LWW) semantics at a granular level.

Unlike complex Operational Transformation (OT) or manual CRDT implementations that require you to rewrite your entire data model, ElectricSQL provides 'Rich-CRDT' behavior automatically. It tracks the causal history of updates. If User A and User B both update the same row while offline, Electric uses the causal metadata to ensure that all nodes eventually converge on the same state once they sync.

For developers, this means you can write standard SQL INSERT and UPDATE statements. You don't have to worry about the 'split-brain' scenarios that typically plague custom-built sync engines.

Real-World Use Case: Field Service Applications

Consider a field service application used by technicians repairing utility infrastructure in remote areas.

The Problem: Technicians often work in basements or rural locations with zero cell service. A traditional web app fails here. A 'cached' app might let them see their schedule, but they can't easily update work orders or add photos without complex manual reconciliation logic.

The ElectricSQL Solution:

1. Initial Sync: When the technician starts their shift at the office (on Wi-Fi), the app syncs the 'Shape' of their assigned work orders for the day into a local SQLite database.
2. Offline Work: Throughout the day, the technician updates statuses, adds notes, and records parts used. Every change is a local SQL transaction. The UI is instantaneous.
3. Background Sync: As the technician drives between sites and moves in and out of cell coverage, the Electric client quietly pushes updates to the cloud and pulls down any schedule changes made by the dispatcher.
4. Conflict Resolution: If a dispatcher reassigns a task at the same time the technician marks it as 'In Progress,' Electric’s causal consistency ensures the final state of the record is predictable and consistent across the system.

Technical Deep Dive: Transactional Integrity

One of the most difficult aspects of sync is maintaining referential integrity. If you sync a task but the project it belongs to hasn't arrived yet, your application logic might crash.

ElectricSQL solves this by guaranteeing transactional consistency during replication. It ensures that if a set of changes were part of a single transaction in Postgres, they are applied as a single transaction in the local SQLite database. This 'all-or-nothing' approach to syncing batches of data prevents the 'partial state' bugs that are common in home-grown sync solutions using WebSockets or Firebase.

Deployment and Scaling Considerations

When moving to production with ElectricSQL, there are several key factors to consider:

Security and Row-Level Security (RLS)

ElectricSQL integrates with Postgres Row-Level Security. This is critical because you must ensure that User A cannot subscribe to a 'Shape' that contains User B's private data. The Sync Service respects the RLS policies defined in your Postgres schema, ensuring that data is filtered at the source before it ever reaches the wire.

Migrations

Schema migrations in a local-first world are notoriously tricky. When you change your Postgres schema, you have thousands of SQLite databases 'in the wild' that also need to change. Electric provides tools to manage this evolution, allowing you to bundle migrations with your application code so that the local SQLite schema stays in sync with the expected client-side types.

Resource Usage

While SQLite is lightweight, running a full database in a browser tab does have a memory footprint. For most applications, this is negligible (a few megabytes), but for data-heavy applications, careful management of 'Shapes' is necessary to ensure the browser's persistent storage limits aren't exceeded.

Conclusion: The Actionable Path Forward

Local-first isn't just a performance optimization; it’s a fundamental improvement in how we build resilient software. By moving the data layer to the edge, we eliminate the most significant bottleneck in modern web apps: the network.

To get started with ElectricSQL, I recommend the following steps:

1. Audit your data access patterns: Identify which parts of your application would benefit most from zero-latency interactions (e.g., forms, editors, dashboards).
2. Start Small: Implement ElectricSQL for a single feature—like a notification feed or a task list—rather than refactoring your entire backend.
3. Leverage Existing Tools: Use the electric-sql CLI to generate your client from your existing Postgres schema. This minimizes the boilerplate and provides immediate type safety.
4. Test Offline Scenarios: Use your browser's 'Network Throttling' and 'Offline' modes early in development to experience the seamless transition that a local-first architecture provides.

The era of the loading spinner is ending. By embracing Postgres-to-SQLite replication, we can finally build applications that feel as fast and reliable as local desktop software, while retaining the collaborative power of the cloud.