You Version Your Code, Why Not Your Database?

D12eaf3ef46e4f0fc6b714fd2b7ffe3b?s=47 derek-b
January 10, 2020

You Version Your Code, Why Not Your Database?

Code versioning is an integral part of any developers daily workflow. However, database changes are often handled manually making automatic code deploys a challenge. If something goes wrong, rolling back a deployment requires either complex and possibly untested scripts or a database restore.

Adding database versioning to your development environment will help you solve these issues. In the talk you will see examples of using Liquibase to manage database changes. You will learn how scripting and versioning your database structures will allow easy, scripted deployments that can be repeated by developers, in QA, and in prod. Once you see the ease at which you can deploy database changes, you will wonder how you ever got along without it.

D12eaf3ef46e4f0fc6b714fd2b7ffe3b?s=128

derek-b

January 10, 2020
Tweet

Transcript

  1. @DerekB_WI 1 You Version Your Code,
 Why Not Your Database?

    Presented By: Derek Binkley
  2. @DerekB_WI 2 Database Administrator Data was king Changes Strictly Managed

    Data Access set in RDBMS
  3. @DerekB_WI 3

  4. @DerekB_WI 4 Developers Take Over Sprints instead of waterfalls Rebellion

    Against Relational Data
  5. @DerekB_WI 5 Faster Changing Data MySQL, Postgres, etc. Non Relational

    Databases
  6. @DerekB_WI 6

  7. @DerekB_WI 7 Relational Data Still Important

  8. @DerekB_WI 8 Version Control Tools Managing Change

  9. @DerekB_WI 9 CI/CD Continuous Integration and Deployments Increase Reliability

  10. @DerekB_WI 10 Manually Changing Data Structures Roadblock to CI/CD

  11. @DerekB_WI 11 NPM Packages such as db-migrate available for Node

    Doctrine PHP based database tools, includes migrations LARAVEL Migrations built into PHP framework PHINX PHP Any framework FLYWAY Java, Maven, Gradle LIQUIBASE Java DATABASE VERSIONING
  12. @DerekB_WI 12 Change Set Define multiple changes XML Changelog

  13. @DerekB_WI 13 Prerequisites Snake Yaml https://bitbucket.org/asomov/snakeyaml/src/default/ YAML Changelog

  14. @DerekB_WI 14 Prerequisites Snake Yaml https://bitbucket.org/asomov/snakeyaml/src/default/ JSON Changelog

  15. @DerekB_WI 15 Create Table

  16. @DerekB_WI 16 Alter Table

  17. @DerekB_WI 17 Add an Index

  18. @DerekB_WI 18 Add a Foreign Key

  19. @DerekB_WI 19 liquibase.properties

  20. @DerekB_WI 20 Apply the Changes

  21. @DerekB_WI 21 Is This Any Better than just SQL?

  22. @DerekB_WI 22 Liquibase Tracks Change Sets

  23. @DerekB_WI 23 Instant Setup Code, plus database from git Database

    Defined Your migrations completely define your database. Really? Can it capture everything for us?
  24. @DerekB_WI 24 1. Generate Full Change Log

  25. @DerekB_WI 25 2. Setup Data

  26. @DerekB_WI 26 3. Combine Changes

  27. @DerekB_WI 27 Check Out and Build . . .

  28. @DerekB_WI 28 Code Changes We may have modified the database

    Keep Track of DDL? Over the course of a feature build you may not have kept an accurate change file
  29. @DerekB_WI 29 Diffs to the rescue

  30. @DerekB_WI 30 Diff Changelog

  31. @DerekB_WI 31 Clear History of Changes

  32. @DerekB_WI 32 Demo Time

  33. @DerekB_WI 33 Rolling Back

  34. @DerekB_WI 34 Prevent Automatic Rollback

  35. @DerekB_WI 35 Rollback to Tag

  36. @DerekB_WI 36 Generating SQL Alternative to Liquibase making changes for

    you.
  37. @DerekB_WI 37 Generating SQL

  38. @DerekB_WI 38 AWS Maven Ant Spring Liquibase can integrate with

    common build tools or frameworks Workflow Integration
  39. @DerekB_WI 39 Direct Access Deployment Server can contact Database Deployment

    Server Deployment could be running directly from deployment machine Deployment Scenario 2 3 1
  40. @DerekB_WI 40 Firewall Blocked Deployment Server cannot access database App

    Server Deployment sends message to application server to run migration Deployment Scenario
  41. @DerekB_WI 41 Multiple Deployment Targets

  42. @DerekB_WI 42 Other Changes addAutoIncrement addColumn addDefaultValue addForeignKeyConstraint addLookupTable addNotNullConstraint

    addPrimaryKey addUniqueConstraint alterSequence createIndex createProcedure createSequence createTable createView customChange delete dropAllForeignKeyConstraints dropIndex dropNotNullConstraint dropPrimaryKey dropProcedure dropSequence dropTable dropUniqueConstraint dropView empty executeCommand insert loadData loadUpdateData mergeColumns modifyDataType output renameColumn renameTable renameView setColumnRemarks setTableRemarks sql sqlFile stop tagDatabase update
  43. @DerekB_WI 43

  44. @DerekB_WI 44 Derek Binkley binkleyd@yahoo.com @DerekB_WI Keep In Touch

  45. @DerekB_WI 45 https://aws.amazon.com/blogs/devops/database-continuous-integration-and- automated-release-management-workflow-with-aws-and-datical-db/ RESOURCES http://www.liquibase.org/tutorial-using-oracle https://youtu.be/m6uNyezQ-7s - integrate Liquibase

    and Hibernate https://www.liquibase.org/bestpractices.html https://bitbucket.org/asomov/snakeyaml/src/default/
  46. @DerekB_WI 46 Download Slides