An update on my current project!

The video version of this blog is here:

The rest api is complete enough for now. I’m able to handle requests for game sessions and status updates with data validation and a system that works well enough for status polling for now. I decided to let the UI poll at an interval to get status on active sessions, and the API will submit status requests to the message queue. When a client polls the API for a status update the API endpoint will check it’s own cache of requests to see if it’s already submitted one, then check its messages to see if it has a reply ready for the client. This avoids any lengthy waits on DB replies that would otherwise cause the api to hang up and be unable to handle additional requests.

I’m working on the database worker now. This isn’t totally uncharted territory for me but I’m taking a more serious approach than I usually do so I’m slowing down and taking my time to learn more about how to use a database more effectively. It’s been really interesting so far learning about SQLite. At some point in the past I googled “easy to use database manager” and that was the first result, and I’ve used it ever since, without thinking about optimization or using it “correctly”. (and without reading any meaningful documentation of course) So now that I’m slowing down a bit to give myself time to learn more about my tools I’m finding out there’s a lot of cool things I can use in the future. For example, SQLite can be mushed together into a single C file to embed inside other programs. That’s pretty neat, and I’d like to try using it for a Godot game at some point. It’s also really good at dealing with what other databases might consider the “poorly optimized” design of just having one giant table with a ton of rows that you add rows to as needed. In other databases when you add a row for an optional field you waste a ton of space because the empty cells still take up some storage space, and if it’s an optional field you’re creating that storage space for every entry in your DB. With SQLite the size of a NULL cell on disk it only 1byte, so it’s not that big of a deal. It also operates from a single file on the OS per database, so making more tables doesn’t necessarily offer more performance in the same way it might in other databases. I’m not an expert at any of this and I’ve hardly used any of the alternatives, but I thought it was neat that SQLite is designed in a way that makes it perform even when there’s a noob designing the database.

I’d like these workers I’m building to be scalable, so I can balance the amount of resources available to the system at different stages. I looked into some orchestration options like supervisor, but I think for now I’ll just use systemd and copy unit files myself as needed. If I move to a distributed environment like AWS I’ll take another look at automating the scaling of workers with plaform-specific tools and roll-your-own solutions like supervisor again. I didn’t go with supervisor because it’s another dependency and its documentation didn’t really make a very good case for using it over something like systemd in my situation. I’m not really worried about users adding workers since I’m the only user, and I don’t need another minimal web interface to spawn workers. I can just as easily ssh into the machine and copy a unit file to start a new service. I don’t anticipate having to make adjustments very often, I should mostly be adding a worker or two when I see queue’s backing up. These workers will be running in an environment where I wont mind having them idling for extended periods in the case where I run too many so I’m not worried about shutting them off.

The database worker is going to be a worker that monitors messages asking for information from the database, or offering data to be written to the database. I’m planning to build them in such a way that one worker can be responsible for one type of database operation. This will let me increase the amount of resources I have to deal with each request, in case one operation ends up being more resource intensive or more frequent than the others. It also means that I can replace some subsystems or modify tasks by changing or adding workers for that one task, which should help me to keep the impact of future changes on the uptime of the system low.

I’m planning on having more than one database, too. I’ll be generating and storing a few categories of data that will have different priorities of access and different uses, some of them being accessed rarely if ever. So having separate databases will let me handle those types of data differently than the data types that will need to be accessed quickly.

I still have more programs to write, and this one is far from complete. I wanted to put some of this out there though to document my thought process and let everyone know I’m still alive and working on something. I think you guys will really like this project once I get it to a state that I can share!