$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

    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