Taking Control Of Your Database Sam Newman ThoughtWorks <annoying-introduction> Thoughtworks: my company Around 800 people worldwide Custom software development, process change, organisational change Clients include Banks Media Companies Miscreants (Greenpeace, BP) Telcos (AOL & Sky) Retail (DSG) </annoying-introduction> So why are we here? Because we all love change Change is good Changing code is good... To add new features To fix bugs To optimize resource use To make it easier to maintain We have tools to help us change code Refactoring IDE’s Source Control Management Continuous Integration Servers Test Harnesses We want to change our databases for the same reasons we change our code Fix Bugs New Features Optimization Ease Maintenance But a database isn’t like code Is that a problem? Example (i): George changes some code in file X George checks in file X The new version gets deployed Example (ii): George changes the schema for database table file X George checks in his change Then it gets deployed... ...but how do you deploy a schema change? “We could blow away the database and recreate it!” But data is important. That’s why we have a database And that’s why changing a database is different to changing code I love my data My customers love their data Blowing it away is rarely an option “Well we could write a migration script…” Better But most of us have several databases Development databases Test databases Production databases! So how do we know which migration to apply? “We could manually create migration scripts for each environment…” But that is: Boring Repetitive Error prone Hard to test So is there a better way? There is – and it’s a simple Make a big change, by making lots of little changes It works like this Split your changes into version scripts Know which change scripts have been applied to each DB During deployment, only apply those changes that need to be made Example (iii) George creates a change script, and calls it 005_my_small_change.sql It gets checked in, and now exists along with scripts 001-004 The system gets deployed to the test environment A version table shows changes 001-003 have already been applied - so changes 004-005 get applied The system gets deployed to the development environment A version table shows changes 001-002 have been applied - so changes 003005 get applied We’ve created an Open Source tool to help you do this… It’s called dbdeploy (http://dbdeploy.com/) For .NET and Java But it’s not the tool that is important – it’s the rules: 1.Break up your changes into little changes 2. Know which changes you’ve applied where Love change Love data Free your inner DBA Thank you! email@example.com http://www.magpiebrain.com/ (p.s. We’re always hiring…) Questions?