Querying with liveQuery

The item hooks cover most reads: useItems lists a model's items and useItem shows one. When you need to filter or sort by a property value in the database, count rows, or watch something the item hooks don't expose, run a live query: a SQL query that runs again whenever the tables it reads change. In React that's useLiveQuery; elsewhere it's BaseDb.liveQuery.

You'll need: the blog schema and the React setup from Reading and writing data, and the Post type from Typing items in TypeScript.

Decide whether you need SQL

Start with the item hooks and Item.all, and reach for SQL only when they can't express the query:

useItems doesn't re-render when a property is edited, so sorting its results by title in JavaScript won't move a renamed post. A live query on the stored value re-runs on every save.

Know what you're querying

Live queries read the SDK's own SQLite tables. The table layout is internal and isn't a public API. It can change in any release, and nothing type-checks your SQL against it. Keep raw queries in one module so there's one place to fix, and prefer the item hooks wherever they're enough.

Tables lists the columns of seeds (one row per item) and metadata (one row per property value). In SQL they're snake_case: local_id, property_value.

metadata keeps more than one row per property: each version of an item has its own rows, and published values sit next to newer local edits. To get a property's current value, take the newest row in the SDK's order: rows that aren't revoked first, then COALESCE(attestation_created_at, created_at) descending, then local_id descending.

Properties of type Relation, Image, File and Html store another row's ID rather than the content, and relations can be stored under another name: a single relation is author when written locally and authorId when synced, and a list of relations gets a generated name. Tables has the rules; in SQL, stick to the other types where you can.

Every value is text. A Boolean is stored as 'true' or 'false' and a Number as a numeric string, so compare with 'true', and CAST(property_value AS REAL) before you sort numbers.

Write the query

This query returns every draft Post with its current title and published value, sorted by title. Each correlated subquery picks the newest row for one property, and seeds.type holds the model's easName, 'post' for Post:

export type PostIndexRow = {
  local_id: string
  title: string | null
  published: string | null
}

export const postIndexQuery = (sql: any) => sql`
  SELECT s.local_id,
    (SELECT m.property_value FROM metadata m
      WHERE m.seed_local_id = s.local_id AND m.property_name = 'title'
      ORDER BY (m.revoked_at IS NOT NULL),
               COALESCE(m.attestation_created_at, m.created_at) DESC, m.local_id DESC
      LIMIT 1) AS title,
    (SELECT m.property_value FROM metadata m
      WHERE m.seed_local_id = s.local_id AND m.property_name = 'published'
      ORDER BY (m.revoked_at IS NOT NULL),
               COALESCE(m.attestation_created_at, m.created_at) DESC, m.local_id DESC
      LIMIT 1) AS published
  FROM seeds s
  WHERE s.type = 'post'
    AND (s.uid IS NULL OR s.uid = '')
    AND (s._marked_for_deletion IS NULL OR s._marked_for_deletion = 0)
    AND (s.revoked_at IS NULL OR s.revoked_at = 0)
  ORDER BY title COLLATE NOCASE ASC
`

The uid condition keeps local drafts only, matching useItems' default. Remove it to include published and synced items. The function is defined once at module level, so it's the same object on every render, which matters below.

Subscribe in a component

Pass the query to useLiveQuery. It returns the rows as a bare array, or undefined until the first result arrives:

import { useLiveQuery } from '@seedprotocol/react'
import { postIndexQuery, type PostIndexRow } from './queries'

export function PostIndex() {
  const rows = useLiveQuery<PostIndexRow>(postIndexQuery)

  if (rows === undefined) return <p>Loading…</p>
  if (rows.length === 0) return <p>No posts yet</p>

  return (
    <ol>
      {rows.map((row) => (
        <li key={row.local_id}>
          {row.title || 'Untitled'} {row.published === 'true' && '(published)'}
        </li>
      ))}
    </ol>
  )
}

Rename a post and the list re-sorts. Toggle published and the label follows. Create or delete a post and it appears or disappears. The hook re-runs the query whenever a write touches a table it reads, including one read only in a subquery.

To show more of each post, or values you'd rather not pull through SQL, use the query for IDs and order only, and render each row with useItem. Building a list view that updates in real time builds that out.

Memoise the query

useLiveQuery matches a SQL tag function by reference, so an inline (sql) => sql`…` is a new query on every render: the hook subscribes, gets rows, re-renders and loops. Define tag functions at module level, or build them in useMemo with the values they use as dependencies; useLiveQuery has the matching rules.

import { useMemo } from 'react'
import { useLiveQuery } from '@seedprotocol/react'

function PostsByStatus({ published }: { published: boolean }) {
  const query = useMemo(
    () => (sql: any) =>
      sql`SELECT m.seed_local_id AS local_id FROM metadata m
          JOIN seeds s ON s.local_id = m.seed_local_id
          WHERE s.type = 'post' AND m.property_name = 'published'
            AND m.property_value = ${published ? 'true' : 'false'}
            AND (s._marked_for_deletion IS NULL OR s._marked_for_deletion = 0)`,
    [published],
  )
  const rows = useLiveQuery<{ local_id: string }>(query)
  return <p>{rows?.length ?? '…'} post(s)</p>
}

Values interpolated with ${…} become bound parameters, so they're safe to take from user input. This query matches every stored row of published, including older versions, which is fine for a rough count. Use the newest-row pattern above when it must be exact.

When the query changes, useLiveQuery goes back to undefined until the new query's first result arrives, so undefined also means a new filter is loading.

Drizzle queries

useLiveQuery also takes a Drizzle select built from BaseDb.getAppDb(). Import the operators from @seedprotocol/sdk/drizzle, the SDK's own copy of Drizzle, rather than installing drizzle-orm (BaseDb.getAppDb). A Drizzle select is matched by its SQL and parameters, so one built during render doesn't loop. getAppDb() throws until the client has set up the database, and the children of SeedClientGate render before that. So build the query only once the client is ready, and include readiness in the dependencies. A useMemo with no dependencies would compute null on the first render and keep it.

import { useMemo } from 'react'
import { and, eq, isNull, or } from '@seedprotocol/sdk/drizzle'
import { BaseDb, seeds } from '@seedprotocol/sdk'
import { useGlobalServiceStatus, useLiveQuery } from '@seedprotocol/react'

function PostCount() {
  const { status } = useGlobalServiceStatus()
  const isReady = status === 'idle'

  const query = useMemo(() => {
    if (!isReady) return null
    return BaseDb.getAppDb()
      .select({ id: seeds.localId })
      .from(seeds)
      .where(
        and(
          eq(seeds.type, 'post'),
          or(isNull(seeds._markedForDeletion), eq(seeds._markedForDeletion, 0)),
        ),
      )
  }, [isReady])

  const rows = useLiveQuery<{ id: string }>(query)
  return <p>{rows === undefined ? '…' : `${rows.length} post(s)`}</p>
}

Rows from a Drizzle select use the keys you gave select(). Rows from a SQL tag use the database's column names, or your AS aliases.

Get errors

useLiveQuery has no error output. If the query fails, for example because of a typo in a column name, it keeps returning what it had, often undefined, so a broken query looks like one that's still loading. The console shows only SQLite's sqlite3_step() warning. The SDK logs the error itself with debug under seedSdk:browser:db:Db: set localStorage.debug = 'seedSdk:browser:db:Db' and reload, and it appears at the console's Verbose level.

useLiveQueryState takes the same query and returns { data, error }. data is what useLiveQuery returns. error is the error the last attempt failed with, including one thrown while setting up the live query. It's cleared by the next result and when the query changes:

import { useLiveQueryState } from '@seedprotocol/react'
import { postIndexQuery, type PostIndexRow } from './queries'

function PostIndex() {
  const { data: rows, error } = useLiveQueryState<PostIndexRow>(postIndexQuery)
  if (error) return <p role="alert">Query failed: {error.message}</p>
  if (rows === undefined) return <p>Loading…</p>
  return <p>{rows.length} post(s)</p>
}

A misspelled column or table gives argument to tables_used() is not a valid SQL statement, not SQLite's own message.

After a failure, both hooks keep the last rows and subscribe again after 0.5, 1, 2, 5 and 10 seconds, then every 30 seconds, until a result arrives. A query that failed for a reason that goes away, such as a table your app creates later, starts returning rows at the next attempt, and error goes back to null. A query with a typo fails at every attempt, so fix the query.

Outside React, and on Node

BaseDb.liveQuery returns an RxJS observable. Subscribe after client.init() has resolved, and unsubscribe when you're done:

import { and, eq, isNull, or } from '@seedprotocol/sdk/drizzle'
import { BaseDb, seeds } from '@seedprotocol/sdk'

const db = BaseDb.getAppDb()

const subscription = BaseDb.liveQuery<{ id: string }>(
  db
    .select({ id: seeds.localId })
    .from(seeds)
    .where(
      and(
        eq(seeds.type, 'post'),
        or(isNull(seeds._markedForDeletion), eq(seeds._markedForDeletion, 0)),
      ),
    ),
).subscribe({
  next: (rows) => console.log(`${rows.length} posts`),
  error: (err) => console.error('Query failed', err),
})

// later
subscription.unsubscribe()

In the browser, this re-runs when a table the query reads changes, in this tab or another tab of the same app. On Node it polls once a second and takes Drizzle queries only; see Browser and Node. Using Seed in Node and Electron covers running the client on Node.

Troubleshooting

A Drizzle query stays undefined. It was built in useMemo before the client was ready and is still null. Add readiness to the dependencies, as in Drizzle queries.

A Drizzle query throws Db not configured. BaseDb.getAppDb() was called before the client set up the database. Build the query only when useGlobalServiceStatus().status === 'idle'.

A filter on an Image, File or relation property matches nothing. Those values hold another row's ID, and a synced single relation is stored under <name>Id. See Know what you're querying.

A count fails with The passed SQL does not read any tables. SQLite answered the query from an index without reading the table, so there's no table to watch. It happens with count(*) on seeds or metadata, even with a WHERE on an indexed column such as type. Count a column no index holds, such as count(created_at) on seeds or count(property_value) on metadata, or add a condition on one, such as the _marked_for_deletion check.

Results include old values. You read every metadata row for a property instead of the newest one.

It worked, then broke after an upgrade. The internal tables changed. Check the release notes and update your queries module.

Related

• useLiveQuery: the hook's parameters and behavior
• Querying: BaseDb, the exported table objects, and liveQuery
• The reactivity model: live queries next to actor subscriptions and events
• Building a list view that updates in real time
• Reading and writing data: useItems and Item.all