PySide6 in Practice #2 The SQLite Data Layer: Repository Pattern and Migrations

With the skeleton from part 1 standing, we now build the app’s memory: the data layer that stores habits and daily check records. The layer has one design goal — SQL must not leak outside this folder. Once no UI code contains a SQL string, the screen work from part 3 onward becomes remarkably simple.

Why SQLite, and where the file goes #

For a local desktop app, SQLite is the de facto default store. No server, one file is the whole database, and Python ships sqlite3 — zero dependencies. Compared to saving JSON files: growing data does not rewrite the whole file, corruption risk under tangled access is far lower, and part 4’s statistics can be pushed into SQL aggregation.

The file’s location is an easy place to get wrong. Next to the source folder is convenient in development, but installed apps often have no write permission in their install directory. The answer is the OS-designated app-data folder — and Qt knows the path.

# src/daily/data/paths.py
from pathlib import Path

from PySide6.QtCore import QStandardPaths


def db_path() -> Path:
    base = QStandardPaths.writableLocation(
        QStandardPaths.StandardLocation.AppDataLocation
    )
    folder = Path(base)
    folder.mkdir(parents=True, exist_ok=True)
    return folder / "daily.db"

On macOS this resolves under ~/Library/Application Support/daily/, on Windows under AppData, on Linux under ~/.local/share/. Setting setApplicationName in part 1 pays off here — Qt names the folder with it.

The schema: two tables are enough #

CREATE TABLE habits (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    created_at TEXT NOT NULL,          -- ISO date string
    archived   INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE checks (
    habit_id INTEGER NOT NULL REFERENCES habits(id) ON DELETE CASCADE,
    day      TEXT NOT NULL,            -- 'YYYY-MM-DD'
    PRIMARY KEY (habit_id, day)
);

Three design calls worth noting. First, a check is the bare fact “habit X was done on day D,” so the composite primary key (habit_id, day) is natural and blocks same-day duplicates at the schema level. Second, deleting a habit uses the archived flag instead of a real DELETE — statistics depend on past habits, so a user’s “delete” should usually mean “archive.” Third, dates are stored as ISO strings (YYYY-MM-DD), the format where SQLite’s string comparison doubles as date comparison.

The repository: SQL’s one and only residence #

The repository is a class exposing “the things you can do to storage” as methods. Callers never learn SQL exists.

# src/daily/data/repository.py
import sqlite3
from datetime import date
from pathlib import Path

from daily.core.habits import Habit

_SCHEMA_VERSION = 1


class HabitRepository:
    def __init__(self, path: Path) -> None:
        self._conn = sqlite3.connect(path)
        self._conn.execute("PRAGMA foreign_keys = ON")
        self._migrate()

    # --- habits ---
    def add_habit(self, name: str) -> int:
        cur = self._conn.execute(
            "INSERT INTO habits (name, created_at) VALUES (?, ?)",
            (name, date.today().isoformat()),
        )
        self._conn.commit()
        return cur.lastrowid

    def active_habits(self) -> list[Habit]:
        rows = self._conn.execute(
            "SELECT id, name FROM habits WHERE archived = 0 ORDER BY id"
        ).fetchall()
        return [Habit(id=r[0], name=r[1]) for r in rows]

    def archive_habit(self, habit_id: int) -> None:
        self._conn.execute("UPDATE habits SET archived = 1 WHERE id = ?", (habit_id,))
        self._conn.commit()

    # --- checks ---
    def set_checked(self, habit_id: int, day: date, checked: bool) -> None:
        if checked:
            self._conn.execute(
                "INSERT OR IGNORE INTO checks (habit_id, day) VALUES (?, ?)",
                (habit_id, day.isoformat()),
            )
        else:
            self._conn.execute(
                "DELETE FROM checks WHERE habit_id = ? AND day = ?",
                (habit_id, day.isoformat()),
            )
        self._conn.commit()

    def checked_days(self, habit_id: int) -> set[str]:
        rows = self._conn.execute(
            "SELECT day FROM checks WHERE habit_id = ?", (habit_id,)
        ).fetchall()
        return {r[0] for r in rows}

Habit is a pure dataclass living in core (just id and name). The UI calls repository methods; the repository returns core types. The one-way dependency drawn in part 1 becomes real code here.

Using only parameter binding (?) and never string formatting for SQL is the same principle stressed in the SQLAlchemy series. And at this scale, raw sqlite3 without an ORM is the appropriate technology — bringing SQLAlchemy to two tables is overkill.

Migrations: schema versioning with user_version #

A shipped app’s data layer carries an obligation web servers do not have: upgrading the old-version database file already sitting on a user’s machine without breaking it. SQLite offers PRAGMA user_version — an integer stored in the DB file — made for exactly this.

    def _migrate(self) -> None:
        version = self._conn.execute("PRAGMA user_version").fetchone()[0]
        if version < 1:
            self._conn.executescript("""
                CREATE TABLE habits (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    created_at TEXT NOT NULL,
                    archived INTEGER NOT NULL DEFAULT 0
                );
                CREATE TABLE checks (
                    habit_id INTEGER NOT NULL REFERENCES habits(id) ON DELETE CASCADE,
                    day TEXT NOT NULL,
                    PRIMARY KEY (habit_id, day)
                );
            """)
        # if version < 2: ...  future schema changes go here
        self._conn.execute(f"PRAGMA user_version = {_SCHEMA_VERSION}")
        self._conn.commit()

The mechanics are simple: a fresh install has version 0 and gets the whole schema; an existing user’s DB applies only the steps above its stored version, in order. One rule to keep: after shipping, never edit an existing step — add new if version < N: blocks only. It is Alembic in miniature, and for a local app, exactly the right size.

Wiring it into the app #

# added to main() in app.py
from daily.data.paths import db_path
from daily.data.repository import HabitRepository

repo = HabitRepository(db_path())
window = MainWindow(repo)   # MainWindow hands repo down to its pages

Passing the repository down through constructors (dependency injection) instead of making it global is foreshadowing for part 7’s tests — a test just injects a repository built on a temp file path.

Summary #

• SQLite is the default store for local apps, and the DB file lives in the app-data folder QStandardPaths reports.
• The schema is two tables, habits and checks. Duplicates are blocked by a composite primary key, and delete is replaced with an archived flag.
• SQL exists only inside the repository. The UI calls methods and receives core types.
• Schema versions are managed with PRAGMA user_version, and changes only ever add new version blocks. A shipped app’s data is the user’s property.
• The repository is constructor-injected. Next part connects this data to the Today screen’s custom model.