insforge-cli/references/database/migrations.md

npx -y @insforge/cli db migrations

Manage developer database migration files for an InsForge project.

Commands

npx -y @insforge/cli db migrations list
npx -y @insforge/cli db migrations fetch
npx -y @insforge/cli db migrations new <migration-name>
npx -y @insforge/cli db migrations up <migration-file-name-or-version>
npx -y @insforge/cli db migrations up --to <migration-file-name-or-version>
npx -y @insforge/cli db migrations up --all

What Each Command Does

Command	Description
list	Show applied remote migrations (version, name, created date)
fetch	Download remote applied migrations into migrations/
new <migration-name>	Create the next local migration file with the next timestamp version
up <filename\|version>	Apply exactly one explicit local migration file
up --to <filename\|version>	Apply pending local migrations up to a chosen target
up --all	Apply every pending local migration file

Filename Format

Migration files must be named exactly:

<migration_version>_<migration-name>.sql

Examples:

• valid: 20260418091500_create-users.sql
• valid: 20260418103045_add-post-index.sql
• invalid: 20260418_create-users.sql
• invalid: 20260418091500_create_users.sql
• invalid: 20260418091500_CreateUsers.sql
• invalid: 20260418091500 create-users.sql

Migration Name Rules

The <migration-name> portion must use:

• lowercase letters
• numbers
• hyphens

No spaces, underscores, uppercase letters, or other special characters.

Local Directory

Migration files live under:

migrations/

Examples

# View remote migration history
npx -y @insforge/cli db migrations list

# Fetch remote migration files into migrations/
npx -y @insforge/cli db migrations fetch

# Create the next migration file
npx -y @insforge/cli db migrations new create-posts

# Apply by exact filename
npx -y @insforge/cli db migrations up 20260418091500_create-posts.sql

# Apply by version
npx -y @insforge/cli db migrations up 20260418091500

# Apply all pending migrations through a target
npx -y @insforge/cli db migrations up --to 20260418110000

# Apply all pending migrations
npx -y @insforge/cli db migrations up --all

# JSON output
npx -y @insforge/cli db migrations list --json

Output

• list prints a table with version, name, and created date
• fetch reports how many files were created and skipped
• new prints the created filename
• up prints the applied filename(s) on success

Command Behavior

list

• Reads the current remote migration history from the project backend
• Shows only applied remote migrations

fetch

• Ensures migrations/ exists
• Writes one local .sql file per applied remote migration
• Skips existing file paths without overwriting them, even if the contents differ

new <migration-name>

• Validates the migration name
• Looks at the latest remote migration version
• Validates local filenames before choosing the next timestamp version
• Uses the greater of current UTC time or the latest known local/remote version, bumping by one second when needed
• Fails if local migration filenames are malformed or duplicated

up <filename|version>

• Resolves exactly one local file target
• Applies exactly one migration file
• The target must be the next pending local migration after the latest remote version
• Fails if the target is ambiguous, missing, empty, invalidly named, or already applied
• Unrelated invalid files elsewhere in migrations/ do not block an explicit valid target

up --to <filename|version>

• Strictly validates every local migration filename first
• Applies pending local migrations in ascending version order
• Stops after the chosen target migration is applied
• Fails if the target is missing, already applied, ambiguous, or not present in the pending set

up --all

• Strictly validates every local migration filename first
• Applies every pending local migration in ascending version order
• Stops on the first failure

Best Practices

1. Use migrations for schema changes

   • Migration SQL runs as project_admin.
   • project_admin can manage and own objects in public, but access to InsForge-managed schemas is restricted.
   • For generic application database work, create and evolve app-owned objects through migration files in public: tables, views, indexes, policies, triggers, helper functions, and grants.
   • Do not create custom schemas or write to InsForge-managed/system schemas such as auth, storage, realtime, payments, graphql, extensions, pg_catalog, information_schema, or system, unless you are working on that specific feature module and its docs explicitly allow the operation.
   • It is allowed to reference built-in objects such as auth.users(id) and auth.uid() from public tables or public RLS policies; do not modify those built-in objects.
   • Group related schema changes into one migration when practical.
   • Reserve db query for row-level data fixes, backfills, and targeted inspection.
   • Migration apply reloads the PostgREST schema cache automatically.
   • Migration SQL runs against public; schema-qualify references such as public.posts and auth.uid().

2. Normalize large JSONB payloads into columns or child tables

   • Avoid designing tables where app code reads/writes large JSONB blobs through PostgREST; large JSONB rows can drive excessive PostgREST memory use.
   • Use typed columns for fields used in filters, sorting, list views, RLS policies, or partial updates.
   • Use child tables for repeated nested objects, with foreign keys and indexes on ownership/lookup columns.
   • Keep JSONB for small, rarely queried metadata/config where whole-object reads and writes are acceptable.

3. Compare remote and local migration history

   • Use list to see applied remote migrations.
   • Use fetch to sync applied remote migration files into migrations/.

4. Use new instead of naming files by hand

   • Let the CLI assign the next timestamp version safely.

5. Use explicit single-target apply for focused changes

   • up <filename> or up <version> is ideal when you want one specific migration.

6. Use batch apply for CI or bootstrap

   • up --to <target> or up --all is safer than hand-looping files in shell scripts because the CLI keeps ordering and fail-fast behavior consistent.

7. Treat fetched files as history

   • Once a migration is applied remotely, avoid editing its local file.

8. Do not include transaction statements in migration files

   • The backend executes each migration inside its own transaction.
   • Do not add BEGIN, COMMIT, or ROLLBACK to the migration SQL.

Common Mistakes

Mistake	Solution
Naming files manually with underscores or spaces	Use npx -y @insforge/cli db migrations new <migration-name>
Reaching for db query to create or alter schema	Use migration files for schema changes; reserve db query for row changes
Trying to alter InsForge-managed tables like app-owned tables	Keep generic schema, RLS, trigger, function, and grant changes on public application objects; use feature-specific docs for managed module hooks or RLS
Storing large app state or repeated nested objects in one JSONB column	Normalize into typed columns and child tables before exposing the table through SDK/PostgREST CRUD
Applying a file out of order	Apply the next pending local migration, or fix/delete the earlier local file that is blocking it
Keeping a local file older than the current remote head	Rename it with a newer timestamp or delete it locally if it is stale
Adding BEGIN / COMMIT / ROLLBACK to migration SQL	Remove them; the backend already wraps the migration in its own transaction
Editing already-fetched remote history casually	Treat fetched files as applied history, not drafts
Assuming fetch overwrites local files	fetch skips existing file paths instead of replacing them

Recommended Workflow

1. Check remote history             → npx -y @insforge/cli db migrations list
2. Sync applied files when useful   → npx -y @insforge/cli db migrations fetch
3. Create the next migration file   → npx -y @insforge/cli db migrations new <migration-name>
4. Edit the SQL file                → migrations/<version>_<migration-name>.sql
5. Apply the migration              → npx -y @insforge/cli db migrations up <filename> or --all
6. If apply fails, read the error, fix the migration, and retry the migration.