Migrating GOTM from MySQL to Turso

I maintain the PG GOTM site, which handles Game of the Month voting for a Discord server.

I’m no stranger to accidentally rm -rf /-ing my VPS. At some point, I realized I didn’t want to be the only thing standing between six years of GOTM voting history and total data loss.

The Project

The MySQL GOTM database has a lot of history (it goes back to September 2018!). It’s a well-structured schema with solid foreign key constraints.

I’m very comfortable with MySQL and use it in production often, but typically in managed environments. While I’ve self-hosted MySQL before, I wanted a simpler setup with easier backup strategies in case I nuked the VPS again.

With PlanetScale dropping its hobby plan, I started exploring alternatives. That’s when I came across Turso via one of ThePrimeagen’s videos. It looked promising, they’ve also recently announced a Rust re-write of SQLite. I just needed a guinea pig project to test it on and GOTM was the perfect candidate.

Why Turso?

I’ve never used Turso before, but I’ve used SQLite a lot. Every single fun side project of mine begins with an SQLite database.

Turns out, Turso is literally just SQLite with a sync layer on top (okay technically it’s a fork called libSQL which extends SQLite to support replication, remote writes, and syncing).

That’s kind of cool honestly. I don’t enjoy managing volumes in k3s and my restic + Backblaze scripts still need a lot of work.

Migration Notes

Fortunately, a lot of the MySQL schema was easily translatable to SQLite. I only had to handle a few things:

• No ENUM type in SQLite, I ended up using another table with a FK
• Replace MySQL’s DATETIME with basic a INTEGER UNIX timestamp

And I think that’s about it. It was fairly painless. I wrote a simple script that move the data of every table from the MySQL server to Turso.

How’s it Going?

We’ve just completed our first month of voting with this new setup. I think it went quite well! Maybe except the part where I copied over the dev .env to prod.

One of my favorite features is Turso’s web-based SQLite client:

I’m used to using DataGrip for everything and it sucks that it can’t read SQLite files over SSH. This is a huge plus.

Here are some stats after a full month of voting:

I’m quite happy with the performance and it also seems like I’ll remain well under the free tier.

I’m not going to miss worrying about backups.

What’s Next?

I like Turso. I’ll probably move a couple more of my projects over to it HamVerBot has already been converted 🙃.