How to Use Databases Inside GitHub Actions

GitHub Actions is a platform that automates the build, test and deployment pipeline of your GitHub projects. Since its introduction, GitHub Actions has been steadily gaining popularity among developers. Not only do GitHub Actions help automate the test and integration cycles, but it can also drastically shorten them, accelerating developer feedback as part of the process.

GitHub Actions are defined as part of your GitHub project repository and automatically triggered on specific events, such as a pull request or push to the repo. As the name suggests, GitHub will run various “actions” as part of the execution. One significant benefit of GitHub Actions is that there are predefined actions from GitHub and an entire marketplace of third-party GitHub Actions that can be used, allowing developers to write their own and share them with the world. For an overview of commonly used GitHub Actions, check out “8 GitHub Actions for Setting Up Your CI/CD Pipelines.” For more of a tutorial on how to build your first GitHub Actions pipeline yourself, you may want to check out “Building a To-Do App using GitHub Actions, Playwright, Next.js.”

GitHub Actions and Databases

Using GitHub Action Services

When it comes to using databases inside your GitHub Actions (GHA), service containers can be used to spin up one or more “services” that will then be available for the particular job.

👁 Image

While services are great for getting a database or similar resource up and running, there are a couple of things that need to be kept in mind:

• The container runtime cannot be chosen, but defaults to Docker. That in itself may not be a big deal but it’s still something to be aware of.
• None of the GHA service container examples as of today show how to use a volume. Although persistency of the database is usually not required during or across multiple GHA runs, many database container images do provide additional functionality exposed via a volume, such as running initial data model setup scripts automatically, for example.
• Even though health check options have been provided to the container runtime `(–health-interval 10s; –health-retries 10; …)`, GitHub adds its own health check delays on top of that, unnecessarily delaying the overall pipeline execution:

👁 Image

GitHub Action services’ implicit delay for checking whether the service is ready.

Manually Running Your Database Container

Of course, given that GHA allows you to run anything on the command line shell directly, there is practically no limit to what you can do yourself. That includes starting your own container if you wish to do so.

👁 Image

There are a couple of things to note here:

1. The new container is started using Podman instead of Docker
2. A volume is attached to the container via `-v ./datamodel:/container-entrypoint-initdb.d`. This particular database image provides a setup mechanism as part of the container startup. Once the database has been brought up inside the container, the container then automatically executes any scripts present in `/container-entrypoint-initdb.d`. This example uses this mechanism to initialize a data model. As you can see in the GHA definition above as a last step, the SQLcl command line prompt no longer selects a string literal `Hey there!` but instead selects `country_code, official_name, population` from the `countries` table inside the database where the country name happens to be `Austria`.
   A side note here, note the leading `./` in front of `datamodel`. It is paramount to specify a relative or absolute filesystem path. If you omit the `./`, the container runtime will automatically create an (empty) named volume called `datamodel`, instead of mounting the local `datamodel` folder. You can exchange the `./` for `$PWD` or `${{ github.workspace }}` for better readability, if you like.
3. Instead of relying on the GHA health check, the script checks every 1 second (indicated by `sleep 1` in the script) whether the container is ready for use. This can be quite important for your overall job execution duration. Remember above where we saw that GitHub Actions automatically added more and more delay for checking the health of the container, the last one being a 32-second delay? What would happen if the database came up just 2 seconds after the delay? Well, nothing at all, the execution would be waiting another 30 seconds before checking the container readiness again. Here, however, there is not such a long delay, at a maximum the job will incur a delay of just 1 additional second. This is not to be underestimated; the difference in overall execution time can be quite significant.

Using a GitHub Action

As mentioned earlier, GitHub has a marketplace where any third-party developer can publish their own GitHub Actions. You will find several actions also for databases, including the Oracle Database that we have used so far. The action is called setup-oracle-free and wraps around the same container image that we have used before.

The action provides the best of both worlds: simple and succinct syntax to spin up a container, but without having to cut back on the functionality provided by the container. Using the action, the same steps as above can be accomplished with just this:

👁 Image

Using GitHub Actions Secrets

Any post on GitHub Actions that showcases the use of credentials or otherwise sensitive information should never be completed without talking about GitHub Actions secrets. The previous GHA jobs all had one thing in common: They used a hardcoded username and password for the database user, in both cases `test`. That’s maybe OK for quick-and-dirty construction of your pipeline but you should never, ever use a plain username/password or any other sensitive information (access tokens, etc.) as part of your GitHub Actions definition, as, remember, the GitHub Actions definition itself is part of the repository and readable to anyone.

For that reason, GitHub introduced the concept of secrets, which are read-only, encrypted variables in the repository that can only be read by GHA if you explicitly include the secret in a workflow. The important part here is that secrets are defined inside the settings of a GitHub repository and hence not available to anybody but only users with admin rights. Furthermore, once set, the secret can never, ever be seen again. If you want to change it or have a typo, you must update the secret with a new value that then again will be invisible going forward.

To add one or more secrets to your GitHub.com repository, head to Settings → Security → Secrets and variables → Actions. There, click on New repository secret.

👁 Image

Enter a name with which you will reference the secret later on and the secret value itself. Note: This is the only time you will see the secret value, once you hit Add secret, it will no longer be readable to you.

👁 Image

👁 Image

Once the secrets are created, in this case, one for the application user (`APP_USER`) and one for the application user password (`APP_USER_PASSWORD`), they can be referenced inside GHA jobs using `${{ secrets.<SECRET NAME> }}`, where `<SECRET NAME>` is the name of the secret that should be used:

👁 Image

👁 Image

GitHub Actions run with GitHub Action secrets instead of hard-coded username and password.

Notice how the SQLcl CLI invocation no longer shows `${{ secrets.APP_USER }}/${{ secrets.APP_USER_PASSWORD }}@localhost/FREEPDB1` as defined in the action’s definition itself, but instead `***/***@localhost/FREEPDB1`. This is done by GitHub Actions automatically so that the values of secrets are not leaked into the GitHub Actions run logs.

Conclusion

GitHub Actions is a powerful platform to arm your GitHub projects with their own CI/CD pipelines. Spinning up databases inside GitHub Actions is officially documented via GitHub Actions services, yet they can also be created either manually or via the many third-party actions on the marketplace.