Simple PHP DB Migration script with example migrations

20081231235959-example_migration.php

<?php
# db/migrations/20081231235959-example_migration.php

/**
 * Example migration. This is just here to serve as a kind of template for creating
 * new migrations.
 *
 * The filename convention is: YYYYMMDDHHIISS-some_class_name.php
 *
 * The name of the class defined within the migration file should match the what
 * is used in the filename (`SomeClassName`) and should be descriptive of what
 * action is being performed by the migration. Letters are cheap, use them.
 *
 * Class names must be camelcase with *no* all-caps parts (i.e: 'HTML' is 'Html').
 *
 * Some example class names:
 *    AddEmailToUsers
 *    RemoveUrlFromImages
 *    AddIndexesToImages
 *    RenameHashToSha1sumForFiles
 *
 */
class ExampleMigration
  extends MigrationBase
{
  public static function up() {
    // Apply changes to the database
    static::run([
      '-- some sql query --',
      '-- some other sql query --',
    ]);
  }

  public static function down() {
    // Revert the changes to the database
    static::run([
      '-- inverse of some sql query --',
      '-- inverse of some other sql query --',
    ]);
  }
}

20171025232200-rename_state_columns.php

<?php
# db/migrations/20171025232200-rename_state_columns.php

class RenameStateColumns
  extends MigrationBase
{
  public static function up() {
    static::run([
      'ALTER TABLE Clients CHANGE COLUMN chrState chrStateCode CHAR(2) DEFAULT NULL AFTER chrCity',
      'ALTER TABLE Users CHANGE COLUMN chrState chrStateCode CHAR(2) DEFAULT NULL AFTER chrCity',
    ]);
  }

  public static function down() {
    static::run([
      'ALTER TABLE Clients CHANGE COLUMN chrStateCode chrState CHAR(2) DEFAULT NULL AFTER chrCity',
      'ALTER TABLE Users CHANGE COLUMN chrStateCode chrState CHAR(2) DEFAULT NULL AFTER chrCity',
    ]);
  }
}

20190823170226-create_timeclock.php

<?php
# db/migrations/20190823170226-create_timeclock.php

class CreateTimeclock
  extends MigrationBase
{
  public static function up() {
    static::run([
      'CREATE TABLE Timeclock (
        idTimeclock bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
        idUser bigint(20) DEFAULT NULL,
        dtIn datetime DEFAULT NULL,
        dtOut datetime DEFAULT NULL
      )'
    ]);
  }

  public static function down() {
    static::run([
      'DROP TABLE Timeclock'
    ]);
  }
}

migrate.php

<?php
# db/migrate.php

/**
 * Load and run database schema migrations from `./migrations`
 */

/**
 * Author: Karl Wilbur <[email protected]>
 */

require(__DIR__ . '/../config/cli-startup.inc.php');

# `$config` is loaded from the `*-startup.inc.php` file

function show_help() {
  echo <<<'EOT'

  # `migrate.php`

  By default, all database migrations stored in `db/migrations/` and not
  previously run will be loaded and their respective `up` methods will be run.
  Upon successful completion of all migrations, a SQL dump of the schema will be
  created as `{DATABASE_NAME}-schema.sql` (overwriting if file already exists).

  ## Options

    `down`

            Run `down` method for the relevant migration(s).

    `dump-schema[=disabled|enabled|only]`

            Create/update schema SQL file after all migrations have been
            successfully completed. [DEFAULT=enabled]

            If 'only', then no migrations will be run, only the the schema file
            will be created/updated.

    `force`

            Run *all* migrations, including those which may not normally be
            considered relevant. Normally, when running `up`, only those
            unapplied migrations are run; when running `down`, those which have
            been previously applied are run.

    `in-directory`

            An alternative directory from which to load files. The full path to
            the directory needs to be provided.

    `to-version`

            The target version number to be used when loading migrations. All
            migrations from current version to targeted version are loaded,
            including the target version.

    `up`

            Run `up` method for relevant migration(s). [DEFAULT]

    `version`

            The version number of the migration to be loaded. Only this
            migration will be run.

    `with-backup[=backup_filename]`

            Create backup of the database before running migration(s). If
            `backup_filename` is provided, it will be used; the default value
            for `backup_filename` is:
               `{DATABASE_NAME}-backup-YYYYMMSDDHHIISS.mysqldump.sql.bz2`

  ## Examples

    Load all unapplied migrations and run their respective `up` methods:
      $ migrate.php

    Migrate `up` all outstanding migrations up to version `12345`, ignoring
    all later migrations:
      $ migrate.php to-version=12345

    Migrate `up` single migration version `12345`:
      $ migrate.php only-version=12345

    Migrate `down` single migration version `12345`:
      $ migrate.php down only-version=12345

    Migrate `down` all migrations that have been previously applied above and
    including version `12345`:
      $ migrate.php down to-version=12345

    Run needed migrations from alternate directory `/opt/alt/db/migrations`:
      $ migrate.php in-directory=/opt/alt/db/migrations

    Only create/update schema file:
      $ migrate.php dump-schema=only

EOT;
}

$now = date('YmdHis');

$DATA = [
  'direction' => 'up',
  'dump_schema' => 'enabled',
  'force' => false,
  'migrations_directory' => __DIR__.'/migrations',
  'backup_filename' => __DIR__.'/'.$config->db->name.'-backup-'.$now.'.mysqldump.sql.bz2',
];

function parse_args() {
  global $argv, $DATA;

  $set_direction = false;
  foreach($argv as $index => $str) {
    if ($index === 0) continue;
    @list($arg,$val) = explode('=', $str);
    switch(ltrim($arg, '-')) {
      case 'down':
        if ( $set_direction ) {
          echo "Multiple directions specified. This doesn't make sense.";
          exit();
        }
        $DATA['direction'] = 'down';
        $set_direction = true;
        break;
      case 'dump-schema':
        if ( $val ) {
          $DATA['dump_schema'] = $val;
        }
        break;
      case 'force':
        $DATA['force'] = true;
        break;
      case 'h':
      case 'help':
        show_help();
        exit();
      case 'version':
        $DATA['version'] = $val;
        break;
      case 'to-version':
        $DATA['to_version'] = $val;
        break;
      case 'up':
        if ( $set_direction ) {
          echo "Multiple directions specified. This doesn't make sense.";
          exit();
        }
        $DATA['direction'] = 'up';
        $set_direction = true;
        break;
      case 'in-directory':
        $DATA['migrations_directory'] = $val;
        break;
      case 'with-backup':
        if ( $val ) {
          $DATA['backup_filename'] = $val;
        }
        backup_database();
        break;
    }
  }
}

# ref: https://github.com/phpfunct/funct/blob/master/src/Strings.php
function classify($string)
{
  return camelize($string, true);
}

# ref: https://github.com/phpfunct/funct/blob/master/src/Strings.php
function camelize($input, $firstLetterUppercase = false)
{
  $input = trim($input);
  if ($firstLetterUppercase) {
    $input = ucfirst($input);
  } else {
    $input = lcfirst($input);
  }
  $input = preg_replace('/^[-_]+/', '', $input);
  $input = preg_replace_callback(
    '/[-_\s]+(.)?/u',
    function ($match) {
      if (isset($match[1])) {
        return strtoupper($match[1]);
      } else {
        return '';
      }
    },
    $input
  );
  $input = preg_replace_callback(
    '/[\d]+(.)?/u',
    function ($match) {
      return strtoupper($match[0]);
    },
    $input
  );
  return $input;
}

function create_versions_table() {
  $query = 'CREATE TABLE IF NOT EXISTS _migrations (version bigint NOT NULL PRIMARY KEY)';
  do_mysql_query($query, 'Creating version table, if it does not already exist.');
}

function backup_database() {
  global $config, $DATA;;

  $backup_filename = $DATA['backup_filename'];
  echo 'Creating backup of database prior to migration. '.$backup_filename."\n";
  exec('mysqldump '.
    '--user="'.$config->db->user.'" '.
    '--password="'.$config->db->pass.'" '.
    '--host="'.$config->db->host.'" '.
    '--port="'.$config->db->port.'" '.
    '--databases '.
    $config->db->name.
    ' 2>/dev/null '. # supress errors and complaints
    ' | bzip2 > '.$backup_filename
  );
}

function dump_schema() {
  global $config, $DATA;;

  $schema_filename = $config->db->name.'-schema.sql';
  $action = 'Generating';
  if(file_exists($schema_filename)) {
    $action = 'Updating';
  }
  echo $action.' schema file: '.$schema_filename."\n";
  exec('mysqldump '.
    '--user="'.$config->db->user.'" '.
    '--password="'.$config->db->pass.'" '.
    '--host="'.$config->db->host.'" '.
    '--port="'.$config->db->port.'" '.
    '--databases '.
    '--no-data '.
    $config->db->name.
    ' > '.$schema_filename.
    ' 2>/dev/null ' # supress errors and complaints
  );
}

function get_current_version() {
  $version = NULL;
  $query = 'SELECT MAX(version) AS version FROM _migrations';
  $result = get_mysql_rows($query, 'Fetching current database version.');
  if(count($result)) {
    $version = $result[0]['version'];
  }
  return $version;
}

function load_migrations() {
  global $DATA;

  $migrations = [];
  $filenames = scandir($DATA['migrations_directory']);
  foreach($filenames as $filename) {
    if($filename=='.' || $filename=='..') continue;
    list($version_number) = explode('-', $filename);
    // strip off the version number and the 4 characters ('.php') at the end.
    $migration_name = substr(
      $filename,
      strlen($version_number)+1,
      strlen($filename)-strlen($version_number)-5
    );
    $class_name = classify($migration_name);
    // determine if this version should be skipped
    if ( isset($DATA['version']) && $version_number != $DATA['version'] ) {
      continue;
    } else if ( isset($DATA['to_version']) &&
      (
        ($DATA['direction'] == 'up' && $version_number > $DATA['to_version'])
        ||
        ($DATA['direction'] == 'down' && $version_number < $DATA['to_version'])
      )
    ) {
      // We do not `break` here because the version numbers may not be in order.
      continue;
    }

    $migrations[] = require_migration_file(
      $filename,
      $version_number,
      $class_name
    );
  }
  return $migrations;
}

function require_migration_file($filename, $version_number, $class_name) {
  global $DATA;

  $migration = ['version'=>$version_number, 'class_name'=>$class_name];
  require_once($DATA['migrations_directory'].'/'.$filename);
  if(!class_exists($class_name)) {
    throw new Exception(
      'Missing migration class. Expected to find class definition for '.
      $class_name.' in '.$DATA['migrations_directory'].'/'.$filename
    );
  }
  return $migration;
}

function version_exists($version) {
  $query = 'SELECT version FROM _migrations WHERE version='.$version;
  return !!count(get_mysql_rows($query, 'Database version lookup.'));
}

function run_migration($migration, $direction) {
  global $DATA;

  $migration['class_name']::$direction();
  echo "\n";
  if($DATA['direction'] == 'up') {
    echo '#### Running migration '.$migration['class_name'].'::'.$direction;
    echo "\n";
    do_mysql_query(
      'INSERT INTO _migrations VALUES ('.$migration['version'].')'
    );
  } else if($DATA['direction'] == 'down') {
    echo '#### Reverting migration '.$migration['class_name'].'::'.$direction;
    echo "\n";
    do_mysql_query(
      'DELETE FROM _migrationsWHERE version='.$migration['version']
    );
  }
}

parse_args();
if ( $DATA['dump_schema'] != 'only' ) {
  create_versions_table();
  $DATA['current_version'] = get_current_version();
  $migrations = load_migrations();
  $migrations_run = 0;
  if ( $DATA['force'] ) {
    echo "\n".'FORCING MIGRATIONS!!'."\n";
  }
  foreach($migrations as $migration) {
    if ( $DATA['force'] ) {
      run_migration($migration, $DATA['direction']);
    } else if (
      $DATA['direction'] == 'down' && version_exists($migration['version'])
    ) {
      run_migration($migration, $DATA['direction']);
      $migrations_run++;
    } else if (
      $DATA['direction'] == 'up' && !version_exists($migration['version'])
    ) {
      run_migration($migration, $DATA['direction']);
      $migrations_run++;
    }
  }
}

if ( $migrations_run == 0 ) {
  echo 'No migrations were run.'."\n";
} else {
  echo 'Ran '.$migrations_run.' migrations.'."\n";
}

if ( $DATA['dump_schema'] != 'disabled' ) {
  dump_schema();
}

MigrationBase.php

<?php
# lib/MigrationBase.php

class MigrationBase {
  protected static function run($queries = []) {
    foreach($queries as $query) {
      echo $query."\n";
      if(do_mysql_query($query, get_called_class().' migration query')) {
        echo '...success.'."\n";
      } else {
        echo '...FAILED!'."\n";
        exit();
      }
    }
  }
}

I just realized that there is a typo in the help section in migrate.php on line 70 YYYYMMSDDHHIISS should be YYYYMMDDHHIISS.