Backfill

When you add a NOT NULL column to a populated table, the planner needs a value for every existing row before it can land the SET NOT NULL constraint. The .backfill() annotation declares that value. The planner classifies the op as needs-backfill (allowed), the applier runs the three-step plan.

Two flavors:

• SQL backfill — .backfill(sql\expression`). The applier emits one UPDATE` statement; the database does the work.
• JS backfill — .backfill((row) => value). The applier streams rows in batches, calls the function locally, writes back.

export const users = table('users', {
  id:        id(),

  // SQL backfill — an @effect/sql Statement.Fragment; one round-trip, scales linearly.
  email:     text().backfill(unknownEmailExpr),

  // JS backfill — when SQL can't express what you need.
  embedding: text().backfill(async (row) => embed(row.title), {
    batchSize: 500,    // rows per batch, default 1000
    sleepMs:   25,     // ms between batches, default 0
  }),
})

#When to pick which

Need	Pick
Constant value for every row	.default(value) — not a backfill at all, the DDL DEFAULT clause does it
Expression of existing column values (`id
Read another table's row ((SELECT id FROM tenants WHERE name = '...' LIMIT 1))	SQL
Call an embedding model / image classifier / external HTTP API	JS
Compute a value with a JS library that has no SQL equivalent (slugify, tokenize, parse)	JS

The bias is firmly toward SQL. Performance is in different leagues — SQL backfills hit a million rows in seconds; JS backfills hit the same set in minutes-to-hours depending on what the function does.

#SQL backfill

email: text().backfill(sql`'unknown-' || id || '@local'`),

The expression goes inside UPDATE <table> SET <col> = <expression>. You can reference:

• Other columns of the same row (id, created_at, etc.) — by name, no aliasing
• Constants and literals — '@local', 42, true
• Standard SQL functions — LOWER(), COALESCE(), EXTRACT(), ||, …
• Subqueries — (SELECT id FROM tenants WHERE name = 'acme') — including correlated ones
• Dialect-specific functions when you know which DB you're on

Don't reference columns the planner is about to drop / rename in the same plan — the UPDATE runs AFTER the ADD COLUMN but BEFORE any drops, so renamed columns are still under their old name at backfill time. The planner orders the plan deterministically (renames first as RENAME COLUMN, then ADD/ALTER); the backfill sees the post-rename names.

#Cross-dialect SQL idioms

The sql\...`fragment is the same@effect/sqltemplate you use in custom handlers. Usesql.onDialectOrElse({...})` for expressions that vary:

const timestampNow = sql.onDialectOrElse({
  mysql:  () => sql`NOW(6)`,
  mssql:  () => sql`SYSUTCDATETIME()`,
  orElse: () => sql`now()`,
})

createdAt: timestamp().backfill(timestampNow),

The framework's sql.onDialectOrElse resolves at compile time, so each dialect only emits its own branch. See Multi-dialect strategy.

#JS backfill

embedding: text().backfill(async (row) => {
  const text = `${row.title} ${row.body}`
  return embed(text)  // calls an external embedding model
}, {
  batchSize: 500,
  sleepMs:   25,
}),

The function receives the full row (with id and every existing column). The applier:

1. Streams rows: SELECT id FROM <table> ORDER BY id with cursor pagination
2. For each batch of batchSize:
   • Calls the function for each row, in parallel
   • Writes back: UPDATE <table> SET <col> = $1 WHERE id = $2 for each result
3. sleepMs between batches to let normal traffic breathe

batchSize controls memory + concurrency (each batch holds N row promises). sleepMs reduces contention with concurrent writes — set it to ~20–50 ms when the function makes external API calls (the API quota matters more than throughput).

The function MUST be deterministic + idempotent for the same input row. A crash mid-backfill restarts the batch; non-idempotent functions double-charge external APIs or write duplicate side effects.

#Performance

Order-of-magnitude rules of thumb for postgres on a modest VM:

Rows	SQL backfill	JS backfill (pure CPU)	JS backfill (calls 50 ms API)
1k	< 50 ms	~200 ms	~25 s
10k	~300 ms	~2 s	~5 min (batched, 500-wide)
100k	~3 s	~25 s	~50 min
1M	~30 s	~5 min	unfeasible — use a separate workflow
10M	~5 min	~50 min	unfeasible

JS backfill is 10×–100× slower than SQL for the same data; with external calls it's 1000× slower. The CLI surfaces an estimate ahead of apply:

⊕ needs-backfill (1) — declared
  + ALTER TABLE posts ADD COLUMN slug text
  → backfill: js fn  (est. 50,000 rows, ~8 minutes — consider .backfill(sql) variant?)

The estimate is conservative (counts rows + multiplies by a per-row JS-fn cost factor). It's a hint, not a refusal — if you genuinely need the JS function, set --note 'backfill via embedding model, expected duration' so the history row records WHY the slow path was chosen.

#Validate a JS backfill before applying

A JS backfill is regular TypeScript — the safest way to confirm it produces sensible output before committing a long UPDATE run is to call the function directly in a unit test (voltro test) over a handful of representative rows. There is no voltro db backfill --dry-run subcommand; .backfill() only runs as part of voltro db apply.

#Failure handling

If the backfill UPDATE fails mid-flight:

• Postgres / MSSQL / SQLite: the whole 3-step is inside one transaction. The failure rolls back the ADD COLUMN too — the schema returns to its pre-apply state. The plan stays pending; fix the backfill expression + re-apply with voltro db apply.
• MySQL / MariaDB: DDL is implicit-commit. The ADD COLUMN landed. The UPDATE rolled back to its savepoint, but the nullable column is now on the table. The next voltro db plan will see the column present but nullable + emit the remaining SET NOT NULL step, which voltro db apply then applies against the current state.

The multi-dialect strategy page covers the forward-roll mechanics in detail.

If a JS backfill function throws partway through, the applier stops the batch loop, records the affected row id range in the log, leaves the column nullable (no SET NOT NULL), and exits non-zero. Re-run voltro db apply once you've fixed the function — because each batch's UPDATE … WHERE <col> IS NULL only touches rows that haven't been filled yet, the re-run picks up where it stopped without double-writing completed rows.

#What about updating an existing column?

.backfill() ONLY applies to ADD-COLUMN ops. Updating values on an existing column isn't a migration concern — it's regular data work. Write a one-off mutation or a *.subscribe.ts handler that watches for the trigger condition, OR a workflow if it spans steps. The migration system stays out of "change data in this column" jobs.