simple-db-migrator

A simple database migration tool written in PHP

Quick introduction.

1. This is a simple database migration commandline application written in php.
2. Contents of each SQL file is run as single transaction.
3. The migrator tool will save the down migration (rollback SQL statement) in DB and cross verify it with the current version of the rollback SQL statement present in the disk and complain if both are different.

Usage

Migrations are arranged in the following directory structure.

├── migrations
│   ├── down
│   │   ├── 001.sql
│   │   ├── 002.sql
│   │   ├── 003.sql
│   │   └── 004.sql
│   └── up
│       ├── 001.sql
│       ├── 002.sql
│       ├── 003.sql
│       └── 004.sql
└── simple-db-migrator.php

To create a new migration, Just create respective xxx.sql file in up and down directories.

Run initial migration ( ie, create db_migration table )

# '-s' option stands for 'setup'
php simple-db-migrator.php -s

Show status ( last completed migration and pending migrations )

php simple-db-migrator.php -l

Apply all pending migrations ( up )

php simple-db-migrator.php

Rollback last migration ( down )

php simple-db-migrator.php -d

Show help

$ php simple-db-migrator.php -h
Description:
  A simple database migration tool

Usage:
  php simple-db-migrator.php [options]

Options:
  -l, --list            Show the current status of applied migrations
  -s, --setup           Create db_migrations table in db and run all pending migrations
  -d, --down            Roll back last migration
  -h, --help            Display help for the given command. When no command is given display help for the db:migrate command
  -v|vv|vvv, --verbose  Increase the verbosity of messages: 1-3 => info,log,debug

Supported RDBMS

Tested with Mysql , PostgreSQL and Sqlite

Cavets

1. From php docs, "Some databases, including MySQL, automatically issue an implicit COMMIT when a database definition language (DDL) statement such as DROP TABLE or CREATE TABLE is issued within a transaction. The implicit COMMIT will prevent you from rolling back any other changes within the transaction boundary."
2. history of completed up/down migrations will be stored in a table called db_migrations . By default, this table is created with default encoding of active database . So if your database's default encoding is not utf-8 and you are planing to write some utf-8 string in the migration file, then you need to manually fix the encoding of db_migrations table . otherwise, migration tool will fail to save the hisotry of migration ( ie, content of up/down SQL file ) in db.
   • I am not integrating this feature into the tool for the sake of simplicity
   • it will be better to apply these "alter table" commands as a new migration.