Automating DB changes with Phinx

We all have our ways to deploy our software--some automatic, some manual. But do you still get that nervous feeling when it's time to upgrade production? It may be time to script your deployment from end to end. Your app probably isn't just source code though. If your change includes database updates, do you have what you need to update it automatically? You do now! It's time to meet Phinx.

Transcript

1. Automating DB changes
   with Phinx
   December 3rd, 2014
   Kansas City PHP User Group
   

   View Slide

2. Who is this talk for?
   ● Web Developers
   who use Databases
   ● PHP Beginners
   ● Familiarity with
   Class / Object Basics
   

   View Slide

3. Overview
   ● Keeping it Simple
   ● CI / Automated Deployment
   ● How do you deploy database changes?
   ● Migrations
   ● Using Phinx with any PHP application
   ● Up / Down or Change?
   ● Scripting your changes
   

   View Slide

4. CI / Continuous Integration
   ● Jenkins / Hudson
   ● PHP CI
   ● Travis CI
   ● Others?
   Create and deploy
   stable builds of your
   software automatically!
   

   View Slide

5. What about “not-so-continuous”?
   Maybe you don’t need
   ● CI Server
   ● Unit tests to run
   ● QA tools / reports
   ● Continuous
   Deployments
   But you still want
   ● Stress free upgrades
   ● DB changes in Repo
   ● What DB changes
   need to happen for
   this release?
   

   View Slide

6. How do you
   update prod?
   

   View Slide

7. Automation is key - Simple Deploy
   #!/bin/bash
   build_id=`date +'%Y-%m-%d_%I%M%S'`
   build=~/_builds/build_${build_id}.tar
   repo='git@bitbucket.org:myorg/repo.git release'
   git archive --format=tar --remote=$repo > gzip $build
   cd ~/site
   tar -xvzf $build
   # Remove stuff we don’t want in prod (better to do before gzip)
   rm -rf puphpet Vagrantfile build.xml
   # Make sure composer changes are installed
   ~/bin/composer.phar install --no-dev
   

   View Slide

8. The One Big Schema File
   Each “change” is
   separated with comments
   so you can kind of tell
   what to copy/paste into
   phpMyAdmin
   May not always know
   where you last left off in
   prod
   Easy to miss updates if
   you aren’t careful
   Of course, you know you
   missed something
   because site is down.
   

   View Slide

9. The “changes” directory
   

   View Slide

10. If Automation is key - What’s missing?
    #!/bin/bash
    build_id=`date +'%Y-%m-%d_%I%M%S'`
    build=~/_builds/build_${build_id}.tar
    repo='git@bitbucket.org:myorg/repo.git release'
    git archive --format=tar --remote=$repo > gzip $build
    cd ~/site
    tar -xvzf $build
    # Remove stuff we don’t want in prod (better to do before gzip)
    rm -rf puphpet Vagrantfile build.xml
    # Make sure composer changes are installed
    ~/bin/composer.phar install --no-dev
    # Make sure database is up to date
    ~site/vendor/bin/phinx migrate -e production
    

    View Slide

11. What is Phinx?
    Environments
    Single config knows about your prod,
    dev and test databases
    Migrations
    Simple scripts that define a set of
    changes to apply or rollback
    Status
    Phinx keeps a table to know what
    migrations need to be installed
    string
    text
    integer
    biginteger
    float
    decimal
    datetime
    timestamp
    time
    date
    binary
    boolean
    createTable()
    renameTable()
    addColumn()
    renameColumn()
    addIndex()
    addForeignKey()
    rename()
    renameColumn()
    removeIndex()
    hasForeignKey()
    dropForeignKey()
    

    View Slide

12. A simple addition
    CREATE TABLE IF NOT EXISTS
    `marketing_url` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `url` varchar(512) NOT NULL,
    `title` varchar(512) DEFAULT NULL,
    `created_date` datetime NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM
    DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
    use Phinx\Migration\AbstractMigration;
    class MyNewMigration extends AbstractMigration
    {
    public function up()
    {
    $table = $this->table('marketing_url');
    $table->addColumn('url','string');
    $table->addColumn('title','string);
    $table->addColumn('created_date','datetime');
    $table->create();
    }
    public function down()
    {
    }
    }
    

    View Slide

13. A simple addition
    CREATE TABLE IF NOT EXISTS
    `marketing_url` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `url` varchar(512) NOT NULL,
    `title` varchar(512) DEFAULT NULL,
    `created_date` datetime NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM
    DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
    use Phinx\Migration\AbstractMigration;
    class MyNewMigration extends AbstractMigration
    {
    public function up()
    {
    $table = $this->table('marketing_url');
    /* columns removed for example */
    $table->create();
    }
    public function down()
    {
    if($this->hasTable('users')) {
    $table->dropTable('marketing_url);
    }
    }
    }
    

    View Slide

14. A simple addition, made simpler
    CREATE TABLE IF NOT EXISTS
    `marketing_url` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `url` varchar(512) NOT NULL,
    `title` varchar(512) DEFAULT NULL,
    `created_date` datetime NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM
    DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
    use Phinx\Migration\AbstractMigration;
    class MyNewMigration extends AbstractMigration
    {
    public function change()
    {
    $table = $this->table('marketing_url');
    $table
    ->addColumn('url','string')
    ->addColumn('title','string')
    ->addColumn('created_date','datetime');
    $table->create();
    }
    }
    

    View Slide

15. Adding Columns
    ALTER TABLE marketing_url
    ADD COLUMN created_by int(11) AFTER title;
    use Phinx\Migration\AbstractMigration;
    class MyNewMigration extends AbstractMigration
    {
    public function up()
    {
    $table = $this->table('marketing_url');
    $table->addColumn(created_by, 'int',
    ['after' => title]
    )->update();
    }
    }
    

    View Slide

16. Adding Columns, with limit
    ALTER TABLE marketing_url
    ADD COLUMN created_by int(11) AFTER title;
    use Phinx\Migration\AbstractMigration;
    class MyNewMigration extends AbstractMigration
    {
    public function up()
    {
    $table = $this->table('marketing_url');
    $table->addColumn(created_by, 'int',
    ['after' => title, 'limit' => 30]
    )->update();
    }
    }
    

    View Slide

17. What is Phinx?
    Environments
    Single config knows about your prod,
    dev and test databases
    Migrations
    Simple scripts that define a set of
    changes to apply or rollback
    Status
    Phinx keeps a table to know what
    migrations need to be installed
    string
    text
    integer
    biginteger
    float
    decimal
    datetime
    timestamp
    time
    date
    binary
    boolean
    createTable()
    renameTable()
    addColumn()
    renameColumn()
    addIndex()
    addForeignKey()
    rename()
    renameColumn()
    removeIndex()
    hasForeignKey()
    dropForeignKey()
    

    View Slide

18. It's super hard to install, right?
    Just add it to your composer.json file
    "require": {
    "robmorgan/phinx": "*"
    }
    update composer
    $ php composer.phar install --no-dev
    Make a "Migrations" directory off the root of your project directory
    $ mkdir migrations
    "Init" Phinx to get started - creates phinx.yaml in current directory
    $ php vendor/bin/phinx init
    If you want to relocate your phinx.yaml file or the migrations, you can do that too!
    export PHINX_CONFIG_DIR='/var/www/mysite/db/migrations/'
    More information at http://docs.phinx.org/en/latest/configuration.html
    

    View Slide

19. Show me the yaml!
    Wondering what the yaml file looks like?
    environments:
    default_migration_table: phinxlog
    default_database: development
    production:
    adapter: mysql
    host: localhost
    name: mysite_prod
    user: root
    pass: ''
    port: 3306
    charset: utf8
    development:
    adapter: sqlite
    name: ./files/db/sqllite.db
    testing:
    adapter: sqlite
    memory: true
    

    View Slide

20. What does running Phinx look like?
    Creating a new migration
    

    View Slide

21. What does running Phinx look like?
    Running a migration Up
    

    View Slide

22. What does running Phinx look like?
    Checking status
    

    View Slide

23. What does running Phinx look like?
    Backing the last one out
    

    View Slide

24. Go forth and Phinx!
    Questions? Comments?
    @dan_holmes
    daniel.holmes@gmail.com
    More Info about Phinx:
    phinx.org |docs.phinx.org
    

    View Slide