Automating DB changes with Phinx

We all have our ways to deploy our software--some automatic, some manual. But do you still get that nervous feeling when it's time to upgrade production? It may be time to script your deployment from end to end. Your app probably isn't just source code though. If your change includes database updates, do you have what you need to update it automatically? You do now! It's time to meet Phinx.

Transcript

1. Automating DB changes with Phinx December 3rd, 2014 Kansas City PHP User Group
2. Who is this talk for? • Web Developers who use Databases • PHP Beginners • Familiarity with Class / Object Basics
3. 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
4. CI / Continuous Integration • Jenkins / Hudson • PHP CI • Travis CI • Others? Create and deploy stable builds of your software automatically!
5. 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?
6. How do you update prod?
7. Automation is key - Simple Deploy #!/bin/bash build_id=`date +'%Y-%m-%d_%I%M%S'` build=~/_builds/build_${build_id}.tar repo='git@bitbucket.org: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
8. 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.
9. The “changes” directory
10. 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='git@bitbucket.org: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
11. 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()
12. 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; <?php use Phinx\Migration\AbstractMigration; class MyNewMigration extends AbstractMigration { public function up() { $table = $this->table('marketing_url'); $table->addColumn('url','string'); $table->addColumn('title','string); $table->addColumn('created_date','datetime'); $table->create(); } public function down() { } }
13. 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; <?php use Phinx\Migration\AbstractMigration; class MyNewMigration extends AbstractMigration { public function up() { $table = $this->table('marketing_url'); /* columns removed for example */ $table->create(); } public function down() { if($this->hasTable('users')) { $table->dropTable('marketing_url); } } }
14. 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; <?php use Phinx\Migration\AbstractMigration; class MyNewMigration extends AbstractMigration { public function change() { $table = $this->table('marketing_url'); $table ->addColumn('url','string') ->addColumn('title','string') ->addColumn('created_date','datetime'); $table->create(); } }
15. Adding Columns ALTER TABLE marketing_url ADD COLUMN created_by int(11) AFTER title; <?php use Phinx\Migration\AbstractMigration; class MyNewMigration extends AbstractMigration { public function up() { $table = $this->table('marketing_url'); $table->addColumn(created_by, 'int', ['after' => title] )->update(); } }
16. Adding Columns, with limit ALTER TABLE marketing_url ADD COLUMN created_by int(11) AFTER title; <?php use Phinx\Migration\AbstractMigration; class MyNewMigration extends AbstractMigration { public function up() { $table = $this->table('marketing_url'); $table->addColumn(created_by, 'int', ['after' => title, 'limit' => 30] )->update(); } }
17. 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()
18. 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
19. 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
20. What does running Phinx look like? Creating a new migration
21. What does running Phinx look like? Running a migration Up
22. What does running Phinx look like? Checking status
23. What does running Phinx look like? Backing the last one out
24. Go forth and Phinx! Questions? Comments? @dan_holmes daniel.holmes@gmail.com More Info about Phinx: phinx.org |docs.phinx.org