Query Caching

Add .cache() to a query to store its results. Holo clears the cached results automatically when you write to the same table through Holo, so you rarely have to invalidate anything by hand.

import { DB } from '@holo-js/db'

const activeUsers = await DB.table('users')
  .where('status', 'active')
  .cache(300)
  .get()

The first call runs the query and caches the rows for 300 seconds. Later calls with the same query and bindings read from the cache. After an insert, update, or delete on users, the next call hits the database again.

Query caching needs @holo-js/cache. See Cache to install it.

Cache model queries

Models support the same method:

import User from '../models/User'

const user = await User.where('id', 1).cache(60).first()
const recent = await User.query().orderBy('created_at', 'desc').cache(60).paginate(20)

.cache() works with the usual ways of reading results, including get(), first(), find(), value(), pluck(), count(), exists(), sum(), paginate(), simplePaginate(), and cursorPaginate().

Options

Pass a number of seconds (or a Date) for a simple TTL, or an object for more control:

const posts = await DB.table('posts')
  .where('published', true)
  .cache({
    ttl: 300,
    key: 'posts.published',
    driver: 'redis',
  })
  .get()

You must set either ttl or flexible. Each option is listed under Available methods.

Stale-while-revalidate

flexible works like cache.flexible(). Results are fresh for the first window, then served stale while one caller re-runs the query in the background:

const leaderboard = await DB.table('scores')
  .orderBy('points', 'desc')
  .limit(10)
  .cache({ flexible: [30, 300] })
  .get()

Custom keys

Set key when you want to clear an entry yourself, for example from a webhook:

import cache from '@holo-js/cache'

await cache.forget('posts.published')

Use a different key for each distinct query. Two queries with the same key share one cache entry.

Query caching bypasses shared reads and writes inside transactions and explicit PostgreSQL/MySQL connection scopes. It also bypasses them for injected clients, pools or pool factories, and after Holo observes raw SQL that may change session or schema resolution. That last restriction lasts until disconnect; simple ordinary reads and framework introspection keep caching enabled on default managed pools. The raw SQL check is conservative: multiple statements, MySQL executable comments, unterminated literals or comments, PostgreSQL U& identifiers, and strings with ambiguous backslash escaping can disable reuse until disconnect even when the SQL only reads data. Semicolons and SQL-like text inside ordinary literals and comments, such as SELECT 'a; b', do not. These conservative paths run the query normally, including when a custom cache key is supplied.

Holo-observed schema changes clear query-cache entries, including custom keys with explicit invalidation lists, and refresh existing realtime queries. Timed-out DDL clears them again when the underlying driver operation settles. SQL executed outside Holo does not provide this signal.

Automatic invalidation

For most queries, Holo records which table a cached result depends on. When you insert, update, upsert, increment, or delete rows in that table through Holo's query builder or models, it clears the matching entries. Inside a transaction, it waits until the transaction commits.

Automatic tracking is skipped for queries that use:

• joins or unions
• raw selects, raw orderBy, or raw where clauses
• subquery selects or subquery where clauses
• whereExists
• most having clauses

For those queries, choose one of these:

• list the tables to watch with invalidate
• set a key and call cache.forget() after writes
• don't cache that query

const feed = await DB.table('posts')
  .join('users', 'users.id', '=', 'posts.user_id')
  .select('posts.title', 'users.name')
  .cache({ ttl: 300, invalidate: ['posts', 'users'] })
  .get()

A plain table name in invalidate uses the query's connection. To watch a table on another connection, write db:<connection>:<table>, for example 'db:analytics:events'.

Values the database compares loosely

A database can match a write to a cached row even when the two values differ as written, so Holo treats values it can't prove different as possibly equal and clears more entries:

• Text that reads as a number ('5', ' 05', '5.0', '1e3') can match the number 5 on every database. A write through where('id', '5') clears a cached where('id', 5), and a write through where('id', 5) clears a cached where('id', '5'). MySQL also converts other text when it compares it with a number, so '5abc' matches 5 and 'abc' matches 0. On MySQL, a write with a number to a text column clears every cached read by key on that table, and a cached read by a number on a text column, or by text on a column that isn't text, is cleared by any write to its table.
• Case- and accent-insensitive text. SQLite COLLATE NOCASE and MySQL's _ci collations, the MySQL default, compare 'fr' and 'FR' as equal. Holo reads each column's collation, and a write with text to a column that doesn't compare exactly clears every cached read by key on that table and queries that filter that column by different text.
• Exact text on SQLite and MySQL. SQLite BINARY, the default, and MySQL _bin collations such as utf8mb4_bin compare text exactly, so writes to those columns keep precise invalidation. MySQL _cs collations aren't exact: they still match strings that differ only by ignorable or decomposed characters. MySQL ENUM and SET columns aren't exact either, because MySQL reads '1' as the first member.
• Trailing spaces. SQLite RTRIM ignores trailing spaces, and MySQL ignores them in PAD SPACE collations such as utf8mb4_bin and trims them when it stores CHAR values or text longer than a column. Text that ends in spaces clears every cached read by key on SQLite RTRIM columns and on every MySQL text column.
• Where Holo reads collations. Holo reads them from the CREATE TABLE statement and unique indexes on SQLite, where an upsert matches rows through a unique key's collation, and from SHOW CREATE TABLE and SHOW FULL COLUMNS on MySQL. On MySQL, a table name that resolves to a temporary table is treated as case-insensitive, since temporary tables belong to one session. A temporary table that another pooled connection's lookup can't see, because it exists only in one session, isn't detected, so give temporary tables names that no permanent table uses. It reads them once per table, and again after a migration that runs through Holo in the same process. If it can't read them, it treats every text column as case-insensitive.
• Upserts through another unique column. An upsert() whose uniqueBy columns aren't the primary key can update a row whose key differs from the one written, so it clears every cached read by key on that table unless the database returns the written rows. MySQL's ON DUPLICATE KEY UPDATE matches any unique key whatever uniqueBy lists, so every MySQL upsert() does this.
• PostgreSQL compares text exactly by default and keeps precise invalidation. Holo reads from PostgreSQL which columns are citext, a domain or array built on citext, or use a nondeterministic collation such as CREATE COLLATION ... (deterministic = false), and treats those columns as case-insensitive. It reads them the first time a write with text values reaches the table, and again after a migration that runs through Holo in the same process.

Realtime queries follow the same rules, so a subscription refreshes after any write the database matched to its rows.

WARNING

Simple raw INSERT INTO, UPDATE … SET, and DELETE FROM statements run through Holo also clear cached reads of the identified target table, including cached reads of individual rows. Invalidation waits for transaction commit. See raw SQL for recognition limits.

A raw WITH … UPDATE with no recognized target leaves cached results unchanged. Do not rely on automatic invalidation for other unrecognized raw writes. Changes from external SQL clients or other apps, and changes to other tables through database triggers, don't clear the cache.

After an unrecognized write, call await DB.connection().invalidateTables(['posts']) with every affected table to clear cached queries, including reads of individual rows, and refresh realtime subscriptions. Inside a transaction, use await tx.invalidateTables(['posts']): it registers the refresh and returns before commit. Commit publishes it; rollback discards it. See the explicit invalidation example.

The list of which entries depend on which tables lives in each server process's memory. If you run several servers or workers against a shared Redis or database cache, a write on one server doesn't clear entries another server cached. Those entries stay until their TTL ends. Keep TTLs short in that setup, or clear keys yourself.

Remote writes reach only Broadcast workers. With Broadcast Redis scaling on, a write in another process clears the cached results for the tables it changed inside each worker, and refreshes the Realtime queries that read those tables. Your web app and queue processes don't receive these invalidations. A shared value store, Redis or database, isn't enough on its own, because each process keeps its own dependency index and no shared index ships with Holo. For cross-process consistency, evict on write with cache.forget(), or keep TTLs short. A cache store that supplies its own dependency index must implement listDependencies() for worker invalidation to work. Without it, the worker logs one warning and keeps the entries until their TTL ends.

When results are not cached

• Queries with lockForUpdate() or sharedLock() always hit the database.
• A Date TTL that is already in the past skips the cache.

Many identical requests that arrive while the cache is empty share one database query instead of each running their own.

Available methods

Everything you can call or set for query caching, grouped by task. Each item links to its own entry below.

Cache a query

• .cache(ttl)
• .cache(config)

Options for .cache(config)

• ttl
• flexible
• key
• driver
• invalidate

Listen for invalidation

• onDatabaseDependencyInvalidated()

.cache(ttl)

Caches the results for ttl seconds, or until a Date. It's shorthand for .cache({ ttl }). A Date that is already in the past skips the cache and runs the query. Available on table and model queries.

const user = await User.where('id', 1).cache(60).first()

.cache(config)

Caches the results with options. Pass an object with ttl or flexible, plus any of the options below. It throws a configuration error if you pass neither ttl nor flexible, or both.

const posts = await DB.table('posts')
  .where('published', true)
  .cache({ ttl: 300, key: 'posts.published', driver: 'redis' })
  .get()

ttl

Seconds to keep the results, or a Date to keep them until. Use this or flexible, not both.

flexible

A [fresh, stale] pair or a { fresh, stale } object, in seconds. Results are fresh for the first window, then served stale while one caller re-runs the query. See Stale-while-revalidate.

key

Your own cache key. Without it, Holo builds one from the SQL, the bindings, and the connection. Use a different key for each distinct query. Two queries with the same key share one entry.

driver

Name of a store from config/cache.ts. Defaults to the default store.

invalidate

Tables whose writes clear this entry. A plain table name uses the query's connection. Write db:<connection>:<table> to watch a table on another connection. A non-empty list replaces automatic tracking for this query. See Automatic invalidation.

onDatabaseDependencyInvalidated(listener)

Calls listener each time a write clears query-cache entries. The listener receives { connectionName, dependencies }, where dependencies lists the tables or db: dependencies that changed. Inside a transaction, it runs after the commit. It returns a function that removes the listener. Errors thrown by the listener are logged, not rethrown.

import { onDatabaseDependencyInvalidated } from '@holo-js/db'

const stopListening = onDatabaseDependencyInvalidated(({ connectionName, dependencies }) => {
  console.log(connectionName, dependencies)
})

Next steps

• Using the cache
• Query builder basics
• Querying models