iplayabc-db-migrate

A simple and efficient database migration tool for MySQL databases.

Prerequisites

• Node.js(12.8.0+)
• MySQL database
• Required Node.js packages: umzug, mysql2 (or whatever MySQL client you're using)

Installation

Install the package using npm:

npm install iplayabc-db-migrate

You could refer to https://iplayabc.coding.net/p/huaweicloud/artifacts/25067203/npm/packages?hash=85b1301efbde44f996542be55f6db677 if you meet credential problems.

Usage

1. First, ensure you have a config.js file in your project with a db property containing your database configuration:
2. Create a db-migration/migrations folder in your project root to store your SQL migration files.
3. In your main server file (e.g., server.js), import and use the migration tool:

const db_migrate = require('iplayabc-db-migrate');
async function startServer() {
    try {
        await db_migrate('config.js');
        console.log('Database migration completed successfully');
        // Continue with your server setup
    } catch (error) {
        console.error('Migration failed:', error);
        process.exit(1);
    }
}
startServer();

Configuration

This module supports customizing the migration table name through the configuration file.

Migration Table Name

You can set the migration table name in your config file. If not specified, the default table name 'migrations' will be used. Example config.js:

exports.migration_table = 'migrations'

SQL File Naming Convention

Name your SQL migration files using the following format:

YYYYMMDDHHMMSS_description.sql

For example:

• 20230615120000_create_users_table.sql
• 20230616093000_add_email_to_users.sql

Running Migrations

Migrations will run automatically when you call db_migrate() in your code. This function returns a promise, so you can await it or use .then() syntax.

The tool will:

1. Check for any new migration files that haven't been executed yet.
2. Execute these new migrations in order.
3. Record the executed migrations in a migrations table in your database.

Error Handling

If a migration fails, the tool will log the error and stop the migration process. It's important to handle these errors in your application code, as shown in the usage example above.

Best Practices

1. Always review migration files before executing them.
2. Test migrations in a non-production environment first.
3. Keep migrations small and focused on a single change.
4. Use descriptive names for your migrations.
5. Include both "up" and "down" migrations when possible for reversibility.

Important: Modifying or deleting migration files that have already been executed can lead to inconsistencies between your database state and migration history. If you need to make changes, create a new migration instead.