Nushell PostgreSQL Migration Tool

A lightweight database migration utility built with Nushell that executes PostgreSQL migrations using psql. Supports multi-track migrations for ERP environments with core and implementation-specific tracks.

The reason this repository exists:

• Needed a CLI-centric solution
• Evaluated sqlx-cli; however, the project determined they would rigidly limit migrations to a single track
• Evaluated flyway; however, they do not natively support unix sockets

Why Multiple Tracks?

In an enterprise setting like ERP where multiple actors contribute to a resulting installation (core + integrators + customers), maintaining a single migration repository/table/track is not acceptable. Core improvements go into the 'core' track maintained by the core team. Implementation firms place improvements in the 'impl' track. Etc...

You may name any track as you deem appropriate. 'core' and 'impl' are simply offered for reference. You may have as many tracks as you wish.

Generally, 'core' team track migrations are executed first as part of any release. Then following tracks are orchestrated and executed as deemed appropriate.

Quick Start

1. Install dependencies:

   • Nushell (v0.80+)
   • PostgreSQL with psql client

2. Add to your project:

   # Copy src/ directory to your project
   cp -r src/ /path/to/your/project/migration-tool/

3. Set database connection:

   export PGHOST=localhost
   export PGPORT=5432
   export PGDATABASE=your_app_db
   export PGUSER=postgres
   export PGPASSWORD=your_password

4. Create migration directories:

   mkdir -p migrations/core migrations/impl

5. Run migrations:

   nu migration-tool/migrate.nu run migrations/core
   nu migration-tool/migrate.nu run migrations/impl

Migration Files

Create timestamped SQL files following this naming pattern:

{timestamp}_{track}_{description}.sql

Examples:
migrations/core/20231201120000_core_create_users_table.sql
migrations/impl/20231201130000_impl_add_custom_fields.sql

Commands

# Apply migrations in directory
nu migrate.nu run ./migrations/core

# Show migration status
nu migrate.nu status ./migrations/core

# Create new migration
nu migrate.nu add ./migrations/core create_users_table

# Show migration history
nu migrate.nu history ./migrations/core

# Validate migrations without running
nu migrate.nu validate ./migrations

Multi-Track Architecture

Organize migrations into separate tracks for different concerns:

migrations/
├── core/     # Base application migrations
├── impl/     # Implementation customizations  
└── customer/ # Customer-specific migrations

Each track maintains its own metadata table (migrations_core, migrations_impl, etc.).

Example Usage

See https://github.com/chuckstack/chuck-stack-core for an example project using this migration tool.

Development & Testing

Run the test suite:

cd test/
nix-shell --run "nu test-runner.nu --all"

For detailed testing information, see test/README.md.

Nix Distribution

Current version for downstream projects:

• Commit: e309c1ac019cf7afeb549eebb6367215aaf471cb
• SHA256: sha256-zYB1TcPQY3hK7MR/OyCi2xwpahdjbOc2+F0Qn6L+zCY=

Getting the Hash for New Releases

1. Update get-hash.nix with your target commit
2. Run nix-shell get-hash.nix
3. Copy the correct hash from the error message
4. Update this README with the new hash
5. Update all downstream consumers (see below)

Downstream Files to Update

The following files reference this repository and need updating when the hash changes:

• chuck-stack-core/test/shell.nix
• chuck-stack-core/deploy-local/shell.nix
• chuck-stack-nixos/nixos/stk-core.nix

To find all references: grep -r "chuck-stack-nushell-psql-migration" --include="*.nix"

Features

• Atomic migrations: All pending migrations execute in a single transaction
• Multi-track support: Separate migration paths for different concerns
• Unix socket support: Full PostgreSQL unix socket compatibility
• Environment isolation: Explicit control of psql environment variables
• Pre-flight validation: Optional Nushell validation scripts (.nu files)