Data: typed tables — tcm-ts

Data: typed tables

import { table, transaction } from "#codebase/app/lib/server";

export type User = { name: string; email: string; joined: Date; plan: "free" | "pro"; logins: number };
export const users = table<User>("users");          // declared at the top level

await users.insert({ name: "ada", email: "ada@x", joined: new Date(), plan: "free", logins: 0 });
await users.find({ name: "ada" });                    // also get / put / delete / count

Typed tables never silently disagree with your code. They come with the server library (tcm lib add tcm server).

TypeScript erases User at runtime, but tcm reads it where the table is declared, derives a schema from it, and attaches the schema and its hash to the table. A table declared anywhere but the top level, or without a row type, says how to declare one when it's used.

Data lives in .tcm/data/store.sqlite, apart from the codebase. Under tcm test, tables live in memory.

Options

export const users = table<User>("users", {
  migrate: [fromV1, fromV2],   // one step per earlier row type, picked by its parameter type
  unique: ["email"],           // a duplicate write fails with a 409
  index: ["plan"],             // find({ plan: "pro" }) uses an index
  renamedFrom: "members",      // the old table's rows move over
});

Writes are checked

Every write is checked against User, and each row records the schema it was written with. Dates, maps and sets round-trip.

When the row type changes

Each stored row is checked as it's read:

  • Rows that still fit the new type (a new optional field, say) just work.
  • Rows that don't are converted by migrate. The result is checked, and the upgraded row is written back.
  • With no migrate, reading refuses with the reason. It never returns the wrong shape.

migrate is one function, or a chain of typed steps, oldest first:

export function fromV1(old: { body: string }) {
  return { text: old.body, tags: [] as string[] };
}
export function fromV2(old: { text: string; tags: string[] }): Note {
  return { ...old, pinned: false };
}
export const notes = table<Note>("notes", { migrate: [fromV1, fromV2] });

A row goes through the first step whose parameter type it fits, then every later step, until it fits the row type. A v1 row here goes through both steps, a v2 row through the second. No one function has to handle every past shape. A row no step takes fails with each step's reason ("step 1 (fromV1): row.body is missing; …").

  • tcm update reports this up front. When a row type changes, it checks the stored rows and says whether they fit, will be migrated, or need a step first. It also reports stored duplicates that would break a new unique field, and rows waiting to move from a renamedFrom table.
  • tcm data migrate <table> migrates every row now, instead of as rows are read. <table> is a table's name or the name of the definition declaring it. Afterwards it says whether migrate still has work. Once every row has the current type you can remove it, but keep it while an older build that writes the old type is still serving.
  • Old pages reload instead of misreading. A page from an older build that reads rows written by newer code gets a 410, and its browser reloads onto the current version.

Queries and indexes

  • find(where, { orderBy, desc, limit, offset }) returns { id, value }[] whose fields equal where's, oldest first unless ordered. findOne(where, opts) returns the first, count(where) how many.
  • get(id), put(id, value), insert(value) (returns the new id) and delete(id) (returns whether there was one) work by id.
  • unique and index fields get SQLite expression indexes. A write that would duplicate a unique field fails with HttpError(409) naming the other row. A write never replaces another row to satisfy uniqueness.
  • Queries run in SQL, using the indexes, once every row has the current type (after tcm data migrate, say). Until then, rows of older types are read through migrate first, so results never depend on whether a row was migrated yet.

Renaming a table

renamedFrom: "members" moves the old table's rows to the new name when the table is first used, and any rows an older build writes under the old name afterwards.

Atomic updates

update(id, fn) is an atomic read-modify-write. fn gets the current value and returns the new one, synchronously, while the row is locked. It returns the new value, or undefined if there's no such row.

await users.update(id, (u) => ({ ...u, logins: u.logins + 1 }));

Transactions

await transaction(async () => {
  const a = (await accounts.get(from))!;
  const b = (await accounts.get(to))!;
  await accounts.put(from, { ...a, balance: a.balance - amount });
  if (a.balance < amount) throw new Error("insufficient funds");   // rolls back the debit
  await accounts.put(to, { ...b, balance: b.balance + amount });
});

transaction(fn) runs any number of reads and writes, across tables, all or nothing. A throw rolls them all back. It returns fn's result.

  • Isolated. A transaction holds the store while it runs. Table calls inside it run in it; other requests' table calls wait until it commits or rolls back. Nobody sees a debit without its credit.
  • Jobs wait for the commit. Jobs enqueued inside a transaction are queued when it commits and dropped if it rolls back, so a failed signup doesn't send its welcome email.
  • Nesting. A transaction inside a transaction is a savepoint: its failure undoes only its own writes. update inside a transaction is a savepoint too.

Trade-offs:

  • Keep transactions short. While one runs, every other table call in the process waits.
  • Don't await anything inside a transaction that needs a table from outside it, such as a request to your own server. It would wait forever.
  • The lock is per process. Another process on the same store (tcm data migrate while the app serves) waits on SQLite's write lock for up to 5 seconds, then fails with "database is locked".

tcm data

  • tcm data lists tables and row counts, and notes tables whose rows span several row types.
  • tcm data <table> [n] shows the newest rows, each with the schema it was written with.
  • The studio's Data pages search, filter, sort, export and edit rows, each write checked as the table's own are.

Built from c482a14e