Database development & testing data

It is helpful when developing, and testing, to have consistent data available in the database. To do this set data can be added to the repository and loaded into the database as part of the setup.

Loading the data

We'll store the data as SQL queries in a specific data file which we'll load after the database migrations have run, but only if the all the migrations have been run i.e. we'll only load the data into an empty database.

To load the data we'll use a environment variable DATABASE_DATA to indicate which data file to load (or not to if it isn't present) and add the following to backend/src/backend/lib/database.py,

from typing import Optional

async def create_database(url: str, data_file: Optional[str]) -> Database:
    ...
    initial_migration = await _setup_schema(db)
    if initial_migration == -1 and data_file is not None:
        await _load_database_data(db, data_file)
    return db

async def _load_database_data(db: Database, data_file: str) -> None:
    with open(data_file, "r") as file_:
        for query in file_.read().split(";"):
            await db.execute(query)

We also need to adapt the create_app function in src/backend/run.py as follows,

def create_app() -> None:
    ...

    @app.before_serving
    async def startup() -> None:
        app.db = await create_database(
            os.environ["DATABASE_URL"],  os.environ.get("DATABASE_DATA"),
        )

This allows the data file location to be specified in the configuration,

backend/development.env

DATABASE_DATA="db/development_data.sql"
...

backend/test.env

DATABASE_DATA="db/test_data.sql"
...

backend/ci.env

DATABASE_DATA="db/test_data.sql"
...

Example data

An example password hash can be created using bcrypt,

bcrypt.hashpw(b"password", bcrypt.gensalt(14))

which can be used to create development and testing data,

backend/db/development_data.sql

INSERT INTO members (email, password_hash)
     VALUES ('member@tozo.invalid', '$2b$14$bziHHTsmQixWO30gBuNOGOiP6A1oSG97gOiDLyyJqmLUhUQE6aL96');
INSERT INTO todos (due, member_id, task) VALUES (now(), 1, 'Task');

backend/db/test_data.sql

INSERT INTO members (email, password_hash)
     VALUES ('test@tozo.invalid', '$2b$14$bziHHTsmQixWO30gBuNOGOiP6A1oSG97gOiDLyyJqmLUhUQE6aL96');
INSERT INTO todos (due, member_id, task) VALUES (now(), 1, 'Task');