Skip to content

Data models

The data processed by the app is best represented by models, as this ensures that the correct structure and types are used. To achieve this I like to make use of Pydantic's dataclass as it leads to very clear code and integrates directly with Quart-Schema's validation.

Question

I prefer to write the SQL queries rather than use an ORM, however if you prefer ORM is a great library that integrates well with Databases.

Todo models

The Todo model should be split into a model for the data entered by the users and a model for the complete todo (including data calculated by the server). Spliting the model allows validation of data sent to and returned by the server.

The models should be added to backend/src/backend/models/todo.py, and are,

from dataclasses import dataclass
from datetime import datetime
from typing import Optional

@dataclass
class TodoData:
    complete: bool
    due: Optional[datetime]
    task: str

@dataclass
class Todo(TodoData):
    id: int

In addition we should add functions to backend/src/backend/models/todo.py to manipulate the todo data in the database,

from dataclasses import asdict

from databases import Database

async def insert_todo(db: Database, data: TodoData, member_id: int) -> Todo:
    result = await db.fetch_one(
        """INSERT INTO todos (complete, due, member_id, task)
                VALUES (:complete, :due, :member_id, :task)
             RETURNING id, complete, due, task""",
        values=asdict(data) | {"member_id": member_id},
    )
    return Todo(**result)

async def select_todos(
    db: Database, member_id: int, complete: Optional[bool] = None
) -> list[Todo]:
    if complete is None:
        query = """SELECT id, complete, due, task
                     FROM todos
                    WHERE member_id = :member_id"""
        values = {"member_id": member_id}
    else:
        query = """SELECT id, complete, due, task
                     FROM todos
                    WHERE member_id = :member_id AND complete = :complete"""
        values = {"member_id": member_id, "complete": complete}
   return [Todo(**row) async for row in db.iterate(query, values)]

async def select_todo(db: Database, id: int, member_id: int) -> Optional[Todo]:
    result = await db.fetch_one(
        """SELECT id, complete, due, task
             FROM todos
            WHERE id = :id AND member_id = :member_id""",
        values={"id": id, "member_id": member_id},
    )
    return None if result is None else Todo(**result)

async def update_todo(
    db: Database, id: int, data: TodoData, member_id: int
) -> Optional[Todo]:
    result = await db.fetch_one(
        """UPDATE todos
              SET complete = :complete, due = :due, task = :task
            WHERE id = :id AND member_id = :member_id
        RETURNING id, complete, due, task""",
        values=asdict(data) | {"id": id, "member_id": member_id},
    )
    return None if result is None else Todo(**result)

async def delete_todo(db: Database, id: int, member_id: int) -> None:
    await db.execute(
        "DELETE FROM todos WHERE id = :id AND member_id = :member_id",
        values={"id": id, "member_id": member_id},
    )

Member model

The member model is simpler, and should be added to backend/src/backend/models/member.py,

from dataclasses import dataclass
from datetime import datetime
from typing import Optional

@dataclass
class Member:
    id: int
    email: str
    password_hash: str
    created: datetime
    email_verified: Optional[datetime]

In addition we should add these functions to backend/src/backend/models/member.py to manipulate the member data in the database,

from databases import Database

async def select_member_by_email(db: Database, email: str) -> Optional[Member]:
    result = await db.fetch_one(
        """SELECT id, email, password_hash, created, email_verified
             FROM members
            WHERE LOWER(email) = LOWER(:email)""",
        values={"email": email},
    )
    return None if result is None else Member(**result)

async def select_member_by_id(db: Database, id: int) -> Optional[Member]:
    result = await db.fetch_one(
        """SELECT id, email, password_hash, created, email_verified
             FROM members
            WHERE id = :id""",
        values={"id": id},
    )
    return None if result is None else Member(**result)

async def insert_member(db: Database, email: str, password_hash: str) -> Member:
    result = await db.fetch_one(
        """INSERT INTO members (email, password_hash)
                VALUES (:email, :password_hash)
             RETURNING id, email, password_hash, created, email_verified""",
        values={"email": email, "password_hash": password_hash},
    )
    return Member(**result)

async def update_member_password(
    db: Database, id: int, password_hash: str
) -> None:
    await db.execute(
        "UPDATE members SET password_hash = :password_hash WHERE id = :id",
        values={"id": id, "password_hash": password_hash},
    )

async def update_member_email_verified(db: Database, id: int) -> None:
    await db.execute(
        "UPDATE members SET email_verified = now() WHERE id = :id",
        values={"id": id},
    )

Email casing

According to the specification the local part of an email address (to the left of the @) is case sensitive, whereas the domain part (to the righ of the @) is case insensitive. In practice, however, email addresses are considered case insensitive.

To account for this apparent contradiction, we store the email address in the casing provided by the user as this ensures any case sensitivity is preserved when sending them emails whilst lowercasing the email for login checks thereby ensuring that email addresses are case insensitive in practice.