Skip to main content
Migrations are versioned SQL files in migrations/ applied with @insforge/cli. Each successful run is recorded in system.custom_migrations. The workflow is forward-only.

​
Concepts

A migration is one SQL file prefixed with a 14-digit UTC timestamp: <YYYYMMDDHHmmss>_<name>.sql. The CLI applies pending files in order inside a transaction, sets search_path to public, and records history only on success. PostgREST reloads schema metadata automatically. BEGIN/COMMIT/ROLLBACK inside a file are rejected.

​
Usage

Link the backend, then create a file.
Write the SQL.
Apply pending migrations and check history.
Target a single file with up <version>, or apply everything pending up to and including a target with up --to <version>.

​
Specific usage cases

Adopting migrations on an existing project: run db migrations fetch first to materialize remote history into local files. Once applied remotely, never edit a migration in place. Write a forward migration instead. Once you opt in, route all schema changes through files. Ad hoc dashboard edits cause drift between git and system.custom_migrations.

​
Non-transactional statements

Because the CLI runs each migration file inside a single transaction (the same reason BEGIN/COMMIT/ROLLBACK are rejected), a few PostgreSQL commands that cannot run inside a transaction block will fail in a migration with an error like CREATE INDEX CONCURRENTLY cannot run inside a transaction block. The common ones are:
  • CREATE INDEX CONCURRENTLY — use a plain CREATE INDEX in a migration when a brief write lock is acceptable, or run the concurrent build outside a migration (below).
  • VACUUM — routine vacuuming is handled by PostgreSQL autovacuum, so you rarely need to run this yourself.
  • REINDEX ... CONCURRENTLY, ALTER TYPE ... ADD VALUE (on older PostgreSQL), and similar commands.
Run these outside a migration with the CLI’s unrestricted raw-SQL path, which executes the statement without wrapping it in a transaction:
Plain npx @insforge/cli db query (without --unrestricted) wraps your statement in a transaction, so it hits the same error — use --unrestricted for non-transactional commands.
Both db query paths enforce a 30-second statement_timeout, so CREATE INDEX CONCURRENTLY or VACUUM on a large table may time out.

​
More resources