Number of ways to setup database in Github actions

short answer: 5̶ (updated: 4)

long answer: in recent CI setup with GitHub actions, I have trouble to find out the best way to setup test databases in Github actions. the options we have right now are:

create a custom action

use custom action to setup database and use that action in your workflow.

this way is the most tedious way, you are required to create a Github repo to get it works. and you are required to understand the file structure of the Github hosted runner and docker

use runner job and database service

with this way, setup a mysql service and map the database port to host

jobs:
  test-host:
    runs-on: ubuntu-latest
    services:
      mysql:
        image: mysql:5.7
        env:
          MYSQL_ROOT_PASSWORD: password
        ports:
        - 32574:3306
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=3
    steps:
    - name: Verify MySQL connection from host
      run: |
        sudo apt-get install -y mysql-client
        mysql --host 127.0.0.1 --port 32574 -uroot -ppassword -e "SHOW DATABASES"

for the above example, database host is localhost (127.0.0.1) and port is 32574

use docker job and database service

this way is similar to the previous, only different is your action’s “steps” is in the same network with your database service, hence no ports mapping is needed

jobs:
  test-container:
    runs-on: ubuntu-latest
    container: ubuntu
    services:
      mydb:
        image: mysql:5.7
        env:
          MYSQL_ROOT_PASSWORD: password
        options: --health-cmd="mysqladmin ping" --health-interval=10s --health-timeout=5s --health-retries=5
    steps:
    - name: Verify MySQL connection from container 
      run: |
        apt-get update
        apt-get install -y mysql-client
        mysql --host mydb -uroot -ppassword -e "SHOW DATABASES"

the database host name is same as the service name and port is the default 3306

u̵s̵e̵ ̵t̵h̵e̵ ̵d̵a̵t̵a̵b̵a̵s̵e̵ ̵i̵n̵s̵t̵a̵l̵l̵e̵d̵ ̵o̵n̵ ̵h̵o̵s̵t̵ (15/May updated: this option has been disabled by github since 13/Mar/2020)

if your database is one of the preinstalled software on the runner, this is the simplest, fastest (cheaper!) way. for mysql, these is all you need to do

- name: Setup Runner Database
  run: |
    mysql -uroot -h127.0.0.1 -proot -e 'CREATE DATABASE IF NOT EXISTS db1;'
    mysql -uroot -h127.0.0.1 -proot -e 'CREATE DATABASE IF NOT EXISTS db2;'

no mysql setup required, the default root user password is “root” and mysql version is 5.7.xx (it could be a deal breaker to you, if you need other version)

use docker-compose in your workflow

I did not try this method, it could be the slowest way (and expensive!) because it involves Github action build time+ docker-compose build time