$30 off During Our Annual Pro Sale. View Details »

Automating DB changes with Phinx

Daniel Holmes
December 03, 2014

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.

Daniel Holmes

December 03, 2014
Tweet

More Decks by Daniel Holmes

Other Decks in Technology

Transcript

  1. Who is this talk for? • Web Developers who use

    Databases • PHP Beginners • Familiarity with Class / Object Basics
  2. 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
  3. CI / Continuous Integration • Jenkins / Hudson • PHP

    CI • Travis CI • Others? Create and deploy stable builds of your software automatically!
  4. 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?
  5. 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
  6. 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.
  7. 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
  8. 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()
  9. 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() { } }
  10. 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); } } }
  11. 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(); } }
  12. 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(); } }
  13. 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(); } }
  14. 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()
  15. 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
  16. 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