I’m currently working on a project that relies heavily on a relational database, and so I’ve found myself having to pick up MySQL in a short span of time.
With that level of reliance also comes a minimum requirement of a level of certainty that base-level code is correct, and so unit tests was the obvious thing to write together with those base functions.
Local tests were easy enough; most things I could solve in a once-and-for-all way by running a system command to fix a local MySQL configuration or just add a script to the project. However, getting a MySQL instance to run server-side GitHub Actions took me far longer than whatever I’ve done locally, and so that’s why I’m staying up an extra hour to write this quick guide so the next person doing the exact same thing doesn’t have to go through the 50-odd failed GitHub Action runs I had to sit through.
So let’s get into it.
I know this might be skipping too far ahead, but this is my entire working workflow file. I’ll break it down and go through each bit:
.github/workflows/cool-name.yml)on:
push:
branches:
- master
- dev
env:
MYSQL_DATABASE: test_db
DB_USER: root
DB_PASSWORD: root
RESTORE: .test.sql
jobs:
check-database-package-json-scripts:
runs-on: ubuntu-20.04
steps:
- name: 🔥 Initialize MySQL
run: sudo systemctl start mysql.service
- name: 📌 Initialize first database
run: |
mysql -e 'CREATE DATABASE ${{ env.MYSQL_DATABASE }};' \
-u${{ env.DB_USER }} -p${{ env.DB_PASSWORD }}
- name: 🚀 Boost user
run: |
mysql -e "ALTER USER '${{ env.DB_USER }}'@'localhost' \
IDENTIFIED WITH mysql_native_password BY 'root';" \
-u${{ env.DB_USER }} -p${{ env.DB_PASSWORD }}
- name: 🍺 Check out repository code
uses: actions/checkout@v3
- name: 🧶 Install yarn
run: yarn install --frozen-lockfile
- name: 📦 run tests
working-directory: ./packages/database
run: |
MYSQL_RESTORE_SOURCE=$RESTORE \
MYSQL_USERNAME=$DB_USER \
MYSQL_PASSWORD=$DB_PASSWORD \
MYSQL_ACTIVE_DATABASE=$MYSQL_DATABASE \
yarn test
on:
push:
branches:
- master
- dev
These define when the entire workflow file is run. Here, it’s ran
after every time someone pushes to branches master or dev.
Note that for it to work as expected it must exist on both the
master and dev branch.
env:
MYSQL_DATABASE: test_db
DB_USER: root
DB_PASSWORD: root
RESTORE: .test.sql
These are just like variables in a programming language. They store values and can be used again later.
To refer to the environment variable DB_USER, we use ${{ env.DB_USER }} in our later code.
jobs:
check-database-package-json-scripts:
runs-on: ubuntu-20.04
steps:
- name: 🔥 Initialize MySQL
run: sudo systemctl start mysql.service
- name: 📌 Initialize first database
run: |
mysql -e 'CREATE DATABASE ${{ env.MYSQL_DATABASE }};' \
-u${{ env.DB_USER }} -p${{ env.DB_PASSWORD }}
- name: 🚀 Boost user
run: |
mysql -e "ALTER USER '${{ env.DB_USER }}'@'localhost' \
IDENTIFIED WITH mysql_native_password BY 'root';" \
-u${{ env.DB_USER }} -p${{ env.DB_PASSWORD }}
- name: 🍺 Check out repository code
uses: actions/checkout@v3
- name: 🧶 Install yarn
run: yarn install --frozen-lockfile
- name: 📦 run tests
working-directory: ./packages/database
run: |
MYSQL_RESTORE_SOURCE=$RESTORE \
MYSQL_USERNAME=$DB_USER \
MYSQL_PASSWORD=$DB_PASSWORD \
MYSQL_ACTIVE_DATABASE=$MYSQL_DATABASE \
yarn test
Here, jobs is a keyword that tells the workflow handler to execute
the following lines of code.
check-database-package-json-scripts is a name that I chose, to label
the group of steps that will be ran under it.
runs-on is a keyword that specifies to GitHub Actions what operating
system you’d like to run the workflow on. Here, I’ve chosen to run it
with Ubuntu 20.04 (an operating system much like MacOS and Windows,
only more modular).
For each bullet point under steps, the contents of run will be
executed. The name is arbitrarily chosen.
uses is a keyword that is immediately followed by a published action
name. So for example the line that says
- name: 🍺 Check out repository code
uses: actions/checkout@v3
will be running borrowed code from the published package GitHub Checkout.
And finally working-directory is a keyword that specifies, relative
to the root of your project, where you want to run a command from.
So for the first step:
- name: 🔥 Initialize MySQL
run: sudo systemctl start mysql.service
I’m starting the MySQL service/daemon. You can have a look at GitHub’s own Virtual Environment documentation. There, they record all the available operating systems and their respective pre-installed apps.
On Ubuntu 20.04 (my workflow operating system of choice), MySQL does come pre-installed, but it doesn’t come pre-enabled. Quoting from GitHub’s docs:
MySQL service is disabled by default. Use the following command as a part of your job to start the service:
sudo systemctl start mysql.service
The next step:
- name: 📌 Initialize first database
run: |
mysql -e 'CREATE DATABASE ${{ env.MYSQL_DATABASE }};' \
-u${{ env.DB_USER }} -p${{ env.DB_PASSWORD }}
is a measure I took in case the code I want to test is not resilient enough to handle initializing a new database on MySQL.
Then the last MySQL-related step:
- name: 🚀 Boost user
run: |
mysql -e "ALTER USER '${{ env.DB_USER }}'@'localhost' \
IDENTIFIED WITH mysql_native_password BY 'root';" \
-u${{ env.DB_USER }} -p${{ env.DB_PASSWORD }}
Is to allow all following code that uses the existing credentials to freely manipulate all databases within the MySQL instance.
A for-instance is that if you tried to import a .sql file into your
database with this shell command
mysql -u root -p"root" test_do < some_file.sql
Then it will complain with some cryptic error that’s unrelated to how it’s meant to be solved.
And yeah. That pretty much sums up how to run a MySQL instance on
GitHub Actions. If you’d like to get a full picture of what’s going
on, such as what tests I run with the last step, head over to the
project’s repository.
Notably, here’s a direct link to the discussed yaml file.
If you’ve any questions at all related to this subject, please shoot me an email at brew4k@gmail.com. I’ll be glad to see how I can help.