Shadow and build environments
Published 20 December 2024
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.
development environment or schema model
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
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.
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:
- 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.
- 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.