PHP Migrations with Phinx

Automated database migrations are extremely useful when doing Continuous Integration and Delivery. Phinx is a simple PHP standalone tool for writing database migrations for existing or new applications. We like it.

Transcript

1. PHP Migrations with Phinx Andy Gale
2. Andy Gale @andygale on Twitter Bristol Rovers fan #UTG Bristol DevOps Organiser What do you do? Owner and DevOps Consultant at Hello Future
3. Hello Future • @hellofutur3 on Twitter • DevOps, Continuous Delivery, Chef and cloud automation consultancy • Web application development What do we do?
4. 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?
5. Phinx • PHP framework independent • Simple to install (composer!) • Easy to use • Difficult to spell Handy PHP database migration tool
6. 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
7. 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
8. Phinx Handy PHP database migration tool Configuration in a YAML file phinx.yml Store this outside the document root
9. Phinx Handy PHP database migration tool Create a migration $ phinx create MyNewMigration
10. Phinx <?php use Phinx\Migration\AbstractMigration; class MyNewMigration extends AbstractMigration { /** * Migrate Up. */ public function up() { } /** * Migrate Down. */ public function down() { } } Generated Migration
11. 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(); }
12. Phinx /** * Migrate Down. */ public function down() { $this->dropTable('users'); } Obviously you should consider if this is a good idea!
13. 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!!
14. Phinx /** * Migrate Up. */ <?php public function up() { $table = $this->table('users'); $table->renameColumn('biography', 'bio'); } Rename a column
15. Phinx <?php public function up() { $table = $this->table('users'); $table->rename('legacy_users'); } public function down() { $table = $this->table('legacy_users'); $table->rename('users'); } Rename a table
16. Phinx <?php public function change() { $table = $this->table('tags'); $table->addColumn('name', 'string', array('limit' => 30)) ->update(); } Alter limit
17. Phinx <?php public function up() { $count = $this->execute('DELETE FROM users'); } Some SQL
18. Phinx Handy PHP database migration tool Run migrations $ phinx migrate Rollback migrations $ phinx rollback Get status of which migrations have been run $ phinx status
19. Phinx Handy PHP database migration tool Funky web app $ php -S localhost:8000 vendor/robmorgan/ phinx/app/web.php
20. 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