Building a Local-First App Infrastructure with ElectricSQL and PostgreSQL on a VPS

Introduction to the Local-First Revolution

In the modern digital landscape, user expectation for application responsiveness is at an all-time high. Traditional cloud-first architectures, which rely on continuous round-trips to a centralized server for every user action, frequently suffer from latency issues and complete failure during network outages. To solve these fundamental flaws, a new architectural paradigm has emerged: Local-First software.

Local-First applications prioritize local data storage on the client device while leveraging background synchronization to keep the centralized database up to date. This ensures that the application remains fully functional, ultra-fast, and responsive, even when completely offline. In this comprehensive guide, we will explore how to architect and deploy a robust Local-First infrastructure using ElectricSQL and PostgreSQL hosted on a virtual private server (VPS).

Why Choose ElectricSQL and PostgreSQL?

Building a synchronization engine from scratch is notoriously difficult, often requiring complex conflict-free replicated data types (CRDTs) and intricate state-handling logic. ElectricSQL simplifies this pipeline by acting as a high-performance replication layer between your backend PostgreSQL database and frontend client storage.

Key Benefits of the Stack

• True Offline Capability: Users can read, write, and modify data without an active internet connection. Changes are captured locally and synced instantly once connectivity is restored.
• Sub-millisecond Latency: Because operations happen against a local database (like SQLite or an in-memory store), the user interface updates instantly, eliminating loading spinners.
• PostgreSQL Power: You maintain the full power, reliability, and ecosystem of PostgreSQL on the backend, ensuring data integrity and advanced querying capabilities.
• Cost-Effective Hosting: By deploying this architecture on a standard VPS, you retain full ownership of your data infrastructure without incurring the unpredictable costs of proprietary cloud sync services.

---

Architectural Overview

Before diving into the configuration, it is essential to understand how data flows through a Local-First architecture powered by ElectricSQL:

1. The Client Store: The frontend application interacts directly with an embedded local database (typically SQLite embedded via WebAssembly or native mobile bindings).
2. The ElectricSQL Satellite: A lightweight client-side agent that monitors local database changes and communicates with the sync service.
3. The ElectricSQL Sync Service: A server-side component running on your VPS that manages replication streams, enforces security rules, and handles conflict resolution.
4. The PostgreSQL Database: The central source of truth enhanced with logical replication features to stream changes back and forth with the ElectricSQL Sync Service.

Local-first is not just about offline support; it is about shifting the source of truth closer to the user to deliver a fundamentally better user experience.

---

Step-by-Step VPS Deployment Guide

Let us walk through the process of setting up this infrastructure on a clean Ubuntu VPS. We will use Docker and Docker Compose to ensure a reproducible and isolated environment.

Step 1: Preparing PostgreSQL for Logical Replication

ElectricSQL relies on PostgreSQL's logical replication capabilities to track database mutations. First, you must configure your PostgreSQL instance to support this feature. Modify your postgresql.conf file with the following settings:

wal_level = logical
max_wal_senders = 10
max_replication_slots = 10

After updating the configuration, restart your PostgreSQL service to apply the changes. Next, ensure you have a dedicated database and a user account with replication privileges configured.

Step 2: Configuring the Docker Compose Environment

Create a docker-compose.yml file on your VPS to orchestrate the PostgreSQL database and the ElectricSQL sync service container. Below is an optimized configuration blueprint:

version: '3.8'

services:
postgres:
image: postgres:15-alpine
environment:
POSTGRES_DB: local_first_db
POSTGRES_USER: electric_admin
POSTGRES_PASSWORD: SecurePassword123
command: ["postgres", "-c", "wal_level=logical"]
ports:
- "5432:5432"
volumes:
- pgdata:/var/lib/postgresql/data

electric:
image: electricsql/electric:latest
environment:
DATABASE_URL: postgresql://electric_admin:SecurePassword123@postgres:5432/local_first_db
AUTH_JWT_ALG: HS256
AUTH_JWT_KEY: YourSuperSecretJWTKeyHere
ports:
- "5133:5133"
- "8080:8080"
depends_on:
- postgres

volumes:
pgdata:

Step 3: Initializing the Database Schema

With the infrastructure running, you must define your database schema and explicitly instruct ElectricSQL which tables should be enabled for replication. Connect to your PostgreSQL instance and execute your DDL statements, followed by the Electric activation commands:

CREATE TABLE items (
id UUID PRIMARY KEY,
title TEXT NOT NULL,
completed BOOLEAN NOT NULL DEFAULT false,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL
);

-- Enable ElectricSQL replication for the table
ALTER TABLE items ENABLE ELECTRIC;

---

Connecting the Frontend Application

Once the server infrastructure is live on your VPS, configuring your client-side application involves instantiating the Electric client and establishing a sync subscription. Here is a conceptual example using JavaScript/TypeScript:

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

// Connect to the local SQLite database and Electric sync service
const config = {
url: 'http://your-vps-ip:5133'
};

const electric = await electromancy(sqliteDb, schema, config);
const { db } = electric;

// Sync a specific shape/subset of the data
const shape = await db.items.sync();
await shape.synced;

From this point forward, any data written to db.items via the local client will instantly reflect in the local UI, persist to the local SQLite database, and automatically stream to your PostgreSQL database on the VPS whenever a network connection is available.

---

Production Best Practices and Security

Deploying a Local-First stack into production requires strict attention to security, data isolation, and monitoring:

• Reverse Proxy and SSL/TLS: Never expose the raw ElectricSQL ports directly to the public internet. Use a reverse proxy like Nginx or Caddy to handle SSL encryption (HTTPS/WSS) and secure client connections.
• Row-Level Security (RLS): Implement robust authentication and authorization. ElectricSQL integrates with JWT tokens, allowing you to control exactly which users have permission to sync specific subsets ("shapes") of data.
• Conflict Resolution: By default, ElectricSQL leverages rich conflict-resolution mechanisms based on Last-Write-Wins (LWW) tracking at the column level. Ensure your application schema includes high-precision updated_at timestamps to prevent data regressions.
• Backup Strategies: Since the server-side PostgreSQL instance remains the ultimate source of truth, implement standard automated logical and physical backup routines for your VPS storage volume.

Conclusion

Building a Local-First application infrastructure using ElectricSQL and PostgreSQL on a self-hosted VPS strikes the perfect balance between cutting-edge performance, modern user experience, and full data sovereignty. By decoupling user interaction from network reliability, you future-proof your application against connectivity challenges, ensuring business continuity and user satisfaction regardless of external infrastructure constraints.