Mysql2 Migrations :: Type module projects

• Create and manager migrations with mysql2 from repositories with configuration "TYPE MODULE"

📫 Disclaimer

• This package must be used with MODULE TYPE IMPORT AND FROM
• Set config on package.json "type": "module"
• NO compatible with MODULE.EXPORT AND REQUIRE

🧠 Configuration

• step 1

  • Install dependencies
  • Should install 'mysql2' dependency in your projects first

  npm i mysql2
  npm i mysql2-migrations

• step 2

  • Execute script to add files configuration in environment (reminder: add credentials in Migration instance on finalize)

  npx mysql2-migrations init

• step 3 (Optional configure environment yourself)

  • 1.Create a folder in root app with name "mysql2-migrations"
  • 2.Create a "migrations_config.js" file in "mysql2-migrations" folder with next configuration(add your db credentials here)

  import Migration from 'mysql2-migrations'
    
  const db_query = new Migration()
  db_query.database = "test"
  db_query.user = "root"
  db_query.password = "password"
  db_query.host = "127.0.0.1"
  db_query.port = 3306
  db_query.name_table_migrations = "table_migrations_app" // two characters minimum
  db_query.show_query = true
  db_query.show_depuration = true // show depuration on finalize migration, recommended
  db_query.start()

  • 3.Create a subfolder "migrations" into "mysql2-migrations" folder:

    • root_app/
      • mysql2-migrations/
        • migrations_config.js
        • migrations/

  • 4.Add scripts commands to package.json configuration:

  "scripts": {
      "db_create": "node mysql2-migrations/migrations_config.js create",           
      "db_refresh": "node mysql2-migrations/migrations_config.js refresh",                
      "db_migrate_all": "node mysql2-migrations/migrations_config.js migrate",   
      "db_migrate": "node mysql2-migrations/migrations_config.js up",                   
      "db_rollback": "node mysql2-migrations/migrations_config.js down",
      "db_status": "node mysql2-migrations/migrations_config.js status"              
  }

👋 Description script commnads

• db_create

  Create file to migrate:

  npm run db_create create_users_table

  npm run db_create alter_sales_table

  Edit migration file with query

• db_migrate

  Migrate last file pending:

  npm run db_migrate

• db_migrate_all

  Migrate all files, Execute first time after initialize repository:

  npm run db_migrate_all

  If already exists:

• db_rollback

  Undo latest migration:

  npm run db_rollback

• db_status

  Check migrations integrity:

  npm run db_status

• db_refresh

  Undo and redo all migrations (CAUTION DATA LOSS, It is not recommended to do it ):

  npm run db_refresh

• too You can also UP or DOWN direct migrations

  DIRECT MIGRATIONS WILL NOT BE SAVED IN THE "table_migrations_app" TABLE

  Example type up:

  node mysql2-migrations/migrations_config.js run 1763854407404_create_users_table.js up

  Example type down:

  node mysql2-migrations/migrations_config.js run 1763854407404_create_users_table.js down

👩‍💻 Add file migrations

• Add file to migrate:

  npm run db_create create_users_table 

• Go to "migrations" folder and edit file with query(should contain up and down query or only up):

  export default {
      "description":"Create Users Table",
      "up":
          `
          CREATE TABLE users(
              user_id BIGINT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
              name VARCHAR(100) NOT NULL,
              surname VARCHAR(100) NOT NULL,
              created_at DATETIME(6) NOT NULL,
              updated_at DATETIME(6) NOT NULL,
              PRIMARY KEY (user_id),
              UNIQUE INDEX user_id_UNIQUE (user_id ASC) VISIBLE)
          `
      ,
      "down":"DROP TABLE users"
  }

⚡️ Run migrations

• Finally, run the migration with the command:

npm run db_migrate

🔥 Others

• Help

npx mysql2-migrations help