Building a Web App with SQLPage - A Database-First Approach to Workout Tracking

--- ![[../../../99 - Meta/attachments/d448b4b337fbbf4e65bd08e3da1598b0_MD5.jpeg|500]] I recently embarked on a unique development journey that involved building a website almost entirely in SQL, powered by [SQLPage](https://sql-page.com/). While I work with SQL daily, the concept of building an entire application with it never occurred to me until I ran across a [post ](https://www.reddit.com/r/programming/comments/1ektv0z/sqlpage_building_data_uis_entirely_in_sql/)about it on Reddit. After reading about the application I thought it would be a good opportunity to rebuild my previous [[A Deep Dive into the 'Automate Your Gains' Workout App UI & Features|"Automate Your Gains"]] workout logger web app, which was originally developed using a combination of Google Apps Script and Google Sheets. That initial version, while functional, began to strain the capabilities of using a spreadsheet as a backend. The previous web app pushed the limits of what a Google Sheet and Apps Script should handle, leading me to seek a more robust system. With SQLPage, I could manage a proper SQLite database and implement DevOps automations. This included creating Python notebooks to automate tasks like script and database migrations, as well as generating comprehensive reports by reading database structures, schemas, and SQL file docstrings. The immediate question any sane developer might ask is: "Why on earth would you build a website in SQL?" This project wasn't about choosing the most difficult path. Instead, it was driven by the need to address the architectural shortcomings of my previous project. The goal was to transition from a fragile "good enough" solution to a robust, **data-centric architecture**. --- ## Transitioning to SQLPage: A Shift in Tooling and Workflow The move from Google Apps Script to SQLPage necessitated a fundamental shift in my development environment and approach. The SQLPage application was deployed locally on my homelab (a QNAP NAS) within a containerized Docker environment. Remote access to the application was then facilitated by setting up a reverse proxy and custom domain names, with Cloudflare proving to be particularly helpful in this setup. The transition involved a significant change in the underlying technologies and operational paradigms, as summarized in the table below: | Feature | Google Apps Script (v1) | SQLPage (v2) | | :--- | :--- | :--- | | **Platform** | Serverless (Google Cloud) | Containerized (Docker) | | **Database** | Google Sheet | SQLite | | **Backend Logic** | JavaScript (`.gs` files) | SQL (`.sql` files) | | **Data Integrity**| Manual validation in code | Enforced by DB schema | | **Core Operation** | `getValue()` -> `process()` -> `setValue()` | `SELECT ... JOIN ... WHERE ...` | --- ## Data Management: Imperative JavaScript vs. Declarative SQL In the Google Apps Script version, getting the details for a workout plan meant fetching data from *multiple* sheets (Templates, Exercises, Progression Models) and then manually "joining" them together in memory using JavaScript loops and filters. ### The Old Way: Manual Joins in Apps Script >[!important]- JS Code Snippet >```js >// main.js (Apps Script Backend) >// 1. Fetch entire sheets into memory (slow!) >const { > data: telData, > headerMap: telHeaderMap >} = getSheetDataWithHeadersAndMap(sheets > .templateExerciseListSheet, > "..."); >const { > data: uepData, > headerMap: uepHeaderMap >} = getSheetDataWithHeadersAndMap(sheets > .userExerciseProgressionSheet, > "..."); >// ... (fetching 3 other sheets) > >// 2. Manually filter the "TemplateExerciseList" data >const exercisesInTemplate = telData > .filter((row) => row[telHeaderMap > .TemplateID] === templateId); > >// 3. Loop through results and manually look up related data in other arrays >for (const telRow of > exercisesInTemplate) { > // ... > const userProgressionRow = > findRowInDataByCriteria(uepData, > uepHeaderMap, { > UserID: userId, > TemplateID: templateId, > // ... > }); > // ... (and so on for other related data) >} >``` This approach was prone to errors, often slow due to loading entire sheets into memory, and challenging to maintain. It placed the burden of managing data relationships on the application code itself. ### The New Way: Declarative Joins in SQLPage In contrast, the SQLPage version leverages a relational database (SQLite), where the database engine intrinsically handles data relationships through its schema. What previously required dozens of lines of complex JavaScript logic is now expressed in a single, declarative SQL query. >[!important]- SQL Code Snippet >```sql >-- index.sql (SQLPage) >-- The database engine handles fetching and joining related data efficiently. >SELECT > ex.exerciseName, > COALESCE(step.targetSets, 3) as targetSets, > -- ... (other columns) >FROM > dimExercisePlan AS plan >JOIN > dimExercise AS ex ON plan.exerciseId=ex.exerciseId >LEFT JOIN > dimProgressionModelStep AS step ON plan.progressionModelId=step.progressionModelId > AND plan.currentStepNumber=step.stepNumber >WHERE > plan.templateId=$template_id > AND plan.userId=$current_user_id >``` This fundamental shift from imperative "glue code" in JavaScript to declarative SQL is the core advantage of adopting a **database-first design**. The database engine efficiently handles the joining and retrieval of related data, significantly simplifying the application logic. --- ## Why This Approach Was Superior The decision to pivot to a database-first design with SQLPage yielded several significant advantages over the Google Apps Script and Google Sheets solution. ### 1. Enhanced Data Integrity By adopting a proper relational database (SQLite), the data became **aggressively structured**. The implementation of foreign key constraints, for instance, prevents logging a workout for an exercise that does not exist in the `dimExercise` table. This inherent database-level enforcement directly addresses the "garbage in, garbage out" issues that were prevalent with the less constrained Google Sheet version. ### 2. Powerful, Data-Aware Tooling Building the project on a solid, predictable database foundation enabled the creation of robust automation tools using Python. - **Automated Documentation:** A Python script was developed to introspect the `workouts.db` schema. This script also parses Javadoc-style comments directly from the SQL source code to generate comprehensive project documentation. - **Automated Migrations:** Another Python script manages all database schema changes. This robust, idempotent migration runner automatically backs up the database, runs new migration files transactionally, and tracks previously applied scripts in a `_migrations` table to prevent re-runs. All database changes are wrapped in transactions, ensuring that any failed migration is safely rolled back. Developing this level of professional-grade tooling around a Google Sheet, which lacks inherent database features, would have been nearly impossible. --- ## Conclusion: Right Tool, Right Job While building your next Software as a Service (SaaS) product entirely in SQL is likely not advisable—modern web frameworks are generally more suitable for that scale and complexity—a database-first approach proves highly effective in specific scenarios. If you are developing a **data-centric internal tool, a personal project, or a prototype**, and find yourself grappling with extensive "glue code" for data management, then considering a database-first architecture is highly recommended. This project underscored a crucial lesson: true efficiency isn't always about leveraging the most cutting-edge tools. Rather, it's about selecting the tool that aligns most directly with the fundamental problem you're trying to solve. For the SQLPage Workout Logger, the core of the problem was, and remains, the data itself. **Project Link:** **[View the full SQLPage Workout Logger project on GitHub](https://github.com/drusho/SQLPage-Workout-Logger)**