Phinx - Speaker Deck

Slide 1

Slide 1 text

Phinx Managing Evolving DB Schemas - Dallas PHP - Nov 2016

Slide 2

Slide 2 text

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.

Slide 3

Slide 3 text

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.

Slide 4

Slide 4 text

Installation $ composer require robmorgan/phinx "require": { "robmorgan/phinx": "^0.4.6", }, $ composer install

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

Blank Default Migration

Slide 10

Slide 10 text

Blank Templated Migration

Slide 11

Slide 11 text

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().

Slide 12

Slide 12 text

PHINX ABSTRACT API

Slide 13

Slide 13 text

Creating New Tables

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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' ]);

Slide 16

Slide 16 text

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.)

Slide 17

Slide 17 text

Disable Auto Autoinc Column $this→Table('ExampleTable',[ 'id' => FALSE ]);

Slide 18

Slide 18 text

Rename Auto Autoinc Column $this→Table('ExampleTable',[ 'id' => 'obj_id' ]);

Slide 19

Slide 19 text

Specify Manual Autoinc Column $this→Table('ExampleTable',[ 'id' => FALSE, 'primary_key' => ['ObjectID'] ]) →AddColumn('ObjectID','biginteger',[ 'identity' => TRUE, 'signed' => FALSE ]);

Slide 20

Slide 20 text

Specify Table Format $this→Table('ExampleTable',[ 'engine' => 'MyISAM' ]); $this→Table('ExampleTable',[ 'engine' => 'InnoDB' ]);

Slide 21

Slide 21 text

Creating New Tables

Slide 22

Slide 22 text

Creating New Tables

Slide 23

Slide 23 text

Creating New Tables

Slide 24

Slide 24 text

Dropping & Restoring Tables

Slide 25

Slide 25 text

Dropping & Restoring Tables

Slide 26

Slide 26 text

Renaming Tables

Slide 27

Slide 27 text

Inserting Data

Slide 28

Slide 28 text

Inserting Data

Slide 29

Slide 29 text

Adding Columns Later

Slide 30

Slide 30 text

Removing Columns Later

Slide 31

Slide 31 text

Changing Columns Later

Slide 32

Slide 32 text

Adding Indexes

Slide 33

Slide 33 text

Removing Indexes

Slide 34

Slide 34 text

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.

Slide 35

Slide 35 text

PRO MODE

Slide 36

Slide 36 text

SQL ALL THE THINGS?

Slide 37

Slide 37 text

SQL ALL THE THINGS?

Slide 38

Slide 38 text

SQL via Third Party Library

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

ROLLING MIGRATIONS SOMETIMES UNROLLING

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

Performing Migrations

Slide 43

Slide 43 text

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.

Slide 44

Slide 44 text

DATA LOSS IS DATA LOST

Slide 45

Slide 45 text

Rolling Back

Slide 46

Slide 46 text

Rolling Back

Slide 47

Slide 47 text

Rolling Way Back

Slide 48

Slide 48 text

Migrating From Nothing

Slide 49

Slide 49 text

END RESULT

Slide 50

Slide 50 text

No content

Slide 51

Slide 51 text

No content

Slide 52

Slide 52 text

No content

Slide 53

Slide 53 text

ACROSS ENVIRONMENTS

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

Pushing To Production

Slide 56

Slide 56 text

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.

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

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.

Slide 59

Slide 59 text

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