Skip to content
Active Record
Esc
↑↓navigate↵open⌘Jpreview
On this page

Counter caches

Keep a count of a record’s associated records on the record itself, so reading it needs no query.

At a glance

To Write
keep an article’s count of comments belongsTo(Article, { counterCache: true }), with commentsCount
keep it in an attribute of another name counterCache: "replyCount", on the belongsTo and the hasMany
read the count, with no query article.comments().size(), isEmpty(), hasAny()
set the count from the rows Article.resetCounters(id, "comments")
fill in a new counter before it’s read counterCache: { attribute: "commentsCount", active: false }

The Models

The examples use these Models, whose attributes come from their tables through the schema active-record schema generates:

import { hasMany } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Comment } from "./comment";

export class Article extends ApplicationRecord {
  comments = hasMany(Comment); // size() reads commentsCount
}
import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Article } from "./article";

export class Comment extends ApplicationRecord {
  article = belongsTo(Article, { counterCache: true }); // keeps Article's commentsCount
}
import { hasMany } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Reply } from "./reply";

export class Forum extends ApplicationRecord {
  replies = hasMany(Reply, { counterCache: "replyCount" }); // not repliesCount
}
import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Forum } from "./forum";

export class Reply extends ApplicationRecord {
  forum = belongsTo(Forum, { counterCache: "replyCount" });
}
import { belongsTo } from "@forestfuture/active-record";
import { ApplicationRecord } from "./application-record";
import { Article } from "./article";

export class ImportedComment extends ApplicationRecord {
  static override table = "comments";

  // Writes keep commentsCount, but nothing reads it until active: false goes.
  article = belongsTo(Article, { counterCache: { attribute: "commentsCount", active: false } });
}
// Generated by `active-record schema` from migrations/ — don't edit.
// schema: 2ed73c98509c

import { defineSchema, type TableAttributes } from "@forestfuture/active-record";
import { d1 } from "@forestfuture/active-record/d1";

/** articles — last changed by 0001_create_tables.sql */
export const articles = {
  id: { type: "string", null: false, generateId: true },
  title: { type: "string", null: false },
  commentsCount: { type: "integer", null: false, default: () => 0 },
  updatedAt: "datetime",
} as const;

/** comments — last changed by 0001_create_tables.sql */
export const comments = {
  id: { type: "string", null: false, generateId: true },
  articleId: { type: "string", null: false },
  body: { type: "string", null: false },
} as const;

/** forums — last changed by 0001_create_tables.sql */
export const forums = {
  id: { type: "string", null: false, generateId: true },
  replyCount: { type: "integer", null: false, default: () => 0 },
} as const;

/** replies — last changed by 0001_create_tables.sql */
export const replies = {
  id: { type: "string", null: false, generateId: true },
  forumId: { type: "string", null: false },
} as const;

/** The database's adapter and binding, its tables (with their primary keys), and the Models built on them. */
export const schema = defineSchema({
  adapter: d1({ binding: "DB" }),
  tables: {
    articles: { attributes: articles, primaryKey: "id" },
    comments: { attributes: comments, primaryKey: "id" },
    forums: { attributes: forums, primaryKey: "id" },
    replies: { attributes: replies, primaryKey: "id" },
  },
  models: {
    Article: "articles",
    Comment: "comments",
    Forum: "forums",
    ImportedComment: "comments",
    Reply: "replies",
  },
});

declare module "../models/article" {
  interface Article extends TableAttributes<typeof articles, (typeof Article)["attributes"]> {}
}

declare module "../models/comment" {
  interface Comment extends TableAttributes<typeof comments, (typeof Comment)["attributes"]> {}
}

declare module "../models/forum" {
  interface Forum extends TableAttributes<typeof forums, (typeof Forum)["attributes"]> {}
}

declare module "../models/imported-comment" {
  interface ImportedComment
    extends TableAttributes<typeof comments, (typeof ImportedComment)["attributes"]> {}
}

declare module "../models/reply" {
  interface Reply extends TableAttributes<typeof replies, (typeof Reply)["attributes"]> {}
}
CREATE TABLE articles (
  id TEXT PRIMARY KEY NOT NULL,
  title TEXT NOT NULL,
  comments_count INTEGER NOT NULL DEFAULT 0,
  updated_at DATETIME
);
CREATE TABLE comments (
  id TEXT PRIMARY KEY NOT NULL,
  article_id TEXT NOT NULL REFERENCES articles (id),
  body TEXT NOT NULL
);
CREATE TABLE forums (
  id TEXT PRIMARY KEY NOT NULL,
  reply_count INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE replies (
  id TEXT PRIMARY KEY NOT NULL,
  forum_id TEXT NOT NULL REFERENCES forums (id)
);
CREATE TABLE articles (
  id UUID PRIMARY KEY,
  title TEXT NOT NULL,
  comments_count INTEGER NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ
);
CREATE TABLE comments (
  id UUID PRIMARY KEY,
  article_id UUID NOT NULL REFERENCES articles (id),
  body TEXT NOT NULL
);
CREATE TABLE forums (
  id UUID PRIMARY KEY,
  reply_count INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE replies (
  id UUID PRIMARY KEY,
  forum_id UUID NOT NULL REFERENCES forums (id)
);

Declaring a counter cache

Declare counterCache on the counted records’ belongsTo, as Comment does, and give the owner an attribute for the count:

  • The attribute is named for the counted Model: commentsCount for comments.
  • To keep it in another attribute, name it, as Reply does: counterCache: "replyCount".
  • A polymorphic belongsTo counts a record on whichever listed Model its type names, so each of them declares the attribute.
  • A counter cache on a Model on another database throws InvalidModel, since its count couldn’t be written in the same batch. So do two belongsTos on one foreign key that keep the same attribute, since each comment would count twice.

Counting records

Creating a comment adds one to its article’s count, and destroying it takes one away:

import { class ArticleArticle } from "./models/article";

const const article: Articlearticle = await class ArticleArticle.findByOrThrow<typeof Article>(this: typeof Article, conditions: SafeSql | Conditions<Article>): Promise<Article>
Like `findBy`, but throws `RecordNotFound` instead of returning null.
findByOrThrow
({ title?: string | readonly string[] | Comparison<string> | undefinedtitle: "Hello" });
const const comment: Commentcomment = await const article: Articlearticle.Article.comments: () => HasManyCollection<Comment, ScopesOf<typeof Comment>>comments().
Relation<Comment, ScopesOf<typeof Comment>>.create(attributes?: Partial<AttributeProperties<OverriddenSpecs<{
    readonly id: {
        readonly type: "string";
        readonly null: false;
        readonly generateId: true;
    };
    readonly articleId: {
        readonly type: "string";
        readonly null: false;
    };
    readonly body: {
        readonly type: "string";
        readonly null: false;
    };
}, Readonly<Record<string, false | AttributeOverride>> | undefined>>>, init?: Init<Comment> | undefined): Promise<Comment>
Builds a record, awaits `init` with it if it's given, and saves it; resolves to it whether or not it was saved (check `isPersisted()`).
create
({ body?: string | undefinedbody: "First!" }); // commentsCount + 1
await const comment: Commentcomment.Model.destroy(): Promise<false | Comment>
Deletes the row and resolves to this record, now a read-only destroyed snapshot. A row that's already gone still counts. `false` means a `beforeDestroy` or `aroundDestroy` callback aborted (or an `aroundDestroy` didn't call `next()`), or a `restrictWithError` association refused.
destroy
(); // commentsCount - 1
  • Changing a comment’s articleId moves the count to the other article. An article you’ve loaded takes the new count too, without a save.
  • Removing comments through the article’s association counts them out: delete, clear, and deleteAll() on article.comments(). Removing records through an association through another counts out its join records the same way.
  • Bulk writes leave the count alone: insert, insertAll, upsert, updateAll, and deleteAll() on any other Relation.

Reading the count

size(), isEmpty(), and hasAny() on the association read the count, without a query:

import { class ArticleArticle } from "./models/article";

const const article: Articlearticle = await class ArticleArticle.findByOrThrow<typeof Article>(this: typeof Article, conditions: SafeSql | Conditions<Article>): Promise<Article>
Like `findBy`, but throws `RecordNotFound` instead of returning null.
findByOrThrow
({ title?: string | readonly string[] | Comparison<string> | undefinedtitle: "Hello" });
const const count: numbercount = await const article: Articlearticle.Article.comments: () => HasManyCollection<Comment, ScopesOf<typeof Comment>>comments().Relation<Comment, ScopesOf<typeof Comment>>.size(): Promise<number>
The number of records: a loaded association's (built records included) without a query; otherwise a `COUNT(*)`, plus an association's unsaved built records.
size
(); // reads commentsCount, with no query
  • An association reads the counter named for it: comments reads commentsCount. If its counter has another name, name it on the hasMany too, as Forum does.
  • An association with any other name counts its rows, so one narrowed by a scope counts only its own records.
  • count() always counts the rows.

Resetting a count

Set a count from the rows with resetCounters, after a bulk write for example:

import { class ArticleArticle } from "./models/article";

const const article: Articlearticle = await class ArticleArticle.findByOrThrow<typeof Article>(this: typeof Article, conditions: SafeSql | Conditions<Article>): Promise<Article>
Like `findBy`, but throws `RecordNotFound` instead of returning null.
findByOrThrow
({ title?: string | readonly string[] | Comparison<string> | undefinedtitle: "Hello" });
await class ArticleArticle.KeyedWriteStatics.resetCounters: <typeof Article>(this: typeof Article, id: string | number, names: "comments" | readonly "comments"[], options?: CounterOptions<Article> | undefined) => Promise<void>
Sets the counter caches of the record with this primary key (#61) to how many records each named hasMany has, counted in the same write: `Topic.resetCounters(id, "replies")`, or a list of names, and `{ touch }`. A missing record is `RecordNotFound`, as `find`'s.
resetCounters
(const article: Articlearticle.id: stringid, "comments", { CounterOptions<Article>.touch?: Touch<Article> | undefinedtouch: true }); // counts the rows
  • It takes one association’s name or a list of them, and sets every counter the association’s records keep on the article.
  • touch: true also sets updatedAt.
  • An association with a scope can’t reset a counter, since its records aren’t all the ones counted.

Adding a counter to existing records

Declare a new counter with active: false, as ImportedComment does, to fill it in before anything reads it:

  • A new counter starts at zero, even for an article that already has comments.
  • With active: false, writes keep the count up to date, while size() and the others count the rows.
  • Run resetCounters for each article, then remove active: false.

Last updated on