Save 37% off PRO during our Black Friday Sale! »

Delivering changes for databases and applications

Delivering changes for databases and applications

Talk presented @ PortoData (28-07-2016)


Eduardo Piairo

July 28, 2016


  1. Delivering changes for applications and databases “The beginning of a

    beautiful friendship” Eduardo Piairo Miguel Alho
  2. Delivering changes for applications and databases @EdPiairo

    Eduardo Piairo, DBA & DevOps @ Celfinet
  3. Delivering changes for applications and databases @MytyMyky

    Miguel Alho, Software Engineer @ Faro Europe
  4. @DevOpsPorto Is Coming

  5. Delivering changes for applications and databases Source Control Continuous Integration

    Continuous Delivery Database + Application
  6. What’s so special about the database?

  7. What’s so special about the database?

  8. DLM – Database Lifecycle Management Data management and data migration

    Data monitoring Data recovery
  9. Typical problems we want to fix • Databases are out

    of pace with application development • Lack of traceability of database changes (changes history) • Manual databases processes prevent the CI and CD utilization • Lack of testability • Database setup time for new environments • Bugs in production
  10. Typical problems we want to fix Databases becoming a bottleneck

    in an agile delivery process
  11. Typical problems we want to fix Databases becoming an easy

    target to blame
  12. None
  13. Why database automation? • Enable control over database development •

    Increase speed of response to change • Keep a versioned “history” of database states and changes • Reliability of the release process • Increase release frequency though repeatability of processes • Reduce time spent fixing bugs - automated tests • Remove/reduce human intervention in the release process
  14. Why database automation? Fearless database changes

  15. 1st step: Source Control • First step in your database

    deployment pipeline • Traceability through change history • SQL as documentation • Shared code-base and shared process • Enforceable standards to reduce conflicts
  16. 1st Step: Source Control Fundamental resource: SQL Script

  17. Migrations-based approach vs State-based approach • State based solutions •

    Script represents the current database state • Your source of truth is how the database should be • Migrations based solutions • Script represents a migration • Migration represents how to transition to the next database version • Your source of truth is how the database should change
  18. Flyway • Flyway – “Database Migrations made Easy” •

    Open source database migration tool • Flyway command-line enabled Simplicity: easy to setup, no need to install Zero dependencies (java + jdbc) Scripts are written in SQL
  19. Flyway – How does it work? • Flyway commands: •

    migrate, clean, info, validate, baseline, repair • Metadata table: • Used to track the state of the database • If the database is empty, flyway won’t find it and will create it • Flyway scans the migrations directory and check migrations against the metadata table • Migrations are sorted based on their version number and applied in order
  20. Version Control System Two components: Database scripts repository Flyway –

    database migrations Rule 1: Script version (timestamp) Rule 2: Operation type Rule 3: Object type Rule 4: Object name Example: V20160728.1100__Create_TB_MyTable.sql
  21. Demo

  22. How about applications? It takes two to tango

  23. Database and Application change alignment • Database schemas should evolve

    along with application evolution ◦ deploy app => deploy changes / migrations • (Integration) Test app and db changes together ◦ should pass tests while developing, ◦ should pass tests while promoting to new environments • Ideally, one application per database • Pipeline deployment only!
  24. Integration test at the database layer • Use TDD techniques

    • Limit database integration tests to data layer ◦ faster tests! ◦ validate sql and mappings • Always test with the latest schema Database Application Database Application
  25. Demo

  26. What we just saw

  27. Database and Application change alignment Fearless database changes

  28. Challenges • Challenge #1: managing multiple branches • Manage merge

    conflicts • The default branch is the main/master branch and all the merges for this branch must be done with caution
  29. Challenges • Challenge #2: Script expiration date • Should the

    migration script have an expiration date? • Sometimes, in the next day, the migration script no longer makes sense
  30. Challenges • Challenge #3: migration scripts stack • How can

    I manage the dependencies between different databases?
  31. Challenges • Challenge #4: developer sandbox • How to supply

    a disposable development environment where the database is included?
  32. What’s Next?

  33. Links • Demo application “Purchases-DbMigration-sample” • • Flyway • • DbUp • • Octopus Deploy • • • Visual Studio •
  34. Links • Redgate • • • • • • • • Simple Talk - DLM Patterns and Practices •
  35. Reading material

  36. Delivering changes for applications and databases @EdPiairo