Skip to content

Database: Tooling

The database is used from the backend code, and so the tooling to control the database is best placed with the backend tooling. Firstly we need commands to delete/drop the database (and user) and then to create them. This will be invoked via,

Run this command in backend/

poetry run recreate_db

by adding the following to the backend/pyproject.toml file,

[tool.poetry.scripts]
recreate_db = "scripts:recreate_db"
...

and to the backend/scripts.py file,

from subprocess import call, DEVNULL

def create_db(database: str = "tozo") -> None:
    call(
        ["psql", "-U", "postgres", "-c", "CREATE USER tozo LOGIN PASSWORD 'tozo' CREATEDB"],
        stdout=DEVNULL,
        stderr=DEVNULL,
    )
    call(
        ["psql", "-U", "postgres", "-c", f"CREATE DATABASE {database}"],
        stdout=DEVNULL,
        stderr=DEVNULL,
    )

def drop_db(database: str = "tozo") -> None:
    call(
        ["psql", "-U", "postgres", "-c", f"DROP DATABASE {database}"],
        stdout=DEVNULL,
        stderr=DEVNULL,
    )
    call(
        ["psql", "-U", "postgres", "-c", "DROP USER tozo"],
        stdout=DEVNULL,
        stderr=DEVNULL,
    )

def recreate_db(database: str = "tozo") -> None:
    drop_db(database)
    create_db(database)

Note

call is used here rather than check_output used elsewhere as these calls shouldn't error on failure. This is to allow the command to succeed if there is no existing database to drop and to allow the individual commands (create_db, drop_db) to be called multiple times without failing.

Testing locally

It can be tempting to try and mock the database in our tests, however in my experience this reduces the utility of the tests and takes a huge amount of effort. Instead we'll test against an actual postgres database. As we want our tests to be deterministic we should create a new clean database before running any tests, we can do this by using the recreate_db function in the existing test function in backend/scripts.py so that it becomes,

def test() -> None:
    recreate_db("tozo_test")
    _check_call_quiet(["pytest", "tests/", *sys.argv[1:]])

Note

The database name is different, tozo_test instead of tozo so as to prevent tests changing our development database.

Testing in CI

In CI we will use a database service to test against. As it is created for us so the recreate_db command isn't required. Therefore we need a new script command, test_ci defined in backend/pyproject.toml,

[tool.poetry.scripts]
test_ci = "scripts:test_ci"
...

and in backend/scripts.py,

def test_ci() -> None:
    _check_call_quiet(["pytest", "tests/", *sys.argv[1:]])