Flyway AutoPilot is a sample project that allows you and your team to experience an end-to-end database development and automated deployment process with minimal setup. 

Projects are currently hosted in GitHub for SQL Server and PostgreSQL. Regardless of whether you use GitHub for versioning and your pipeline, the concepts are identical for other tools such as Azure DevOps, GitLab, Octopus Deploy, etc.  Get in touch if you have any questions.

Flyway AutoPilot provides: 

1. A SQL script to create ready-to-use sample databases
2. A pre-configured Flyway project and pipeline in GitHub for continuous integration and automated deployments
3. Also included are
   1. already deployed migration scripts
   2. database changes saved to the project but yet to be reflected in migration scripts
   3. Reports generated in the CI/CD pipeline to provide more visibility and confidence

Steps to follow

Prerequisites

1. A GitHub account to fork the Autopilot project
2. Git must be present on your desktop machine (if you don’t already have it)
3. Flyway Desktop must be installed and licensed for Flyway Enterprise. A free 28-day trial can be initiated from within Flyway Desktop on startup or by selecting the Licensing menu item. A trial license can also be requested via this web form.
4. You will need to create databases for the Autopilot project and pipeline. You can install the free SQL Server Developer edition, if needed. You'll need to enable TCP/IP, which can be done via SQL Server Configuration Manager.

Flyway AutoPilot comes with a very handy exercise book! This helps with onboarding, and is extremely useful as it can help break down each learning stage, with examples of how to learn anything and everything inside of Flyway Enterprise!

Getting started

1. Go to Flyway SQL Server AutoPilot on GitHub or the PostgreSQL AutoPilot on GitHub and click Use This 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.  Steps may vary slightly, depending on the choice of DBMS, but this should be clear!
   
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. Click on the green Code button and under the HTTPS submenu copy the URL.  You’ll need this to create a new project from version control in Flyway Desktop.
   
   
   
4. Launch Flyway Desktop from the Start Menu on your developer machine.
   
   
5. Click the Open project… drop down > Open from version control.  Paste the copied url, select an empty folder as the directory, and click Clone.
   
    
   
   
6. Select flyway.toml file and click Open.
   
   

   
   

7. 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.
   
   
   
8. 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).

9. In your IDE (eg. SSMS), run the following query to alter the stored procedure on WidgetDev. If on Postgres, use a similar script or use the IDE to edit an object!

   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

10. 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.
       
       
11. 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. Open the Uncommitted changes panel in the Version Control sidebar.
   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.