Live Queries

Turn on reactivity over arbitrary SQL with { live: true }.

const result = await db.query('SELECT * FROM posts', { live: true });

You get back a live view of your query, in realtime.

General Idea

Live queries are a first-class concept in LinkedQL.

They happen over the same db.query() API:

const result = await db.query('SELECT * FROM posts', { live: true });

In live mode, the query stays open and result.rows becomes a self-updating result set.

It remains a normal JavaScript array:

console.table(result.rows);
console.log(result.rows.length); // 3

But it stays in sync with the underlying database truth:

INSERT INTO posts (title) VALUES ('Post A');

console.table(result.rows);
console.log(result.rows.length); // 4

result.rows will grow, shrink, reorder, and mutate to reflect the latest truth of the query as changes happen underneath it. This holds not only for simple table scans, but also for joins, limits, aggregates, subqueries, and derived tables.

Live mode can be stopped explicitly:

await result.abort();

Above, result is a RealtimeResult.

Deep Dive

The mechanics of the engine are covered in the LinkedQL Realtime Engineering Paper.

Enabling Realtime Capabilities

LinkedQL’s realtime capabilities (live queries and WAL subscriptions) depend on the support mode of the underlying database. For FlashQL and the Edge runtime client, this is automatic. But for the mainstream database family, this works behind a configuration.

See the Enabling Realtime Capabilities documentation for details.

Live Queries in Practice

Live querie have various real-world use cases. Consider the following.

Analytics Dashboards

A live metric board where aggregates and rollups update as source data changes.

The important behavior is not just "new rows arrive." It is that totals, subtotals, and grouped outputs rebalance in place as the underlying rows change.

SELECT
  region,
  product,
  SUM(sales) AS total_sales,
  ROLLUP(region, product)
FROM orders
GROUP BY GROUPING SETS ((region, product), (region), ());

Observed effect: totals and subtotals rebalance in place as orders stream in. Charts can update directly from the live view instead of being rebuilt from ad hoc event logic.

Collaborative Workspaces

A shared document list showing current editors, owners, or status indicators.

SELECT
  d.id,
  d.title,
  u.name AS active_editor
FROM documents d
LEFT JOIN users u ON d.active_user = u.id;

When a user opens or leaves a document, the corresponding row in the result updates as a row.

Observed effect: the row persists while the joined field changes in place. The view stays continuous.

Live Feeds

A user-facing feed ordered by recency and limited in size.

SELECT *
FROM posts
ORDER BY created_at DESC
LIMIT 20;

Here the live result keeps representing "the newest twenty posts", not anymore "the twenty posts that happened to be present at first render."

Observed effect: new posts slide into the top of the view; older ones fall off. Ordering and window limits continue to hold.

Rankings and Leaderboards

Window functions and aggregates often define the visible shape of the result itself.

SELECT
  user_id,
  SUM(score) AS total_score,
  RANK() OVER (ORDER BY SUM(score) DESC) AS rank
FROM scores
GROUP BY user_id;

As scores change, ranks move as part of the query result.

Observed effect: scores accumulate in place and ranks shift as the underlying rows change.

Mixed and Derived Sources

Queries that combine subqueries, derived tables, and inline relations behave the same way.

SELECT
  p.id,
  p.title,
  r.reaction_count,
  c.comment_count
FROM posts p
JOIN (
  SELECT post_id, COUNT(*) AS reaction_count
  FROM reactions
  GROUP BY post_id
) r ON r.post_id = p.id
LEFT JOIN (
  SELECT post_id, COUNT(*) AS comment_count
  FROM comments
  GROUP BY post_id
) c ON c.post_id = p.id;

Observed effect: changes to reactions or comments are reflected in the joined aggregate view itself, not leaked as unrelated table events for the application to reconcile manually.

Live Views in Detail

result.rows is a self-updating array of objects, each row in the array reflecting the current query truth in realtime.

For a query like:

const result = await db.query(
  'SELECT id, title FROM posts ORDER BY id',
  { live: true }
);

You get an initial result:

[A, B, C, D] // initial result

When a matching row is inserted in the database, it appears in the view:

INSERT INTO posts (title) VALUES ('Hello World');

[A, B, C, D, E] // E added

When a matching row is updated in the database, the corresponding row object in the view updates in place:

UPDATE posts SET title = 'Hello Again' WHERE title = 'Hello World';

[A, B, C^, D, E] // C updated in place

When a matching row is deleted in the database, the corresponding row leaves the view:

DELETE FROM posts WHERE title = 'Hello Again';

[A, B, D, E] // C deleted

That is the simplest live-view model: the query result automatically remains current.

Joins and Join Transitions

Once a query involves joins:

const result = await db.query(
  `SELECT
     posts.id,
     posts.title,
     users.name AS author_name
   FROM posts
   LEFT JOIN users ON posts.author_id = users.id`,
  { live: true }
);

the semantics get deeper.

Here, rows in the result are composed from multiple relations. As underlying tables change, the visible effect is not always a simple add, update, or remove.

A table-level mutation may affect only the right-hand or left-hand side of a join. That may mean that a right-hand or left-hand side of the join that once matched no longer matches, or the reverse. In LinkedQL, this is treated as Join Transition.

By default, certain join transitions should cause the corresponding old row in the view to disappear and be regained as a new row. But that would break continuity and identity. LinkedQL prevents that by handling these transitions specially to preserve identity and continuity.

The observed effect is: stable rows that simply mutate in-place in however the underlying tables change.

Scenario 1: A Join Materializes After an INSERT

Suppose a post already exists with an author_id: 42, but the matching user row does not yet exist. The following INSERT would materialize the join:

INSERT INTO users (id, name) VALUES (42, 'Bob');

The observed effect in the view is that the existing row now gains a right-hand match.

┌──────────────────────────────────────────────────────────────┐
│ { id: 1, title: 'Untitled', author_name: null ─────→ 'Bob' } │
└──────────────────────────────────────────────────────────────┘

Scenario 2: The Join Relationship Changes After an UPDATE

Suppose author_id is updated to point to another user. That would change the join relation.

UPDATE posts SET author_id = 43 WHERE title = 'Untitled';

The observed effect becomes:

• the row persists
• the joined field changes
• continuity is preserved

┌───────────────────────────────────────────────────────────────┐
│ { id: 1, title: 'Untitled', author_name: 'Bob' ─────→ 'Ada' } │
└───────────────────────────────────────────────────────────────┘

Scenario 3: The Join Dissolves After a DELETE

Suppose the current right-hand match is deleted. That would dissolve the relationship.

DELETE FROM users WHERE id = 43;

The observed effect becomes: the corresponding row remains in the view but loses its right-hand match.

┌──────────────────────────────────────────────────────────────┐
│ { id: 1, title: 'Untitled', author_name: 'Ada' ─────→ null } │
└──────────────────────────────────────────────────────────────┘

Overall effect: identity persists. The view stays true to join semantics without leaking low-level mutation shape into the result model.

Frames and Ordinality

Queries that have ordering, limits, or offsets applied materialize in the view with the semantics of each modifier fully maintained.

const top5 = (await client.query(
  `SELECT *
  FROM posts
  ORDER BY created_at DESC
  LIMIT 5`,
  { live: true }
)).rows;

This view will always represent “the newest 5 posts” across INSERT and DELETE operations.

Initially:

[A, B, C, D, E] // initial result

Then on "INSERTing a new row N":

[N, A, B, C, D]   // N enters the view; E falls off because it’s now #6

Then on "UPDATEing a post’s created_at field and promoting it one step higher in the list":

[N, A, C, B, D]   // C and B swap places without initiating a full re-ordering

Essentially, ordering and slicing remain stable relationships — they evolve as data changes, without recomputation.

Precision and Granularity

Live updates apply the smallest possible change needed to keep the view correct. This is a key design goal in LinkedQL.

(a) Field-level updates

If only one column changes in one row, only that column is updated in the corresponding row in the view.

const current3 = result.rows[2];

UPDATE posts SET title = 'Draft' WHERE id = 3;

console.log(result.rows[2].title); // 'Draft'
console.log(current3 === result.rows[2]); // true; persistent, only patched

(b) Reordering without teardown

When the effect of an operation is simply a change in position (for queries with an ORDER BY clause), the view isn't re-ordered in full. Instead, the engine issues a positional swap event: “swap B and C”

Before: [A, B, C, D, E]
After:  [A, C, B, D, E]

C and B simply swap places.

Why that matters:

Precision and granularity keeps the system – all the way to the consumers bound to the view – highly efficient. When rendering on the UI, for example:

• The UI maintains state, avoids unnecessary rerenders, and never flickers.
• Components keyed by row identity keep their state.
• You don’t lose scroll position.

Observability and Atomicity

Live views are not just auto-updating — they are also observable.

LinkedQL exposes them through the Observer API. Observer is a general-purpose JavaScript API for observing object and array-level mutations.

This makes result.rows observable like any object.

Observer.observe(result.rows, (mutations) => {
    console.log(`${mutations[0].type}: ${mutations[0].key} = ${mutations[0].value}`);
});

You pass a callback, as shown above, to observe root-level changes — which, for result.rows, would mean row additions and deletions:

You observe field-level changes by adding the Observer.subtree() directive:

Observer.observe(result.rows, Observer.subtree(), (mutations) => {
    console.log(`${mutations[0].type}: ${mutations[0].key} = ${mutations[0].value}`);
});

LinkedQL leverages Observer's batching feature to preserve the atomicity of the database transactions behind the emitted events. It guarantees that all mutations that happen inside a single database transaction arrive together in one callback turn.

For example:

BEGIN;
UPDATE posts SET title = 'Pinned' WHERE id = 3;
UPDATE posts SET title = 'Pinned' WHERE id = 4;
COMMIT;

Observer will fire once, with a batch containing both updates:

Observer.observe(result.rows, Observer.subtree(), (mutations) => {
    console.log(mutations);
    /*
    Example output:
    [
        { type: 'set', path: [0, 'title'], value: 'Pinned' },
        { type: 'set', path: [1, 'title'], value: 'Pinned' }
    ]
    */
});

Essentially, transactions aren't torn across multiple emissions.

Live Bindings

LinkedQL’s live views are ordinary JavaScript objects and arrays. They simply happen to mutate over time as the database changes.

Those mutations themselves are the basis for reactivity in the design. Because they happen via the Observer API protocol, you get automatic binding and mutation-based reactivity across contexts or runtimes where mutations are a first-class concept.

For example, with the Webflo framework’s live response capability, result.rows — like any object — can be returned from a route as live response, with reactivity preserved over the wire.

export default async function(event) {
    const result = await client.query(`SELECT * FROM posts`, { live: true, signal: event.signal });
    event.waitUntilNavigate(); // Tell Webflo to keep the connection open until the user navigates away
    return { posts: result.rows };
}

That object materializes in the client as the same live object, which in Webflo is accessible via document.bindings.data:

<script src="https://unpkg.com/@webqit/observer/main.js"></script>
<script>
const { Observer } = webqit;

const data = document.bindings.data;
Observer.observe(data, console.log);
</script>

These live objects automatically bind to UI in any mutation-based data-binding framework like OOHTML. OOHTML is an addition to the DOM that brings mutation-based reactivity to the UI — without a compile step:

<script src="https://unpkg.com/@webqit/oohtml/dist/main.lite.js"></script>
<div><?{ data.list.length }?></div>

The UI in this example updates as posts are added or removed — with no glue code. (List rendering has been omitted here for brevity.)

TIP

Try updating the posts table from a terminal to see the UI update the total count.

Essentially, with the Observer protocol as the shared vocabulary of change, continuity stays intact from database to DOM. Each layer in the chain — LinkedQL → Webflo → OOHTML — simply makes or reacts to mutations.

The Callback Mode

In LinkedQL, live views (result.rows) are the high-level interface. Underneath it is a lower-level commit stream.

You can subscribe to this stream directly and maintain your own state store. This is useful if you’re building a custom cache, or replication layer.

const q = `
SELECT id, title
  FROM posts
  ORDER BY created_at DESC`;

const liveHandle = await client.query(q, (commit) => {
    console.log(commit);
}, { live: true });

In this mode:

• all changes are observed from the callback alone as events
• result.rows represents only the initial result of the query, and behaves as a static result set as against a live object
• result.mode is set to 'callback' – indicating the consumption mode

Compared to the default live view concept, custom event handling sits closer to the wire.

The Event Stream

The live query event stream is made of three event types:

Event	Meaning
diff (commit.type === 'diff')	Incremental inserts, updates, and deletes
swap (commit.type === 'swap')	Positional swaps that satisfy an ORDER BY clause
result (commit.type === 'result')	A new snapshot of the query result

const liveHandle = await client.query(q, (commit) => {
    if (commit.type === 'diff') for (const e of commit.entries) mutationState(e);
    if (commit.type === 'swap') applySwaps(commit.entries);
    if (commit.type === 'result') replaceState(commit.rows, commit.hashes);
}, { live: true });

The diff Event

A typical diff event contains one or more entries describing result-level changes.

{
  type: 'diff',
  entries: [...],
  ...commitMeta
}

insert Descriptor

{
  op: 'insert',
  new: { id: 1, name: 'Ada' },
  newHash: '[[1]]',
}

update Descriptor

{
  op: 'update',
  old: { id: 1, name: 'Ada' },
  new: { id: 1, name: 'Ada Lovelace' },
  oldHash: '[[1]]',
  newHash: '[[1]]',
}

delete Descriptor

{
  op: 'delete',
  old: { id: 1, name: 'Ada Lovelace' },
  oldHash: '[[1]]',
}

The hashes are the stable result-level row identifiers.

The swap Event

This event contains positional swaps that satisfy an ORDER BY clause.

{
  type: 'swap',
  entries: [...],
  ...commitMeta
}

Entries are pairs of positional swaps by ID (the hashes):

[
  ['[[3]]', '[[1]]'],
  ['[[1]]', '[[3]]'],
]

The above should have a re-ordering effect like:

[row1, row2, row3] -> [row3, row2, row1]

The result Event

This event represents a new snapshot of the query result – for when diffrential updates aren't feasible for the query type. This typically happens with queries with aggregates.

{
  type: 'result',
  rows: [{ id: 1, name: 'Ada' }, { id: 2, name: 'Jane' }],
  hashes: ['[[1]]', '[[2]]']
  ...commitMeta
}

The rows and hashes array have the exact same meaning as the standard result's rows and hashes.

The handler is expected to replace local state with the new result.

The Two-Mode Consumption Model

The "callback" mode above and the default "live view" mode give you two ways to consume live queries. This is by design. And each caters to two very different needs:

• Live views: state-based consumption
• The callback mode: event-based consumption

A live view literally translates the event stream into application-ready state.

A callback opts out of that and in to the stream itself to manually interprete events.

Stable Subscription Slots

You can attach an id to a subscription:

const result = await db.query(
  'SELECT * FROM posts ORDER BY id',
  { live: true, id: 'posts_slot' }
);

That id is more than a label. It gives the query a durable slot identity, and LinkedQL binds that subscription to the same slot each time it is recreated with the same id.

Behaviour

With a durable slot identity, the runtime:

• is able to resume from the same logical slot on requeries
• catches the subscriber up on commits that were missed while away
• continues emitting to the subscriber from that state

State continuity also means:

• previous initial snapshot isn't redelivered; consequently...
• result.rows is empty
• result.initial is false

That matters when live queries back application caches, replicas, sync workers, or long-lived UI sessions that must continue from a known point rather than restarting blindly from "now."

Example

const commits = [];

const result = await db.query(
  'SELECT * FROM posts ORDER BY id',
  (commit) => commits.push(commit),
  { live: true, id: 'posts_slot' }
);

await db.query(`
  INSERT INTO public.posts (id, title) VALUES (1, 'Hello');
  UPDATE public.posts SET title = 'Hello World' WHERE id = 1;
`);

await result.abort();

await db.query(`DELETE FROM public.posts WHERE id = 1`);

What happens:

• you get one commit event containing two diffs: insert and update
• you called result.abort() and don't get the second commit

const result = await db.query(
  'SELECT * FROM posts ORDER BY id',
  (commit) => events.push(commit),
  { live: true, id: 'posts_slot' }
);

What happens now:

• you re-subscribed to the same subscription slot
• you get the one commit event you missed: delete
• result.rows is empty
• result.initial is false

Dropping Slots

To drop the slot itself, pass { forget: true } to the abort() call:

await result.abort({ forget: true });

Appendix A – Query Inheritance and Scaling

Live queries are efficient because LinkedQL does not have to treat each subscription as an isolated process.

When queries overlap strongly, LinkedQL can share work instead of recomputing each subscription independently for every listener.

For example:

SELECT * FROM posts;
SELECT * FROM posts WHERE author_id = 1;
SELECT * FROM posts WHERE author_id = 2;
SELECT * FROM posts WHERE author_id = 3;

These are not four unrelated realtime systems. They overlap heavily around the same underlying relation.

Instead of running three (or four) separate live computations, LinkedQL maintains a single canonical “posts” stream and lets each narrower query filter from it.

This applies not only to filters, but also to slices like ORDER BY … LIMIT …, projections, and other refinements that can be derived locally.

The benefit is twofold:

1. Work is shared. A row change from the database is processed once, then fanned out to all relevant derived views.
2. State stays consistent. Every subscriber downstream sees the same truth, because they inherit from the same canonical source.

Deep Dive

The mechanics of the engine are covered in the LinkedQL Realtime Engineering Paper.

Scaling Behavior

Traditional realtime systems (GraphQL subscriptions, ad-hoc changefeeds, client-side replicas) recompute each subscription independently. Cost scales with the number of listeners.

LinkedQL avoids that.

LinkedQL’s cost scales with query diversity — the number of distinct canonical queries currently in play — not with the number of subscribers.

Put differently:

• 10,000 users watching variations of SELECT * FROM posts still converge to one canonical stream of posts.
• Each user’s filtered/sorted/limited view is derived from that stream instead of being recomputed from scratch.

Essentially, thanks to query inheritance; the system does not explode as the audience grows. Reactivity over SQL remains, not just feasible, but efficient over traditional database connections.

Appendix B — Implied Schema and Dialect

The examples in this document assume a simple illustrative schema and a specific SQL dialect.

Default Dialect

Unless otherwise noted, all examples assume the PostgreSQL syntax and semantics.
Equivalent behavior applies across other SQL dialects (e.g., MySQL, MariaDB) where supported.

Reference Schema

The following minimal schema underpins most examples:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT,
  email TEXT
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title TEXT,
  author_id INTEGER REFERENCES users (id),
  created_at TIMESTAMP DEFAULT NOW()
);