-
Notifications
You must be signed in to change notification settings - Fork 32
PHP database migration
- Introduction
1.1 Features list
- Installation
2.1. Project initialisation
2.2. Customizing configuration
- Generating a migration script
- Migrating UP and DOWN
4.1 Migrating UP
4.2 Migrating DOWN
4.3 Force a unique migration
4.4 Make migrations transactional
- Migrations status
- Dealing with environements
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.
- generating plain SQL timestamped migration script
- listing applied and pending migrations depeding on the specified environement
- migrating up all pending scripts
- migrating up to a specified version
- migrating up a single script
- migrating down one version back
- migrating down to a specified version
- migrating down a single script
- transactional migration script (rollback last migration on failure)
- full transactional migrations (rollback all applied migration on failure)
- email notifier (send an email containing applied script to a specified email depending on environment)
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
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.
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.
example:
; BDD development configuration
url="pgsql:dbname=the_database;host=localhost"
username=the_username
password=the_password
; notification email for SQL migrations
; uncomment the line below for activation
notification_email="mail1@mail.com;mail2@mail.com"
notification_email_subject="Migration applied for MyApplication"
; name of the changelog table
; id | applied_at | description
; create table changelog (id numeric(20,0), applied_at character varying(25), description character varying(255));
changelog=changelog
PHP database migration is based on migrations scripts that contains plain SQL (depending on the vendor specificities). To generate an empty migration script type
./migrate --generate alter_table_user_add_column_password_varchar_255 migration: migrations/20120607101608_alter_table_user_add_column_password_varchar_255.sql
All migration scripts are stored in the directory ./migrations.
Migration scripts structure is as follow
--// alter table user add column password varchar 255 -- Migration SQL that makes the change goes here. --//@UNDO -- SQL to undo the change goes here.
The next step is to fill the migration script with the appropriate SQL.
--// alter table user add column password varchar 255 -- Migration SQL that makes the change goes here. ALTER TABLE user ADD COLUMN password character varying(255); --//@UNDO -- SQL to undo the change goes here. ALTER TABLE user DROP COLUMN password;
NB: The down script is mandatory. It is a good practice to always write the down section of a migration
./migrate --status ID Applied At description ===================================================================================== 20120530113517 2012-05-30 11:05:10 create table user and insert the first one 20120607101608 Pending... alter table user add column password varchar 255 20120607101726 Pending... create table user roles 20120607101846 Pending... create table books 20120607101854 Pending... create table stores
There is different way to migrate UP depending on the situation. The common way is to migrate all pending migrations.
./migrate --status ID Applied At description ===================================================================================== 20120530113517 2012-05-30 11:05:10 create table user and insert the first one 20120607101608 Pending... alter table user add column password varchar 255 20120607101726 Pending... create table user roles 20120607101846 Pending... create table books 20120607101854 Pending... create table stores
Simply type
./migrate --up ============= UP 20120607101608 ============= --// alter table user add column password varchar 255 -- Migration SQL that makes the change goes here. some SQL UP SUCCESS ============= UP 20120607101726 ============= --// create table user roles -- Migration SQL that makes the change goes here. some SQL UP SUCCESS ============= UP 20120607101846 ============= --// create table books -- Migration SQL that makes the change goes here. some SQL UP SUCCESS ============= UP 20120607101854 ============= --// create table stores -- Migration SQL that makes the change goes here. some SQL
The result on success will be
./migrate --status ID Applied At description ===================================================================================== 20120530113517 2012-05-30 11:05:10 create table user and insert the first one 20120607101608 2012-06-07 10:06:17 alter table user add column password varchar 255 20120607101726 2012-06-07 10:06:17 create table user roles 20120607101846 2012-06-07 10:06:17 create table books 20120607101854 2012-06-07 10:06:17 create table stores
All Pendings migration have been played against the default environment (dev) database.
./migrate --status ID Applied At description ===================================================================================== 20120530113517 2012-05-30 11:05:10 create table user and insert the first one 20120607101608 Pending... alter table user add column password varchar 255 20120607101726 Pending... create table user roles 20120607101846 Pending... create table books 20120607101854 Pending... create table stores
Simply type
./migrate --up=20120607101726 ============= UP 20120607101608 ============= --// alter table user add column password varchar 255 -- Migration SQL that makes the change goes here. some SQL UP SUCCESS ============= UP 20120607101726 ============= --// create table user roles -- Migration SQL that makes the change goes here. some SQL UP SUCCESS
The result on success will be
./migrate --status ID Applied At description ===================================================================================== 20120530113517 2012-05-30 11:05:10 create table user and insert the first one 20120607101608 2012-06-07 10:06:36 alter table user add column password varchar 255 20120607101726 2012-06-07 10:06:36 create table user roles 20120607101846 Pending... create table books 20120607101854 Pending... create table stores
All Pending migrations up to 20120607101726 included have been played against the default database (dev).
./migrate --status ID Applied At description ===================================================================================== 20120530113517 2012-05-30 11:05:10 create table user and insert the first one 20120607101608 Pending... alter table user add column password varchar 255 20120607101726 Pending... create table user roles 20120607101846 Pending... create table books 20120607101854 Pending... create table stores
Simply type
./migrate --up=20120607101726 --force ============= UP 20120607101726 ============= --// create table user roles -- Migration SQL that makes the change goes here. some SQL UP SUCCESS
Only the migration 20120607101726 have been played.
./migrate --status ID Applied At description ===================================================================================== 20120530113517 2012-05-30 11:05:10 create table user and insert the first one 20120607101608 Pending... alter table user add column password varchar 255 20120607101726 2012-06-07 11:06:37 create table user roles 20120607101846 Pending... create table books 20120607101854 Pending... create table stores
./migrate --up
============= UP 20120607101608 =============
--// alter table user add column password varchar 255
-- Migration SQL that makes the change goes here.
ALTER TABLE user ADD COLUMN password character varying(255);
UP FAILURE !!!!!
42601
7
ERROR: syntaxe error on or near « user »
LINE 3: ALTER TABLE user ADD COLUMN password character varying(255);
^
============= UP 20120607101608 ============= --// alter table user add column password varchar 255 -- Migration SQL that makes the change goes here. select 1; UP SUCCESS ============= UP 20120607101726 ============= --// create table user roles -- Migration SQL that makes the change goes here. ALTER TABLE user_roles ADD COLUMN test INTEGER; UP FAILURE !!!!! 42P01 7 ERROR: relation « user_roles » does not exists