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

PHP Migrations with Phinx

Andy Gale
April 08, 2015

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.

Andy Gale

April 08, 2015
Tweet

More Decks by Andy Gale

Other Decks in Technology

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