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
thomasvitale
kaz1437
ohbarye
os1ma
osyoyu
heleeen
qsona
lycorptech_jp
bkuhlmann
ham0215
takakiyo
pelelgrino
erikaheidi
mclloyd
stephaniewalter
destraynor
kneath
lara
3n
jeffersonlam
colly
thoeni
revolveconf
lauravandoore
