Automating DB changes with Phinx December 3rd, 2014 Kansas City PHP User Group 

Who is this talk for? ● Web Developers who use Databases ● PHP Beginners ● Familiarity with Class / Object Basics 

Overview ● Keeping it Simple ● CI / Automated Deployment ● How do you deploy database changes? ● Migrations ● Using Phinx with any PHP application ● Up / Down or Change? ● Scripting your changes 

CI / Continuous Integration ● Jenkins / Hudson ● PHP CI ● Travis CI ● Others? Create and deploy stable builds of your software automatically! 

What about “not-so-continuous”? Maybe you don’t need ● CI Server ● Unit tests to run ● QA tools / reports ● Continuous Deployments But you still want ● Stress free upgrades ● DB changes in Repo ● What DB changes need to happen for this release? 

How do you update prod? 

Automation is key - Simple Deploy #!/bin/bash build_id=`date +'%Y-%m-%d_%I%M%S'` build=~/_builds/build_${build_id}.tar repo='[email protected]:myorg/repo.git release' git archive --format=tar --remote=$repo > gzip $build cd ~/site tar -xvzf $build # Remove stuff we don’t want in prod (better to do before gzip) rm -rf puphpet Vagrantfile build.xml # Make sure composer changes are installed ~/bin/composer.phar install --no-dev 

The One Big Schema File Each “change” is separated with comments so you can kind of tell what to copy/paste into phpMyAdmin May not always know where you last left off in prod Easy to miss updates if you aren’t careful Of course, you know you missed something because site is down. 

The “changes” directory 

If Automation is key - What’s missing? #!/bin/bash build_id=`date +'%Y-%m-%d_%I%M%S'` build=~/_builds/build_${build_id}.tar repo='[email protected]:myorg/repo.git release' git archive --format=tar --remote=$repo > gzip $build cd ~/site tar -xvzf $build # Remove stuff we don’t want in prod (better to do before gzip) rm -rf puphpet Vagrantfile build.xml # Make sure composer changes are installed ~/bin/composer.phar install --no-dev # Make sure database is up to date ~site/vendor/bin/phinx migrate -e production 

What is Phinx? Environments Single config knows about your prod, dev and test databases Migrations Simple scripts that define a set of changes to apply or rollback Status Phinx keeps a table to know what migrations need to be installed string text integer biginteger float decimal datetime timestamp time date binary boolean createTable() renameTable() addColumn() renameColumn() addIndex() addForeignKey() rename() renameColumn() removeIndex() hasForeignKey() dropForeignKey() 

A simple addition CREATE TABLE IF NOT EXISTS `marketing_url` ( `id` int(11) NOT NULL AUTO_INCREMENT, `url` varchar(512) NOT NULL, `title` varchar(512) DEFAULT NULL, `created_date` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=100; table('marketing_url'); $table->addColumn('url','string'); $table->addColumn('title','string); $table->addColumn('created_date','datetime'); $table->create(); } public function down() { } } 

A simple addition CREATE TABLE IF NOT EXISTS `marketing_url` ( `id` int(11) NOT NULL AUTO_INCREMENT, `url` varchar(512) NOT NULL, `title` varchar(512) DEFAULT NULL, `created_date` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=100; table('marketing_url'); /* columns removed for example */ $table->create(); } public function down() { if($this->hasTable('users')) { $table->dropTable('marketing_url); } } } 

A simple addition, made simpler CREATE TABLE IF NOT EXISTS `marketing_url` ( `id` int(11) NOT NULL AUTO_INCREMENT, `url` varchar(512) NOT NULL, `title` varchar(512) DEFAULT NULL, `created_date` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=100; table('marketing_url'); $table ->addColumn('url','string') ->addColumn('title','string') ->addColumn('created_date','datetime'); $table->create(); } } 

Adding Columns ALTER TABLE marketing_url ADD COLUMN created_by int(11) AFTER title; table('marketing_url'); $table->addColumn(created_by, 'int', ['after' => title] )->update(); } } 

Adding Columns, with limit ALTER TABLE marketing_url ADD COLUMN created_by int(11) AFTER title; table('marketing_url'); $table->addColumn(created_by, 'int', ['after' => title, 'limit' => 30] )->update(); } } 

What is Phinx? Environments Single config knows about your prod, dev and test databases Migrations Simple scripts that define a set of changes to apply or rollback Status Phinx keeps a table to know what migrations need to be installed string text integer biginteger float decimal datetime timestamp time date binary boolean createTable() renameTable() addColumn() renameColumn() addIndex() addForeignKey() rename() renameColumn() removeIndex() hasForeignKey() dropForeignKey() 

It's super hard to install, right? Just add it to your composer.json file "require": { "robmorgan/phinx": "*" } update composer $ php composer.phar install --no-dev Make a "Migrations" directory off the root of your project directory $ mkdir migrations "Init" Phinx to get started - creates phinx.yaml in current directory $ php vendor/bin/phinx init If you want to relocate your phinx.yaml file or the migrations, you can do that too! export PHINX_CONFIG_DIR='/var/www/mysite/db/migrations/' More information at http://docs.phinx.org/en/latest/configuration.html 

Show me the yaml! Wondering what the yaml file looks like? environments: default_migration_table: phinxlog default_database: development production: adapter: mysql host: localhost name: mysite_prod user: root pass: '' port: 3306 charset: utf8 development: adapter: sqlite name: ./files/db/sqllite.db testing: adapter: sqlite memory: true 

What does running Phinx look like? Creating a new migration 

What does running Phinx look like? Running a migration Up 

What does running Phinx look like? Checking status 

What does running Phinx look like? Backing the last one out 

Go forth and Phinx! Questions? Comments? @dan_holmes [email protected] More Info about Phinx: phinx.org |docs.phinx.org