Database Backend

Still hoping for minimal complexity, I started with a sqlite file, but as soon as I deployed to production, I realized that file would have the same issue as the YAML file: changes made in production would not persist, and anyway I’d need to re-deploy the site after every word.

Now, I’ve implemented a postgres backend.

Migration

I already had a postgres cluster on my VPS, so all I had to do was add a user and database for Hindki and hook it up.

In the initial sqlite migration, I had already implemented Drizzle ORM to interface between a database and my app, and because I had started with YAML, I already had a good sense of the schema I wanted (often my pain point with database-driven apps, since I end up with a ton of migrations as I change my mind about data architecture while building).

In the migration, I changed the rigid categories I had been using to tags, which are much more flexible.

Previously:

- slug: food-and-drink
  about: Words about food and drinks.
  words:
    - english: to eat
      hindi: खाना
    - ...
- ...

This was kind of bothering me, actually, because a given word might belong in multiple categories, which created a bit of friction when adding words. With tags, this is now possible.

How it works now

My vocabulary “page” is just one file, [tag].astro, which uses the storage backend to pull the list of words with tag tag dynamically from the api, parses the list to generate the right-hand sidebar to organize by parts of speech (I’ve just called these “types” with the expectation that in some categories, I’ll want to organize words by more interesting dilineations), and then renders a component, VocabWord.astro, that looks identical to the textbook-like look I had initially set up when the site was just a static site generator using YAML.

Finally, I created a custom sidebar overriding Starlight/Astro’s default, which also dynamically pulls the list of tags by fetching /api/tags.json so that when a word is added in the web form, the sidebar and content both immediately update.

Essentially, I was able to move the data to postgres — and access the same database from both dev and prod — while maintaining the user experience perfectly!