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.

Transcript

1. @DerekB_WI
   1
   You Version Your Code,

   Why Not Your Database?
   Presented By: Derek Binkley
   

   View Slide

2. @DerekB_WI
   2
   Database Administrator
   Data was king
   Changes Strictly Managed
   Data Access set in RDBMS
   

   View Slide

3. @DerekB_WI
   3
   

   View Slide

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

   View Slide

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

   View Slide

6. @DerekB_WI
   6
   

   View Slide

7. @DerekB_WI
   7
   Relational Data Still Important
   

   View Slide

8. @DerekB_WI
   8
   Version Control Tools
   Managing Change
   

   View Slide

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

   View Slide

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

    View Slide

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
    

    View Slide

12. @DerekB_WI
    12
    Change Set
    Define multiple changes
    XML Changelog
    

    View Slide

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

    View Slide

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

    View Slide

15. @DerekB_WI
    15
    Create Table
    

    View Slide

16. @DerekB_WI
    16
    Alter Table
    

    View Slide

17. @DerekB_WI
    17
    Add an Index
    

    View Slide

18. @DerekB_WI
    18
    Add a Foreign Key
    

    View Slide

19. @DerekB_WI
    19
    liquibase.properties
    

    View Slide

20. @DerekB_WI
    20
    Apply the Changes
    

    View Slide

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

    View Slide

22. @DerekB_WI
    22
    Liquibase Tracks Change Sets
    

    View Slide

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?
    

    View Slide

24. @DerekB_WI
    24
    1. Generate Full Change Log
    

    View Slide

25. @DerekB_WI
    25
    2. Setup Data
    

    View Slide

26. @DerekB_WI
    26
    3. Combine Changes
    

    View Slide

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

    View Slide

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
    

    View Slide

29. @DerekB_WI
    29
    Diffs to the rescue
    

    View Slide

30. @DerekB_WI
    30
    Diff Changelog
    

    View Slide

31. @DerekB_WI
    31
    Clear History of Changes
    

    View Slide

32. @DerekB_WI
    32
    Demo Time
    

    View Slide

33. @DerekB_WI
    33
    Rolling Back
    

    View Slide

34. @DerekB_WI
    34
    Prevent Automatic Rollback
    

    View Slide

35. @DerekB_WI
    35
    Rollback to Tag
    

    View Slide

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

    View Slide

37. @DerekB_WI
    37
    Generating SQL
    

    View Slide

38. @DerekB_WI
    38
    AWS
    Maven Ant Spring
    Liquibase can integrate with common build tools or frameworks
    Workflow Integration
    

    View Slide

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
    

    View Slide

40. @DerekB_WI
    40
    Firewall Blocked
    Deployment Server
    cannot access database
    App Server
    Deployment sends
    message to application
    server to run migration
    Deployment Scenario
    

    View Slide

41. @DerekB_WI
    41
    Multiple Deployment Targets
    

    View Slide

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
    

    View Slide

43. @DerekB_WI
    43
    

    View Slide

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

    View Slide

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/
    

    View Slide

46. @DerekB_WI
    46
    Download Slides
    

    View Slide