MySQL Data Migrator

A lightweight MySQL data migration runner for MySQL databases using raw SQL queries, migration history tracking, folder-based migrations, existing connection support, and rollback support.

This package is mainly designed for data migrations such as:

• UPDATE
• INSERT
• DELETE
• Backfills
• Cleanup operations
• Data normalization
• Moving data between tables

You can also use it for schema migrations like ALTER TABLE, but use them carefully because MySQL may auto-commit DDL statements.

Use this package at your own risk while using schema operations like ALTER TABLE, DROP TABLE, TRUNCATE TABLE, etc.

Note: For this package, using mysql2 is recommended for proper functioning of this package. Usage of any deprecated versions of mysql can lead to performance issues.

Install

npm install @atith/mysql-data-migrator

npm install mysql2

Features

• Supports raw SQL migrations
• Supports parameterized SQL queries
• Supports migration array
• Supports migration folder path
• Supports .migration.js files
• Automatically derives migration name from file name if name is not provided
• Supports up migrations
• Supports down rollback migrations
• Supports rollback using steps
• Supports rollback using specific migration file paths
• Supports creating a MySQL connection from a connection config object
• Supports passing an existing MySQL connection instance
• Tracks migration operation as up or down
• Updates timestamp when the same migration name and operation already exists
• Uses a migration history table

Connection Configuration

You can pass the database connection in two ways.

1. Pass Connection Config Object

This is the default and simplest way.

The package creates and manages the MySQL connection internally.

const { runMigrations } = require("@atith/mysql-data-migrator");

async function main() {
  const result = await runMigrations({
    connection: {
      host: "localhost",
      user: "root",
      password: "password",
      database: "test_db",
      port: 3306,
    },

    migrationTableName: "_data_migrations",
    migrations: "./migrations",
  });

  console.log(result);
}

main().catch(console.error);

2. Pass Existing MySQL Connection

You can also create the MySQL connection yourself and pass the connection instance to the package.

This is useful when:

• Your app already manages the database connection
• You want to reuse an existing connection
• You want more control over connection creation
• You want to use custom mysql2 connection options

CommonJS Example

Use this when your project is using CommonJS.

const { runMigrations } = require("@atith/mysql-data-migrator");
const mysql = require("mysql2/promise");

async function main() {
  const connection = await mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "password",
    database: "test_db",
    port: 3306,
  });

  try {
    const result = await runMigrations({
      connection,
      migrationTableName: "_data_migrations",
      migrations: "./migrations",
    });

    console.log(result);
  } finally {
    await connection.end();
  }
}

main().catch(console.error);

ES Module Example

Use this when your project has "type": "module" in package.json, or when your file is running as an ES module.

import { runMigrations } from "@atith/mysql-data-migrator";
import mysql from "mysql2/promise";

const connection = await mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  database: "test_db",
  port: 3306,
});

try {
  const result = await runMigrations({
    connection,
    migrationTableName: "_data_migrations",
    migrations: "./migrations",
  });

  console.log(result);
} finally {
  await connection.end();
}

Important: mysql.createConnection() returns a promise, so always use await.

Correct:

const connection = await mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  database: "test_db",
  port: 3306,
});

Incorrect:

const connection = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  database: "test_db",
  port: 3306,
});

Migration History Table

By default, the package creates a migration table named: "_data_migrations"

The table stores migration history like this:

CREATE TABLE IF NOT EXISTS `_data_migrations` (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  operation ENUM('up', 'down') NOT NULL,
  executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_migration_operation (name, operation)
);

The operation column stores whether the migration was executed as:

up
down

If the same name and operation already exist, the package updates the executed_at timestamp instead of inserting a duplicate record.

Example:

| id | name | operation | executed_at | | -: | --------------------- | --------- | ------------------- | | 1 | 001_add_status_column | up | 2026-05-29 10:00:00 | | 2 | 001_add_status_column | down | 2026-05-29 10:05:00 |

Basic Usage With Migration Array

const { runMigrations } = require("@atith/mysql-data-migrator");

async function main() {
  const result = await runMigrations({
    connection: {
      host: "localhost",
      user: "root",
      password: "password",
      database: "test_db",
      port: 3306,
    },

    migrationTableName: "_data_migrations",

    migrations: [
      {
        name: "001_trim_users_test_names",
        up: {
          sql: `
            UPDATE users_test
            SET name = TRIM(name)
            WHERE name IS NOT NULL
          `,
          params: [],
        },
      },
    ],
  });

  console.log(result);
}

main().catch(console.error);

Migration File Usage

You can also pass a folder path instead of an array.

const { runMigrations } = require("@atith/mysql-data-migrator");

async function main() {
  const result = await runMigrations({
    connection: {
      host: "localhost",
      user: "root",
      password: "password",
      database: "test_db",
      port: 3306,
    },

    migrationTableName: "_data_migrations",

    migrations: "./migrations",
  });

  console.log(result);
}

main().catch(console.error);

Folder structure:

project-root/
  migrations/
    001_add_status_column.migration.js
    002_update_status_values.migration.js

Only files ending with .migration.js will be executed and rest of them will be excluded.

Migration files are loaded in sorted order, so use a number prefix:

001_first_migration.migration.js
002_second_migration.migration.js
003_third_migration.migration.js

Example Migration File

Create:

migrations/001_add_status_column.migration.js

module.exports = {
  up: {
    sql: `
      ALTER TABLE users_test
      ADD COLUMN status VARCHAR(50) DEFAULT 'active'
    `,
    params: [],
  },

  down: {
    sql: `
      ALTER TABLE users_test
      DROP COLUMN status
    `,
    params: [],
  },
};

If name is not provided, the package automatically uses the file name without .migration.js.

For this file:

001_add_status_column.migration.js

The migration name becomes:

001_add_status_column

You can also explicitly provide a name:

module.exports = {
  name: "001_add_status_column",

  up: {
    sql: `
      ALTER TABLE users_test
      ADD COLUMN status VARCHAR(50) DEFAULT 'active'
    `,
    params: [],
  },

  down: {
    sql: `
      ALTER TABLE users_test
      DROP COLUMN status
    `,
    params: [],
  },
};

Running Up Migrations

runMigrations() executes the up query of pending migrations.

const { runMigrations } = require("@atith/mysql-data-migrator");

await runMigrations({
  connection,
  migrationTableName: "_data_migrations",
  migrations: "./migrations",
});

Behavior:

| Latest operation | What happens | | -------------------------- | --------------- | | No record exists | Runs up | | Latest operation is down | Runs up again | | Latest operation is up | Skips migration |

Example result:

{
  "executed": [
    {
      "name": "001_add_status_column",
      "status": "success"
    }
  ],
  "skipped": [],
  "failed": []
}

Running Down Migrations Using Steps

rollbackMigrations() executes the down query.

const { rollbackMigrations } = require("@atith/mysql-data-migrator");

await rollbackMigrations({
  connection,
  migrationTableName: "_data_migrations",
  migrations: "./migrations",
  steps: 1,
});

steps: 1 rolls back the latest applied migration.

Example:

001_add_status_column
002_update_status_values

If both were applied, then:

steps: 1

rolls back:

002_update_status_values

Example result:

{
  "rolledBack": [
    {
      "name": "002_update_status_values",
      "status": "rolled_back"
    }
  ],
  "skipped": [],
  "failed": []
}

Running Down Migrations Using Specific Files

You can rollback specific migration files using rollbackFiles.

const { rollbackMigrations } = require("@atith/mysql-data-migrator");

await rollbackMigrations({
  connection,
  migrationTableName: "_data_migrations",
  migrations: "./migrations",

  rollbackFiles: [
    "./migrations/002_update_status_values.migration.js",
  ],
});

This rolls back only:

002_update_status_values

If both steps and rollbackFiles are provided, rollbackFiles takes priority and steps is ignored.

Example:

await rollbackMigrations({
  connection,
  migrationTableName: "_data_migrations",
  migrations: "./migrations",
  steps: 5,

  rollbackFiles: [
    "./migrations/002_update_status_values.migration.js",
  ],
});

The above will rollback only:

002_update_status_values

It will not rollback 5 migrations.

Example Migration With Params

module.exports = {
  up: {
    sql: `
      UPDATE users_test
      SET name = ?
      WHERE name = ''
    `,
    params: ["UNKNOWN"],
  },

  down: {
    sql: `
      UPDATE users_test
      SET name = ?
      WHERE name = 'UNKNOWN'
    `,
    params: [""],
  },
};

The ? placeholders are filled using the params array.

This is safer than directly injecting values into the SQL string.

Full Test Example

Create test table:

DROP TABLE IF EXISTS _data_migrations;
DROP TABLE IF EXISTS users_test;

CREATE TABLE users_test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO users_test (name)
VALUES
  ('Atith'),
  ('John'),
  ('Mike');

Create migration:

migrations/001_add_status_column.migration.js

module.exports = {
  up: {
    sql: `
      ALTER TABLE users_test
      ADD COLUMN status VARCHAR(50) DEFAULT 'active'
    `,
    params: [],
  },

  down: {
    sql: `
      ALTER TABLE users_test
      DROP COLUMN status
    `,
    params: [],
  },
};

Run up using connection config:

const { runMigrations } = require("@atith/mysql-data-migrator");

async function main() {
  const result = await runMigrations({
    connection: {
      host: "localhost",
      user: "root",
      password: "password",
      database: "test_db",
      port: 3306,
    },

    migrationTableName: "_data_migrations",
    migrations: "./migrations",
  });

  console.log(JSON.stringify(result, null, 2));
}

main().catch(console.error);

Run up using existing connection:

const { runMigrations } = require("@atith/mysql-data-migrator");
const mysql = require("mysql2/promise");

async function main() {
  const connection = await mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "password",
    database: "test_db",
    port: 3306,
  });

  try {
    const result = await runMigrations({
      connection,
      migrationTableName: "_data_migrations",
      migrations: "./migrations",
    });

    console.log(JSON.stringify(result, null, 2));
  } finally {
    await connection.end();
  }
}

main().catch(console.error);

Run down:

const { rollbackMigrations } = require("@atith/mysql-data-migrator");

async function main() {
  const result = await rollbackMigrations({
    connection: {
      host: "localhost",
      user: "root",
      password: "password",
      database: "test_db",
      port: 3306,
    },

    migrationTableName: "_data_migrations",
    migrations: "./migrations",
    steps: 1,
  });

  console.log(JSON.stringify(result, null, 2));
}

main().catch(console.error);

Important Notes

Data Migrations

This package is best suited for data migrations such as:

UPDATE
INSERT
DELETE
Backfills
Cleanup operations
Data normalization

Existing Connection Management

When you pass a connection config object, the package can create and manage the connection internally.

When you pass an existing MySQL connection instance, you are responsible for closing it.

await connection.end();

A good pattern is to use try...finally:

try {
  await runMigrations({
    connection,
    migrations: "./migrations",
  });
} finally {
  await connection.end();
}

Schema Migrations

You can run schema migrations such as:

ALTER TABLE users_test ADD COLUMN status VARCHAR(50)

But MySQL may auto-commit DDL statements.

That means rollback may not behave the same way as it does for normal data queries.

Use schema migrations carefully.

Irreversible Data Migrations

Some data migrations cannot be perfectly reversed.

Example:

UPDATE users_test
SET name = TRIM(name)

Before:

"  Atith  "

After:

"Atith"

A down query cannot restore the exact original spaces unless you saved a backup.

For such cases, avoid adding down, or set it to null.

API

runMigrations(config)

Runs pending up migrations.

await runMigrations({
  connection,
  migrationTableName: "_data_migrations",
  migrations: "./migrations",
});

Config options:

| Option | Type | Required | Description | | -------------------- | ------------------- | -------- | -------------------------------------------------------------------- | | connection | object | Yes | MySQL connection config object or existing MySQL connection instance | | migrationTableName | string | No | Migration history table name. Defaults to _data_migrations | | migrations | array or string | Yes | Migration array or migration folder path |

rollbackMigrations(config)

Runs down migrations.

Rollback by steps:

await rollbackMigrations({
  connection,
  migrationTableName: "_data_migrations",
  migrations: "./migrations",
  steps: 1,
});

Rollback by specific files:

await rollbackMigrations({
  connection,
  migrationTableName: "_data_migrations",
  migrations: "./migrations",
  rollbackFiles: [
    "./migrations/002_update_status_values.migration.js",
  ],
});

Config options:

| Option | Type | Required | Description | | -------------------- | ------------------- | -------- | -------------------------------------------------------------------- | | connection | object | Yes | MySQL connection config object or existing MySQL connection instance | | migrationTableName | string | No | Migration history table name. Defaults to _data_migrations | | migrations | array or string | Yes | Migration array or migration folder path | | steps | number | No | Number of latest applied migrations to rollback | | rollbackFiles | array | No | Specific migration file paths to rollback. Takes priority over steps |

License

MIT