I have created a fictitious scenario to help explain how to do this. I have been asked to create a simple interface for the Adventure Works database, apparently the old Visual Basic version is showing it’s age.
I've put all the code examples in a repository on github.
Structure
As I am starting a new project I have time to plan a good structure. I know that I will have source code, external tools and database backups for Roundhouse (more on these later). So I decide upon this tree:
With the folder structure complete, I can create the solution and add a project for the database schema. Here I am using a class library and have named it AdventureWorks.Database. I have also added two folders, db and deployment for the Roundhouse files.
Now to populate the db folder with the entities from the AdventureWorks and put them in to the relevant folders. My previous client had many legacy database with thousands of entities so I created sql-baseline to automate the process. Running sql-baseline requires three parameters, the server name, database name and place to put the scripts:
Sql-baseline has extracted the sprocs, views and functions and put them in the correct folders. The steps to add the files to Visual Studio are:
- Click the “Show all file” icon in Solution Explorer
- Expand the db folder
- Select the sprocs, views and functions folder
- Right click the selected folders and select “Include in project”
With the structure of the project setup, it is time to concentrate on working with Roundhouse.
The workflow
To support this process I have created a dos batch file, called LOCAL.DBDeployment.bat, to run Roundhouse. This is in the deployment folder.The batch file executes the Roundhouse command line runner which is in the Tools folder. It sets the run mode to restore.
In a team environment this file would be omitted from source control allowing each developer to modify the variables. When executed it restores the database using the backup in the DatabaseBackups folder at the top of the repository tree.
To keep development as quick as possible I add an External Tool to Visual Studio with the following settings
The full command text is
$(ProjectDir)\deployment\LOCAL.DBDeployment.bat. Running the migrations is now a menu click. I can also map a keyboard shortcut to make it quicker.
Following theses steps and conventions Roundhouse is integrated into the development process. I have found that being able to run the process from Visual Studio keeps the focus on development and keeps context switching to a minimum.
Roundhouse modes
I have configured Roundhouse to run in what I called “Restore mode”. Roundhouse has three options for running the migration scripts:- Drop and Create - Roundhouse will drop the database and recreate it from the scripts
- Restore - Roundhouse will restore the database from a backup and then run the scripts
- Normal - Roundhouse will run the scripts against the database
Drop and Create is typically used when developing a new database where the schema is developing rapidly but is not a good choice for an existing schema. In this situation we need confidence that the deployment to production will work and to achieve that we must develop using the current schema. This is why I use a backup of the production schema and have Roundhouse restore this before running the migration. In later posts I will explain how restore mode is used at every step in the deployment pipeline which rigorously tests the changes. Normal mode is used when the changes are finally deployed to the production database. When this is complete a new backup is taken and this is used as the new baseline during development.
Conclusion
There are many benefits of adding database development to source control.For the development process it keeps all of the work together. When I relied on SQL scripts I would work on a feature in Visual Studio by adding tests and refactoring the code. This would be checked in and the code for the feature would evolve. A side task would be to maintain the migration scripts, deploying them and testing they work. With the database in source control each commit includes changes to the whole system; the source code and the database schema. Anyone viewing the commit will see the complete set of changes. For the person doing the work the development workflow is simpler. By including the database you can bring the benefits of test driven development to the database changes and they become part of the “Red, Green, Refactor” TDD cycle.
For the deployment process it constantly tests the database migration scripts. Previously this would be a manual task whereby each developer would test a single database script before the deployment. To test the script before deployment would be done on a shared server with a recent copy of the production schema. This method was prone to errors as another developer could have made a change to the production schema which the script was not tested against. By storing the changes in source control they are integrated during development so every developer is aware of them instantly. This brings the benefits of Continuous Integration to database development.
In my next post I will explain how Roundhouse can be added to the Continuous Integration process.
