PHP Migrations with Phinx Andy Gale 

Andy Gale @andygale on Twitter Bristol Rovers fan #UTG Bristol DevOps Organiser What do you do? Owner and DevOps Consultant at Hello Future 

Hello Future • @hellofutur3 on Twitter • DevOps, Continuous Delivery, Chef and cloud automation consultancy • Web application development What do we do? 

Migrations • Changes to databases • Store them in your SCM system (Git, SVN, Mercurial, CVS…) • Handy for Continuous Integration, Continuous Delivery and Continuous Deployment What do you mean by that? 

Phinx • PHP framework independent • Simple to install (composer!) • Easy to use • Difficult to spell Handy PHP database migration tool 

Phinx • Portable amongst different database vendors (MySQL, PostgreSQL, SQLite, SQL Server) • Good for existing projects with a custom framework where no existing migration tools exist • Also good for new projects Handy PHP database migration tool 

Phinx Handy PHP database migration tool Get up and running $ php composer.phar require robmorgan/phinx $ php composer.phar install --no-dev $ mkdir migrations $ php vendor/bin/phinx init 

Phinx Handy PHP database migration tool Configuration in a YAML file phinx.yml Store this outside the document root 

Phinx Handy PHP database migration tool Create a migration $ phinx create MyNewMigration 

Phinx

Phinx /** * Migrate Up. */ public function up() { $users = $this->table('users'); $users->addColumn('username', ‘string’, array('limit' => 20)) ->addColumn('password', ‘string’, array('limit' => 40)) ->addColumn('salt', 'string', array('limit' => 40)) ->addColumn('email', ‘string’, array('limit' => 100)) ->addIndex(array('username', ‘email’), array('unique' => true)) ->save(); } 

Phinx /** * Migrate Down. */ public function down() { $this->dropTable('users'); } Obviously you should consider if this is a good idea! 

Phinx /** * Change. */ public function change() { $users = $this->table('users'); $users->addColumn('username', ‘string’, array('limit' => 20)) ->addColumn('password', ‘string’, array('limit' => 40)) ->addColumn('salt', 'string', array('limit' => 40)) ->addColumn('email', ‘string’, array('limit' => 100)) ->addIndex(array('username', ‘email’), array('unique' => true)) ->create(); } Change will automatically create the down migration for you, again this may not be what you want!! 

Phinx /** * Migrate Up. */ table('users'); $table->renameColumn('biography', 'bio'); } Rename a column 

Phinx table('users'); $table->rename('legacy_users'); } public function down() { $table = $this->table('legacy_users'); $table->rename('users'); } Rename a table 

Phinx table('tags'); $table->addColumn('name', 'string', array('limit' => 30)) ->update(); } Alter limit 

Phinx execute('DELETE FROM users'); } Some SQL 

Phinx Handy PHP database migration tool Run migrations $ phinx migrate Rollback migrations $ phinx rollback Get status of which migrations have been run $ phinx status 

Phinx Handy PHP database migration tool Funky web app $ php -S localhost:8000 vendor/robmorgan/ phinx/app/web.php 

Phinx • Use on existing projects • Good for CD with projects as you can do migrations as part of your deployment scripts (think about the order you do things) • Simple to use, simple solution to most problems Recap