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

Painless version controlled, database refactoring

B75805f5c6b8a35089415ae88eebfd10?s=47 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


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

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

  3. Schema Evolution?

  4. Schema Migration?

  5. DB Patch Management?

  6. Executable Documentation

  7. Set the bar for Professionalism!

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

    Transparent Code Review.
  9. Automated Deployment

  10. New Developer Ramp-Up

  11. Transparent Code-Review

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

  13. Quick Start

  14. % 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
  15. liquibase.properties Teach Liquibase how to connect to your DB.

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

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

  18. Validate Setup % ./liquibase validate

  19. db-changelog.xml

  20. Git (issue/story) Branching

  21. Branch == (Story|Issue) % git checkout -b issue-001 Switched to

    a new branch ‘issue-001’ % $EDITOR issue-001.sql
  22. Update db-changelog.xml

  23. ./liquibase update

  24. Describe “work” Table

  25. 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
  26. Shameless Plug: git-hub(1) https://github.com/wilmoore/git-hub

  27. Merge % git checkout master % git merge issue-001 %

    git push origin master
  28. Y-U-NO Boolean?

  29. Add a new change set % git checkout -b issue-002

    Switched to a new branch ‘issue-002’ % $EDITOR issue-002.sql
  30. Boss now also wants to `archive` % $EDITOR issue-002.sql %

    ./liquibase update
  31. 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
  32. Seed Data

  33. demo & qa contexts

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

  35. Liquibase: Under the covers

  36. show databasechangelog*

  37. SELECT id, filename, tag FROM databasechangelog

  38. Generate SQL % ./liquibase updateSql

  39. Start Over % dropdb timetracker ; createdb --echo --encoding=utf8 timetracker

    % ./liquibase update
  40. Other Commands

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

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

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

  44. Generating Change Logs % ./liquibase generateChangeLog http://www.liquibase.org/manual/ generating_changelogs Stored procedures,

    functions, packages, and Triggers do not get exported.
  45. Generate HTML Docs % mkdir -p ./build/dbdoc % ./liquibase dbdoc

    ./build/dbdoc Lists changes, authors, tables, etc.
  46. Database Diff % ./liquibase diffChangeLog --referenceUrl=jdbc:...://remote/db Outputs the SQL to

    get a target DB up-to-date
  47. Find Stuff with Git % git log -S ‘issue-002’ --

    db-changelog.xml % git show 1a26f4f
  48. Find Stuff with Git

  49. SQL Pocket Guide SQL Cookbook SQL Antipatterns Useful Books

  50. 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
  51. 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
  52. on JoindIn https://joind.in/7932 on Twitter http://twitter.com/wilmoore on Github http://github.com/wilmoore Thanks

    for your feedback
  53. Thank You :)