npm package: @2amtech/sql-migration-tool
Description: SQL Migration Tool by 2am.tech - A comprehensive database migration management library and CLI
Author: Aleksandar Panic
Latest version: 1.0.1

# @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:

```bash
npm run build
```

## Global CLI Usage

Install globally:

```bash
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.

```bash
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.

```bash
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.

```bash
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.

```bash
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.

```bash
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.

```bash
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.

```bash
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.