@2amtech/sql-migration-tool

A comprehensive database migration management library and CLI tool supporting MySQL, PostgreSQL, and MS SQL Server.

Features:

• Migrations are represented as .sql files allowing pure SQL scripts for both applying and rolling back changes supported by this tool and all other native desktop applications.
• Multiple environments. Use one project to deploy to multiple environments like staging or production.
• Dynamic replacements. Perform a string replace on pieces in the SQL file dependent on the selected environments allowing for targeting different tables or columns or inserting different data based on the selected environment.
• Different credential sources. Avoid storing credentials in the code. Get credentials from .env files or AWS Secrets Manager.
• Scripts support. Run various SQL scripts as .sql files with dynamic replacements applied.
• Suitable for CI/CD. You can migrate to the latest or a specific migration in your pipeline during deployment.

Build

The package is built using TypeScript:

npm run build

Global CLI Usage

Install globally:

npm install -g @2amtech/sql-migration-tool

Commands

Note: <env> refers to an environment name defined in your configuration.yml file (e.g., local, dev, staging, production). Each environment contains specific database connection settings.

init

Initialize a new project with a configuration.yml file.

2am-smt init [options]

Options:

• -d, --dir <directory> - Absolute path to the directory where new project should be initialized (defaults to current directory)

Creates a new project configuration file with default settings for MySQL database.

connection:test

Test the database connection for a specific environment.

2am-smt connection:test <environment> [options]

Options:

• -p, --project <projectConfig> - Absolute path to the project config file (defaults to ./configuration.yml)

Validates database credentials and connectivity for the specified environment.

migrate:new

Create a new migration file.

2am-smt migrate:new <migrationName> [options]

Options:

• -p, --project <projectConfig> - Absolute path to the project config file (defaults to ./configuration.yml)

Generates a timestamped migration file with up/down query templates in the migrations directory.

migrate:to

Migrate database to a specific migration state.

2am-smt migrate:to <env> [migrationName] [options]

Options:

• -p, --project <projectConfig> - Absolute path to the project config file (defaults to ./configuration.yml)
• -a, --auto-apply - Automatically apply the migrations without confirmation
• -c, --count <count> - Number of migrations to apply (positive) or rollback (negative)

If no migration name is provided, all pending migrations will be applied.

status

Check database migration status.

2am-smt status <env> [options]

Options:

• -p, --project <projectConfig> - Absolute path to the project config file (defaults to ./configuration.yml)
• -l, --list-migrations - List applied migrations

Shows the last applied migration and optionally lists all migrations that have been applied.

script:new

Create a new script file.

2am-smt script:new <scriptName> [options]

Options:

• -p, --project <projectConfig> - Absolute path to the project config file (defaults to ./configuration.yml)

Generates a script file template in the scripts directory for one-time or repeatable SQL operations.

script:run

Execute a script against the database.

2am-smt script:run <env> <scriptName> [options]

Options:

• -p, --project <projectConfig> - Absolute path to the project config file (defaults to ./configuration.yml)

Runs the specified script file against the database in the given environment.

License

Copyright 2025 2am

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the “Software”), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.