Wiring It All Together: Vue 3, Nuxt 4, and the Local-First Stack

Core Concepts

Reactive Queries: From SQLite to Vue

The bridge between a local SQLite database and Vue's reactivity system is not a simple ref wrapping a query result. It is a subscription model. A reactive query system parses each SELECT statement to determine which tables it touches. When a write occurs, only the subscriptions that touch the affected tables are re-executed — not every live query in the application.

This table-level tracking is what makes hundreds of concurrent subscriptions feasible without degrading UI thread latency. The cycle — Write → Notification → Re-render — operates at microsecond scale when the database runs in a Worker, because the main thread is never blocked. A secondary benefit is that complex loading states become largely unnecessary: data is either present locally or it is not, and updates arrive without async ceremony.

Vue Composables for Sync State

A composable in Vue 3 is the natural unit for encapsulating sync logic. Two libraries provide ready-made composables for local-first patterns:

PowerSync ships a @powersync/vue package (currently in beta) with utilities that make sync state and query results reactive out of the box. Components that consume these composables automatically re-render when query results update or when connectivity status changes — no manual watcher wiring required. Standard Vue primitives (watch(), computed(), reactive()) work directly with PowerSync queries.

Electric SQL provides useShape, getShape, and getShapeStream — functions that bind materialized Shapes to Vue's reactive state. When the underlying data in the local SQLite database changes, the bound reactive state updates, and any component reading it re-renders.

Both approaches follow the same composable contract: the caller gets back a reactive reference that reflects local state and updates live.

Optimistic Mutations and Server Authority

The write path in a local-first app has two phases: the optimistic phase and the confirmation phase.

In the optimistic phase, the client immediately applies the mutation to local state, simulating what the server result will be. The UI reflects the change instantly — no spinner, no waiting. In the confirmation phase, the server processes the mutation and responds. If the server confirms, the optimistic result is replaced with the canonical server result. If the server rejects, the application rolls back by restoring the previous state.

The server never loses authority. The client only ever speculates. If the speculation is wrong, the server wins.

This is the server-authority model: the server remains the sole authority for validating and applying changes. Systems like Replicache and PowerSync implement this by having clients create speculative results that are then pushed to and executed by the server to produce confirmed, canonical results. The client's role is limited to synchronization and speculative execution.

In Vue, this pattern is implemented using libraries like TanStack Query or Pinia Colada, which expose lifecycle hooks:

• onMutate — apply the speculative update and capture a snapshot for rollback
• onError — restore the snapshot if the server rejects
• onSettled — refetch to reconcile local and server state

Postgres WAL as the Sync Engine's Source of Truth

On the server side, the pipeline that feeds client replicas starts with the Write-Ahead Log (WAL). The WAL is an append-only, sequential record of every committed change in Postgres. Because the log is immutable, replaying it is deterministic and fast.

Sync engines consume the WAL through logical replication. Rather than replicating raw byte-level changes, logical replication decodes WAL entries into row-level change events (INSERT, UPDATE, DELETE) that the sync engine can translate into client-consumable diff streams. This is the same principle that powers log shipping across Postgres, MySQL, and Oracle.

Replication Lag and Its Effect on Client Freshness

Replicas do not always stay current. Replication lag can accumulate through several mechanisms:

• Network issues: the replica fails to communicate with the primary, causing it to fall behind.
• Write throughput: the primary writes faster than the replica can apply changes.
• WAL misconfiguration: log segments are not shipped or applied correctly, resulting in missing changes.

The consequence for local-first clients is stale data. If a client is reading from a replica that lags by several seconds, it will not see writes that other clients have already confirmed. This matters for conflict detection, ordering, and UI correctness.

Postgres supports both asynchronous and synchronous replication. Asynchronous (the default) considers a transaction committed once it is written on the primary; replicas consume changes on their own timeline. Synchronous replication requires acknowledgment from configured replicas before the transaction completes, providing stronger durability but potentially cutting write throughput by more than half over slow networks. Postgres 10 introduced quorum commit as a middle ground.

Long-Running Transactions: An Anti-Pattern for Sync Pipelines

Postgres uses Multi-Version Concurrency Control (MVCC): readers do not block writers. However, long-lived transactions break this model. An open transaction holds a snapshot that prevents Postgres from cleaning up dead tuples — rows that have been updated or deleted but must remain visible to the old snapshot. Autovacuum cannot reclaim this space, leading to heap bloat, index swelling, and cascading performance degradation under write load.

In a sync pipeline, this is especially damaging. A sync engine continuously reads WAL entries; a long-running transaction upstream of that pipeline can stall WAL advancement, delay replication slot consumption, and ultimately cause the sync engine to fall behind. The fix is simple in principle: keep transactions short. Never hold a transaction open while waiting for user input, an external API call, or any other I/O that is not bounded by a tight timeout.

NuxtHub and Drizzle: The Nuxt Integration Path

NuxtHub Database provides type-safe database access powered by Drizzle ORM, supporting Postgres, MySQL, and SQLite. Schema migrations run automatically at build time. For local development, if no environment variables are set, NuxtHub falls back to PGlite — an embedded Postgres instance running in the process — making zero-config local development possible.

For read scaling, NuxtHub supports Drizzle's withReplicas() feature, which automatically routes read queries to replicas and write queries to the primary. This maps cleanly to the replication topology a sync engine requires: writes go to the authoritative primary, reads can fan out to replicas.

Step-by-Step Procedure

Wiring a Reactive SQLite Query in Vue 3

This procedure applies to either PowerSync or a custom reactive query layer on top of SQLite WASM.

Step 1 — Define the query and its table dependencies. Identify which tables the query reads. The subscription system needs to know which tables to watch. With PowerSync's Vue SDK, this is automatic — the SDK parses the query. With a custom setup, you declare dependencies explicitly.

Step 2 — Create a composable that returns a reactive reference. Wrap the query subscription in a composable function. Inside, register the subscription and update a ref or shallowRef whenever the subscription fires. Return the ref to callers.

Step 3 — Consume the composable in the component. The component calls the composable and binds the returned ref in its template. No manual watch() or lifecycle hooks are needed; the subscription keeps the ref current.

Step 4 — Handle the unsubscribe on unmount. Use onUnmounted to cancel the subscription. Without this, stale subscriptions accumulate and waste resources.

Implementing an Optimistic Mutation

Step 1 — Capture the current state as a rollback snapshot. In the onMutate hook, read the current cached value and save it. This is the data you will restore if the server rejects the write.

Step 2 — Apply the speculative update locally. Update the local cache (or local SQLite, depending on the architecture) with the expected result of the mutation. The UI reflects the change immediately.

Step 3 — Send the mutation to the server. Dispatch the mutation to the server — via REST, GraphQL, or the sync engine's mutation API.

Step 4 — Handle rejection in onError. If the server returns an error, restore the rollback snapshot captured in Step 1. Optionally surface the error to the user.

Step 5 — Reconcile in onSettled. Whether the mutation succeeded or failed, refetch the authoritative data from the server (or wait for the next sync cycle to deliver it). This closes the gap between the speculative state and the confirmed state.

Setting Up NuxtHub with Drizzle in a Nuxt 4 Project

Step 1 — Install NuxtHub and Drizzle. Add @nuxthub/core and drizzle-orm to the project. Enable the hub.database option in nuxt.config.ts.

Step 2 — Define the schema. Create a Drizzle schema file. NuxtHub reads this at build time and generates automatic migrations.

Step 3 — Run locally without environment variables. NuxtHub will detect the absence of a remote database configuration and start PGlite locally. No separate Postgres installation is needed for development.

Step 4 — Access the database in server routes. Use useDatabase() from NuxtHub inside Nitro server routes or API handlers. This returns a Drizzle instance bound to the configured database.

Step 5 — Configure read replicas for production. Use Drizzle's withReplicas() wrapper to register replica connection strings. NuxtHub will route SELECT queries to replicas and writes to the primary automatically.

Worked Example

A useTodos Composable with Optimistic Add

Consider a todo list application backed by a local SQLite database synced via Electric SQL.

The composable exposes two things: the list of todos as a reactive reference, and a mutation function for adding a new todo.

// composables/useTodos.ts
import { useShape } from '@electric-sql/vue'
import { useMutation, useQueryClient } from '@tanstack/vue-query'

export function useTodos() {
  // Reactive live query via Electric SQL's useShape
  const { data: todos } = useShape({
    url: `${import.meta.env.VITE_ELECTRIC_URL}/v1/shape`,
    params: { table: 'todos' },
  })

  const queryClient = useQueryClient()

  const { mutate: addTodo } = useMutation({
    mutationFn: (newTodo: { text: string }) =>
      fetch('/api/todos', {
        method: 'POST',
        body: JSON.stringify(newTodo),
      }),

    // Step 1: capture snapshot and apply speculative update
    onMutate: async (newTodo) => {
      await queryClient.cancelQueries({ queryKey: ['todos'] })
      const previous = queryClient.getQueryData(['todos'])
      queryClient.setQueryData(['todos'], (old: any[]) => [
        ...old,
        { id: crypto.randomUUID(), text: newTodo.text, completed: false },
      ])
      return { previous }
    },

    // Step 4: rollback on server rejection
    onError: (_err, _newTodo, context) => {
      queryClient.setQueryData(['todos'], context?.previous)
    },

    // Step 5: reconcile after settlement
    onSettled: () => {
      queryClient.invalidateQueries({ queryKey: ['todos'] })
    },
  })

  return { todos, addTodo }
}

What to notice:

• useShape from Electric SQL binds the todos Shape to Vue reactive state. Any row change delivered by the sync engine updates todos automatically.
• The onMutate hook applies the speculative item before the POST returns. The user sees the new todo immediately.
• If the server returns an error — say, a validation failure — onError restores the previous list, and the speculative item disappears.
• onSettled triggers a refetch, which pulls in the confirmed server state (or the Electric sync cycle delivers it shortly after).

The server authority principle is preserved: the item shown during the optimistic phase is a guess. The confirmed item that arrives from Electric after the sync cycle is the canonical truth. If they differ, the canonical version wins.

Active Exercise

Build a Reactive Counter with Rollback

This exercise reinforces the composable pattern and the optimistic mutation lifecycle.

Setup: Use a local SQLite database (PGlite or SQLite WASM) with a single counters table: id INTEGER PRIMARY KEY, value INTEGER NOT NULL.

Task 1 — Write a useCounter composable. The composable should subscribe to the counters table using a reactive query. Return the current counter value as a ref. When the underlying row changes (simulated by a direct database write in a second browser tab or worker), the ref should update without a page refresh.

Task 2 — Add an increment mutation. Implement an increment() function inside the composable. It should:

1. Optimistically increment the local ref immediately.
2. Send a POST /api/counters/:id/increment to the server.
3. On server error (simulate this by returning a 500), roll back the local ref to its previous value.
4. On settlement, reconcile with the server's returned value.

Task 3 — Observe the WAL. If you are using a local Postgres instance, open pg_stat_replication before and after running rapid successive increments. Observe how WAL position advances. Then deliberately open a long transaction (BEGIN; — do not commit) and run the increments again. Compare the lag in pg_stat_replication. Explain what you observe.

Reflection questions:

• What happens to the optimistic value if the network is down for 5 seconds and then the server responds with a different value?
• How would you design the onSettled step if the server response does not include the new canonical value — only a 200 OK?