Local-First Sync with ElectricSQL: Bridging Postgres and SQLite

The traditional request-response model is showing its age. In an era where users expect sub-millisecond feedback and seamless operation across spotty mobile connections, the latency inherent in the 'client-server-database' roundtrip is becoming a bottleneck. As engineers, we've spent years masking this latency with optimistic UI updates and complex state management libraries, but these are often just patches on a fundamentally synchronous architecture.

Enter the Local-First paradigm. Instead of treating the server as the primary source of truth for the UI, we treat a local, on-device database as the primary source. The UI reacts to the local database, and a synchronization engine handles the heavy lifting of keeping that local data in sync with a central cloud database.

ElectricSQL has emerged as a frontrunner in this space, providing a robust bridge between the world's most trusted relational database, PostgreSQL, and the ubiquitous edge database, SQLite. This article explores how to implement this architecture and why it represents a significant leap forward for application resilience and user experience.

The Architecture: Postgres at the Core, SQLite at the Edge

To understand ElectricSQL, we must first look at its architectural components. It isn't a new database; rather, it is a synchronization layer that sits between your central Postgres instance and your distributed SQLite clients.

1. Postgres (The Source of Truth): This remains your standard backend database. It holds the authoritative state, handles complex migrations, and integrates with your existing backend services.
2. The Electric Sync Service: This is the 'brain' of the operation. Written in Elixir for high concurrency, it connects to Postgres via logical replication. It monitors changes in Postgres and streams them to clients, while also accepting writes from clients and applying them back to Postgres.
3. SQLite (The Local Store): On the client side (web, mobile, or desktop), Electric uses SQLite (often via WA-SQLite in the browser). The application code interacts directly with SQLite using standard SQL or an ORM.

This setup provides a 'cloud-to-edge' replication stream. When a user makes a change, it is written instantly to the local SQLite database. The UI updates immediately. The Electric client then pushes that change to the Sync Service, which updates Postgres. Conversely, if another user updates the same data, Postgres notifies the Sync Service, which pushes the update to all relevant local SQLite instances.

Why This Matters: Beyond Just 'Offline Mode'

While offline capability is the most obvious benefit, it’s actually a secondary advantage compared to the architectural simplifications local-first provides:

• Zero Latency UI: Because the UI reads from a local SQLite file, queries are effectively instantaneous. There are no loading spinners for data fetching because the data is already there.
• Simplified State Management: You can stop juggling Redux, Vuex, or complex React Context providers for server state. Your database is your state. You query it, and the UI re-renders when the database changes.
• Resilience: If the server goes down or the user enters a tunnel, the app continues to function. The sync engine automatically resumes once connectivity is restored, handling the reconciliation without manual intervention.

Implementing ElectricSQL: A Practical Workflow

Implementing ElectricSQL involves three main phases: configuring your Postgres schema, running the Electric sync service, and integrating the client-side SDK.

1. Preparing the Postgres Schema

ElectricSQL works by 'electrifying' specific tables. You don't have to sync your entire database—only the parts that need to be available at the edge.

First, you must enable logical replication in your Postgres configuration (wal_level = logical). Then, you define your tables as usual. Electric requires that electrified tables have a primary key and that you enable a specific replication identity:

-- Create a standard table
CREATE TABLE projects (
  id UUID PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Electrify the table
ALTER TABLE projects ENABLE ELECTRIC;

2. The Sync Service and 'Shapes'

One of the biggest challenges in local-first is preventing the 'Big Bang' sync—where a new user's device tries to download 50GB of corporate data. ElectricSQL solves this through Shapes.

A Shape is a subset of the database defined by a query. It allows the client to subscribe only to the data it needs. For example, a user should only sync projects they own or are assigned to.

// Client-side shape subscription
const shape = await db.projects.sync({
  where: {
    owner_id: currentUser.id
  },
  include: {
    tasks: true
  }
});

await shape.synced; // Wait for the initial data load

This selective sync is what makes the architecture scalable. The Sync Service manages these subscriptions efficiently, ensuring that the client only receives relevant deltas.

3. Client-Side Integration

On the client, you use the Electric SDK to initialize the connection and generate a type-safe client based on your Postgres schema. If you're using TypeScript, this provides a developer experience similar to Prisma, but targeting the local SQLite store.

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

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

// Connect to the local SQLite and the Electric service
const { db } = await electrify(sqliteDb, schema, config);

// Use the DB as if it were a local object
const projects = await db.projects.findMany();

Handling Conflicts: The Reality of Distributed Systems

In a distributed system where multiple clients can write to the same record while offline, conflicts are inevitable. ElectricSQL handles this using Causal Integrity and a default Last-Write-Wins (LWW) resolution policy at the column level.

Because Electric tracks the causal history of updates, it can often resolve conflicts automatically. If User A updates the 'name' of a project and User B updates the 'description' simultaneously, both changes are merged. If they both update the 'name', the update with the later timestamp (using a hybrid logical clock) wins.

For more complex business logic where LWW is insufficient, you should design your schema to be additive (e.g., using a table of 'events' or 'comments' rather than a single 'status' field that gets overwritten).

Migrations in a Sync-Heavy World

Migrations are notoriously difficult in local-first apps. How do you update the schema on 10,000 distributed devices without breaking the sync?

ElectricSQL handles this by bundling migrations into the sync stream. When you apply a migration to Postgres, the Electric service detects it and propagates the DDL (Data Definition Language) changes to the clients. The clients apply these migrations to their local SQLite instances before processing any further data DML (Data Manipulation Language). This ensures that the client and server schemas stay in lockstep.

Security and Authorization

In a local-first world, you cannot rely on server-side middleware to check every 'request' because there are no requests—only data sync. Security must be handled at the sync layer.

ElectricSQL integrates with standard JWT-based authentication. You can define row-level security (RLS) policies in Postgres that Electric respects. When a client connects with a JWT, the Electric Sync Service uses the claims in that token to filter the data stream. If a user doesn't have permission to see a row in Postgres, that row will never be replicated to their local SQLite database.

Operational Considerations

Before moving to production with ElectricSQL, there are a few operational realities to consider:

1. Storage Constraints: Mobile devices have limited storage. Use Shapes aggressively to ensure you aren't bloating the user's device with unnecessary data.
2. Initial Sync Time: The first time a user opens the app, they need to download their 'Shape'. Optimize your queries to keep this initial payload small.
3. Conflict Monitoring: While LWW handles most cases, you should log conflict resolutions on the server to identify if users are frequently overwriting each other's work, which might indicate a need for a different UI flow (like locking or explicit merging).

Practical Example: Collaborative Field Service App

Imagine a field service application for technicians repairing utility poles in remote areas.

• Without ElectricSQL: The technician tries to open a work order, sees a loading spinner, and eventually a 'Connection Error' message. They have to take notes on paper and manually enter them when they return to the office.
• With ElectricSQL: The technician opens the app. All assigned work orders for the day were synced to their device while they were at the office. They update the status, upload photos, and log parts used—all while completely offline. The UI is instant. As they drive back into cellular range, the app silently syncs the changes back to the central Postgres database, notifying the dispatch team in real-time.

In this scenario, the developer didn't have to write a single line of code for 'retry logic,' 'offline caching,' or 'background sync.' They just wrote to a database.

Conclusion: The Actionable Path Forward

Local-first is no longer a niche requirement for 'offline-only' apps; it is a superior architecture for building any high-performance web or mobile application. By moving the data closer to the user, you eliminate the biggest source of friction in modern software: the network.

To get started with ElectricSQL:

1. Audit your data: Identify which parts of your Postgres schema would benefit from being available at the edge.
2. Prototype a 'Shape': Define a small subset of data and try syncing it to a local SQLite instance using the Electric CLI.
3. Refactor one feature: Take a high-latency part of your app (like a complex search or a multi-step form) and rebuild it using a local-first approach.

The shift from 'fetching data' to 'syncing data' requires a change in mindset, but the result is an application that feels faster, works everywhere, and is significantly more resilient.