POSTGRESQL / FILE REVIEW

PostgreSQL migration dry runs

Schema Migrator can discover and validate SQL files and print their order without opening a database connection. The CLI collapses whitespace and shortens SQL longer than 120 characters. Review the complete source files alongside this preview. This local example creates a table before its index.

Schema Migrator

Prepare the complete two-file input

Use a local checkout of Schema Migrator, a JDK and sbt. Run the commands from apps/schema-migrator. Create a new empty directory named review-example and save only the two files below in its tables and indexes folders. Keep this disposable input separate from the repository SQL tree. No target credentials are needed for these offline commands.

Local example directory. All listed folders exist

review-example/
  extensions/
  schemas/
  types/
  tables/001_observations.sql
  indexes/002_observed_at.sql
  functions/
  views/
  cron/
  materialized_views/

Empty folders avoid missing-folder discovery warnings. This example uses folder ordering, not a manifest.

tables/001_observations.sql

-- object: observations
-- folder: tables
-- depends_on: -
create table if not exists observations (
  id bigint primary key,
  source text not null,
  observed_at timestamptz not null
);

Illustrative source SQL for the local example. It is not a production migration.

indexes/002_observed_at.sql

-- object: observations observed_at index
-- folder: indexes
-- depends_on: observations
create index if not exists observations_observed_at_idx
  on observations (observed_at desc);

Illustrative source SQL for the local example. It is not a production migration.

PostgreSQL discovery orders extensions, schemas, types, tables, indexes, functions, views, cron, then materialized_views. Filenames sort inside each folder.
The object, folder, and depends_on headers declare the object and its dependency. The index names observations as its prerequisite.
For a directory using core/manifest.yaml, apply_order determines the sequence instead. Unlisted SQL is reported as ignored.

Discover files, then review validation findings

The application output below was captured for the local example and excludes sbt startup messages. list reports order. validate reports file-specific findings. Warnings do not fail the command, while validation errors do.

sbt "run --db-kind postgres --sql-dir ./review-example list"

tables/001_observations.sql
indexes/002_observed_at.sql

Discovery prints files in apply order. No database is contacted.

sbt "run --db-kind postgres --sql-dir ./review-example validate"

warning: tables/001_observations.sql: expected 'CREATE TABLE IF NOT EXISTS' for idempotency

Findings are reported per file with the path that produced them. A clean directory prints nothing and exits 0, and warnings do not fail the run.

The lowercase CREATE TABLE in this fixture still produces the shown idempotency warning. Read findings alongside the complete SQL rather than treating a zero exit status as proof of execution safety.
As a separate negative test, an unterminated single-quoted SQL string produces a parsing error with the file path and a non-zero exit. Keep invalid test files out of the two-file preview input.

Read the ordered preview beside the full files

--dry-run apply prints file paths and compacted SQL in apply order. It does not execute the table or index statement.

sbt "run --db-kind postgres --sql-dir ./review-example --dry-run apply"

tables/001_observations.sql                      -- object: observations -- folder: tables -- depends_on: - create table if not exists observations ( id bigint primary ...
indexes/002_observed_at.sql                      -- object: observations observed_at index -- folder: indexes -- depends_on: observations create index if not exists obs...

The preview prints each path with its SQL compacted to one line and cut after 120 characters. The current CLI shows ordered, shortened previews, not a complete executable deployment script.

The printer collapses whitespace and shortens SQL after 120 characters. Retain the full files as the review artifact. The displayed preview cannot be used as a complete deployment script.
Check that each referenced table, type and extension precedes its dependent objects. Review destructive operations and transaction requirements in the full statements.

Choose the next check for the unresolved question

A file review answers what would be submitted, not what already exists or what a target will permit. Select the next step according to the evidence you still need.

Separate offline review from target-dependent checks
Question	Check	Boundary
Do files parse and dependencies resolve?	Offline validation	No target execution evidence
What structure exists now?	Connected catalog drift inspection	Findings require review. No automatic correction
Will these statements work with these data and permissions?	Rehearsal on a non-production target	Production locks, data, and concurrent activity can still differ

Source code

The implementation behind the examples. Examples are educational inputs, not production records or measured results.

CLI validation and offline command paths
list, validate and dry-run apply use SQL-only configuration validation.
PostgreSQL folder order
The supported folder sequence for this example.
Preview output formatting
File paths, warnings, errors and dry-run previews are printed as review output.