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,
DATABASE_DATA="db/development_data.sql"
...
DATABASE_DATA="db/test_data.sql"
...
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,
INSERT INTO members (email, password_hash)
VALUES ('member@tozo.invalid', '$2b$14$bziHHTsmQixWO30gBuNOGOiP6A1oSG97gOiDLyyJqmLUhUQE6aL96');
INSERT INTO todos (due, member_id, task) VALUES (now(), 1, 'Task');
INSERT INTO members (email, password_hash)
VALUES ('test@tozo.invalid', '$2b$14$bziHHTsmQixWO30gBuNOGOiP6A1oSG97gOiDLyyJqmLUhUQE6aL96');
INSERT INTO todos (due, member_id, task) VALUES (now(), 1, 'Task');