Write Patterns for ElectricSQL

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

ElectricSQL handles the read path—syncing data from PostgreSQL to clients in real time—but deliberately leaves the write path to you. This isn’t a limitation; it’s a design choice that lets you pick the write pattern that matches your application’s needs. The ElectricSQL documentation describes a progression of write patterns, from simple API calls to full local-first with an embedded database. Each pattern adds capability at the cost of complexity.

In this post, we’ll cover:

• Online writes - The simplest approach: standard API calls
• Optimistic state - Instant UI feedback while writes are in flight
• Shared persistent optimistic state - Cross-component, survives reloads
• Through-the-database - Full local-first with PGlite
• TanStack DB - The recommended modern approach
• Choosing a pattern - When each one makes sense

Pattern 1: Online writes

The simplest pattern. Electric handles the read path via Shapes, and writes go through your existing API—REST, GraphQL, RPC, whatever you already have. The user must be online to make changes.

The flow is straightforward: data syncs to the client via Electric, the user performs an action, the client sends a POST/PUT/DELETE to your backend, the backend writes to Postgres, and the change syncs back to the client through Electric’s Shape stream.

import { useShape } from "@electric-sql/react";

function TodoList() {
  const { data: todos } = useShape({
    url: `${API_URL}/v1/shape`,
    params: { table: "todos" },
  });

  const addTodo = async (title: string) => {
    // Standard API call — user must be online
    await fetch("/api/todos", {
      method: "POST",
      body: JSON.stringify({ title, completed: false }),
    });
    // Change syncs back through Electric automatically
  };

  return (
    <ul>
      {todos.map((todo) => (
        <li key={todo.id}>{todo.title}</li>
      ))}
    </ul>
  );
}

This is how most web applications already work, with Electric replacing the polling or manual refetching you’d normally do after a mutation. The benefit is zero additional complexity on the write side. The downside is that every write blocks on a network round trip—the UI doesn’t update until the server responds and the change syncs back through Electric.

For many applications—admin dashboards, internal tools, settings pages—this is perfectly fine. If your users are always online and can tolerate a brief delay after clicking “Save,” there’s no reason to add complexity.

Pattern 2: Optimistic state

This pattern extends online writes by showing the change in the UI immediately while the API call is still in flight. React’s built-in useOptimistic hook handles the local state, and Electric’s matchStream utility lets you wait for the change to appear in the sync stream before discarding the optimistic state.

import { useShape, matchStream } from "@electric-sql/react";

function TodoList() {
  const { data: todos, stream } = useShape({
    url: `${API_URL}/v1/shape`,
    params: { table: "todos" },
  });

  const [optimisticTodos, addOptimistic] = useOptimistic(
    todos,
    (synced, { operation, value }) => {
      if (operation === "insert") return [...synced, value];
      if (operation === "update")
        return synced.map((t) => (t.id === value.id ? { ...t, ...value } : t));
      if (operation === "delete") return synced.filter((t) => t.id !== value.id);
      return synced;
    }
  );

  const addTodo = async (title: string) => {
    const newTodo = { id: crypto.randomUUID(), title, completed: false };

    startTransition(async () => {
      // Show optimistic state immediately
      addOptimistic({ operation: "insert", value: newTodo });

      // Wait for both the API response AND the sync stream confirmation
      const fetchPromise = fetch("/api/todos", {
        method: "POST",
        body: JSON.stringify(newTodo),
      });
      const syncPromise = matchStream(stream, ["insert"], (msg) => msg.id === newTodo.id);

      await Promise.all([fetchPromise, syncPromise]);
    });
  };

  return <ul>{optimisticTodos.map(/* ... */)}</ul>;
}

The key detail is matchStream. Instead of just waiting for the API response and hoping the sync catches up, the code waits for both the API call and the matching message in Electric’s Shape stream. This ensures the optimistic state is only discarded once the real data has arrived via sync—no flash of stale data.

The tradeoff is that optimistic state is component-scoped and ephemeral. Other components that subscribe to the same Shape don’t see the optimistic changes. If the user refreshes the page while a write is in flight, the optimistic state is lost (though the API call may still succeed, and the change will appear on the next sync).

Pattern 3: Shared persistent optimistic state

When optimistic state needs to be visible across components and survive page reloads, you move it out of React’s component tree and into a shared store. Electric’s write-patterns examples demonstrate this with Valtio and localStorage, but the concept applies to any persistent state management library.

The architecture separates two layers of state:

1. Synced state — immutable data from Electric (the source of truth from the server)
2. Local state — mutable optimistic writes stored in a shared persistent store

A merge function combines them at read time, with local state taking precedence for pending writes. When Electric’s sync stream confirms a change (matching by a writeId), the corresponding local optimistic entry is discarded.

// Simplified conceptual model
function useTodosWithOptimisticState() {
  const { data: synced } = useShape({ /* ... */ });
  const localWrites = useLocalStore(); // shared, persistent

  return merge(synced, localWrites);
}

function merge(synced, local) {
  // Local writes override synced data for the same keys
  // Deleted items are filtered out
  // New items are appended
  return /* merged result */;
}

This pattern supports per-write rollback—if the server rejects a specific write, you can remove just that entry from the local store without clearing everything. Because writes are tracked individually with IDs, you can show pending/failed states in the UI and let users retry or discard specific operations.

The pattern works well for applications that need a good offline experience without the weight of an embedded database. The persistent store (localStorage, IndexedDB) is lightweight, and the merge-at-read-time approach keeps the synced and local state cleanly separated. The main complexity is writing the merge logic correctly, especially when handling concurrent edits from other users that arrive through the sync stream while local writes are pending.

Pattern 4: Through-the-database with PGlite

The most capable pattern uses PGlite—an embeddable, WASM-compiled PostgreSQL that runs in the browser—as the local database. Your application reads from and writes to a local Postgres instance, and a background process syncs changes to and from the server.

The architecture uses shadow tables and database triggers to manage the complexity:

-- Immutable synced state (populated by Electric)
CREATE TABLE todos_synced (
  id UUID PRIMARY KEY,
  title TEXT NOT NULL,
  completed BOOLEAN NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  write_id UUID
);

-- Mutable local optimistic state
CREATE TABLE todos_local (
  id UUID PRIMARY KEY,
  title TEXT,
  completed BOOLEAN,
  created_at TIMESTAMPTZ,
  changed_columns TEXT[],
  is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
  write_id UUID NOT NULL
);

-- Change log for background sync
CREATE TABLE changes (
  id BIGSERIAL PRIMARY KEY,
  operation TEXT NOT NULL,
  value JSONB NOT NULL,
  write_id UUID NOT NULL,
  transaction_id XID8 NOT NULL
);

-- Merged view — your application reads from this
CREATE VIEW todos AS
  SELECT
    s.id,
    CASE WHEN l.changed_columns @> '{title}' THEN l.title ELSE s.title END AS title,
    CASE WHEN l.changed_columns @> '{completed}' THEN l.completed ELSE s.completed END AS completed,
    s.created_at
  FROM todos_synced s
  LEFT JOIN todos_local l ON s.id = l.id
  WHERE l.is_deleted IS NOT TRUE
  UNION ALL
  SELECT l.id, l.title, l.completed, l.created_at
  FROM todos_local l
  LEFT JOIN todos_synced s ON l.id = s.id
  WHERE s.id IS NULL AND l.is_deleted IS NOT TRUE;

INSTEAD OF triggers on the view intercept INSERT, UPDATE, and DELETE operations, routing them to todos_local and the changes table. A NOTIFY trigger on changes signals a background sync process that reads the change log and sends mutations to your backend API. When the server processes the write and the change syncs back through Electric into todos_synced, a cleanup trigger automatically removes the corresponding entry from todos_local.

Your application code becomes simple—it just reads and writes to the todos view using standard SQL:

// Write — triggers handle the rest
await db.exec(
  `INSERT INTO todos (id, title, completed, created_at)
   VALUES ($1, $2, false, now())`,
  [id, title]
);

// Read — merged view includes both synced and local state
const result = await db.exec(`SELECT * FROM todos ORDER BY created_at DESC`);

The tradeoff is schema complexity. You need shadow tables, a change log, merge views, and multiple triggers for every synced table. PGlite adds roughly 3MB (gzipped) to your bundle. And rollback handling is harder—when a background sync encounters a rejection, it doesn’t have the user interaction context to show a meaningful error. The default approach is to clear all local state, which is safe but aggressive.

For applications that need a full local-first experience—offline reads and writes, immediate responsiveness, and background sync—this is the most complete pattern. For more on running databases in the browser, see our post on local-first databases.

Pattern 5: TanStack DB

TanStack DB is the approach Electric recommends for most applications. It’s a reactive client store built by Tanner Linsley in collaboration with the Electric team, designed specifically for building local-first applications with Electric as the sync layer.

TanStack DB provides three core primitives: Collections (typed data containers backed by Electric shapes or TanStack Query), Live Queries (reactive queries with sub-millisecond latency via differential dataflow), and Mutations (optimistic writes with automatic lifecycle management).

import { createCollection, electricCollectionOptions } from "@tanstack/db";

// Create a sync collection backed by an Electric shape
const todoCollection = createCollection(
  electricCollectionOptions({
    id: "sync-todos",
    shapeOptions: {
      url: `${ELECTRIC_URL}/v1/shape`,
      params: { table: "todos" },
    },
    getKey: (item) => item.id,
    schema: todoSchema,
  })
);

Reading uses live queries that automatically update when underlying data changes:

import { useLiveQuery } from "@tanstack/react-db";

function TodoList() {
  const { data: todos } = useLiveQuery((query) =>
    query
      .from({ todo: todoCollection })
      .where(({ todo }) => eq(todo.completed, false))
      .orderBy(({ todo }) => asc(todo.created_at))
  );

  return <ul>{todos.map(/* ... */)}</ul>;
}

Writing uses collection mutations with automatic optimistic state management:

// Simple single-collection mutation
todoCollection.insert({
  id: crypto.randomUUID(),
  title: "Buy groceries",
  completed: false,
  created_at: new Date().toISOString(),
});

// Cross-collection atomic mutation
const createProjectWithTasks = createOptimisticAction({
  (input) => {
    projectCollection.insert(input.project);
    for (const task of input.tasks) {
      todoCollection.insert(task);
    }
  },
  mutationFn: async (input, { transaction }) => {
    const txid = await api.post("/ingest", {
      mutations: transaction.mutations,
    });
    // Wait for Electric to sync back the transaction
    await Promise.all(
      [...transaction.collections].map(({ utils }) => utils.awaitTxId(txid))
    );
  },
});

The awaitTxId mechanism ties the optimistic state lifecycle directly to the sync stream. Your server returns the Postgres transaction ID after processing the write, and TanStack DB watches Electric’s sync stream for that specific transaction before discarding the optimistic state. This ensures the transition from optimistic to confirmed data is seamless.

On the server side, Electric provides Phoenix.Sync for Elixir backends with a Writer module that can ingest TanStack DB mutations generically:

def mutate(conn, %{"mutations" => mutations}) do
  {:ok, txid, _changes} =
    Writer.new()
    |> Writer.allow(Todos.Todo)
    |> Writer.allow(Projects.Project)
    |> Writer.ingest(mutations, format: Writer.Formats.TanstackDB)
    |> Writer.transaction(Repo)

  json(conn, %{txid: txid})
end

TanStack DB is incrementally adoptable. You can start with TanStack Query collections (standard fetch-based data loading), then migrate to Electric sync collections for specific data sets. The live query API stays the same regardless of the collection type.

Choosing a pattern

The patterns form a clear progression from simple to powerful:

Pattern	Offline writes	Cross-component	Survives reload	Bundle cost	Complexity
Online writes	No	N/A	N/A	None	Very low
Optimistic state	No	No	No	None	Low
Shared persistent	Yes	Yes	Yes	Light (~5KB)	Medium
Through-the-database	Yes	Yes	Yes	Heavy (~3MB)	High
TanStack DB	Yes	Yes	Yes	Moderate	Medium

Start with online writes if your users are always connected and a brief delay after mutations is acceptable. This is the right choice for admin panels, internal tools, and any application where the read path benefits from Electric’s sync but the write path is straightforward.

Add optimistic state when individual interactions need to feel instant—toggling a checkbox, adding an item to a list, updating a field. The component-scoped nature of useOptimistic is actually an advantage for simple cases: no global state management, no cleanup logic.

Move to shared persistent optimistic state when you need offline writes, multiple components reflecting the same pending changes, or per-write rollback capability. This is a good balance of capability and complexity for most production applications.

Use through-the-database when your application is truly local-first—offline for extended periods, complex local queries, or when you want the full power of SQL on the client. The PGlite approach is the most capable but also the most complex to set up and maintain.

Use TanStack DB for new applications or when migrating an existing app incrementally. It provides the best developer experience with automatic optimistic state management, reactive queries, and a clean integration path with Electric’s sync. The Electric team recommends this approach for most apps.

Wrapping up

ElectricSQL’s read-only sync model means you get to choose the write pattern that matches your needs rather than being locked into a single approach. The progression from simple API calls to TanStack DB to embedded PGlite covers a wide range of requirements, and you can start simple and move to more capable patterns as your application demands it.

For the full picture on ElectricSQL’s architecture, see our post on ElectricSQL as a Postgres sync engine. To see how other sync engines handle writes, check out our posts on write patterns for PowerSync and write strategies for Replicache. For a broader comparison of all three tools, see ElectricSQL vs. PowerSync vs. Replicache.