Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Delivering changes for databases and applications

Delivering changes for databases and applications

Talk presented @ PortoData (28-07-2016)

Eduardo Piairo

July 28, 2016

More Decks by Eduardo Piairo

Other Decks in Technology


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

    beautiful friendship” Eduardo Piairo Miguel Alho
  2. DLM – Database Lifecycle Management Data management and data migration

    Data monitoring Data recovery http://assets.red-gate.com/products/dlm/DLM_tools_and_processes_diagram.pdf
  3. 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
  4. 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
  5. 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
  6. 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
  7. Flyway • Flyway – “Database Migrations made Easy” http://flywaydb.org/ •

    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
  8. 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
  9. 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
  10. 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!
  11. 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
  12. 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
  13. 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
  14. Challenges • Challenge #3: migration scripts stack • How can

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

    a disposable development environment where the database is included?
  16. Links • Demo application “Purchases-DbMigration-sample” • https://github.com/MiguelAlho/Purchases-DbMigration-sample • Flyway •

    https://flywaydb.org/ • DbUp • http://dbup.github.io/ • Octopus Deploy • https://octopus.com/ • http://docs.octopusdeploy.com/display/OD/Deploying+ASP.NET+Core+Web+Applications • Visual Studio • https://www.visualstudio.com/
  17. Links • Redgate • http://www.red-gate.com/ • http://www.red-gate.com/products/sql-development/sql-source-control/ • http://www.red-gate.com/products/sql-development/sql-compare/ •

    http://www.red-gate.com/products/sql-development/sql-data-compare/ • http://www.red-gate.com/products/dlm/dlm-automation/ • http://www.red-gate.com/products/dlm/dlm-dashboard • http://www.red-gate.com/solutions/dlm/ • Simple Talk - DLM Patterns and Practices • https://www.simple-talk.com/collections/database-lifecycle-management-patterns-practices-library/