Database Migrations
Schema changes go through numbered SQL migrations in packages/api/migrations.
- Create the next file with
pnpm --filter @saltmarsh/api exec node scripts/new-migration.mjs <name>. - Write the forward migration only. We do not write down migrations.
- Never edit a migration that has been merged. Add a new one.
- Adding a column to
bookingsorpitchesmust be nullable or have a default, because those tables are too big to rewrite during a deploy. - Add an index with
CREATE INDEX CONCURRENTLYin its own migration file, with-- no-transactionon the first line. - Dropping a column takes two releases: stop reading and writing it in release one, drop it in release two.
- Run
pnpm db:migratelocally, thenpnpm db:generateto refresh the typed client. - Commit the migration and the regenerated
packages/api/src/db/types.tstogether.
Gotchas
- Migrations run before the new API version starts, so each one must work with the previous version of the code.
- A
NOT NULLconstraint on an existing column is added asNOT VALID, then validated in a second migration.