Painless version controlled, database refactoring

Slide 1

Slide 1 text

Painless version controlled, database refactoring Wil Moore III @wilmoore

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

Schema Evolution?

Slide 4

Slide 4 text

Schema Migration?

Slide 5

Slide 5 text

DB Patch Management?

Slide 6

Slide 6 text

Executable Documentation

Slide 7

Slide 7 text

Set the bar for Professionalism!

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

Automated Deployment

Slide 10

Slide 10 text

New Developer Ramp-Up

Slide 11

Slide 11 text

Transparent Code-Review

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Quick Start

Slide 14

Slide 14 text

% 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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

Download Postgres.app http://postgresapp.com

Slide 18

Slide 18 text

Validate Setup % ./liquibase validate

Slide 19

Slide 19 text

db-changelog.xml

Slide 20

Slide 20 text

Git (issue/story) Branching

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

Update db-changelog.xml

Slide 23

Slide 23 text

./liquibase update

Slide 24

Slide 24 text

Describe “work” Table

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

Y-U-NO Boolean?

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

Seed Data

Slide 33

Slide 33 text

demo & qa contexts

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

Liquibase: Under the covers

Slide 36

Slide 36 text

show databasechangelog*

Slide 37

Slide 37 text

SELECT id, filename, tag FROM databasechangelog

Slide 38

Slide 38 text

Generate SQL % ./liquibase updateSql

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

Other Commands

Slide 41

Slide 41 text

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

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

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

Slide 44

Slide 44 text

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

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

Find Stuff with Git

Slide 49

Slide 49 text

SQL Pocket Guide SQL Cookbook SQL Antipatterns Useful Books

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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-ﬁle LiquiBase Command Line http://www.liquibase.org/manual/command_line Documentation

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

Thank You :)