Simplified Architecture for Small Websites: Mildred League Moves to SQLite

As fantasy football season rolled around again this year, I found myself dreading having to make some planned Mildred League site updates. This is no way for a proper programmer to live, of course, so something had to be done about it (although I am definitely guilty of rewriting this particular site too many times instead of working on new projects). It also happens to fit in with a larger goal of mine to reduce both:

• reliance on external services that we as individuals don’t own, whether it’s a subscription to Apple Music or a hosting provider like GCP, and
• complexity (and usually by association, power consumption)

A few years ago, it made sense for me to build out Mildred League as a full three-tier (frontend, backend, database) application, because I was still learning how to contribute to enterprise architecture and be a sound engineer in the corporate world. But my goals are much different now — I’ve gotten plenty of reps from work at this point and I’m much more interested in right-sizing projects, so this architecture is now well beyond overkill for a site that roughly a dozen people use regularly every year. Let’s talk about why exactly it’s overkill and how we can right size it.

Re-selecting a Database

I spent about a week brainstorming everything that I wanted to accomplish with another rewrite, and my number one hangup was the use of PostgreSQL (currently running in a free-tier VM on Oracle Cloud). When I last rewrote the site two years ago, I think I just went with PostgreSQL because I was most comfortable with it, thanks to on-and-off use of it since 2018 (when I was still an actuary running queries to analyze insurance data). But now it’s overkill, because:

• The dataset for the site is tiny: only six tables, and 1,137 rows in the largest table (one record for each game played in the entire league’s history, dating back to 2013). This table also grows by exactly six rows each week.
• At most, future expansion plans might warrant a couple new tables here and there on the scale of thousands of rows to add new functionality (things like showing old team rosters, simulating potential playoff outcomes, or supporting data visualizations).
• I am the only database writer when I update the site each week; in theory, there could be multiple database readers if multiple people are using the site, but this is either rare or non-existent.

All of these bullets point to an obvious candidate: SQLite. The benefits are thoroughly documented: it’s extremely well-tested, it supports reads in the hundreds of thousands per day, and it does not require a dedicated server. It’s just a file.

Takeaway: we’ll permanently shut down our PostgreSQL cloud instance, and replace it with a SQLite file.

Re-selecting a Frontend

With database decided, I jumped over to the frontend…which was last written in Angular (hosted on free-tier Vercel). Two years ago, this made a ton of sense: our team at work was using Angular to serve huge quantities of data to several hundred users, and I needed to get up to speed fast. But now it’s overkill: I don’t have enough JS, CSS, or HTML to justify four different files per route/component, and I’m not doing anything fancy that would particularly benefit from Angular’s MVC architecture or dependency injection.

On the other hand, I’ve really enjoyed writing Svelte sites lately. It’s ridiculously easy to spin up a new component with zero boilerplate. Here’s the entire code file for a simple navigation drawer (JS+HTML+CSS, all-in):

<script lang="ts">import NavDrawer from "./NavDrawer.svelte";
export let headerText;
let drawerIsOpen = false;
function toggleNavMenu() {
  drawerIsOpen = !drawerIsOpen;
}
</script>

<header>
	<div class="visible-header">
		<h2 class="page-title">{headerText}</h2>
		<button on:click="{toggleNavMenu}">menu</button>
	</div>
	<NavDrawer bind:drawerIsOpen />
</header>

<style>
	@use '$lib/scss/_breakpoints.scss';

	.visible-header {
		padding: 15px;
		display: flex;
		justify-content: space-between;
		flex: 0 0 auto;
		background-color: var(--was-dark-maroon);

		button {
			font-size: var(--font-size-small);
		}

		@include breakpoints.mobile {
			.page-title {
				font-size: 1.5rem;
			}
		}
	}
</style>

Bonuses:

• I did enjoy using SCSS for styling in Angular, and this support comes for free in Svelte (note the lang="scss" tag).
• We might even be able to move to a fully static site; more on this down below…

Re-selecting a Backend

Time for the glue. The last layer to figure out was the backend (written in Go, another language from my work team’s stack at the time, hosted in a free Cloud Run container on GCP). When you visited a page on the old Angular site, it would make an HTTP call to an API route on this backend Go container, which would in-turn run the appropriate SQL query against the PostgreSQL instance and return the result to the frontend. Pretty standard web app architecture.

And then it clicked…do we need to do any of this live at all? The data in the database only changes when:

• I create a new season (happens once a year)
• I run a weekly update (a week of matchups has finished, so I add the latest scores and recalculate the standings)

As folks visit the site throughout a given week, we’re re-running the same SQL queries over and over again for the same results, just to re-display the same standings and statistics? Madness…overkill. I’m starting to think we don’t even need a backend at all…

The detour: can we make this a static site?

If all of the data for the site is available at build-time (it is), then we should just make this a static website. When we update the data each week, we simply rebuild and redeploy the static site to our provider of choice (we’ll use Cloudflare Pages since my domains are managed there, but this applies to GitHub Pages or any other static site host).

Instead of HTTP calls to a backend service, our “API routes” are now just function calls sitting inside a Svelte load function:

import {
	getOwners,
	getSeasonGames,
	getSeasonNotes,
	getSeasons,
	getSeasonTeams
} from '$lib/data/api';

export const load = ({ params }) => {
	// query DB
	const owners = getOwners();
	const seasons = getSeasons();
	const teams = getSeasonTeams(params.season_year);
	const games = getSeasonGames(params.season_year);
	const notes = getSeasonNotes(params.season_year);
	return {
		owners: owners,
		seasons: seasons,
		teams: teams,
		games: games,
		notes: notes
	};
};

Here’s a closer look at one example, the getOwners() function, which returns a list of anyone who’s ever owned a fantasy team in our league:

export function getOwners(): Owner[] {
	const query = 'SELECT * FROM owners';
	return queryDB<Owner[]>(query);
}

Each of these get functions looks similar: a SQL query is defined and then passed to queryDB(), which passes it along to the database:

/**
 * Provide the db object for inserting and fetching
 * data from sqlite.
 */
import Database from 'better-sqlite3';

const DB_PATH: string = 'src/mildredleague.sqlite';

export function getOrCreateDB() {
	const db = new Database(DB_PATH);
	return db;
}

export function queryDB<T>(query: string, params: Object = {}): T {
	const db = getOrCreateDB();
	const stmt = db.prepare(query);
	const result = stmt.all(params);
	db.close();
	return (result ?? []) as T;
}

All of these queries will run during npm run build as Svelte assembles our pages, so at this point all we have to do is commit our .sqlite file to the repository and we’re done…but we would committing a blob to Git, which is probably not ideal. Can we do even better?

A familiar tech: ye olde CSV

Git was designed for version controlling text data…CSV files happen to fall into that category. Instead of having to store an entirely new .sqlite file in our Git-tracked repo each week, we’ll just store .csv files containing our league’s data and incrementally update them. But we’re not abandoning SQLite either — we can just remake the entire database from scratch every time there’s a CSV update!

(It’s not super relevant to the main idea of this post, but I use a Python script each week to call the Sleeper API and update the CSV files with the latest results from the league. I could also update the CSV files by hand; the point is that version-controlled data will very much come in handy if I ever screw things up.)

Rebuilding our entire database every time we rebuild the site adds a bit of preprocessing to our data pipeline, but it’s very fast and looks something like this in a file I’ve called rebuild.ts:

/**
 * Run this file before building the website.
 * It will use the CSVs in version control to
 * rebuild the SQLite database in preparation
 * for building/running the website.
 */
import { deleteDB } from '../data/db';
import { createTables, syncDataFromCSV } from '../data/sync';

deleteDB();
createTables();
syncDataFromCSV();

These functions are pretty self-explanatory, but for completeness:

• deleteDB() deletes any .sqlite file that might already exist in the work folder, to ensure that we’re rebuilding the database from scratch
• createTables() runs a bunch of CREATE TABLE SQL statements
• syncDataFromCSV() runs a bunch of INSERT INTO SQL statements to populate the SQLite database

Then, we just redefine our npm run build step in package.json to call these new preprocessing steps every time we build the site:

- "build": "vite build",
+ "build": "npx tsx ./src/lib/scripts/rebuild.ts && vite build",

The CSV files become our source of truth, and now we’ve unlocked the additional perk of version-controlled data updates! But we also didn’t have to redesign any of our “backend” SQL queries: they’re blissfully unaware that they are interfacing with a SQLite file that is being reconstructed from scratch before every vite build.

New world

The end result is a dramatically simplified architecture:

• Oracle VM running PostgreSQL, deleted. Data is now stored and version-controlled in CSV files, which are used to build a .sqlite file any time data is updated.
• GCP Cloud Run container, deleted. The “backend” is now just TypeScript functions that run SQL queries against the .sqlite file.
• Vercel deployment, deleted. The frontend is a Svelte static site deployed to Cloudflare Pages, and all data is reloaded every time the site is rebuilt.

This has re-granted me enormous freedom to quickly add and deploy new site features (and I could probably write another blog post worth on some of the frontend-specific quirks I’ve encountered when porting site logic from Angular -> Svelte, but for now let’s call that a teaser for next time).

We live in a society with a relentless focus on consumption and scaling up, so it’s easy (and sometimes highly appropriate) to go beyond today’s needs and look into the future when planning and building. I’d like to continue advocating for downscaling and right-sizing so these ideas don’t fall by the wayside in this finite world that we all share.