Database Migration Tool

Universal tool for automatic database structure comparison and SQL migration script generation. Works with any environment and database.

Features

• Automatic database detection - scans folder and matches dev/sit/prod pairs
• Universal configuration - works for any environments and naming conventions
• CREATE TABLE generation - automatically creates missing table definitions
• Intelligent comparison - detects differences in columns, types, indexes
• Flexible configuration - full control over migration process
• Clear reports - detailed SQL comments

Quick Start

1. Install dependencies

npm install

2. Export database structures

First, export structures from both environments:

# Edit export-dbs.js and configure connections
# Then run:
node export-dbs.js

This creates files in export/ folder:

export/
├── app_dev_database1.md
├── app_sit_database1.md
├── app_dev_database2.md
└── app_sit_database2.md

3. Generate migration

node compare-and-migrate.js

The script automatically:

• Detects all database pairs
• Compares their structures
• Generates SQL scripts in migration/ folder

4. Review and execute

# Review generated files
ls migration/

# Execute migration
mysql -h <host> -u <user> -p < migration/migrate_all_dev_to_sit.sql

Configuration

Basic configuration (in compare-and-migrate.js)

const CONFIG = {
    // Folders
    exportDir: path.join(__dirname, "export"),
    outputDir: path.join(__dirname, "migration"),

    // File pattern: <prefix>_<env>_<dbname>.md
    filePattern: /^(.+?)_([a-zA-Z0-9]+)_(.+)\.md$/,

    // Migration direction
    sourceEnvironment: "dev",      // Source
    targetEnvironment: "sit",      // Target

    // Options
    normalizeTypeSizes: true,      // bigint(20) === bigint
    generateCreateTable: true,     // Generate CREATE TABLE
    warnExtraColumns: true,        // Warn about extra columns
    caseInsensitiveDbNames: true   // ECF === ecf
};

File Naming Pattern

Default pattern: <prefix>_<environment>_<database>.md

Valid examples:

• app_dev_users.md + app_sit_users.md
• myapp_prod_payment.md + myapp_dev_payment.md
• project_local_db1.md + project_staging_db1.md

Invalid examples:

• dev-users.md (missing separator _)
• users_dev.md (wrong order, missing prefix)
• dev_users.txt (wrong extension, must be .md)

What Does It Detect?

Structural Differences

• Missing tables - generates CREATE TABLE
• Missing columns - generates ALTER TABLE ADD COLUMN
• Type differences - generates ALTER TABLE MODIFY COLUMN
• Nullable differences - generates ALTER TABLE MODIFY COLUMN
• Missing indexes - generates ALTER TABLE ADD INDEX/UNIQUE
• Extra tables - comment with warning
• Extra columns - comment with warning

Example Generated SQL

-- Migration for database: users
-- From dev to sit

USE `users`;

-- Creating table 'activation_link' from dev
CREATE TABLE `activation_link` (
  `id` bigint(20) NOT NULL,
  `token` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_token` (`token`)
);

-- Table: user
-- Added missing column 'email' from dev
ALTER TABLE `user` ADD COLUMN `email` varchar(255) NULL;
-- Changed column type 'phone' from varchar(20) to varchar(255)
ALTER TABLE `user` MODIFY COLUMN `phone` varchar(255) NULL;
-- Added missing unique index 'UK_email'
ALTER TABLE `user` ADD UNIQUE INDEX `UK_email` (`email`);

Project Structure

db_migration/
├── export/                      # Database structure exports
│   ├── app_dev_ecf.md
│   ├── app_sit_ecf.md
│   ├── app_dev_payment.md
│   └── app_sit_payment.md
│
├── migration/                   # Generated migrations
│   ├── migrate_ecf.sql
│   ├── migrate_payment.sql
│   └── migrate_all_dev_to_sit.sql
│
├── compare-and-migrate.js       # Main script
├── export-dbs.js                # Structure export script
├── config.example.js            # Example configuration
├── package.json
└── README.md

FAQ

How to change migration direction (e.g., SIT to DEV instead of DEV to SIT)?

Change in CONFIG:

sourceEnvironment: "sit",
targetEnvironment: "dev",

Can I use other environment names (e.g., local, staging)?

Yes! You can use any names, for example:

sourceEnvironment: "local",
targetEnvironment: "staging",

Make sure files in export/ folder use the same names:

• app_local_users.md
• app_staging_users.md

Does the script transfer data?

NO. The script synchronizes only database STRUCTURE (tables, columns, indexes). It does not transfer records.

How to execute for a specific database pair?

Currently the script processes all found pairs. If you want only one database:

1. Move other files outside export/ folder
2. Run the script
3. Restore the files

Or use a specific generated file, e.g., migrate_users.sql.

Security

IMPORTANT: Before executing migration:

1. Backup - Always backup the target database
2. Test on dev - Test migration on test environment
3. Check SQL - Review generated SQL scripts
4. NOT NULL - NOT NULL columns may require default values
5. Production data - Be especially careful with production

Support

For issues:

1. Check FAQ above
2. Read migration/README.md (generated automatically)
3. Contact development team

License

MIT

Author

Database Migration Tool