Flyway

Flyway Desktop integrates with Git, which has become the most popular version control system. Lots of teams take advantage of Git's ability to create and merge branches easily. We all know that long-lived branches can be bad, but branching can be useful to separate work that's ready to deploy and work that is still in progress. More teams are considering feature flags for this and continuously integrating with their main work to avoid long-lived branches and merge pain.

Branches have the benefit of providing you with isolated development areas. But how does that work with a shared development database or even your own dedicated copy of a development database? If there's only one database that supports different branches, then it's not really isolated. This is where our database cloning technology comes in to allow you to quickly spin up copies of your development database in seconds that take minimal space.

Note: If the remote branch doesn't appear in your list, please close the project and re-open.

Redgate Database Cloning

Redgate Clone - SQL Server, Oracle, PostgreSQL, and MySQL

Redgate Clone can be used to create database instances with full copies of your database for each piece of work during development, and switch between them automatically when you change branches in Git. This allows you to make changes to your database on one branch, and store those changes when you need to work on another branch. When you return to the original branch all your database changes including data will be preserved.

Install the Redgate Clone command line on your desktop and/or agents to use for Dev and Test use cases.

Create a helper-files folder in your local repository and save the following 3 files there.

post-checkout

#!/bin/sh
#exec powershell.exe -NoProfile -ExecutionPolicy Bypass -File "<TODO_RELATIVE_PATH>\clone.sh"


exec  "<TODO_RELATIVE_PATH>\clone.sh"

exit

clone.sh

#!/bin/bash
DATA_IMAGE="<TODO_IMAGE_NAME>"
FW_KEY="<TODO_LICENSE_KEY>"
BRANCH_NAME="$(git rev-parse --symbolic-full-name --abbrev-ref HEAD)"
CONTAINER_NAME="<TODO_PROJECT_NAME>.$BRANCH_NAME.$USERNAME"
STANDBY_NAME="<TODO_PROJECT_NAME>.standby"
DEV_PORT="10000"

# Timing the total operation
totalStartTime=$(date +%s)

# In case the proxy isn't running/working, we kill it here and reinstate it later
echo "Kill any rgclone proxy that may be running"
tskill rgclone

if ! rgclone.exe get data-container "$CONTAINER_NAME" &> /dev/null ; 
then
    
    echo "No data container for the current branch - will try to provision one"
    if ! rgclone.exe get dc $STANDBY_NAME &> /dev/null ; 
    then
        echo "No standby found - please wait a couple of minutes while we create a new clone (and a standby for future use)"

        startTime=$(date +%s)

        sh $PWD/clone-standby.sh

        # Now we have an up-to-date migrated standby we can rename immediately for our branch
        rgclone.exe update dc $STANDBY_NAME -n "$CONTAINER_NAME" --lifetime 30m
        echo "Standby data container has been renamed to $CONTAINER_NAME"
        endTime=$(date +%s)
        echo ""
        echo "######## DATA CONTAINER CREATED ###############"
        echo "Time taken: $(expr $endTime - $startTime) seconds"  

    else
        echo "We have found a standby that we can update and rename for our new branch!" 
        # Update the standby in case pending migrations have been created since it was created
        echo "Running Flyway migrate on standby"
        CLONE_PASSWORD="$(rgclone.exe get data-container $STANDBY_NAME -o json | jq .password -j)"
        PORT="$(rgclone.exe get data-container $STANDBY_NAME -o json | jq .port -j)"
        HOST="$(rgclone.exe get data-container $STANDBY_NAME -o json | jq .host -j)"
        URL="jdbc:sqlserver://$HOST:$PORT;databaseName=StackOverflow;encrypt=true;trustServerCertificate=true;"

        echo "Running Flyway info migrate: $URL port $PORT host $HOST pass $CLONE_PASSWORD"
        flyway info migrate -url=$URL -user=sa -password=$CLONE_PASSWORD info -licenseKey=$FW_KEY
        
        rgclone.exe update dc $STANDBY_NAME -n "$CONTAINER_NAME" --lifetime 30m
        echo "Standby data container has been renamed to $CONTAINER_NAME"
    fi   

    # need to update the state from the schema model
    # We have to restart the proxy to use flyway-dev as this pulls the connection details from flyway-dev.json
    echo "Now we start the proxy on port $DEV_PORT"
    rgclone.exe proxy dc $CONTAINER_NAME --port $DEV_PORT &
    
    echo "Updating the schema state (this will overwrite any changes you haven't committed)" 
    flyway-dev diff --project $PWD --from SchemaModel --to Dev --i-agree-to-the-eula --artifact changes.zip 
    echo "Running Take and piping to Apply (through less as I can't get it working without this)"
    flyway-dev take --artifact changes.zip --project $PWD --i-agree-to-the-eula | less | flyway-dev apply --artifact changes.zip --project $PWD --i-agree-to-the-eula
    rm changes.zip
    echo "#####################################################"
    echo ""
    echo "###### DEV DB up to date and ready to use! ##########"
    echo ""
    echo "## (although a new standby will be kicked off now) ##"
    echo "#####################################################"
    # Now we should create a new standby for the next branch/person
    sh $PWD/clone-standby.sh &> /dev/null

else
    echo ""
    echo "A data container already exists for the branch $CONTAINER_NAME"
    echo ""
    echo "We reset the lifetime of the container to 30m"
    rgclone update dc $CONTAINER_NAME --lifetime 30m

    echo "Now we start the proxy on port $DEV_PORT"
    rgclone.exe proxy dc $CONTAINER_NAME --port $DEV_PORT &
fi

totalEndTime=$(date +%s)

echo "###########################################"
echo ""
echo "######## ALL DONE !!! #####################"
echo "Time taken: $(expr $totalEndTime - $totalStartTime) seconds"
echo "###########################################"

clone-standby.sh

#!/bin/bash
IMAGE="<TODO_IMAGE_NAME>"
KEY="<TODO_LICENSE_kEY>"
BRANCH_NAME="$(git rev-parse --symbolic-full-name --abbrev-ref HEAD)"
CONTAINER_NAME="<TODO_PROJECT_NAME>.$BRANCH_NAME.$USERNAME"
DEV_PORT="10000"
LIFETIME="240m"
CONTAINER="<TODO_PROJECT_NAME>.standby"

echo ""
echo "Creating a brand new data container based on $IMAGE"
echo ""
echo "######## Data Container CREATED ###############"

rgclone.exe create dc --name "$CONTAINER" --image "$IMAGE" --lifetime $LIFETIME

CLONE_PASSWORD="$(rgclone.exe get data-container $CONTAINER -o json | jq .password -j)"
PORT="$(rgclone.exe get data-container $CONTAINER -o json | jq .port -j)"
HOST="$(rgclone.exe get data-container $CONTAINER -o json | jq .host -j)"
#Ideally all in the following section are pre-configured in the data image
# TODO - try to combine these to make this part faster
URL="jdbc:sqlserver://$HOST:$PORT;databaseName=StackOverflow;encrypt=true;trustServerCertificate=true;"
echo "URL is: $URL"
flyway -initSql="EXEC sp_addrolemember 'db_owner', 'Dev1'" -url=$URL -user=sa -password=$CLONE_PASSWORD info -licenseKey=$KEY -connectRetries=7

echo "Updating Server adding Dev1 to sysadmin role - required for tSQLt" 
echo "Enabling CLR - required for tSQLt" 
echo "Enabling Advanced options so strict security can be disabled - required for tSQLt" 
echo "Disabling strict security - required for tSQLt" 
URL="jdbc:sqlserver://$HOST:$PORT;databaseName=Master;encrypt=true;trustServerCertificate=true;"
flyway -initSql="ALTER SERVER ROLE [sysadmin] ADD MEMBER [Dev1];EXEC sp_configure 'clr enabled', 1; EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'clr strict security', 0; RECONFIGURE;" -url=$URL -user=sa -password=$CLONE_PASSWORD info -licenseKey=$KEY

echo "Baselining dev DB" 
echo "Migrating dev DB to the most recent version" 
URL="jdbc:sqlserver://$HOST:$PORT;databaseName=StackOverflow;encrypt=true;trustServerCertificate=true;"
flyway baseline migrate -url=$URL -user=sa -password=$CLONE_PASSWORD info -licenseKey=$KEY 

# need to update the state from the schema model
# but can't as this only works when the proxy is on, as this uses the flyway-dev.json dev connection string
#flyway-dev diff --project $PWD --from SchemaModel --to Dev --i-agree-to-the-eula --artifact changes.zip 
#echo "Running Take and piping to Apply (through less)"
#flyway-dev take --artifact changes.zip --project $PWD --i-agree-to-the-eula | less | flyway-dev apply --artifact changes.zip --project $PWD --i-agree-to-the-eula
#rm changes.zip

#################################################

echo "Updating Shadow DB"
URL="jdbc:sqlserver://$HOST:$PORT;databaseName=StackOverflow_Shadow;encrypt=true;trustServerCertificate=true;"
flyway -initSql="EXEC sp_addrolemember 'db_owner', 'Dev1'" -url=$URL -user=sa -password=$CLONE_PASSWORD info -licenseKey=$KEY

Copy the post-checkout file to the .git\hooks folder in your local repo . Each developer will need to copy this to their own local repo.
Make sure the path to rgclone.exe and flyway are on your Systems Environment Variables Path so the post-checkout script can find them. You may need to restart your computer after adding these.
You'll also need jq.
1. Run cmd as admin and run chocolatey install jq.
Switch to the Version Control tab and create a new branch from dev. This will use the post-checkout hook to call the clone-branch.sh script to spin up a new dedicated development environment for you on that branch. The shell script also updates your localproxy to point to this new environment. (Sometimes you may need to refresh in FWD if the db connection time out.)
If you want to work on main, just switch your branch back to main and a dedicated environment for main will be created.

Learn more about Redgate Clone.

SQL Clone - SQL Server (new users should use Redgate Clone),

SQL Clone can be used to create full copies of your database for each piece of work during development, and switch between them automatically when you change branches in Git. This allows you to make changes to your database on one branch, and store those changes when you need to work on another branch. When you return to the original branch all your database changes including data will be preserved.

See the SQL Clone documentation for creating a git post-checkout hook to do this automatically.

Delete and recreate your database

One way to switch branches and have your database match the schema from that branch is to drop and recreate your database. This resets your environment so you can deploy from the new branch. To do this:

Take a backup of your development database, in case you need to restore it later.
Delete your development database.
Create a new database with the same name and credentials.
In the version control tab in Flyway Desktop, create or switch to the branch you're working on.
Click Pull to make sure all the latest changes from the remote repository are on your local repository.
In the schema model tab, make sure you're on the Apply to Database section.
Select all the objects on the screen and click Apply to database. You will need to confirm this action since you're development database will be updated with all your selections.

Your database now matches the schema definition that is on that branch.

You can also follow these steps to test whether your databases can be reproduced from source files.

Create a new database / branch

This option is similar to deleting and recreating your development database, but you must also remember to update your development database connection details every time you switch branches.

Create a new database.
In Flyway Desktop, in the settings cog in the upper right , edit your development database connection details to this new database.
In the version control tab , create or switch to the branch you want to work on.
Click Pull to make sure all the latest changes from the remote repository are on your local repository.
In the schema model tab, make sure you're on the Apply to Database section.
Select all the objects on the screen and click Apply to database. You will need to confirm this action since you're development database will be updated with all your selections.