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

Painless version controlled, database refactoring

Wil Moore III
February 26, 2013

Painless version controlled, database refactoring

Emailing *.sql files to the in-house DBA before each release used to work for your single-node, single-environment website. You have recently been tasked with building a multi-environment application architecture when you realize that you need to come up with a more professional process which is less error-prone.

Wil Moore III

February 26, 2013
Tweet

More Decks by Wil Moore III

Other Decks in Technology

Transcript

  1. Painless version
    controlled,
    database
    refactoring
    Wil Moore III
    @wilmoore

    View full-size slide

  2. Full-Stack Web
    Application Craftsman,
    TDD and Devops
    Advocate.
    http://github.com/wilmoore

    View full-size slide

  3. Schema
    Evolution?

    View full-size slide

  4. Schema
    Migration?

    View full-size slide

  5. DB Patch
    Management?

    View full-size slide

  6. Executable
    Documentation

    View full-size slide

  7. Set the bar for
    Professionalism!

    View full-size slide

  8. manager wants to see $
    Automated Deployments.
    Quick developer ramp-up.
    Transparent Code Review.

    View full-size slide

  9. Automated
    Deployment

    View full-size slide

  10. New Developer
    Ramp-Up

    View full-size slide

  11. Transparent
    Code-Review

    View full-size slide

  12. Liquibase.
    ...manages and applies your SQL
    change scripts

    View full-size slide

  13. % mkdir timetracker
    % cd !$
    % wget http://liquibase.org/liquibase-3.0.0-beta1-bin.zip
    % wget http://jdbc.postgresql.org/download/postgresql-9.2-1002.jdbc3.jar
    % wget https://gist.github.com/wilmoore/5034783/raw/liquibase.properties
    % wget https://gist.github.com/wilmoore/5034783/raw/db-changelog.xml
    % unzip liquibase-3.0.0-beta1-bin.zip
    % rm liquibase-3.0.0-beta1-bin.zip
    % rm -rf docs
    % chmod +x liquibase
    % git init
    % git stage --all
    % git commit -m ‘Liquibase Bootstrap’
    Bootstrap

    View full-size slide

  14. liquibase.properties
    Teach Liquibase how to
    connect to your DB.

    View full-size slide

  15. JDBC drivers listed @
    http://www.sql-workbench.net/manual/jdbc-setup.html

    View full-size slide

  16. Download
    Postgres.app
    http://postgresapp.com

    View full-size slide

  17. Validate Setup
    % ./liquibase validate

    View full-size slide

  18. db-changelog.xml

    View full-size slide

  19. Git (issue/story)
    Branching

    View full-size slide

  20. Branch == (Story|Issue)
    % git checkout -b issue-001
    Switched to a new branch ‘issue-001’
    % $EDITOR issue-001.sql

    View full-size slide

  21. Update db-changelog.xml

    View full-size slide

  22. ./liquibase update

    View full-size slide

  23. Describe “work” Table

    View full-size slide

  24. Happy? then push it!
    % git stage db-changelog.xml issue-001.sql
    % git commit -am 'issue-001: creates initial work table'
    % git push origin HEAD # then make a pull request @ github

    View full-size slide

  25. Shameless Plug: git-hub(1)
    https://github.com/wilmoore/git-hub

    View full-size slide

  26. Merge
    % git checkout master
    % git merge issue-001
    % git push origin master

    View full-size slide

  27. Y-U-NO Boolean?

    View full-size slide

  28. Add a new change set
    % git checkout -b issue-002
    Switched to a new branch ‘issue-002’
    % $EDITOR issue-002.sql

    View full-size slide

  29. Boss now also wants to `archive`
    % $EDITOR issue-002.sql
    % ./liquibase update

    View full-size slide

  30. Checking our work
    % git stage db-changelog.xml issue-002.sql
    % git commit -am 'issue-002: archived/deleted boolean columns'
    % git checkout master
    % git merge issue-002
    % git push origin master

    View full-size slide

  31. demo & qa contexts

    View full-size slide

  32. demo only
    % ./liquibase update --contexts=demo

    View full-size slide

  33. Liquibase:
    Under the covers

    View full-size slide

  34. show databasechangelog*

    View full-size slide

  35. SELECT id, filename, tag FROM
    databasechangelog

    View full-size slide

  36. Generate SQL
    % ./liquibase updateSql

    View full-size slide

  37. Start Over
    % dropdb timetracker ; createdb --echo --encoding=utf8 timetracker
    % ./liquibase update

    View full-size slide

  38. Other Commands

    View full-size slide

  39. Tags
    % ./liquibase tag {{NAME}}
    http://www.liquibase.org/manual/rollback

    View full-size slide

  40. roll back to a tag
    % ./liquibase rollback {{NAME}}
    http://www.liquibase.org/manual/rollback

    View full-size slide

  41. rollback a # of changes
    % ./liquibase rollbackCount 1
    http://www.liquibase.org/manual/rollback

    View full-size slide

  42. Generating Change Logs
    % ./liquibase generateChangeLog
    http://www.liquibase.org/manual/
    generating_changelogs
    Stored procedures, functions, packages, and
    Triggers do not get exported.

    View full-size slide

  43. Generate HTML Docs
    % mkdir -p ./build/dbdoc
    % ./liquibase dbdoc ./build/dbdoc
    Lists changes, authors, tables, etc.

    View full-size slide

  44. Database Diff
    % ./liquibase diffChangeLog --referenceUrl=jdbc:...://remote/db
    Outputs the SQL to get a target DB
    up-to-date

    View full-size slide

  45. Find Stuff with Git
    % git log -S ‘issue-002’ -- db-changelog.xml
    % git show 1a26f4f

    View full-size slide

  46. Find Stuff with Git

    View full-size slide

  47. SQL Pocket Guide
    SQL Cookbook
    SQL Antipatterns
    Useful Books

    View full-size slide

  48. Liquibase Website
    http://www.liquibase.org/
    Presentation Code
    https://github.com/slidedecks/schema-evolution-vcs
    What are the alternatives to LiquiBase?
    quora.com/What-are-the-alternatives-to-LiquiBase
    PostgreSQL Connection URIs
    http://www.postgresql.org/docs/9.2/static/libpq-
    connect.html#AEN38149
    Resources

    View full-size slide

  49. Liquibase Best Practices
    http://liquibase.org/bestpractices
    Formatted SQL Changelogs
    http://liquibase.org/manual/
    formatted_sql_changelogs
    Formatted SQL Includes
    http://forum.liquibase.org/topic/liquibase-formatted-
    sql-how-to-include-other-changeset-file
    LiquiBase Command Line
    http://www.liquibase.org/manual/command_line
    Documentation

    View full-size slide

  50. on JoindIn
    https://joind.in/7932
    on Twitter
    http://twitter.com/wilmoore
    on Github
    http://github.com/wilmoore
    Thanks for your feedback

    View full-size slide

  51. Thank You :)

    View full-size slide