Database are out of pace with application development • No traceability of database changes (changes history) • Manual databases processes prevent the CI and CD utilization in their full extent • Database release process is manual • Time consuming and error prone • Releases are less frequent and risky
Tests are manual or do not exists • Bugs in production environment • Database related bugs are only discovered after deployment to production • Fixes and hotfixes have time cost, what can lead to delay a release • Inability to roll back to the previous version of a database • Database setup time of a new environment • Databases become a bottleneck in agile delivery processes
automation? • Enable control over database development • Keep a versioned “history” of database sates • Increase release frequency though repeatability of processes • Greater predictability over releases • Efficiency by eliminating the repetition of a range of manual activities • Reduce time spent fixing bugs • More control, less exposure to error
automation? • Faster speed of response to change • Automated deployment of smaller units of change • Remove/reduce human intervention in the release process • The build step is automatic triggered by a “push” into source control repository • The deploy step is automatic triggered by a successfully build process • Greater reliability of the release process • Providing a series of automated test stages prior to deployment • Maintain an agile development environment
vs State-based approach • Fundamental resource - SQL Script • State based solutions • Script represents the current database state • Migrations based solutions • Script represents a migration • Migration represents the next database version State: Your source of truth is how the database should be Migrations: Your source of truth is how the database should change Alex Yates, Pre-sales engineer at Redgate
Why database migrations? • The code side • Version control is now universal with better tools everyday • We have reproducible builds and continuous integration • We have well defined release and deployment processes • The database side • What state is the database in on this machine? • Has this script already been applied or not? • Has the quick fix in production been applied in test afterwards • How do you set up a new database instance?
Why database migrations? • Migrations • Recreate a database from scratch • Make it clear at all time what state a database is in • Migrate in a deterministic way from your current version of the database to a newer one
How it works? • Metadata table • Used to track the state of the database • Default name: “schema_version” • If the database is empty, flyway won’t find it and will create it
How it works? • Metadata table • Flyway scans the migrations directory (java/sql) • Migrations are checked against the metadata table • Migrations are sorted based on their version number and applied in order
System – Refactoring databases • Simple change to a database schema that improves its design while retaining its behavioral and informational semantics • Transition Period (Deprecation Period) • Both schemas (original and new) are supported • Refactoring categories • Structural refactoring • Data quality refactoring • Architectural refactoring • Etc. How good are you doing T-SQL code?
System – Challenges • Challenge 1 – manage multiple branches • In other words, is necessary to mange merge/interests conflicts • The default branch is the main/master branch and all the merges for this branch must be done with caution • Challenge 2 – script expiration date • Should the migration script have expiration date? • Sometimes, in the next day the migration script makes no sense anymore
System – Challenges • Challenge 3 – migration scripts stack • In other words, is necessary to mange merge/interests conflicts • How can I manage the dependencies between different databases? • And how about dependencies between applications/components • Challenge 4 – developer sandbox • How to supply a disposable development environment where the database is included?
eduardopiairo
salaboy
msato
yuki_ink
cmusudakeisuke
sakaik
shigashiyama
tubone24
nobutomurata
toridori_dev
tkikuchi
oracle4engineer
stephaniewalter
addyosmani
mongodb
jeffersonlam
ammeep
morganepeng
afnizarnur
jnunemaker
jonrohan
irinanazarova
philnash
