To work in an Agile way, change should be expected and easy to manage. How does this look from a database perspective?
Anything that you can “CREATE OR REPLACE” works fine – it can be stored in version control and the latest full version checked out to release to any environment. But what about those things that require migration? Like a table, or data.
A simple example could be an existing table with a ‘person_name’ column that contains first and last name separated by a space. You need to:
- create two new columns ‘first_name’ and ‘last_name’
- migrate the data from the ‘person_name’ column
- then remove the ‘person_name’ column.
A simple question right now would be (assuming I have the existing database and a new database with the changes applied): Is there any tool that could generate a difference script between the two database states to perform those actions? To date I have not found anything that could handle the “simple example”.
To further complicate the problem, not only is there is migration of objects over time, there are dependencies between objects at a point in time (eg foreign keys).
Some initial questions:
- Do you put the original table creation script into source code control and keep it up to date? For what reason (it can never be used except to build a ‘blank’ database)?
- Do you have a database “patch” script associated with a release? How will this work in a development environment with multiple developers adding to the script during development, and also wanting to only run the new changes to the script into their own development areas? How do you rollback a database patch? Or how do you complete a database patch that failed halfway through?
- How to manage continuous integration, where each new change to the database patch should be applied as soon as it is checked in?
- Are you interested in seeing the current state of a table creation script?
- Are you interested in seeing the history of changes to a specific table?
- How to ensure that the objects referenced by a foreign key exist before the foreign key creation?
- How do you branch the database DDL code?
I plan to explore these issues in some further blog posts, ending with a possible solution. Keep reading 🙂