Flyway

Shadow Database or Shadow Schema

The shadow database is a separate database that is needed in development only.  It's a schema-only (and maybe some seed data) copy of the development database.

When you go to the Generate migrations tab in Flyway Desktop, we run the migration scripts in the project against the shadow database.  Then, we use our comparison technology to compare the schema model to the shadow database.  This allows us to show what changes have been made to your development database that have been captured in the schema model that have not been imported into a migration script in the project yet.  These are the changes that need to be scripted for deployment.     

Because we run all the migration scripts in your project on the shadow database/schema, this also helps you shift-left and identify any problems in your migration scripts as early as possible during development.  

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. 

When setting the shadow database/schema connection, ensure it isn't an important database/schema as this may be cleaned and rebuilt when generating migration scripts in Flyway Desktop.  There is a checkbox that you must explicitly click to confirm that all the data and schema in the shadow database can be erased.  

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. 

Configuring the shadow database

If no shadow database is configured, you will be prompted to configure it from the UI when navigating to the Generate Migrations tab.

Once it is configured, the connection details can be altered via the Cog icon, which will display the following options.

Additionally you can edit the settings manually with the user settings file in your project root directory. The settings are fully described here.


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.

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.

Some users like to name their shadows as z_<DbName>_shadow_<DevName>.  This is especially true on a shared centralized development server so all these databases appear at the end of the list.  If you are using SQL Server and SSMS, you can filter out and databases whose name contain "_shadow" from the Object Explorer view.
 


Oracle

Setting up a shadow database for 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 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 doesn't contain any transactional data, this should be ok.
  • 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)

Ensure that you have set sufficient permissions for the shadow database user. It will need to create objects and drop objects of all types.


Setting up a shadow schema for Oracle

For Oracle projects that are tracking changes to a single schema, it is possible to use a Shadow Schema rather than a separate shadow database. If your project contains multiple schemas then a separate shadow database is required so we maintain the schema name in object identifiers.

To enable this mode, navigate to the root folder of your project and open the user settings file.  Edit this file to add the "schema" property to your shadow database configuration.  You will then need to close your project (X in the top left corner next to the project name) and re-open it from the recently opened list on the home screen.  The *.json.user file should look something like:

{
  "version": "1.0",
  "developmentDatabase": {
    "connectionProvider": {
      "url": "jdbc:oracle:thin:@//localhost:1521/Dev1",
      "type": "Inline"
    }
  },
  "deployment": {
    "shadowDatabase": {
      "schema": "Shadow_Schema_Name",
      "connectionProvider": {
        "url": "jdbc:oracle:thin:@//localhost:1521/Dev1",
        "type": "Inline"
      }
    }
  }
}

When using a shadow schema for Oracle, it is recommended to set the ExcludeTargetSchemaName comparison option. This will ensure migration scripts are generated without explicit schema references.

"capabilities": {
    "comparison": {
        "ignoreRules": "./IgnoreRules.scpf",
        "filter": "./Filter.scpf",
        "options": {
            "ExcludeTargetSchemaName": true
        }
    }
}

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.

For example:

CREATE TABLE log (
message VARCHAR2(50),
timestamp INTEGER
);

-- this object references log in the same schema, so log must not be schema-qualified
CREATE OR REPLACE TRIGGER SetTimestampForLog BEFORE INSERT ON log
FOR EACH ROW
DECLARE
v_today DATE;
BEGIN
SELECT CURRENT_DATE INTO v_today FROM DUAL;

-- date2epoch is in a different schema, so must be schema-qualified
:new.timestamp := util.date2epoch(v_today);
END;

Technical details

Why is a shadow database/schema required?

A shadow database/schema is required so we can identify any changes to your development database that have not been scripted out to the project yet.  We consider the migration scripts 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 scripts produces to compare to the development database's schema model 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 on disk builds is to actually run the scripts against a real database. 

When is the shadow cleaned?

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 cleaning the database (i.e. dropping all objects) prior to updating it with the migration scripts depending upon the current state of the shadow.

If the project uses a database as the development source, and

  • the schema history table in development contains no scripts, then the shadow will be cleaned.
  • the schema history table in development contains only repeatable migration scripts, then the shadow will be cleaned before being migrated with the repeatable migration scripts.
  • the shadow version is ahead of the development version, or the schema history table in development contains Missing or Future migrations (see Migration scripts), the shadow will be cleaned and then migrated up to the current development version.

Otherwise, the shadow will not be cleaned.  Only the pending migration scripts will be applied to have it reflect the end state of the migration scripts in the project.

If the project uses a Source Control for Oracle project as the development source (i.e. Hybrid mode), then the shadow will be cleaned only if the schema history table in the shadow contains Missing or Future migrations. The shadow will be migrated using all the scripts in the current project.


Creating multiple shadow databases at once

We sometimes get asked how to create a shadow database for the 300 databases I currently have on my dev server.  Here's a sample script for SQL Server that may give you ideas to help with this.

SELECT 'CREATE DATABASE ' + 'z' + name + '_Shadow'
FROM master.sys.databases
WHERE name NOT IN
  ('master', 'model', 'msdb', 'tempdb', 'Resource',
       'distribution' , 'reportserver', 'reportservertempdb')

If there are other databases that are not being actively developed and you don't want a shadow for, then add these names to the NOT IN list.



Didn't find what you were looking for?