Phinx

Introduction to migrating your database using Phinx in PHP.
[Updated Nov 8 - 8 new slides]

Transcript

1. Phinx Managing Evolving DB Schemas - Dallas PHP - Nov 2016
2. What is it for? • Database Migrations... • Taking changes from one database and replaying them on another database to keep their structures in sync. • Example: Add a new column on development server, production database now is out of date. • Until we migrate.
3. How does it work? • Generate script templates for you to fill in. • Scripts can: ◦ Attempt to automanage Change()s ◦ Specific Push changes Up() ◦ Specific Roll changes Down() • Execute Phinx, changes happen.
4. Installation $ composer require robmorgan/phinx "require": { "robmorgan/phinx": "^0.4.6", }, $ composer install
5. Initial Setup... • Create a directory called phinx in the project root. • Create a phinx.yml file in your project root. Tell Phinx where to store its scripts, and where to track its current state in the database. paths: migrations: %%PHINX_CONFIG_DIR%%/phinx environments: default_migration_table: phinxlog
6. Initial Setup... Add your database connections to the phinx.yml as well. This will allow you to build migrations against dev database, and then either push finished migrations to production, or let production pull migrations itself. environments: default_migration_table: phinxlog default_database: dev dev: adapter: mysql host: localhost name: DevelDatabase user: DevelUsername pass: DevelPassword port: 3306 prod: adapter: mysql host: db1 name: ProductionDatabase user: ProductionUsername pass: ProductionPassword port: 3306
7. Initial Setup... • If you would like to define a template to use for the auto generated scripting, you can also define that in the phinx.yml file. templates: file: %%PHINX_CONFIG_DIR%%/phinx/Template.txt
8. Creating A Migration Script Generate a new migration script. Pick a name that describes what this database change is and PascalCaseTheThing (it will complain if you don't...) $ phinx create UserTableCreate
9. Blank Default Migration
10. Blank Templated Migration
11. Parts of a Migration Script The first component of a migration script is the Up() method. This method will be executed when Phinx is told to update the database with the latest changes. The second component of a migration script is the Down() method. This method will be executed when Phinx is told to undo changes. The third component of a migration script is an optional mix: Change(). Using the Abstract API that Phinx provides it can attempt to automatically handle upgrades and downgrades without you having to write the specifics. If you define an Up() and Down(), you cannot define a Change().
12. PHINX ABSTRACT API
13. Creating New Tables
14. Table Column Types • biginteger • binary • boolean • date • datetime • decimal • float • integer • string • text • time • timestamp • uuid MySQL Specific • enum • set • blob • json Postgres Specific • smallint • json • jsonb • uuid
15. Table Column Options All column type share the same basic options. • limit / length • default • null • after • comment And the specific types have their specific options. http://docs.phinx.org/en/latest/migra tions.html#valid-column-options $this→Table('ExampleTable') →AddColumn('Title','string',[ 'length' => 64, 'null' => TRUE, 'comment' => 'title of book' ]) →AddColumn('ViewCount','integer',[ 'default' => 0, 'signed' => FALSE, 'comment' => 'how many times read' ]);
16. Auto Autoinc Column • By default, Phinx will automatically add an auto incrementing integer column called "id". • You can either rename it, or disable it, if you elect to. • (I think you should elect to. Its default type kinda sucks.)
17. Disable Auto Autoinc Column $this→Table('ExampleTable',[ 'id' => FALSE ]);
18. Rename Auto Autoinc Column $this→Table('ExampleTable',[ 'id' => 'obj_id' ]);
19. Specify Manual Autoinc Column $this→Table('ExampleTable',[ 'id' => FALSE, 'primary_key' => ['ObjectID'] ]) →AddColumn('ObjectID','biginteger',[ 'identity' => TRUE, 'signed' => FALSE ]);
20. Specify Table Format $this→Table('ExampleTable',[ 'engine' => 'MyISAM' ]); $this→Table('ExampleTable',[ 'engine' => 'InnoDB' ]);
21. Creating New Tables
22. Creating New Tables
23. Creating New Tables
24. Dropping & Restoring Tables
25. Dropping & Restoring Tables
26. Renaming Tables
27. Inserting Data
28. Inserting Data
29. Adding Columns Later
30. Removing Columns Later
31. Changing Columns Later
32. Adding Indexes
33. Removing Indexes
34. Save() vs Create() vs Update() Save() is safe to use any time you are unsure if a command was committed to the database or not. You use it within your Up() and Down() methods. Create() and Update() are shortcut methods which tell Phinx what is happening - you are creating a table, or you are updating a table. You use them within Change() so that Phinx can automatically generate the steps to undo what you just did on rollback.
35. PRO MODE
36. SQL ALL THE THINGS?
37. SQL ALL THE THINGS?
38. SQL via Third Party Library
39. Execute() Vs Query() Execute() Takes SQL. Returns the number of affected rows. Query() Takes SQL. Returns the PDOStatement object of the query you just ran. http://php.net/pdostatement
40. ROLLING MIGRATIONS SOMETIMES UNROLLING
41. Performing Migrations Migrations are iterative. Each one builds on the last. You cannot cherry pick migrations. You can however stop at a certain point in time. Phinx tracks the most recent migration in the `phinxlog` table. This way it knows where it left off, if you are multiple migration scripts behind it will execute only the ones you are missing. $ php vendor/bin/phinx migrate
42. Performing Migrations
43. Rolling Back Bound by the same rules as migrations, they are iterative. To successfully rollback you must have used proper Change() methods or written both your Up() an Down(). Data loss due to a change is data lost. Like if you change an INT size, you may be S.O.L.
44. DATA LOSS IS DATA LOST
45. Rolling Back
46. Rolling Back
47. Rolling Way Back
48. Migrating From Nothing
49. END RESULT
53. ACROSS ENVIRONMENTS
54. Pushing To Production environments: default_migration_table: phinxlog default_database: dev dev: adapter: mysql host: localhost name: phinxdemo user: derp pass: derp port: 3306 prod: adapter: mysql host: notlocalhost name: phinxdemo user: notderp pass: notderp port: 3306
55. Pushing To Production
56. Letting Production Pull phinx.yml and migration scripts both get source pulled onto production. Production defines environment var: PHINX_ENVIRONMENT cat ~/.bash_profile export PHINX_ENVIRONMENT="prod" Then your build system when pulling the latest source will also run phinx migrate locally.
57. Letting Production Pull $ ssh web1 $ php bin/set-site-maintain 1 $ git pull origin master --no-edit $ composer install $ phinx migrate $ php bin/set-site-maintain 0
58. Documentation Phinx has great documentation. It is written in plain speak. http://docs.phinx.org/ If you want to fill the database quickly with test data, be sure to check out the Database Seeding section.
59. The End The Scripts. You can totally github them and run them for yourself to build the database exactly how I had it at the end of tonight. Like, that was the entire point, a portadatabase. https://github.com/bobmagicii/dallasphp-201611-phinx These Slides. Because slides. https://speakerdeck.com/bobmajdakjr/phinx Dallas PHP - Nov 2016 - @bobmagicii