Database Schema Migrations with Doctrine

Slide 1

Slide 1 text

Database Schema Migrations with Doctrine #NomadPHP #lightningTalk Doctrine 2

Slide 2

Slide 2 text

Diego Sapriza Associate Director of Web Applications PHPer ~ DevOps

Slide 3

Slide 3 text

http://AV4TAr.com PHP.meetup.uy DevOps.meetup.uy . .uy @AV4TAr

Slide 4

Slide 4 text

Database Schema Migrations

Slide 5

Slide 5 text

code versioning? and your schema?

Slide 6

Slide 6 text

Classic workflow 1. production dump 2. change code & schema 3. overwrite production

Slide 7

Slide 7 text

¿how about intermediate changes? in the database

Slide 8

Slide 8 text

schema synchronization

Slide 9

Slide 9 text

MySQL Workbench

Slide 10

Slide 10 text

scripting undo changes from latest script? ¿rollback? IF NOT EXISTS … … …

Slide 11

Slide 11 text

… count(devs) > 1

Slide 12

Slide 12 text

centralized dev DB

Slide 13

Slide 13 text

… count(devs) > 1 AND env=‘distributed’

Slide 14

Slide 14 text

different DB versions

Slide 15

Slide 15 text

continuous Deployment code and schema not in sync

Slide 16

Slide 16 text

management of incremental, reversible changes to relational database schemas. http://en.wikipedia.org/wiki/Schema_migration

Slide 17

Slide 17 text

No content

Slide 18

Slide 18 text

create migration ﬁle edit - up() & down() execute migration commit & push

Slide 19

Slide 19 text

pull your code execute migrations continue coding…

Slide 20

Slide 20 text

Doctrine Migrations http://bit.ly/doctrine-migrations

Slide 21

Slide 21 text

download “.phar” https://github.com/doctrine/migrations

Slide 22

Slide 22 text

migrations.yml http://doctrine-migrations.readthedocs.org/en/latest/reference/introduction.html#conﬁguration

Slide 23

Slide 23 text

migrations-db.php http://doctrine-migrations.readthedocs.org/en/latest/reference/introduction.html#php-binary-phar

Slide 24

Slide 24 text

No content

Slide 25

Slide 25 text

$ php doctrine-migrations.phar --configuration="configuration.yml"

Slide 26

Slide 26 text

new migration migrations:generate

Slide 27

Slide 27 text

No content

Slide 28

Slide 28 text

No content

Slide 29

Slide 29 text

No content

Slide 30

Slide 30 text

migration status migrations:status

Slide 31

Slide 31 text

test a migration migrations:migrate —dry-run

Slide 32

Slide 32 text

execute migration migrations:migrate

Slide 33

Slide 33 text

migrations:migrate

Slide 34

Slide 34 text

how doctrine knows?

Slide 35

Slide 35 text

state

Slide 36

Slide 36 text

state

Slide 37

Slide 37 text

0 1 2 3 4 rollback migrations:migrate #version

Slide 38

Slide 38 text

rollback migrations:migrate #version

Slide 39

Slide 39 text

Doctrine Migrations http://bit.ly/doctrine-migrations

Slide 40

Slide 40 text

Tips

Slide 41

Slide 41 text

avoid complex rollbacks … its easier to restore a backup

Slide 42

Slide 42 text

“rollbacks does not exist, we always go forward” - @nachexnachex

Slide 43

Slide 43 text

0 1 2 3 4 rollback migrations:migrate 0

Slide 44

Slide 44 text

decouple code deploys from database migrations from to deploy & migrate deploy 1 deploy 2 migrate https://speakerdeck.com/nduthoit/the-path-to-smoother-database-migrations

Slide 45

Slide 45 text

deploy 1 deploy 2 migrate code works before and after migration code depends on migration being completed https://speakerdeck.com/nduthoit/the-path-to-smoother-database-migrations decouple code deploys from database migrations

Slide 46

Slide 46 text

+ tips migration-agnostic code add model - don’t use it- migrate schema - use model.

Slide 47

Slide 47 text

+ tips • nullable FK -> migrate data -> migrate code • test migrations in production backups

Slide 48

Slide 48 text

backup your data before schema migration ;)

Slide 49

Slide 49 text

@AV4TAr http://AV4TAr.com irc freenode #AV4TAr https://joind.in/13400

Slide 50

Slide 50 text

Resources • http://en.wikipedia.org/wiki/Schema_migration • http://phinx.org • https://github.com/doctrine/migrations • https://speakerdeck.com/austinsmorris/doctrine-migrations • https://speakerdeck.com/nduthoit/the-path-to-smoother- database-migrations • https://speakerdeck.com/trumpi/database-migrations • https://speakerdeck.com/austinsmorris/doctrine-migrations