Writing reliable migrations with Nextras Migrations

Learn how to write reliable database migrations which you can safely run on production and how Nextras Migrations can help you do so.

Transcript

1. Jan Tvrdík
2. Q1 Q2 Q3 Δ1 Δ2 = states Δ = transitions
3. SNAPSHOTS Q1 Q2 Q3 Δ1 Δ2 Git stores snapshots…
4. DELTAS Q1 Q2 Q3 Δ1 Δ2 …but in migrations we store deltas. If the situation looked as on the diagram above we could actually use snapshots for migrations as well.
5. Δ1,0 Q1 Q2* Q1,1 Q1,n … Q1,2 Δ1 Δ1,1 Δ1,2 But it looks more like this. The database state is modified not only by migrations but by application as well. = database state , = database state after one or more application delta Δ = migration delta (written by programmer) Δ, = application delta (caused by application itself)
6. How to write Δ1 ? UNKNOWN STATE Δ1,0 Q1 Q2* Q1,1 Q1,n … Q1,2 Δ1 Δ1,1 Δ1,2 KNOWN STATE If Δ1, can change anything then we know nothing about 1,
7. How to write Δ1 ? IMPOSSIBLE UNKNOWN STATE Δ1,0 Q1 Q2* Q1,1 Q1,n … Q1,2 Δ1 Δ1,1 Δ1,2 KNOWN STATE Without knowing anything about initial state there is nothing you can safely change.
9. 1, must become partially known UNKNOWN PARTIALLY KNOWN STATE Δ1,0 Q1 Q2* Q1,1 Q1,n … Q1,2 Δ1 Δ1,1 Δ1,2 KNOWN STATE
10. 1, must become partially known restriction of operations permitted in Δ1, implies set of safe operations in Δ1 e.g. allowing only DML (insert, update…) in Δ1, makes DDL (alter, drop…) in Δ1 safe UNKNOWN PARTIALLY KNOWN STATE Δ1,0 Q1 Q2* Q1,1 Q1,n … Q1,2 Δ1 Δ1,1 Δ1,2 KNOWN STATE
11. ∀ , ∗ Δ +1 ∗ Δ is safe if for all possible combinations of application deltas the transition is always correct
12. must work Δ +1 initial state must be , ∗ all of Δ1 to Δ−1 and none other Δ must have been safely executed ∀: Δ must contain only operations safe regarding to our knowledge of , ∗ which depends on restriction placed on Δ, , ∗ Δ +1 ∗
13. app DML only migrations DDL only simple but unusable you need DML to perform some DDL
14. app DML only migrations DML + DDL not all DML is safe in migrations (e.g. UNIQUE constraint)
15. app DML on tables 1 migrations DML on tables 2 + DDL on all tables 1 ∩ 2 = ∅ you need DML to perform some DDL
16. app DML on tables 1 migrations DML + DDL on all tables, but unsafe DML only on tables 2 1 ∩ 2 = ∅
17. app DML on tables 1 migrations DML + DDL on all tables, but unsafe DML only on tables 2 1 ∩ 2 = ∅ But how do we know whether DML is safe or not?
19. I don’t know – master thesis idea formally describe and prove whether DML is safe. write software which can check if migrations are OK possibly integrate with Nextras Migrations
20. inserting with explicit ID may break UNIQUE safe when inserting into 2 safe when inserting into 1 when ID range is split into disjoint subsets (for app and for migrations) inserting without ID makes UPDATE impossible should be avoided
21. identifiers (e.g. FK names) created by database makes further ALTERs unsafe safe when identifiers are always explicitly assigned You cannot remove or modify FK if you don’t know its identifier.
23. migrations/ ├── 001.sql ├── 002.sql └── ... +---------+----------+---------------------+----+ | file | checksum | executed | ok | +---------+----------+---------------------+----+ | 001.sql | 78634... | 2015-08-28 17:05:03 | 1 | | 002.sql | dda40... | 2015-08-28 17:05:03 | 1 | | 003.sql | 5b9c6... | 2015-08-28 17:05:03 | 1 | +---------+----------+---------------------+----+
24. order is strictly defined new migrations can be only at the end you must always „rebase“ your migrations once executed migration cannot be changed previous migration must have not failed programmer must fix failed migration manually This ensures that migration starts every time from (at least partially) known state.
25. Δ1,0 support for more complex responsibility separation everything so far can be expressed with two groups – app and migrations Q1 Q2* Q1,1 Q1,n … Q1,2 Δ1 Δ1,1 Δ1,2
26. Δ1,0 Q1 Q2* Q1,1 Q1,n … Q1,2 Δ1 Δ1,1 Δ1,2 Δ1 Δ2 Δ1,0 Δ1,1 Δ1,2 Δ2,0 Δ2,1 Δ0 You can represent database migrations as directed acyclic graph of dependencies among deltas.
27. Δ1,0 Q1 Q2* Q1,1 Q1,n … Q1,2 Δ1 Δ1,1 Δ1,2 Δ1 Δ2 Δ1,0 Δ1,1 Δ1,2 Δ2,0 Δ2,1 Δ0
28. Δ4 Δ0 Δ3,0 Δ3 Δ4,0 Δ6,0 Δ1 Δ2 Δ5
29. S B P D L Structures Basic data Dummy data Production Localhost This is the default and recommended groups graph used by Nextras Migrations.
30. structures DDL only + DML required for DDL basic data DML for tables in 2 (countries, languages…) dummy data for testing app on localhost w/o strict rules because not used on production
31. migrations/ ├── structures/ │ ├── 2015-07-21-170126-init.sql │ └── 2015-07-22-144133-photo-description.sql ├── basic-data/ │ └── ... └── dummy-data/ └── ...
32. Symfony commands migrations:continue migrations:reset migrations:create
33. simple web interface when CLI is not available
34. PHP 5.4+ databases PostgreSQL MySQL DBALs Nextras DBAL Nette Database Doctrine DBAL dibi
35. SQL executes all queries in *.sql file PHP for complex migrations
36. useless for most applications difficult or impossible to write backward compatible migrations are better https://youtu.be/KTmlw5AKM8E by Martin Major
37. SQL is usually better easy to read supports all database features PhpStorm has autocompletion feel free to use query builder of your choice with PHP extension handler
38. feel free to use generator of your choice we may implement in the future some tool for Nextras ORM
39. https://github.com/nextras/migrations http://nextras.org/migrations/docs/
40. Twitter + GitHub @JanTvrdik nextras.org