Building Resilient Apps: Local-First Architecture with ElectricSQL and PostgreSQL on a VPS
Introduction to the Local-First Revolution
In the modern digital landscape, user experience is heavily dictated by latency and connectivity. Traditional web and mobile applications rely on a request-response paradigm, where every user action must travel to a remote server, await database execution, and return to the client. When connectivity drops or network congestion occurs, the application grinds to a halt, leading to frustrating loading spinners and potential data loss.
Local-First architecture fundamentally redefines this relationship. Instead of treating the server as the primary runtime environment, local-first applications store data locally on the user's device first (using embedded databases like SQLite or IndexedDB). The application remains fully functional offline, executing reads and writes at memory speed. Synchronization with a central cloud database happens asynchronously in the background. In this comprehensive guide, we will explore how to implement this cutting-edge architecture using ElectricSQL to synchronize real-time data between client devices and a standard PostgreSQL instance hosted on a Virtual Private Server (VPS).
---Understanding Local-First Architecture
To appreciate the value of ElectricSQL, it is essential to understand what makes an application truly "local-first." As coined by researchers at Ink & Switch, local-first software combines the productivity of local desktop tools with the collaboration capabilities of cloud apps.
Core Principles of Local-First Applications
- Fast as Thought: Operations happen instantly on local state without waiting for a network round-trip.
- Multi-Device Sync: Data seamlessly syncs across the user’s phone, tablet, and laptop.
- Offline Capabilities: Being disconnected from the internet is a normal state, not an error condition.
- Conflict Resolution: When multiple devices modify the same data offline, the system merges changes cleanly without destroying user input.
Achieving these principles manually is notoriously difficult, historically requiring complex Conflict-Free Replicated Data Types (CRDTs) or fragile custom sync engines. This is where ElectricSQL enters the framework.
---What is ElectricSQL?
ElectricSQL is an open-source sync layer for building local-first applications. It bridges the gap between client-side databases (like SQLite in web or mobile apps) and server-side relational databases (specifically PostgreSQL).
ElectricSQL utilizes active-active replication based on CRDT math to handle concurrent writes automatically. It ensures that data converging from thousands of offline clients merges deterministically on your central PostgreSQL database.
By leveraging PostgreSQL's native Logical Replication capabilities, ElectricSQL captures changes in real-time, filters them based on user permissions, and streams them efficiently to local client databases via WebSockets.
---Architectural Overview: Client to VPS-hosted PostgreSQL
When deploying a local-first stack independently on a VPS, the architecture comprises three main layers:
- The Central Database Layer: A standard PostgreSQL instance running on your VPS, equipped with standard logical replication (wal_level = logical).
- The Sync Layer (Electric Service): A lightweight Elixir-based container running on the same VPS (or a managed cluster) that communicates directly with Postgres via replication slots.
- The Client Layer: The frontend application (Web, React Native, Expo, etc.) using the Electric client SDK, binding an embedded SQLite database directly to the user interface.
When a user updates a record offline, the local SQLite database accepts the change immediately, updating the UI in microseconds. Once the device regains connection, the Electric SDK streams the delta changes to the Electric Service on your VPS, which safely applies them to your central PostgreSQL database.
---Step-by-Step Guide: Setting Up the Stack on a VPS
Let us walk through the process of provisioning your VPS, configuring PostgreSQL, and launching ElectricSQL to enable real-time replication.
Step 1: Configuring PostgreSQL on your VPS
ElectricSQL requires access to PostgreSQL\'s write-ahead log (WAL) to track data mutations. Ensure your PostgreSQL configuration file (postgresql.conf) includes the following critical settings:
wal_level = logical
max_wal_senders = 10
max_replication_slots = 10After modifying these parameters, restart your PostgreSQL service to apply the changes. Next, create a dedicated database and user account with replication privileges for the Electric sync engine:CREATE USER electric WITH PASSWORD 'YourSecurePassword' REPLICATION;
CREATE DATABASE local_first_db OWNER electric;
Step 2: Deploying the Electric Service via Docker Compose
The most efficient way to run ElectricSQL on a VPS is via Docker. Below is a production-ready docker-compose.yml snippet to connect Electric to your local Postgres instance:version: '3.8'
services:
electric:
image: electricsql/electric:latest
environment:
- DATABASE_URL=postgresql://electric:YourSecurePassword@postgres_host:5432/local_first_db
- ELECTRIC_WRITE_TO_PG_MODE=direct
- AUTH_MODE=insecure # Replace with JWT authentication for production
ports:
- "5133:5133"
restart: always
Run docker compose up -d to initialize the synchronization engine. Electric will automatically connect to your database, establish a logical replication slot, and begin listening for schema changes.
Step 3: Defining and Electrifying Your Database Schema
For ElectricSQL to sync a table, you must explicitly "electrify" it. This tells the sync engine to track mutations and generate corresponding client-side types. Consider a simple project management table:CREATE TABLE tasks (
id UUID PRIMARY KEY,
title TEXT NOT NULL,
is_completed BOOLEAN DEFAULT false,
updated_at TIMESTAMPTZ NOT NULL
);
-- Electrify the table
ALTER TABLE tasks ENABLE ELECTRIC;---
Integrating the Client-Side SDK
With the backend infrastructure live on your VPS, you can now instantiate the client-side local-first sync. In a JavaScript/TypeScript environment, install the necessary dependencies:npm install @electric-sql/pglite wa-sqlite electric-sql
Initialize the local embedded database and establish the live synchronization stream using the code block below:import { instantiate } from 'electric-sql/browser';
import { schema } from './generated/client'; // Generated via electric-sql CLI
const config = {
url: 'ws://your-vps-ip:5133'
};
// Initialize local SQLite and connect to your VPS
const { db } = await instantiate(schema, config);
// Start syncing data locally
const shape = await db.tasks.sync();
await shape.synced;
Your application can now query the local database directly. When network connection drops, queries like db.tasks.findMany() continue working flawlessly from local memory, and db.tasks.create() operations queue automatically until connectivity is restored.
Key Operational Considerations for Business Applications
Transitioning to a local-first architecture introduces architectural paradigms that enterprise decision-makers and technical leads must account for:
- Security and Row-Level Filtering: You rarely want to sync an entire corporate database to a single user\'s phone. ElectricSQL handles this via "Shapes," which allow clients to subscribe to specific subsets of data relevant only to their permissions or assigned projects.
- Conflict Resolution Realities: ElectricSQL employs Last-Write-Wins (LWW) element-set CRDTs by default. Designing schema structures with high-precision timestamp tracking (e.g.,
updated_at) ensures consistent, deterministic states across all global clients. - VPS Resource Management: Since Electric streams data over WebSockets, network I/O and memory consumption scale with the number of concurrent active connections. Ensure your VPS sizing accounts for persistent WebSocket overhead, utilizing reverse proxies like Nginx or Caddy to handle SSL/TLS termination cleanly.
Conclusion: Why Local-First Matters for the Future
Adopting a Local-First architecture using ElectricSQL and PostgreSQL provides businesses with a formidable competitive edge. By decoupling application responsiveness from internet quality, companies can deliver software that feels instantaneously responsive, functions reliably in low-connectivity environments, and minimizes server-side processing overhead.
Deploying this stack on an independent VPS strikes an ideal balance between cost-efficiency, data sovereignty, and cutting-edge performance. As modern user expectations gravitate toward instant interaction, local-first engineering stands out as the next logical evolution in scalable application design.