Local-First Architecture: Implementing ElectricSQL and SQLite for Zero-Latency Mobile Experiences

For the last decade, mobile development has been dominated by the 'Thin Client' architecture. Our apps are essentially windows into a remote server, where every interaction—a like, a comment, a status update—is gated by a network request. This 'Cloud-First' approach introduces a fundamental problem: latency. Even on high-speed 5G networks, the round-trip time to a data center can exceed 200ms, leading to a UI that feels sluggish or, worse, breaks entirely when the connection drops.

As a Senior Architect at TechSheet, I have seen a massive shift toward Local-First development. This paradigm treats the local device as the primary source of truth, utilizing a local database for all reads and writes, while an asynchronous background process handles synchronization with the cloud.

In this guide, we will dive deep into implementing a local-first stack using ElectricSQL, SQLite, and React Native (Expo) to build apps that are reactive, offline-capable, and incredibly fast.

The Anatomy of a Local-First Mobile App

In a traditional REST or GraphQL setup, your app state is a cached reflection of the server state. In a local-first setup, your app state is the local database. The architecture consists of three core components:

1. The Local Store: A persistent database on the device (SQLite) that handles all UI interactions.
2. The Replication Engine: A sync layer that tracks local changes and fetches remote changes.
3. The Cloud Database: A central authority (typically PostgreSQL) that aggregates data from all clients.

Why ElectricSQL?

While technologies like Realm or Couchbase have existed for years, ElectricSQL is a game-changer. It leverages PostgreSQL's logical replication to stream data changes directly to a local SQLite database. It provides 'Active-Active' replication, meaning the client and server can both write to the same rows, and the system automatically resolves conflicts using causal consistency and CRDT-like behaviors.

Setting Up the Infrastructure

To build a local-first app, we need to bridge the gap between our cloud Postgres instance and the mobile device's SQLite file. ElectricSQL provides a 'Sync Service' that sits between them.

1. Defining the Schema

ElectricSQL works by syncing your Postgres schema down to the mobile device. You define your tables in standard SQL or via a tool like Prisma. For a collaborative task app, our schema might look like this:

-- On your Postgres Backend
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) ON DELETE CASCADE,
  title TEXT NOT NULL,
  completed BOOLEAN DEFAULT false,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Expose tables to the Electric sync engine
ALTER TABLE projects REPLICA IDENTITY FULL;
ALTER TABLE tasks REPLICA IDENTITY FULL;

2. The React Native Integration

On the mobile side, we use the electric-sql library alongside expo-sqlite. The goal is to instantiate a client that manages the local SQLite file and maintains a WebSocket connection to the Electric sync service.

import { makeElectricContext } from 'electric-sql/react';
import { initElectric } from 'electric-sql/expo';
import { type Electric, schema } from './generated/client';

// Create the context for our React components
const { ElectricProvider, useElectric } = makeElectricContext<Electric>();

export const App = () => {
  const [db, setDb] = useState<Electric>();

  useEffect(() => {
    const setup = async () => {
      const config = {
        url: 'https://your-electric-service-url.com',
        // In a real app, use secure auth tokens
        token: 'dev-token'
      };

      const client = await initElectric('my_app_db.db', schema, config);
      setDb(client);
    };

    setup();
  }, []);

  if (!db) return <LoadingSpinner />;

  return (
    <ElectricProvider db={db}>
      <TaskDashboard />
    </ElectricProvider>
  );
};

Achieving Sub-Millisecond UI Updates

The magic happens when we interact with the data. Because we are writing to a local SQLite database, the UI update is instantaneous. There is no 'pending' state.

Reactive Hooks

ElectricSQL provides hooks that allow our components to subscribe to the local database. When the sync engine receives an update from another user via Postgres, the local database is updated, and the React hook triggers a re-render automatically.

const TaskList = ({ projectId }) => {
  const { db } = useElectric();
  
  // This hook creates a live query against the local SQLite
  const { results: tasks } = useLiveQuery(
    db.tasks.liveMany({
      where: { project_id: projectId },
      orderBy: { updated_at: 'desc' }
    })
  );

  const toggleTask = async (id: string, currentStatus: boolean) => {
    // Immediate write to local SQLite
    await db.tasks.update({
      data: { completed: !currentStatus, updated_at: new Date() },
      where: { id }
    });
    // Syncing to Postgres happens in the background automatically
  };

  return (
    <FlatList 
      data={tasks}
      renderItem={({ item }) => (
        <TaskItem task={item} onToggle={() => toggleTask(item.id, item.completed)} />
      )}
    />
  );
};

Deep Dive: Handling Conflict Resolution

A major hurdle in mobile development is what happens when two users edit the same record while one is offline. ElectricSQL handles this using Causal Integrity.

By default, it utilizes a Last-Write-Wins (LWW) resolution based on the updated_at timestamp, but because it is integrated into Postgres's logical replication, it preserves foreign key constraints and referential integrity during the sync. If a user deletes a project while another user is adding a task to it offline, Electric will handle the resolution based on your defined SQL cascades, ensuring the local SQLite state never drifts into an invalid state.

Real-World Use Case: Field Service Management

Imagine an app for elevator repair technicians. These technicians often work in basements or elevator shafts with zero connectivity.

1. Old Way: The technician tries to update a repair log, the request fails, they have to retry manually later or the app caches the request in an unstable AsyncStorage queue that might fail if the app crashes.
2. Local-First Way: The technician updates the record. The app works perfectly because it writes to SQLite. They can continue navigating the app, viewing schematics, and logging parts. As soon as they walk out of the building and hit 4G, the sync engine silently pushes the delta to the central Postgres server.

Performance Considerations

While local-first is powerful, it requires shifting your mindset regarding data management:

• Partial Replication: Do not sync your entire multi-terabyte Postgres DB to a phone. Use 'Shapes' in ElectricSQL to define exactly which subsets of data a user needs.
• Migrations: Schema changes must be handled carefully. Since the client has a physical database file, you need a robust migration strategy that ensures the local SQLite schema matches the expected shape of the sync engine.
• Battery and Data: Continuous sync can drain battery. Electric uses an efficient binary protocol over WebSockets, but for apps with very high-frequency updates, you may want to implement 'Sync Windows' or manual sync triggers.

Conclusion

The transition to local-first architecture marks the next evolution in mobile app development. By moving the data layer to the edge (the user's device), we eliminate the most significant bottleneck in UX: the network.

Tools like ElectricSQL are lowering the barrier to entry for this architecture, allowing developers to use familiar SQL patterns while gaining the benefits of real-time, offline-ready applications. If you are building a professional mobile app today, you should no longer ask 'How do I optimize my API calls?', but rather 'How do I sync my database?'

In the era of instant gratification, a loading spinner is a failure. Go local-first and give your users the speed they deserve.