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.

Transcript

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 :)