sql-migration

SQL Migration Tool

A command-line tool for executing SQL migration scripts against PostgreSQL and SQLite databases with migration history tracking.

Features

• Support for PostgreSQL and SQLite databases
• Migration history tracking to avoid re-running scripts
• Automatic database backup before applying migrations
• Database restore from backup
• Dry-run mode for testing migrations without applying changes
• Ordered execution of scripts (tables → views → stored procedures → data)
• SHA256 hash verification to detect script changes
• Environment variable support for configuration
• Verbose logging for debugging

Installation

From Source

git clone https://github.com/adeotek/adeotek-tools.git
cd adeotek-tools/sql-migration
make build

The binary will be available at build/sql-migration.

Using Go Install

go install github.com/adeotek/adeotek-tools/sql-migration/cmd/sql-migration@latest

Usage

Basic Usage

sql-migration --target-path /path/to/sql/scripts --provider postgresql --host localhost --port 5432 --database mydb --user myuser --password mypass

Using Connection String

sql-migration --target-path /path/to/sql/scripts --provider postgresql --connection-string "host=localhost port=5432 dbname=mydb user=myuser password=mypass sslmode=disable"

SQLite Usage

sql-migration --target-path /path/to/sql/scripts --provider sqlite --database /path/to/database.db

Dry Run Mode

sql-migration --target-path /path/to/sql/scripts --provider postgresql --host localhost --port 5432 --database mydb --user myuser --password mypass --dry-run

Verbose Output

sql-migration --target-path /path/to/sql/scripts --provider postgresql --host localhost --port 5432 --database mydb --user myuser --password mypass --verbose

Command Line Options

Environment Variables

You can use environment variables instead of command-line flags. Prefix them with CLI_SQL_MIGRATION_:

• CLI_SQL_MIGRATION_PROVIDER
• CLI_SQL_MIGRATION_CONNECTION_STRING
• CLI_SQL_MIGRATION_HOST
• CLI_SQL_MIGRATION_PORT
• CLI_SQL_MIGRATION_DATABASE
• CLI_SQL_MIGRATION_USER
• CLI_SQL_MIGRATION_PASSWORD
• CLI_SQL_MIGRATION_DRY_RUN
• CLI_SQL_MIGRATION_VERBOSE

Script Organization

Within the target directory, scripts are executed in alphabetical order, and then any subdirectories are processed in the same manner in alphabetical order recursively. To achieve the correct result, the tool expects SQL scripts to be organized in the optimal execution order.

Example Directory Structure

sql-scripts/
├── 01_tables/
│   ├── 01_audit/
│   │   └── 002_create_audit.sql
│   ├── 02_custom/
│   │   ├── 001_create_custom1.sql
│   │   └── 002_create_custom2.sql
│   ├── 001_create_users.sql
│   └── 002_create_posts.sql
├── 02_views/
│   └── 001_user_posts_view.sql
├── 03_stored_procedures/
│   └── 001_get_user_posts.sql
└── 04_data/
│   ├── 01_post_seed/
│   │   └── 001_seed_posts.sql
    └── 001_seed_data.sql

The above scripts will be executed in the following order:

1. 01_tables/001_create_users.sql
2. 01_tables/002_create_posts.sql
3. 01_tables/01_audit/002_create_audit.sql
4. 01_tables/02_custom/001_create_custom1.sql
5. 01_tables/02_custom/002_create_custom2.sql
6. 02_views/001_user_posts_view.sql
7. 03_stored_procedures/001_get_user_posts.sql
8. 04_data/001_seed_data.sql
9. 04_data/01_post_seed/001_seed_posts.sql

Migration History

The tool creates a __migrations_history table to track executed scripts. This table stores:

• Script file name
• SHA256 hash of the script content
• Execution timestamp

Scripts are only re-executed if their content has changed (detected by hash comparison).

Backup and Restore

The tool supports automatic database backup before applying migrations and manual restore of previous backups.

Backup

Use the --backup flag to create a backup before applying migrations:

# PostgreSQL example
sql-migration \
  --target-path ./sql-scripts \
  --provider postgresql \
  --host localhost \
  --port 5432 \
  --database mydb \
  --user myuser \
  --password mypass \
  --backup

# SQLite example
sql-migration \
  --target-path ./sql-scripts \
  --provider sqlite \
  --database ./mydb.db \
  --backup

Backup behavior:

• Backups are only created if there are unapplied migration scripts
• Backup files are stored in .db-backups directory in the current working directory
• Backup filenames include a timestamp: {database}_backup_{yyyyMMdd_HHmmss}.{ext}
• PostgreSQL backups use pg_dump and create .sql files
• SQLite backups are simple file copies with .db extension
• If backup fails, migrations will not be applied

Prerequisites for PostgreSQL:

• pg_dump must be installed and accessible in PATH
• User must have sufficient privileges to dump the database

Backup Only

Use the --backup-only flag to create a backup without running any migrations:

# PostgreSQL example
sql-migration \
  --provider postgresql \
  --host localhost \
  --port 5432 \
  --database mydb \
  --user myuser \
  --password mypass \
  --backup-only

# SQLite example
sql-migration \
  --provider sqlite \
  --database ./mydb.db \
  --backup-only

Backup-only behavior:

• Creates a backup regardless of whether there are unapplied scripts
• No migrations are run when --backup-only flag is used
• The --target-path flag is not required for backup-only operations
• Uses the same backup mechanism as --backup flag (pg_dump for PostgreSQL, file copy for SQLite)
• Backup files are stored in .db-backups directory with timestamps

Restore

Use the --restore flag to restore the most recent backup:

# PostgreSQL example
sql-migration \
  --provider postgresql \
  --host localhost \
  --port 5432 \
  --database mydb \
  --user myuser \
  --password mypass \
  --restore

# SQLite example
sql-migration \
  --provider sqlite \
  --database ./mydb.db \
  --restore

Restore behavior:

• Restores the most recent backup from .db-backups directory
• No migrations are run when --restore flag is used
• The --target-path flag is not required for restore operations
• PostgreSQL restore uses psql command
• SQLite restore overwrites the database file with the backup copy

Prerequisites for PostgreSQL:

• psql must be installed and accessible in PATH
• User must have sufficient privileges to restore the database

Dry-run Mode

Both backup and restore operations respect the --dry-run flag:

# Simulate backup without creating actual backup file
sql-migration \
  --target-path ./sql-scripts \
  --database mydb \
  --backup \
  --dry-run

# Simulate restore without actually restoring
sql-migration \
  --database mydb \
  --restore \
  --dry-run

Examples

PostgreSQL Example

# Using individual connection parameters
sql-migration \
  --target-path ./sql-scripts \
  --provider postgresql \
  --host localhost \
  --port 5432 \
  --database myapp \
  --user myuser \
  --password mypass \
  --verbose

# Using connection string
sql-migration \
  --target-path ./sql-scripts \
  --provider postgresql \
  --connection-string "host=localhost port=5432 dbname=myapp user=myuser password=mypass sslmode=disable"

SQLite Example

sql-migration \
  --target-path ./sql-scripts \
  --provider sqlite \
  --database ./myapp.db \
  --verbose

Using Environment Variables

export CLI_SQL_MIGRATION_PROVIDER=postgresql
export CLI_SQL_MIGRATION_HOST=localhost
export CLI_SQL_MIGRATION_PORT=5432
export CLI_SQL_MIGRATION_DATABASE=myapp
export CLI_SQL_MIGRATION_USER=myuser
export CLI_SQL_MIGRATION_PASSWORD=mypass

sql-migration --target-path <sql-scripts-dir> --verbose

Set environment variables from .env file

export $(grep -v '^#' .env | xargs)

sql-migration --target-path <sql-scripts-dir> --verbose

Building

Build for Current Platform

make build

Build for All Platforms

make build-all

Run Tests

make test

Clean Build Artifacts

make clean

Publishing a New Version

This tool is part of a monorepo, so version tags must be prefixed with the module path.

Steps to Publish

1. Commit all changes:

   git add .
   git commit -m "Your commit message"
   git push origin main
   

2. Create and push a version tag:

   # Format: sql-migration/vMAJOR.MINOR.PATCH
   git tag sql-migration/v0.4.0
   git push origin sql-migration/v0.4.0
   

   Or use an annotated tag (recommended):

   git tag -a sql-migration/v0.4.0 -m "Release v0.4.0: Description of changes"
   git push origin sql-migration/v0.4.0
   

3. Wait for Go proxy indexing: The Go proxy (proxy.golang.org) needs 15-30 minutes to index the new version.

4. Verify the tag:

   git ls-remote --tags origin | grep sql-migration
   

Installing Specific Versions

Users can install the latest version or a specific version:

# Install latest version
go install github.com/adeotek/adeotek-tools/sql-migration/cmd/sql-migration@latest

# Install specific version
go install github.com/adeotek/adeotek-tools/sql-migration/cmd/sql-migration@v0.4.0

Updating After a New Release

To update to the latest version:

go install github.com/adeotek/adeotek-tools/sql-migration/cmd/sql-migration@latest

If the new version doesn't appear immediately, try:

# Clear module cache
go clean -modcache
go install github.com/adeotek/adeotek-tools/sql-migration/cmd/sql-migration@latest

# Or bypass proxy cache
GOPROXY=direct go install github.com/adeotek/adeotek-tools/sql-migration/cmd/sql-migration@latest

On Windows PowerShell:

$env:GOPROXY="direct"; go install github.com/adeotek/adeotek-tools/sql-migration/cmd/sql-migration@latest

Important Notes

• Tag Format: Must use sql-migration/vX.Y.Z format (not just vX.Y.Z)
• Version Prefix: Always prefix tags with the module subdirectory name
• Semantic Versioning: Follow semantic versioning (MAJOR.MINOR.PATCH)
• Go Proxy Cache: Allow time for proxy.golang.org to index new versions

License

This project is licensed under the MIT License - see the LICENSE file for details.