Your database schema evolves over time, and each new release of your application will come with changes to your database schema or reference data.
There are two main approaches to managing database definitions and applying differences when deploying:
- Model-driven: where you define the desired state of your database in terms of tables and columns. During deployment, a tool compares the model with the actual target database, and then figures out what changes (column additions, table drops, etc.) need to be made to bring the target database in line with the model.
- Change-script-driven: where the scripts to migrate from one version of the schema to the next are kept in source control, and are only ever run once.
Octopus can be used to orchestrate database changes using both approaches, and using a variety of tools. For a more in-depth discussion, read our documentation on managing SQL databases using Octopus.
Execute change scripts during deployments
Write your change scripts as sequentially numbered .SQL files, and have Octopus run them during deployments. You can use open source tools like Flyway or DbUp (originally built by people at Octopus), or using Redgate's ReadyRoll.
Many Object-Relational Mapping libraries include an approach for data schema migrations. These can also be packaged and invoked from a script in your deployment. This works for ORM's like .NET's Entity Framework code first migrations, Liquibase, and Rails Active Record migrations.
The Octopus library contains over 250 community-contributed step templates that you can use in your deployments. Among them are many steps for working with SQL databases, including:
- Creating databases and granting permissions
- Backing up and restoring databases
- Executing arbitrary scripts
You might also like...
Currently updating a deployment process in @OctopusDeploy. I forgot how easy this was to use, everything just makes sense.— David Swindells (@d_swindells) January 30, 2019
Tools like @OctopusDeploy can be great in enabling culture change, we've been able to scale and improve our configuration story since we started using it.— Neil Chalk (@_neilch) July 19, 2018