Make adjustments to your database schema using SQL change scripts and Octopus Deploy

Automated database schema migrations

Easily deploy changes to your database schema by running SQL change scripts, using Entity Framework migrations, or with Redgate's database DevOps tooling.

Download Watch Video Demo

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.

Zip file containing scripts

Migrations libraries

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.

EF code first migrations

Database tasks

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

SQL database tasks