Testing database-connected code
Getting GitHub actions and Tox to work well with databases takes a little extra work. This shows how to get MariaDB/MySQL working. The principles should be the same for other databases, like PostgreSQL. This project has a working example of this.
First, include MariaDB as a service by adding this under jobs
in
.github/workflows/commit.yml
and .github/workflows/pull.yml
.
Note that the MYSQL_DATABASE: test
does not refer to your database.
services:
mysql:
image: mariadb:latest
env:
MYSQL_ROOT_PASSWORD: root
MYSQL_DATABASE: test
ports:
- 3306:3306
options: \
--health-cmd="mysqladmin ping" \
--health-interval=10s \
--health-timeout=5s \
--health-retries=3
Then, add a step to test the MariaDB connection. Add it before other steps so the workflow fails early.
-
name: Initialize MariaDB
run: |
mysqladmin --host=127.0.0.1 ping
Then, add the SQL schema and rows needed for the tests to tests/resources/testdb.sql
.
There’s no security relevance here, so we can just use the root throughout.
Warning
Make sure the name of your test database won’t ever conflict with a real database. Otherwise, you’ll lose your database.
Then, in tox.ini
, mysql
to whitelist_externals
.
Then add this to the commands
. It’s likely to be fast, so consider adding it as the first step.
mysql -e 'SOURCE tests/resources/testdb.sql;' --host=127.0.0.1 --user=root --password=root
Oddly, the -host=127.0.0.1
is important; “localhost” or leaving it as default won’t work.
You may also want to execute a DROP DATABASE
query as the last command, but that may not be needed.
Your SQL file might start with something like:
DROP DATABASE IF EXISTS myfakedatabase;
CREATE DATABASE myfakedatabase CHARACTER SET = 'utf8mb4' COLLATE 'utf8mb4_unicode_520_ci';
DROP USER IF EXISTS 'myfakeuser'@'localhost';
CREATE USER 'myfakedatabase'@'localhost' IDENTIFIED BY 'fakeuser123';
GRANT SELECT, INSERT, UPDATE, DELETE ON myfakedatabase.* TO 'myfakeuser'@'localhost';
USE valartest;
The SQL is executed using the root user, but your code may expect something different.