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 ORMs 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...
Creating happy deployments at more than 25,000 companies, including:
We've been overhauling our internal infrastructure and back-end systems over the past month, including a move back to full @OctopusDeploy deployments; rediscovering how nice it is to have a platform-agnostic orchestrator that can deploy practically anything, anywhere ❤Nicholas Blumhardt