EDITION: ENTERPRISE

Build environment and shadow environment are fundamentally the same concept. Both are databases which may be cleaned or dropped and recreated frequently, used in Flyway workflows to capture the effect of running migrations.

The shadow environment just refers to the build environment used in Flyway Desktop for the migration generation workflow.

These databases will be small because they will only contain the schema objects and static data.  They won't contain any transactional data.

These databases are expected to be initially empty, or matching the baseline state, or configured with a provisioner.

Build Environment

The purpose of a build environment is to see what a database would look like, if we ran all the migrations on an empty database from V0 up to latest. To do this, flyway needs a dedicated environment that flyway is allowed to manage that is used to run to migrations against.

Flyway may need to rebuild this environment if the build environment contains changes that aren't in the migrations on disk (e.g. we've removed or edited a migration file after it has run). As such this database is subject to being cleaned or dropped and recreated.

Uses for a build environment include:

• Validating that your migrations can be deployed against a clean environment or an environment which matches production
• Capturing the differences between your development environment or schema model and migrations, in order to automatically generate a new migration
• Generating a snapshot representing the current state of a database with all the migrations run
  • This can be stored, capturing migration state at a particular point in history, and allows for running repeat comparisons on the same state

Shadow environment

The shadow database is a separate database that is only needed in your development environment.

A shadow database/schema is required so we can identify any changes to your development environment or schema model that have not been scripted out to the project yet.  We consider the migrations in your project to be the source of truth for deployments, so we need to know what the final state of running all the migrations produces and we do this by running them on the Shadow.  We then compare the development database's schema model to the Shadow to show the differences when you go to the Generate migrations tab.  The only reliable way to get this final state of what all the migrations in the project results in is to actually run the scripts against a real database.

You should never have to make changes to your shadow database/schema.  You also don't need to baseline your shadow database/schema.  It will be cleaned (meaning all objects will be dropped) as required when you visit the Generate migrations tab, so make sure that you are not using a database/schema that contains important data. 

The Shadow Database/Schema only gets updated with the migration scripts in your project, so the size should be small since there shouldn't be any transactional data in this database.  It will just contain the schema objects and reference data from your migration scripts and any data updates ran on these.  If you have a transactional data update in a migration script in your project, it won't do anything on the shadow since the transactional tables are empty. 

This database is just an environment like any other from the perspective of the Flyway CLI.

The shadow environment id can be customized although it cannot be edited from the UI.

Optimal shadow environment configuration

It is best if there is one shadow database per developer per project.  This improves performance because it won't have to be cleaned as often, and avoids potential conflicts.

It is best to put the shadow database on the same server as your development database.  This could be a shared centralized development environment or if you're using a local instance to develop against, then the shadow can go there.  This is especially important if there are any cross-database dependencies because the shadow will need to reference these in order for the scripts not to fail when they are executed.

Reprovisioning

In Flyway Desktop the shadow database/schema gets automatically updated from the migration scripts in your project when you visit/refresh the Generate migrations tab. This update involves potentially reprovisioning the database (i.e. resetting the shadow database to an empty or baseline state) prior to updating it with the migration scripts depending upon the current state of the shadow.

Build environments more generally can be explicitly reprovisioned over the command line.

Provisioning the shadow database

Typically reprovisioning is done through clean, resetting the shadow database to an empty state.

However, any form of reprovisioning is supported if a different provisioner is configured, allowing the shadow database to be reset to a known state, for example using Redgate Clone or Docker.

If the provisioned database is populated and matches the baseline state of your production environment, then this state can be treated as the baseline and no baseline migration script is needed. This solves a lot of issues baselining with large or complex databases. For more information see our Clones as baselines documentation.

When is the shadow database reprovisioned or cleaned?

Reprovisioning requires restoring the database or schema to a clean or baseline state and then applying all the migration from the project

While it may be that in a pipeline you want to always make sure a build environment is recreated like this, in Flyway Desktop it is preferable to only reprovision the shadow database when necessary.

Because of this, the info command is run under the hood and the shadow is only reprovisioned in the following situations:

• If the migration scripts have been edited in a way that changes their checksum from those recorded in the schema history table.
• If the version of the shadow database is greater than the migration scripts.
• If names or descriptions of the migration scripts on disk do not match what's recorded in the history table.
• If a prior migration attempt failed when applying to the shadow.
• If your project only contains repeatable migration scripts.

If the shadow database is not reprovisioned, then Pending migrations are simply applied on top of the existing state of the shadow.

Where to locate your shadow database

It is generally important that the connection to the shadow database be reasonably fast as a lot of queries and updates get run against it, though cost may also be an important consideration depending upon database flavour.

Here are some recommendations:

SQL Server

For SQL Server, there are a few options to use for the Shadow Database:

• A database on LocalDB
• A database on SQL Server Express Edition
• Another database on the same instance as your development database (or another environment, but we usually see development)
• A database on another instance 
• Redgate Clone
  Spin up terabyte-scale databases in just a few seconds and only use MBs of disk space.  This is ideal if your database has a lot of objects or the baseline script takes a while to run.
• Containers
  If you're familiar with docker/containers, you could spin up an SQL Server database in a container to use for the shadow. Since there is no transactional data, no persistence is required and therefore a transient container should just work.

Note:

1. If you are using different files and filegroups, make sure these are setup on the shadow database.  Or, you can add these into the top of the generated baseline script, so that if the baseline script is ran on a new database, then the filegroups/files will be created. 
2. If you have any x-database dependencies or linked servers, these will need to be available to the shadow

Oracle

For Oracle there are a few options to use for the Shadow Database:

• Pluggable Database
  If you are using Oracle 12c and above, the easiest way might be to use another pluggable database (pdb) on your development instance.  
• Oracle Express Edition (XE)
  Oracle XE is a free version of Oracle that can be used for development purposes.  There are some size restrictions, but because the shadow database does not contain any transactional data, this should be ok.  Make sure you connect to the pluggable database (default service is XEPDB1) and not the container database (default service is XE).  Read more about this in the Oracle docs.
  • If you get an "Error running flyway clean", this is usually the problem.  Make sure you are using the XEPDB1 service and not XE. 
• Redgate Clone
  Spin up terabyte-scale databases in just a few seconds and only use MBs of disk space.  This is ideal if your database has a lot of objects or the baseline script takes a while to run.
• Containers
  If you're familiar with docker/containers, you could spin up an Oracle database in a container to use for the shadow. Since there is no transactional data, no persistence is required and therefore a transient container should just work.
• Create another Oracle instance for this.
• A separate schema within the same Oracle database (see below)

MySQL

For MySQL, there are a few options to use for the Shadow Database:

• Containers: If you're familiar with docker/containers, you could spin up a MySQL instance in a container to use for the shadow. Since there is no transactional data, no persistence is required and therefore a transient container should just work. Example command: docker run -d -e MYSQL_ROOT_PASSWORD=password -p 3307:3306 mysql 
• Creating another MySQL instance on the same machine: One option is to run another copy of MySQL and specify something like mysqld --port=3307 on the commandline to make the second copy of MySQL listen on a different port.
• Redgate Clone can spin up terabyte-scale databases in just a few seconds and only use MBs of disk space.  This is ideal if your database has a lot of objects or the baseline script takes a while to run.  

Shadow schema

Where it is not possible to create a separate database, a shadow schema may be appropriate. In this case a schema which can be thrown away and recreated is needed within your development database.

Note that privileges may need to be configured to enable this.

Single schema mode

The most straightforward scenario where this works is where you are only working on one schema. In this case, you need to configure the development database on one schema, the shadow database on a separate schema, and configure migration generation to exclude schema information from your migration scripts.

As there are no schema references in the scripts, they can be deployed to a different schemas again when migrating downstream databases.

There are a couple of caveats when writing migration scripts in this single-schema mode:

• Any references to objects in the managed schema must not be schema-qualified. Since the migration scripts will run against different schemas with different names, managed objects must always be created in the current default schema.
• Any references to objects in other schemas must be schema-qualified. You'll be responsible for making sure any dependencies of the managed schema are present in the database as appropriate.
• Single schema mode is only supported for Oracle and MySQL

Multiple schemas

If you are working on multiple schemas it is not possible to exclude schema references from the migration scripts. Therefore, it is only possible to handle multiple shadow schemas if the names of the shadow schemas match the names of the schemas in the downstream environments, while the development schemas are different.

This is because the shadow schemas are what are used when generating the migration scripts.

Note that this is currently only supported on Oracle.