Flyway AutoPilot allows you to trial an end-to-end database development and automated deployment process with very little setup.  With Flyway AutoPilot, you get: 

1. A SQL script to create ready-to-use sample databases, with the ability to test additional objects and scenarios
2. A pre-configured Flyway project to get started quickly
3. Integration with version control to track your changes and collaborate with your team
4. Example migration scripts that are already deployed to the sample databases and the ability to generate new migration scripts (both versioned and undo scripts)
5. A pipeline for Continuous Integration and Continuous Deployments (CI/CD) to your sample databases
6. Reports to provide more visibility and safety checks integrated into the pipeline 

Anyone can use Flyway AutoPilot to explore the database development and deployment possibilities with your team.  This first project uses SQL Server / PostgreSQL and GitHub as an example.  This should give you a good understanding of how database changes can be version controlled and flow through an automated pipeline regardless of what database or CI/CD system (Azure DevOps, GitLab, Octopus Deploy, etc) you use.  Flyway works with over 20 database systems and any CI/CD system, so use this to get started and then explore the rest of our documentation to learn more about your specific needs.  We hope to add more examples for PostgreSQL and Oracle soon.  Get in touch if you have any questions.

Steps to follow

Prerequisites

1. Have a GitHub account.  There’s a free edition or a free trial for paid tiers.
2. Install the following on your developer machine:
   1. Flyway Enterprise - Desktop - There’s a free 28-day trial.
   2. Git, if you don’t already have it
3. Permission to create some SQL Server databases.
   1. Ideally, this is on a local SQL Server instance that’s accessible at localhost:1433. If not, that’s ok.  You’ll be prompted to change your project's connection strings, which we explain below. 
   2. You can install the free SQL Server Express edition on your developer machine, if needed.

Getting started

1. Go to Flyway SQL Server AutoPilot on GitHub or the PostgreSQL AutoPilot on GitHub and click Use As Template. This means that you have your own copy, all of the files you need to get started, as well as your own Git Actions Pipeline. 
   
2. Give this repository an easily accessible name and set it to Private, allowing us to potentially move our own schema into it without it being public! Then click Create Repository.
3. 
   
4. In your new repo’s Code tab, click Code and copy the URL.  You’ll need this to create a new project from version control in Flyway Desktop.
   
   
   
5. Launch Flyway Desktop from the Start Menu on your developer machine.
   
   
6. Click the Open project… drop down > Open from version control.  Paste the copied url, select an empty folder as the directory, and click Clone.
   →
   
   
7. Select flyway.toml file and click Open.
   
   

   
   

8. You will get an error about your database connection.  This is because we haven’t created them yet.  Click on the blue folder icon in the upper right to jump to the files on disk so we can create the necessary databases.
   
   
   
9. Open the CreateWidgetDatabases.sql in your preferred IDE (SSMS, VS Code, etc.).  If possible, connect to SQL Server localhost and execute the script to create the databases.   Make sure you are connected with a user that has permission to create databases.  (See below for information on what databases are created and why.)
   
   
   1. If you don’t have a local SQL Server, then execute the script on an instance that you can create databases on for the purposes of a Proof of Concept (POC).

10. In your IDE (eg. SSMS), run the following query to alter the stored procedure on WidgetDev.

    USE WidgetDev
    GO
    
    CREATE OR ALTER PROCEDURE [dbo].[GetAllWidgets]
    AS
    BEGIN
    	SELECT w.RecordID,
               w.Description ,
    		   wp.Price
    	FROM Widgets w INNER JOIN dbo.WidgetPrices wp 
    	ON wp.RecordID = w.RecordID
       END

11. In Flyway Desktop, click Refresh.  You'll see a list of all the objects in your database that have not been saved to the Schema model yet.  The Schema model is the CREATE DDL script for each object in your development database so you can see how it changes over time and go back to previous versions if necessary.   
    
    1. If you could not create your databases on localhost on the default instance and port, then Edit development database connection to the Server, Instance, and Port that you used and clicked Save.
       
       
12. In this example, there are 2 changes; one is the stored procedure change we made above.  You can click on any object to see a side-by-side difference at the bottom. 
    
    
    

This completes the initial set up.  We can now start capturing changes to our development database in Version Control.

Saving your changes to the Schema Model

1. Select the checkbox in front of GetAllWidgets and click Save to project.  The create DDL for the object is now saved to the schema-model on disk.  This will be committed to Git to see how the object changes overtime.
   
   
2. Click on Generate migration.
   
   
   1. If you did not create your databases on localhost:1433, you’ll get an error. You can click on the button to update your Shadow Database details.
3. Select the checkbox in front of GetAllWidgets and click Generate scripts.  We give you the option of selecting the objects again in case there are only a subset of objects ready for deployment.
   
   
   
4. Review the script and optionally give it a more descriptive name.  The version number automatically picks up the latest versioned migration script number in the project and increments it by one.  It also adds a timestamp to avoid conflicts with other developers because each version must be unique.  This project is setup to create Undo scripts as well, which can be reviewed at the bottom.  Click Save.
   
   
   
5. Click on Commit changes.
   
   
   1. If you want to click Verify generated migrations instead, you can. This will run the new Versioned Migration Script against the Shadow Database to make sure there are not any syntax or dependency errors.  This is especially helpful if you edited the script.  After verifying, click on the Version control tab to continue with the next step.  We'll have resources to learn more about the Shadow Database at the end of this page.
      
      
6. You should see scripts that represent your schema model changes and how you want to deploy this change (the Versioned and Undo scripts).  If you had to change your connection string details, then you’ll also see flyway.*.toml in the list.  Select all the changes, enter a commit message, and click Commit and push (or Commit and then Push changes at the top as two separate actions).
   
7. In your GitHub repository, refresh to see the changes you just pushed.
   
   
   

Deploying your changes

1. In your GitHub repository, click on Actions.  You can review and accept the forked workflows to enable them.
   → 
   
   
2. If the CI/CD process doesn’t kick off automatically, go into Actions > workflow and click Run workflow.  Make sure you are on the main.yml workflow and not the reporting.yml.
   →
   
   1. This example doesn’t use any branches, so just use main and click Run workflow.
      
      
3. Click on the run to open it.  You might see that the run is Queued.
   →
   
   
4. Click on the Deploy Build job to see the logs. If the logs show that it is waiting for a runner (a machine that will execute the steps in this workflow), we need to set this up.
   
   
   
5. Add a GitHub runner:
   1. Click on Settings.  Then click on Actions > Runners in the left menu and click New self-hosted runner.
      
      
      
      
   2. Follow the instructions for setting up your self-hosted runner on your Operating System. For me, I used Windows and executed the commands in the Windows PowerShell IDE on my machine.
      →
       
6. After adding the runner, you’ll need to enter a Flyway trial license key. Go to GitHub > Settings > Secrets and variables > Actions on the left menu and click New repository secret.  
   
   
   
7. The Name must be FLYWAY_LICENSE_KEY.  Enter your license key and click Add secret. If you need a Flyway Enterprise trial key, contact us.
   
   
   
8. Go back to your failed workflow and Re-run jobs.
   
   
   
9. Your queued build should now run.
   
   
   
10. In your repository, check out the reports.
    

About the databases

WidgetDev, WidgetTest, WidgetStaging, and WidgetProduction represent a simple, linear pipeline where changes flow from Dev to Test, to a Staging environment that is similar to Production, and then to Prod. 

The script also creates the following databases that a user shouldn’t have to interact with.  These are just to support the process:

1. WidgetZBuild – This is the database that is used for your CI process.
2. WidgetZCheck – This database is used during your CD process when deploying to Prod to produce a changes and drift report. Learn more.
3. WidgetZShadow – This database exists in development only and is needed to generate migrations. Learn more.

Your turn

Now it’s your turn.  You can play around with your project as much as you want to try different scenarios.  Remember to:

1. Make changes to your development database.
2. In Flyway Desktop, select the changes and save them to the schema model to have an object level history of how your database changes.
3. Generate a migration script to deploy changes that have been saved to the schema model.
4. Commit and push your changes.
5. Watch the CI/CD pipeline run and see the changes deployed in your downstream environments (Test > Staging > Prod).

Here are some other scenarios you can try:

1. Create a new table or procedure.
2. Add a column to an existing table.
3. Add a new table and a new stored procedure that queries that table. This will show how dependencies work.
4. Add Static Data to your project.  You can configure this on the Schema model tab. 
5. Share your GitHub URL with your team members to test multiple developers working on the project. How can you tell what changes they have made?
6. You could even try configuring a new project in Flyway Desktop using your own databases.
7. If you’re familiar with GitHub actions, modify the yml to add an approval step before going to Prod.

What other scenarios did you test?  Let us know. 

Questions

1. Check out our online training courses on Redgate University.

2. See our documentation for different CI/CD systems.

3. Get in touch if you have any questions.