Database changes on a branch

How a branch's new tables and columns reach main: the migration script, the database check that refuses a merge without it, and the seed script for test data.

A merge moves code, never database changes. When a branch adds or changes tables, columns or enum values, a short migration script makes the same changes on the target's database during the merge. Your assistant writes it; this page explains what it does and why a merge waits for it.

The migration script

Each branch can have one migration script, the file static/__dev/migration.js. It runs once, during the merge, on the database of the branch being merged into, and can also copy stored files from the branch to the target or delete files there. It is a dev file: it is never published, a new branch starts without one, and it is not carried by a merge. It can't be written on main.

export default async function (ctx) {
  // Runs on the target's database, inside the merge.
  await ctx.targetQuery(
    "ALTER TABLE orders ADD COLUMN IF NOT EXISTS gift_note text",
  );
  await ctx.targetQuery(
    "CREATE INDEX IF NOT EXISTS orders_created_at_idx ON orders (created_at)",
  );
}
ctx.targetQuery(sql, params?)
Runs one SQL statement on the target's database. Parameters use $1, $2.
ctx.sourceQuery(sql, params?)
Reads the branch's own database (SELECT only), for example to copy a few rows.
ctx.storageCopy(path, toPath?)
Copies a stored file from the branch to the target.
ctx.storageDelete(path)
Deletes a stored file on the target.

How it runs

  • The merge typechecks the script, runs it as a dry run that is rolled back, then runs it for real in one transaction. If it fails, nothing changes on the target.
  • Write it so it is safe to run more than once: IF NOT EXISTS, ON CONFLICT, guarded updates.
  • One statement per call. The script has no network, files, timers or env vars.
  • The dry run and the real run each get about 7 minutes, and a merge not finished after 20 minutes fails. While it runs, it holds its locks on the target's tables. Keep it to schema changes and small data fixes, and do a large backfill after the merge as a background job.

When the branch's database changed and there is no script yet, Ask AI on the merge review gets a starter script drafted from the changes.

The database check

Before a merge, Floot compares the two databases with how they were when the branch was made, and works out what the branch changed. The merge review lists those changes: what the script has to do, what changed differently on both sides, and what only the target has (nothing to do). The check Database matches the merged code then runs the script's dry run and looks again: while anything the branch changed is still missing on the target, the merge is refused. There are two ways forward: make the script do the changes, or undo them on the branch's database if the target shouldn't get them.

The check works both ways: merging main into a branch is refused too while the branch's database lacks main's changes. It compares tables, columns, indexes, constraints and enum types. Views, functions, triggers, sequences and extensions are not compared, so a change to one of those needs the script even though nothing refuses the merge.

Changing the database with SQL on a branch

When you or your assistant change a branch's tables with SQL, the change is on the branch's database only. It reaches main only through the migration script, written in the same go.

Test data with a seed script

A branch with an empty database starts with no rows to test on. Keep made-up test data in static/__dev/seed.sql: plain SQL that fills the project's tables. Every new branch whose database starts empty (every Empty database branch and every isolated branch) runs it right after it is made, in one transaction. If it fails, the branch is made anyway with nothing from the seed, and your assistant is told why.

  • Never put real people's data in it: it is in the code, and every branch gets it.
  • Make it safe to run twice (ON CONFLICT DO NOTHING, fixed ids), and insert parents before children.
  • For volume, such as thousands of orders for a performance test, generate rows with generate_series instead of writing them out.
  • It is an ordinary file: branches inherit it and a merge carries it, so merge it into main to give every later branch the same start.
  • To run it again on a branch, ask your assistant to run the file's SQL.
  • When a branch changes the tables, update the seed in the same go: a merge out of a branch runs the merged seed on that branch's database, rolled back, and is refused while it no longer fits the tables.

Made-up rows miss the odd cases real data has, such as empty fields and old formats. To rehearse a migration on data that isn't sensitive, use a branch with a Full copy, as a scratch branch.