No LeetCode yesterday.
Instead, I decided to put my efforts into migrating from each post being a static HTML file in a folder on the server, to using a database to store the posts. This will be better for longevity and SEO purposes as I can store items like metadata and titles in each post and use them in multiple places.
My only criterion was something I could learn quickly and a relational database (blog posts are pretty structured pieces of data). After some quick Googling, PostgreSQL seemed like it would fit my needs.
I spun up a PostgreSQL server on my local machine and then defined a database model. Nothing too complicated to start and IntelliJ has a visual editor for this, so no need for learning the commands. One table and columns for id (auto-numbered), content, title, and metadata to start.
Next, I SSH'd into my server and copied my posts folder to my local machine. I wrote a shell script (with a little help from ChatGPT) to iterate over each HTML file and store its contents in the content column, and insert it into the database. Fortunately, there are only 19 posts so the title, metadata, and created date/time I was able to fill in manually.
This all looked good, so now I had to work on my Node server. Luckily there's an npm package for postgres.
With a few changes to my post service, all was working locally.
The next part was the most difficult. Getting this all onto my server for this site. I don't have a CI/CD pipeline set up (still on the to-do list!) so it was time to SSH in and start setting up PostgreSQL from the command line. Doing this in production is fine for now, but if my site had thousands of active viewers, I'd need a staging environment. Also ironic, I thought I had avoided having to learn PostgreSQL commands!
For moving the database between development and production, I used pg_dump and pg_restore commands to create a file and securely copy it over to the server to be restored. Come to find out, there was a mismatch between my pg_dump and pg_restore versions and I received a dreaded error:
pg_restore: [archiver] unsupported version (1.15) in file header
So, I went down the rabbit hole of upgrading my Ubuntu server's PostgreSQL installation and restarting the service. This was easier said than done but was ultimately the solution.
Having the database copied over from development, the last step was to deploy my updated server code.
Now that's done, I will pivot over to CI/CD and making deployments more streamlined. Maybe Docker can help with this. I need to do some research!