---
title: "@octabits-io/backfill"
summary: "One-shot data backfills, the idempotent layer above your SQL migrations: a named unit of work that selects only the rows still needing it, a marker table that records a fully clean run so the next deploy skips it in one primary-key lookup, and a chain runner that owns the skip / mark / partial-retry protocol — a pending or partial run is never marked, a failure aborts the chain so the deploy fails loudly."
install: "pnpm add @octabits-io/backfill"
availability: "Available from November 2026"
version: "0.5.2"
license: "MIT"
related: []
canonical_url: "https://octabits.io/platform/backfill"
---

# `@octabits-io/backfill`

SQL migrations change shapes; backfills move data into them. This is that
second half, made repeatable: a named, idempotent unit of work, a marker table
that records a fully clean run, and a chain runner that owns the
skip / mark / partial-retry protocol so a deploy pipeline can call the whole
list on every deploy and pay one primary-key lookup per finished backfill.
Pure functions with **no dependency at all**; the marker table ships as a
plain-SQL store over a structural executor and as a Drizzle adapter.

```bash
npm i @octabits-io/backfill            # nothing else — bring the executor or the ORM you already have
```

```ts
import { runBackfills } from '@octabits-io/backfill';
import { createPostgresBackfillMarkerStore } from '@octabits-io/backfill/postgres';

// after your schema migrations, on every deploy:
await runBackfills(createPostgresBackfillMarkerStore({ db: pool }), [
  {
    name: 'backfill-invoice-totals-v2',
    run: async () => {
      const { processed, failures } = await transformPendingRows();   // WHERE new_col IS NULL AND legacy_col IS NOT NULL
      const pending = await countRowsStillNeedingWork();
      return { processed, failures, pending };
    },
  },
]);
```

| Subpath | Deps | Contents |
|---|---|---|
| `.` | none | the `BackfillMarkerStore` contract, `runBackfills` and its protocol, `createInMemoryBackfillMarkerStore()`, the `BackfillDef` / `BackfillOutcome` / `BackfillRunSummary` / `DataMigrationRun` / `RunBackfillsOptions` / `BackfillLogger` types |
| `./postgres` | none (structural executor) | `createPostgresBackfillMarkerStore({ db, table? })` and `dataMigrationRunsDdl({ table? })` over any `query(text, params) → { rows }` — a `pg` Pool or Client, PGlite, a postgres.js wrapper |
| `.` (migrations) | none | `scanMigration(sql, { name })`, `splitMigration(sql)`, `ACKNOWLEDGEABLE_RISKS`, the `MigrationFinding` / `MigrationRisk` types — reading a migration for what breaks existing rows |
| `./postgres` (migrations) | none (structural executor, one connection) | `checkMigrationsOnRows({ db, migrations })` on a throwaway database, `dryRunMigrations({ db, migrations })` in a rolled-back transaction |
| `./drizzle` | `drizzle-orm` (optional peer) | `dataMigrationRunsColumns` to spread into your schema, `createDrizzleBackfillMarkerStore(db)`, the marker helpers `ensureDataMigrationRunsTable` / `isDataMigrationCompleted` / `markDataMigrationCompleted`, and `runBackfills(db, …)` — the root's runner with a db as its first argument |

A boundary lint keeps the root and `./postgres` importing nothing at all and
`drizzle-orm` confined to `./drizzle`, and the whole package type-checks with
no Node typings.

## The protocol

A backfill is a named, idempotent unit of work whose `run()` selects **only**
the rows that still need it — re-running on a clean database must do nothing —
and reports `{ processed, failures, pending }`. `runBackfills` decides what
that means:

- a **marked** backfill is skipped, so a finished one costs one primary-key
  lookup per deploy;
- a **clean** run (`failures === 0 && pending === 0`, no `skipMarking`) is
  marked once, and never re-marked — the first marker's timestamp and notes
  win;
- a run with **`pending > 0`** is left unmarked and retried on the next
  deploy, automatically;
- **`skipMarking: true`** says the invocation covered only part of the data,
  so a partial-scope run never claims global completion;
- **`failures > 0`** throws and aborts the chain, so the deploy fails loudly
  instead of quietly marking bad state.

`force: true` re-runs a marked backfill without re-marking it.

## The marker table

Markers live in an append-only `data_migration_runs` table — the same
category as an ORM's own migrations table, created on demand with
`CREATE TABLE IF NOT EXISTS`, no migration file and no snapshot footprint.
`dataMigrationRunsDdl({ table? })` emits it for schemas that would rather
declare it. Name a different table with `table` on either store.

It has **no scope column, on purpose**: "done" is a global state, because a
backfill targets a legacy shape that disappears everywhere at once. Scope
fan-out — per organisation, per workspace, per account — lives inside your
`run()`; encode a scope in the name if a deployment genuinely needs per-scope
marking, and return `skipMarking: true` from a filtered invocation.

## On Drizzle

```ts
import { runBackfills, ensureDataMigrationRunsTable } from '@octabits-io/backfill/drizzle';

await runBackfills(db, backfills);      // the same protocol, over the Drizzle marker table
```

Same name as the root's runner, different first argument: this one takes the
db, the root's takes a marker store. The db is a **structural** view
(`execute` / `select` / `insert`), so a Drizzle instance and a transaction
context both satisfy it and a second copy of `drizzle-orm` in the tree cannot
break it. The marker helpers are exported for standalone CLIs that own the
protocol themselves — individually invocable backfill commands with a
`--force` flag, say.

A host that declares every platform table in its own schema and lets
drizzle-kit own the migration spreads `dataMigrationRunsColumns` — the marker
table column for column, held equal to `dataMigrationRunsDdl()` by a test —
and `ensureDataMigrationRunsTable` runs that same DDL, so the two adapters can
never create two tables:

```ts
import { dataMigrationRunsColumns } from '@octabits-io/backfill/drizzle';

export const dataMigrationRuns = pgTable('data_migration_runs', dataMigrationRunsColumns);
```

## Migrations that keep the rows

A backfill is the middle step of every change to a table that holds data:
**expand** (add the new shape next to the old), **fill** (move the data),
**contract** (constrain, drop what is gone). The two checks below fail a
migration that skips a step, before it runs anywhere that matters.

`scanMigration` reads a migration's SQL — what drizzle-kit, Prisma or a person
writes — and names what needs a second look:

| Risk | Statement | Cleared by |
|---|---|---|
| `not_null_without_default` | `ADD COLUMN … NOT NULL` with no default, on a table the migration did not create | the safe form only: nullable or with a default, `NOT NULL` later |
| `set_not_null_without_fill` | `ALTER COLUMN … SET NOT NULL` with no `UPDATE` of that column before it in the same migration | the safe form only: fill first, in the same migration or by a finished backfill plus that `UPDATE` as the net |
| `drop_table`, `drop_column`, `rename`, `change_type`, `deletes_rows` | they pass every dry run and still lose data, or break the version still running during the deploy | the sequence, or a `-- reviewed: <reason>` comment directly above the statement — the finding comes back `acknowledged` with the reason, for the reviewer |

```ts
import { scanMigration } from '@octabits-io/backfill';

const findings = scanMigration(sql, { name: '0007_contact_phone.sql' });
const blocking = findings.filter((f) => !f.acknowledged);   // fail CI on these
const reviewed = findings.filter((f) => f.acknowledged);    // print these for the reviewer
```

`checkMigrationsOnRows` runs them. On a **throwaway** database it applies the
migrations in order, and before each one puts a row into every table that is
still empty — nullable columns left `NULL`, every other column a plain value
of its type, a foreign key pointing at its parent's row — then applies the
migration in a transaction. A migration that only works on empty tables fails
with the statement and Postgres's own message, and the check stops there, as
a deploy would. Tables it cannot fill (a type with no plain value, a check the
synthetic row breaks) come back as `notes`.

```ts
import { PGlite } from '@electric-sql/pglite';
import { checkMigrationsOnRows } from '@octabits-io/backfill/postgres';

const report = await checkMigrationsOnRows({ db: await PGlite.create(), migrations });
// report.failures: [{ migration, statement, error, message }] — empty when every migration keeps the rows
```

`dryRunMigrations` is the same idea on real data: the pending migrations
inside one transaction, rolled back whatever happens — for a restored copy of
production before a deploy. Both take **one connection** (a `pg` `Client`,
PGlite), not a pool, and both rely on Postgres running DDL in a transaction.
Which migrations are pending is your migration tool's knowledge, not this
package's.

The octabits starter runs both: the guard applies every migration over a row
per table and reads each one, and `pnpm db:dry-run` tries the pending ones on
the developer's data or `DATABASE_URL`.

## When not to use it

- **To change a schema.** This is the layer *above* your migrations. It does
  not create columns, and it does not replace `drizzle-kit`, Prisma Migrate,
  Atlas or `psql` — it checks what they wrote.
- **For recurring work.** A backfill is one-shot: it runs until the legacy
  shape is gone, then it is a lookup forever. Anything on a schedule is a job
  for a queue.
- **For long streaming work inside a request.** The runner is a deploy-time
  chain. Batch inside `run()` and report `pending` so a big backfill finishes
  over several deploys, or drive it from a job if it must run for hours.
- **As a lock.** Two deploys running the chain concurrently may both execute
  a backfill; the marker insert is `ON CONFLICT DO NOTHING`, so the marking is
  safe, but the work is not serialised for you.

## Conformance

One scenario suite runs against all three marker stores — the in-memory
reference, the plain-SQL store and the Drizzle adapter — on an embedded PGlite
(real Postgres, no Docker). A new adapter passes the suite or it is not an
adapter.

## Stability

Pre-1.0: patches never break, minors say so in the changelog. The DDL helper
is part of the surface — a column it emits is a column a store will read — and
so are the summary `status` values (`completed`, `already_completed`,
`pending`, `unmarked`), which a deploy log is read against.