PG Schema and Migrations Guide

Use this guide when creating or reviewing database schema changes, migrations, or table structures with @faasjs/pg.

Applicable Scenarios

  • Creating or modifying a migration
  • Changing tables, columns, indexes, or constraints
  • Deciding whether a schema change should use builder helpers or raw SQL
  • Reviewing rollback expectations

Default Workflow

  1. Create a timestamped .ts migration file, usually with faasjs-pg new <name>.
  2. Implement up(builder) with SchemaBuilder and TableBuilder helpers first.
  3. Implement down(builder) for rollback when practical.
  4. Run faasjs-pg status from the project root to inspect migration history, then use faasjs-pg migrate, faasjs-pg up, or faasjs-pg down for the execution path you need.
  5. Keep migration files in src/db/migrations unless you intentionally reconfigure tooling, because both the CLI and PgVitestPlugin() look there by default.
  6. Keep related DDL in one builder run so it stays transactional.
  7. Fall back to raw() only for SQL the current helpers do not support.

Minimal Example

import type { SchemaBuilder } from '@faasjs/pg'

export function up(builder: SchemaBuilder) {
  builder.createTable('users', (table) => {
    table.number('id').primary()
    table.string('name')
    table.jsonb('metadata').defaultTo('{}')
    table.timestamps()
    table.index('name')
  })
}

export function down(builder: SchemaBuilder) {
  builder.dropTable('users')
}

Rules

1. Keep migration filenames lexically sortable

  • Migration files should remain timestamp-based and sortable by filename.
  • Prefer the generated faasjs-pg new <name> naming pattern, <timestamp>-<name>.ts, unless there is a strong reason not to.
  • Prefer hyphenated names such as create-users so generated filenames stay readable and match the CLI separator.
  • Avoid custom naming schemes that break lexical ordering.

2. Prefer builder helpers over handwritten DDL

  • Use createTable, alterTable, renameTable, dropTable, and TableBuilder column helpers first.
  • Use specificType(...) when the schema needs a PostgreSQL type not covered by a built-in helper.
  • Use raw DDL only for unsupported features or carefully scoped one-off statements.

3. Preserve transactional schema execution

  • SchemaBuilder.run() executes accumulated statements in a single transaction.
  • Write migrations assuming the batch should succeed or fail as one unit.
  • Do not split one logical schema change across unrelated builder runs unless partial application is intentional.

4. Keep migrations deterministic and reversible

  • up and down should be direct, readable descriptions of the schema transition.
  • Avoid time-sensitive or environment-sensitive SQL inside migrations unless it is explicitly required.
  • Do not use defensive IF EXISTS or IF NOT EXISTS DDL clauses in migrations; let unexpected schema state fail immediately so drift is caught during migration.
  • Prefer reversible changes when practical so down() can restore the previous state.

5. Keep migration history semantics stable

  • faasjs_pg_migrations is the source of migration history.
  • migrate() applies all pending files, up() applies the next pending file, and down() rolls back the latest recorded file.
  • Treat those behaviors as the default mental model for app code, tooling, and troubleshooting.

6. Keep the execution path obvious

  • Keep migrations in the project-root src/db/migrations folder unless project tooling is configured otherwise.
  • Use faasjs-pg status to inspect history, faasjs-pg migrate to apply all pending files, faasjs-pg up for the next file, and faasjs-pg down for the latest rollback.
  • If a project customizes the folder or wrapper commands, document that override explicitly in the project README or contributor guide.
  • Keep migrations focused on application-owned schema. Framework-managed tables such as faasjs_jobs are initialized by their owning package and should not be recreated in app migrations.

See Also

Review Checklist

  • the migration file name remains timestamp-sorted
  • up and down are both present when rollback is practical
  • the status/migrate/up/down execution flow is obvious for the project
  • builder helpers are used before raw DDL
  • raw DDL does not hide drift with IF EXISTS or IF NOT EXISTS
  • schema changes expect SchemaBuilder.run() to be atomic
  • risky schema changes are covered by focused migration or integration tests
  • framework-owned tables are not duplicated in app migrations