Strata · guide

A sequence for your tables

Two migrations run on a Postgres, a third appended later, and the ledger that remembers all three.

Install it

pnpm add @niscorp/strata zod @electric-sql/pglite

Step 1 of 5

A Postgres, and a pool

PGlite is Postgres compiled to run wherever Node runs, so this file needs no server.

Strata does not ask for a driver. It asks for two functions: query, and transaction, which it needs because a run lands whole or not at all. A pg pool is wrapped the same way.

A Postgres, and a pool

import { PGlite } from '@electric-sql/pglite';
import { defineSequence } from '@niscorp/strata';
import { migrate, readLedger, type StrataPool } from '@niscorp/strata/postgres';

const db = new PGlite();
const pool: StrataPool = {
  query: (text, values) => db.query<Record<string, unknown>>(text, values),
  transaction: (run) => db.transaction((tx) => run({ query: (text, values) => tx.query(text, values) })),
};

Step 2 of 5

Two migrations

A sequence is a list of migrations with an owner. The id says whose it is: the theatre’s app. A migration is a description and its steps, and a step is one SQL statement.

There is no version number to write. A migration’s number is its place in the list.

Two migrations

const theatre = defineSequence({
  id: 'theatre.app',
  migrations: [
    {
      description: 'Shows',
      steps: [{ kind: 'sql', sql: 'CREATE TABLE shows (id text PRIMARY KEY, title text NOT NULL)' }],
    },
    {
      description: 'Bookings',
      steps: [{ kind: 'sql', sql: 'CREATE TABLE bookings (id text PRIMARY KEY, show_id text REFERENCES shows(id))' }],
    },
  ],
});

Step 3 of 5

Run it, twice

migrate works out what this database has not run yet, runs it in one transaction, and writes each migration into a ledger table. The report says what was applied.

The second run finds both in the ledger and applies nothing. That is why an app can call it every time it starts.

Run it, twice

const first = await migrate(pool, [theatre]);
console.log('first run:', first.applied.map((migration) => migration.ref));

const second = await migrate(pool, [theatre]);
console.log('second run:', second.applied.map((migration) => migration.ref));

Step 4 of 5

Append a third

A change to the tables is a new migration at the end of the list. The two before it are left exactly as they were, and only the new one runs.

Append a third

const withPasses = defineSequence({
  id: 'theatre.app',
  migrations: [
    ...theatre.migrations,
    {
      description: 'Season passes',
      steps: [{ kind: 'sql', sql: 'CREATE TABLE passes (member_id text PRIMARY KEY, season text NOT NULL)' }],
    },
  ],
});
const third = await migrate(pool, [withPasses]);
console.log('after appending:', third.applied.map((migration) => migration.ref));

Step 5 of 5

The ledger

The ledger is a table beside your own, strata_ledger. Each row is a migration this database has run, with a checksum of its steps. The checksum is how Strata knows later that the code still says what was run.

The ledger

for (const row of await readLedger(pool)) {
  console.log(row.sequence + '/' + row.n, row.description, row.checksum.slice(0, 8));
}

Then

Run it.

Two migrations applied by the first run, none by the second, one after the third was appended, and the three rows the ledger then holds.

Run it

npx tsx tables.mts

It prints

first run: [ 'theatre.app/1', 'theatre.app/2' ]
second run: []
after appending: [ 'theatre.app/3' ]
theatre.app/1 Shows e8bb35db
theatre.app/2 Bookings 040135ea
theatre.app/3 Season passes de724bcc