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

Automating DB changes with Phinx

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.

Daniel Holmes

December 03, 2014
Tweet

More Decks by Daniel Holmes

Other Decks in Technology

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='[email protected]: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='[email protected]: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
    [email protected]
    More Info about Phinx:
    phinx.org |docs.phinx.org

    View Slide