“Society must adapt to diamonds, diamonds don’t adapt to society.“
– Abhijit Naskar
The world is changed… I feel it in the water… I feel it in the earth… smell it in the air. On a totally unrelated note did you know December 2021 marks the 20th Anniversary of the Lord of the Rings films? Just in case you were looking for your reminder to go and watch those masterpieces again, this is that sign!
Seriously though – gone are the days when I would demonstrate database pipelines on 1 or two different technologies. Over the last 6 years I have walked people through database deployments using an array of CICD options; Jenkins, TeamCity, Octopus Deploy, Bamboo… and most recently I’ve spent most of my time on Azure DevOps. At times it can even feel like Azure DevOps is the only solution you’ll need, but increasingly it’s becoming obvious that isn’t the case and there are new, shiny providers who offer some amazing experiences and awesome functionality.
Now seems like the best time to explore 3 of the ones I’m coming across more and more – CircleCI, GitLab and GitHub Actions.
The interesting part of this is that I genuinely believe that this will be incredibly easy. Maybe I’m naïve but from the looks of all 3 they seem straight-forward, understandable… and of course I’ll be using Flyway in my pipeline, which is the easiest, cross-platform friendly solution to use for this.
Note: I will assume you have some familiarity with Flyway in this post, if you don’t read more about the capabilities of Redgate Deploy here.
The Setup
For this “challenge” (if I can call it that) I’m going to be using Flyway Desktop installed on my Windows laptop, GitHub as my Version Control system and 5 Azure SQL Databases: 2 for “Dev” & “Dev_Shadow” (from which I will generate 3 independent repos) and 3 environments for PROD_GitLab, PROD_CircleCI and PROD_GitHub respectively. The structure of the database will be the DMDatabase, unsurprisingly the database I use for pretty much everything I do on this blog.
Note: Everything I’m doing today uses SQL Server (well… Azure SQL Database) however everything here is cross OS – you can set up similar pipelines for everything from Oracle to PostgreSQL to CockroachDB if you would like!
Fortunately CTRL+C, CTRL+V exists, so I’ll only have to setup once and then I’ll just copy the files across into the other two repos; I set up a new private Repo in my GitHub specifically for GitLab but you could easily repeat these steps below separately for GitHub Actions or CircleCI:
I clone this down onto my Windows machine using Git Bash and then linked to and created my Flyway Desktop project (don’t know how? Try this!):
Link the development database and the shadow, generate the Schema Model and the Baseline Migration from DMDatabase_PROD_GitLab (I just grab the relevant JDBC connectors from the Azure Portal – this makes it much easier!), don’t forget to specify the list of Schemas, I did and it ain’t pretty (but is an easy enough fix)
Then I commit and push the schema model files and the baseline migration up into GitHub:
For good measure I also changed the DM_CUSTOMER table on the Dev environment and generated a new schema-model and migration change so I know what is going to be deployed to my “Prod” environments as part of this test:
Then after committing and pushing to my repo, I copied all of the files over to my GitHub and CircleCI repositories too:
A quick check of my other repos and everything seems good to go!
Principles
I’m setting up 3 separate pipelines in this post which will all effectively do the same thing, but for different “Prod” copies of databases, however when building and deploying in practice you will have a number of tasks you will want to accomplish in and around the process itself (such as really useful things like Unit Tests, Code Analysis etc.). To keep things simple I will be creating a 6th Database – the “Build” database which will act as our CI validation step and our process for all 3 pipelines will be:
- Invoking a Flyway Clean against the “Build” database – this step will remove every object on the database leaving it empty
- Invoking a Flyway Migrate against the “Build” database – this step will build the database from scratch to validate our baseline script and any further migrations build successfully
- Invoking a further Flyway Migrate against our respective “Prod” database, to deploy the latest scripts we have generated.
GitLab
After following the Setup instructions above, in GitLab I need to create a New Project and I want it to Build/Deploy from my GitHub repo, so I pick “Run CI/CD for external repository“
Fortunately it’s very easy to connect directly from GitHub, but you will have to generate a Personal Authentication Token which you can do by going to https://github.com/settings/tokens and then authorizing the main repo you want to build from – for me this is GitLab_Flyway:
Painless! From here I select CI/CD template and because I’m starting from scratch I’m going to use the starter 3 stage template:
It has a rather neat layout and is pretty darn easy to get up and running with:
I may have tried several combinations to get the Flyway Docker container up and running but essentially the code I ended up running for my pipeline was:
stages: # List of stages for jobs, and their order of execution
- build
- deploy
variables:
userName: "MyUserName"
password: "MyPassword"
prodJDBC: "jdbc:[TheJDBCConnectionToTheProdDBYoureUsing]"
ciJDBC: "jdbc:[TheJDBCConnectionToTheBuildDBYoureUsing]"
migrationPath: $CI_PROJECT_DIR
build-job: # This job runs in the build stage, which runs first.
image:
name: flyway/flyway:latest-alpine
entrypoint: [""]
stage: build
script:
- flyway clean -url="$ciJDBC" -user="$userName" -password="$password" -locations="filesystem:$migrationPath"
- flyway migrate -url="$ciJDBC" -user="$userName" -password="$password" -locations="filesystem:$migrationPath"
deploy-job: # This job runs in the deploy stage.
image:
name: flyway/flyway:latest-alpine
entrypoint: [""]
stage: deploy # It only runs when *both* jobs in the test stage complete successfully.
script:
- flyway migrate -url="$prodJDBC" -user="$userName" -password="$password" -locations="filesystem:$migrationPath" -baselineOnMigrate=true -baselineVersion="MyBaselineVersion"
It was actually quite easy to spot where I had failed in previous runs and GitLab breaks things up quite nicely for us to see:
Some interesting things I noted using this setup:
- Because we are deploying up stream to an environment that hasn’t been deployed to with Flyway before you have to pass in the -baselineOnMigrate switch, what was interesting though is that I also needed to specify the -baselineVersion, otherwise it tried to baseline V1, which of course did not exist as my baseline was named something completely different (V001_DateTimeStamp_blahlablah)
- The entrypoint is specified as it is because it drops you right at the Flyway executable so you can issue the Flyway commands – without this it doesn’t work
- You can ABSOLUTELY pass your variables in (like *cough* password and username) in a much more secure way through GitHub using variables, but this was a great start for me
- To pass in the files using a hosted repo, I had to use the environment variable $CI_PROJECT_DIR and that’s where the repo is checked out to, where your migrations are
But it ultimately ended up in what I was expecting – the database was migrated using the Flyway command steps:
Conclusion
Is it possible to setup a nice easy pipeline from Dev -> Prod with Redgate Deploy and GitLab? Yes, absolutely it is, and you can build out the pipeline in whatever fashion you want. Thankfully, the Docker container makes things much much easier!
Now, let’s see how we get on with GitHub Actions!
PlantBasedSQL 