Local-first application and real-time updates with the ElectricSQL plugin

Local-first applications using relational databases is a complex topic. The ElectricSQL project provides a synchronization layer between PostgreSQL and its clients, but it is difficult to setup.

An ElectricSQL plugin has been developed for ExpressX to simplify this setup and expose the data access methods through ExpressX services. It is composed of two parts:

@jcbuisson/express-x-plugins/electric-server registers models/services in the backend
@jcbuisson/express-x-plugins/electric-client exposes models in the frontend

Electric sync process

ElectricSQL use a separate synchronization process connected to the database. The simplest way to manage it is to use the official docker image.

bash

# docker-compose.yml
services:
  electric-todo:
    image: electricsql/electric:1.7.8
    extra_hosts:
      - "host.docker.internal:host-gateway"
    environment:
      DATABASE_URL: postgresql://user:password@host.docker.internal:5432/database
      ELECTRIC_INSECURE: "true"
      ELECTRIC_STORAGE: FAST_FILE
      ELECTRIC_PERSISTENT_STATE: FILE
      ELECTRIC_STORAGE_DIR: /var/lib/electric
    ports:
      - "3216:3000"
    volumes:
      - electric_todo_data:/var/lib/electric

volumes:
  electric_todo_data:

start it: docker compose up -d --wait
stop it: docker compose down

It runs on port 3000 by default.

Server setup

Install the server dependencies:

bash

npm install @jcbuisson/express-x @jcbuisson/express-x-plugins pg

Each synchronized table needs a primary key. The default is uid; configure primaryKey when a table uses another column.

// app.js
import { Pool } from 'pg'
import { expressX } from '@jcbuisson/express-x/server'
import { electricOfflinePlugin } from '@jcbuisson/express-x-plugins/electric-server'

const app = expressX()
const db = new Pool({ connectionString: process.env.DATABASE_URL })

app.configure(electricOfflinePlugin, db, [
   'post',
   {
      name: 'project',
      table: 'projects',
      primaryKey: 'uid',
   },
], {
   electricUrl: process.env.ELECTRIC_URL,
   sourceId: process.env.ELECTRIC_SOURCE_ID,
   sourceSecret: process.env.ELECTRIC_SOURCE_SECRET,
   authorize: async (context, operation) => {
      const user = context.request?.user ?? context.socket?.data?.user // check ExpressX authentication
      return Boolean(user && canAccess(user, operation)) // check authorization
   },
})

app.httpServer.listen(8000)

authorize(context, operation) is required. operation contains modelName, action, and args; its action is shape, create, update, or delete.

Server options:

Option	Default	Description
`authorize`	() => true)	Called on every request
`electricUrl`	`ELECTRIC_URL` or `http://localhost:3000/v1/shape`	Upstream Electric Shape endpoint
`shapePath`	`/electric/v1/shape/:model`	Express route exposed to clients
`sourceId`	none	Electric source ID, kept server-side
`sourceSecret`	none	Electric source secret, kept server-side
`fetch`	`globalThis.fetch`	Optional custom Fetch implementation

Models may be table-name strings or { name, table, primaryKey } objects. When using a string, it is both the model the table, and the primary key is `id', automatically generated server-side.

Client setup

Install the optional browser dependencies:

bash

npm install @jcbuisson/express-x @jcbuisson/express-x-plugins socket.io-client @electric-sql/client rxjs

import { io } from 'socket.io-client'
import { createClient } from '@jcbuisson/express-x/client'
import { electricClientPlugin } from '@jcbuisson/express-x-plugins/electric-client'

const socket = io('http://localhost:8000', { transports: ['websocket'] })
const app = createClient(socket)

app.configure(electricClientPlugin, {
   shapePath: '/electric/v1/shape',
})

const post = app.createElectricModel('post')

createElectricModel(modelName, options) accepts an optional Shape url, streamOptions, and idGeneration. The model name must match the ExpressX service registered on the server.

CRUD operations

The plugin registers one ExpressX service per model with the following methods:

Mutation methods

Method	Description
`create(uid, data)`	Insert or update a row with a client-supplied key
`create(data)`	Insert a row and let the database generate its key
`update(id, data)`	Update a row by primary key
`delete(id)`	Delete a row by primary key

create, updte, delete return the created, updated, or deleted row directly.

const created = await post.create({ title: 'Hello', published: false })
await post.update(created.uid, { published: true })
await post.remove(created.uid)

By default, the browser generates new IDs with crypto.randomUUID(). For a database-generated integer key, configure the model with app.createElectricModel('post', { idGeneration: 'server' }); create(data) then returns the row with its generated key. Mutations are network operations and are not queued while offline. Electric's Shape stream retries transient read failures and catches up after reconnection.

Real-time observable

For each model, the following method provides access to data:

getObservable(where) returns an RxJS Observable that emits the current rows for filter where, whenever they change:

const subscription = post.getObservable({
   published: true,
   created_at: { gte: new Date('2026-01-01') },
}).subscribe(rows => {
   console.log(rows)
})

// Dispose it when the owning view or process is finished.
subscription.unsubscribe()

Filters support exact values, null, and the range operators gt, gte, lt, and lte. Values are parameterized; unsupported objects, invalid dates, non-finite numbers, and unsafe identifiers are rejected.

Step-by-step working example

project/
   backend/
      src/
         app.js
      docker-compose.yml
      package.json
      .env
   frontend/
      index.html
      package.json
      vite.config.js

Create PostgreSQL database

bash

createdb todoDB
echo "CREATE TABLE todo (id serial primary key, label text NOT NULL, completed BOOLEAN DEFAULT FALSE);" | psql todoDB

Run Elecric sync process

bash

# docker-compose.yml
services:
  electric-todo:
    image: electricsql/electric:1.7.8
    extra_hosts:
      - "host.docker.internal:host-gateway"
    environment:
      DATABASE_URL: postgresql://user:password@host.docker.internal:5432/todoDB
      ELECTRIC_INSECURE: "true"
      ELECTRIC_STORAGE: FAST_FILE
      ELECTRIC_PERSISTENT_STATE: FILE
      ELECTRIC_STORAGE_DIR: /var/lib/electric
    ports:
      - "3200:3000"
    volumes:
      - electric_todo_data:/var/lib/electric

volumes:
  electric_todo_data:

start it: docker compose up -d --wait
stop it: docker compose down

Backend

bash

cd backend
npm init es6

Setup env

bash

# .env
PORT="8000"
DATABASE_URL="postgresql://user:password@localhost:5432/todoDB"
ELECTRIC_URL="http://127.0.0.1:3200/v1/shape"

Install plugin

bash

npm install @jcbuisson/express-x @jcbuisson/express-x-plugins pg

// app.js
import { Pool } from 'pg'
import { expressX } from '@jcbuisson/express-x/server'
import { electricOfflinePlugin } from '@jcbuisson/express-x-plugins/electric-server'

const app = expressX()
const db = new Pool({ connectionString: process.env.DATABASE_URL })

app.configure(electricOfflinePlugin, db, ['todo'])

app.httpServer.listen(process.env.PORT, () => console.log(`App listening at http://localhost:${process.env.PORT}`))

Run it: node --env-file=.env src/app.js

Frontend

bash

cd frontend
npm init es6

Install plugin

bash

npm install @jcbuisson/express-x @jcbuisson/express-x-plugins
npm install -D vite

import io from 'socket.io-client'
import { createClient } from '@jcbuisson/express-x/client'
import { electricClientPlugin } from '@jcbuisson/express-x-plugins/electric-client'

const socket = io('http://localhost:8000', { transports: ["websocket"] })

const app = createClient(socket)
app.configure(electricClientPlugin)

const post = app.createElectricModel('todo')
const subscription = post.getObservable({ completed: true }).subscribe(posts => {
   console.log('Completed todos', posts)
})

await post.create({ title: 'Hello', published: true })

index.html

html

<html>
   <input id="value-id" type="number" placeholder="Enter value"><br>
   <button id="square-id" class="btn">Square</button>
   <button id="cube-id" class="btn">Cube</button>
   <p id="result-id"></p>
</html>

<script type="module">
import io from 'socket.io-client'
import { createClient } from '@jcbuisson/express-x/client'
import { electricClientPlugin } from '@jcbuisson/express-x-plugins/electric-client'

const socket = io('http://localhost:8000', {
   path: '/todo-socket-io/',
   transports: ["websocket"],
})

const app = createClient(socket)

app.configure(electricClientPlugin)

const valueInput = document.getElementById('value-id');
const squareBtn = document.getElementById('square-id');
const cubeBtn = document.getElementById('cube-id');
const resultParagraph = document.getElementById('result-id');

squareBtn.addEventListener('click', async (ev) => {
   const result = await app.service('math').square(valueInput.value);
   resultParagraph.innerHTML = result;
})

cubeBtn.addEventListener('click', async (ev) => {
   const result = await app.service('math').cube(valueInput.value);
   resultParagraph.innerHTML = result;
})
</script>

vite.config.js

import { defineConfig } from 'vite'

export default defineConfig({
   server: {
      port: 8080,
      open: true,
      proxy: {
         '^/todo-socket-io/.*': {
            target: 'http://localhost:3016',
            ws: true,
            secure: false,
            changeOrigin: true,
         },
         '^/electric/v1/.*': {
            target: 'http://localhost:3200',
            secure: false,
            changeOrigin: true,
         },
      }
   },
})

Run frontend: npx vite

Server guide

Client guide

Electric guide

Local-first application and real-time updates with the ElectricSQL plugin

Electric sync process

Server setup

Client setup

CRUD operations

Mutation methods

Real-time observable

Step-by-step working example

Create PostgreSQL database

Run Elecric sync process

Backend

Setup env

Install plugin

Frontend

Install plugin

Local-first application and real-time updates with the ElectricSQL plugin ​

Electric sync process ​

Server setup ​

Client setup ​

CRUD operations ​

Mutation methods ​

Real-time observable ​

Step-by-step working example ​

Create PostgreSQL database ​

Run Elecric sync process ​

Backend ​

Setup env ​

Install plugin ​

Frontend ​

Install plugin ​

Local-first application and real-time updates with the ElectricSQL plugin

Electric sync process

Server setup

Client setup

CRUD operations

Mutation methods

Real-time observable

Step-by-step working example

Create PostgreSQL database

Run Elecric sync process

Backend

Setup env

Install plugin

Frontend

Install plugin