Evolutionary Database Design for PHP Apps with Phinx

We’ve all heard the talk on Agile and Lean, and how we should develop our applications incrementally, but even if you’ve not dabbled into that yet, “change” is a truism in software, and soon or later you’re going to need to change something -- to cater for a new need or to fix a bug.

For the most part, applying code changes incrementally is known and handled by most people graciously, yet there’s one -- dreadful - component that is usually a little harder to manage, the database.

Typically, people will be using some sort of ORM such as Hibernate to interact with the database and define their schema, and during development, any change to their “models” implies resetting the database as to apply the new changes. That works fine for development, but once the app is in production they can no longer just “reset”. Which is when they finally resort to writing SQL by hand and applying it directly onto the database. There’s nothing wrong with that, but it could be better for now they’ve got a few more problems:

- How can they ensure that the schema is in sync with the code?
- How can they share the schema changes with their colleagues?
- How can they ensure that they can go back to a safe state if some problem happens during their manual schema updates run? “Taking a backup before, doing the change, and rolling back if a problem arises perhaps?”
- If they use branches for working on different tasks, how can they avoid the “oh, I was working on feature A and I changed the schema, so now that I’m on feature B the schema is broken”.

Enter database schema migrations. Migrations are a mechanism that allows you to solve all of the issues above. In this session, we’ll know exactly what they’re and how we can use them in the context of PHP applications.

I’d be remiss not to say that most PHP frameworks today already come with a database migration tool which you can use to construct and make changes to your database and easily share them through your version control system of choice. Though that is true, not all software is greenfield, a lot of it is brownfield. Thus, learning Phinx may still useful.

This talk is about how we can design our databases in an evolutionary fashion by using database schema migrations, more specifically in the PHP language using the [Phinx](https://phinx.org) library.

Transcript

1. Evolutionary Database Design for PHP Apps with Phinx Introduction
2. Yo! • @dareenzo everywhere • Holy wars • tea not coffee • evil (vim + emacs) • spaces not tabs (!Makefile)
3. Disclaimer • Opinions are my own. Not of my employers / customers / family & friends. • I didn’t (won’t) violate any dark-secret pacts I have with my employers / customers / family & friends today • Contains highly-contagious traces of controversial ancient wisdom • YMMV
4. Why do we need migrations? Migrations are about evolutionary database design that rely on applying continuous integration and automated refactoring to database development, together with a close collaboration between DBAs and application developers. The techniques work in both pre-production and released systems, in green field projects as well as legacy systems. ... We began around 2000 with a project whose database ended up with around 600 tables. As we worked on this project we developed techniques that allowed to change the schema and migrate existing data comfortably. This allowed our database to be completely flexible and evolvable. — Pramod Sadalage & Martin Fowler, Evolutionary Database Design
5. What • schema migration (also database migration, database change management[ ) refers to the management of incremental, reversible changes to relational database schemas. A schema migration is performed on a database whenever it is necessary to update or revert that database's schema to some newer or older version. • Migrations are performed programmatically by using a schema migration tool. When invoked with a specified desired schema version, the tool automates the successive application or reversal of an appropriate sequence of schema changes until it is brought to the desired state
6. Common features of Migrations • Database Versioning • Schema Versioning • Downgrading • Persistence Ignorance • Repeatability • Consistency • Idempotence (deterministic)
7. Advantages • Automation • Allows automating deployment • Changes are more reliable, repeatable, and reversible • Changes to the code are kept together and in sync with changes to the database
8. PHP Tools for migrations • Laravel migrations • Doctrine migrations • Phinx
9. Why Phinx • Not tied to any framework/library • Super easy to install and setup • Write migrations in pure SQL or using phinx’s DB helper • Well documented (https://phinx.org) • Easy to integrate with your deploy tool • FOSS (https://github.com/cakephp/phinx)
10. What is Phinx? Phinx is a standalone command-line tool for managing database migrations in PHP
11. Rotten Tomatoes clone A worked example
14. The “MVP” Schema * Purposefully limited for MVP
15. The Project Structure
16. Installing composer require —dev robmorgan/phinx
17. Setup
18. Migrating & Rolling Back 1. create migration (phinx create migration <Name>) 2. edit migration file 3. do a dry-run (phinx migrate -e env —dry-run) 4. migrate (phinx migrate -e env) 5. check status (phinx status -e env) 1. check if tables exists in fact and that it has correct schema 2. check on phinxlog table and structure 6. rollback (phinx rollback -e env) 7. check status (phinx status -e env)
19. 1. create migration
20. 2. edit migration file
21. 2. edit migration file (cont.)
22. 3. do dry-run
23. 4. migrate
24. 5. check status
25. 5.1 check if tables exist
26. 5.2 check on phinxlog table and structure
27. 6. rollback
28. 6. status after rollback
29. change() vs. up() and down() • change() relies on using the DB helper to migrate forward in order to know how to rollback. If you’re using change, then make sure to use DB helper • up() and down() are more “verbose" forms of change() but are more flexible since we can use raw SQL — which is not supported by the DB helper • Using the DB Helper makes the code more portable. But there is no support for views, triggers, functions, and procedures, so SQL is your only way out
30. Seeding 1. Create seed (phinx seed:create Name) 2. Edit seed file 3. Run seed (phinx seed:run -e env or phinx seed:run -e env —s Name) 4. Confirm data
31. 1. create seed
32. 1. create seed (cont)
33. 2. edit seed file
34. 3. run seed
35. 4. Confirm data
36. Notes/Tips • RTFM • Don’t commit phinx.yml to source-code control. But commit the migration and seed files • Phinx adds an auto incrementing id column to every table, unless we specify otherwise • Do dry runs and increase verbosity (—dry-run and —vvv) for debugging • When using the DB helper remember to call save() or your changes won’t be applied • If you get lost help is available via phinx list, phinx help <command>, https:// stackoverflow.com/questions/tagged/phinx and https://github.com/cakephp/phinx/ issues • If using multiple DBs, e.g emprego’s filter and portal DB, you can use different config files for each DB and run then phinx with the -c flag
37. Q&A
38. References • Evolutionary Database Design - https:// martinfowler.com/articles/evodb.html • Phinx – the Migration Library You Never Knew You Needed - https://www.sitepoint.com/phinx-the- migration-library-you-never-knew-you-needed/ • Database Migrations in PHP With Phinx - http://wern- ancheta.com/blog/2015/09/20/database-migrations-in- php-with-phinx/
39. That’s all folks! Follow @dareenzo (anywhere) for more… Slides are available at http://dareenzo.github.io/talks/