Does a FOR loop in SurrealDB run in one transaction?

FOR loop over every record of a table

Warning: the statement succeeds, then writes fail, access changes, or the table is held up.

What happens

Observed on SurrealDB 3.0.2: a FOR loop over SELECT VALUE id FROM t that updates each record rolls back every change when one iteration fails its field TYPE.

Why it is dangerous on a populated table

SurrealDB checks a schema change against new writes, not the records already stored, so an empty table hides it: on a populated one every old record is a write waiting to fail, and whole-table work runs for the length of the table in one transaction.

Fires on

FOR $u IN (SELECT VALUE id FROM user) { UPDATE $u SET plan = 'free'; };

The safe pattern

Loop over a bounded selection (a WHERE or a LIMIT), or move the backfill into a batched job outside the migration.

FOR $u IN (SELECT VALUE id FROM user WHERE plan IS NONE LIMIT 1000) { UPDATE $u SET plan = 'free'; };

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (2)

for bare select

FOR $u IN SELECT * FROM user { DELETE $u.id; };

for subquery

FOR $u IN (SELECT VALUE id FROM user) { UPDATE $u SET plan = 'free'; };

Stays silent (3)

array source

FOR $name IN ['free', 'pro'] { CREATE plan SET name = $name; };

limit

FOR $u IN (SELECT VALUE id FROM user WHERE plan IS NONE LIMIT 1000) { UPDATE $u SET plan = 'free'; };

where

FOR $u IN (SELECT VALUE id FROM user WHERE plan IS NONE) { UPDATE $u SET plan = 'free'; };

How to check locally

Catch this before it ships

SurrealDB support is in beta: this rule runs locally in the free CLI over .surql migrations, static only, and not in the hosted service yet. No install, nothing leaves your machine:

npx bolvrk check migration.surql --engine=surrealdb

Browse all 135 rules

← SR014 SR016 →