PHP database migration

Table of contents

1. Introduction
   
2. Installation
   2.1. Project initialisation
   2.2. Customizing configuration
   
3. Generating a migration script
   
4. Migrating UP and DOWN
   4.1 Migrating UP
   4.2 Migrating DOWN
   4.3 Force a unique migration
   4.4 Make migrations transactional
   
5. Migrations status
   
6. Dealing with environements
   

1. Introduction

PHP database migration is a small utility inspired by two great migration tool called MyBatis and Rails rake that allow to easily maintain database schema on multiple environments without much effort.

2. Installation

Just clone the git repository somewhere on the project folder (each installation maintain one project). git clone git://github.com/alwex/php-database-migration.git

2.1. Project initialisation

execute the following comand ./migrate --init --driver=pgsql --database=my_dev_database --host=my_dev_db_host --login=my_db_login --password=my_password --changelog=changelog explanation

--driver    => PDO supported driver
--database  => the name of the development database
--host      => the hostname where is located the development database
--login     => database login
--password  => database password
--changelog => changelog and versioning table recording migrations

It will output

======= Initialization =======

Configuration files created:
  ./environments
         |----development.ini
         |----preproduction.ini
         `----production.ini

Migration directory created:
   ./migrations

You may modify the environemnts configuration files
and create the changelog table changelog

create table changelog (id numeric(20,0), applied_at character varying(25), description character varying(255));

The init command create default configuration files for the three commons environments

• development
• preproduction
• production

Each of the default environment file are initialized with the development configuration.

2.2. Customizing configuration

Environments are not finals, it is always possible to add/remove environments other than the development, preproduction and production. For example if a localhost environment is needed, just add a localhost.ini with the correct configuration in the environments directory and then use it with --env=localhost option.

3. Generating a migration script

PHP database migration is based on migrations scripts that contains plain SQL (depending on the vendor specificities). Migration scripts structure is as follow

--// my empty migration script                                                                                        
-- Migration SQL that makes the change goes here.

--//@UNDO
-- SQL to undo the change goes here.

4. Migrating UP and DOWN

All migration scripts are stored in the directory ./migrations.

4.1 Migrating UP

To migrate UP the migrations scripts simply type the following:

4.2 Migrating DOWN

4.3 Force a unique migration

4.4 Make migrations transactional

5. Migrations status

6. Dealing with environements