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
   

   View Slide

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

   View Slide

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

   View Slide

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?
   

   View Slide

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

   View Slide

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
   

   View Slide

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
   

   View Slide

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

   View Slide

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

   View Slide

10. Phinx
    use Phinx\Migration\AbstractMigration;
    class MyNewMigration extends AbstractMigration {
    /**
    * Migrate Up.
    */
    public function up() {
    }
    /**
    * Migrate Down.
    */
    public function down() {
    }
    }
    Generated Migration
    

    View Slide

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();
    }
    

    View Slide

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

    View Slide

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!!
    

    View Slide

14. Phinx
    /**
    * Migrate Up.
    */
    public function up() {
    $table = $this->table('users');
    $table->renameColumn('biography', 'bio');
    }
    Rename a column
    

    View Slide

15. Phinx
    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
    

    View Slide

16. Phinx
    public function change() {
    $table = $this->table('tags');
    $table->addColumn('name', 'string', array('limit' => 30))
    ->update();
    }
    Alter limit
    

    View Slide

17. Phinx
    public function up() {
    $count = $this->execute('DELETE FROM users');
    }
    Some SQL
    

    View Slide

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
    

    View Slide

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

    View Slide

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
    

    View Slide