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

You Version Your Code, Why Not Your Database?

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.

derek-b

January 10, 2020
Tweet

More Decks by derek-b

Other Decks in Technology

Transcript

  1. @DerekB_WI
    1
    You Version Your Code,

    Why Not Your Database?
    Presented By: Derek Binkley

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  5. @DerekB_WI
    7
    Relational Data Still Important

    View full-size slide

  6. @DerekB_WI
    8
    Version Control Tools
    Managing Change

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  9. @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 full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  13. @DerekB_WI
    15
    Create Table

    View full-size slide

  14. @DerekB_WI
    16
    Alter Table

    View full-size slide

  15. @DerekB_WI
    17
    Add an Index

    View full-size slide

  16. @DerekB_WI
    18
    Add a Foreign Key

    View full-size slide

  17. @DerekB_WI
    19
    liquibase.properties

    View full-size slide

  18. @DerekB_WI
    20
    Apply the Changes

    View full-size slide

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

    View full-size slide

  20. @DerekB_WI
    22
    Liquibase Tracks Change Sets

    View full-size slide

  21. @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 full-size slide

  22. @DerekB_WI
    24
    1. Generate Full Change Log

    View full-size slide

  23. @DerekB_WI
    25
    2. Setup Data

    View full-size slide

  24. @DerekB_WI
    26
    3. Combine Changes

    View full-size slide

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

    View full-size slide

  26. @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 full-size slide

  27. @DerekB_WI
    29
    Diffs to the rescue

    View full-size slide

  28. @DerekB_WI
    30
    Diff Changelog

    View full-size slide

  29. @DerekB_WI
    31
    Clear History of Changes

    View full-size slide

  30. @DerekB_WI
    32
    Demo Time

    View full-size slide

  31. @DerekB_WI
    33
    Rolling Back

    View full-size slide

  32. @DerekB_WI
    34
    Prevent Automatic Rollback

    View full-size slide

  33. @DerekB_WI
    35
    Rollback to Tag

    View full-size slide

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

    View full-size slide

  35. @DerekB_WI
    37
    Generating SQL

    View full-size slide

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

    View full-size slide

  37. @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 full-size slide

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

    View full-size slide

  39. @DerekB_WI
    41
    Multiple Deployment Targets

    View full-size slide

  40. @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 full-size slide

  41. @DerekB_WI
    43

    View full-size slide

  42. @DerekB_WI
    44
    Derek Binkley
    [email protected]
    @DerekB_WI
    Keep In Touch

    View full-size slide

  43. @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 full-size slide

  44. @DerekB_WI
    46
    Download Slides

    View full-size slide