WordPress and SQLite

For a long time I have hated MySQL with a passion. I guess mostly because it is cumbersome. Requires additional credentials, is non-trivial to host on desktop OSes, most of the time requires an additional UI for management, such as PHPmyAdmin.

It is probably reasonable for a project like WordPress to use it as storage, given it can scale moderately easily and comes preinstalled on many web hosts. But in recent years, I have come to really like SQLite as an alternative. Most of the projects I work on focus on a single user, or are websites that have a few 100 visitors a day at most. This is something that SQLite can easily handle, especially if you use some sort of caching mechanism with it.

The things I like about SQLite are the portability and simplicity. Portability in a sense that I can just download the website directory and it will contain the files *and* database. Simplicity just because there are fewer column data types to handle. A string is always TEXT and a number is INTEGER, most of the time anyway. Prevent the file from being downloaded in the browser, either by placing it outside the document root, or by 403ing the file extension via .htaccess (which I prefer, because then the database can stay with the website code and assets.)

For the past decade I have looked for solutions for using WordPress with a SQLite DB, each time finding out either there is no solution, or it’s to cumbersome or unmaintained to use. Listening to a recent episode of ATP, I stumbled onto wp-sqlite-db. It does everything!

I read up on it and decided to give it a go (with this very blog!) It turns out, I had not read up on it enough, and it doesn’t *convert* your existing MySQL database to SQLite, it just creates a new DB (as a file) and WordPress thinks it’s a fresh install. Bummer.

I used some recent experience converting a SQL dump to SQLite with the aptly named mysql2sqlite – and it worked. (A few weeks back I modified Shaun Inman’s Fever to run on PHP 8+ and SQLite, so I knew this could work)

I guess now I need to look into some caching plugins, in case a million people all of a sudden start to read my blog. But at least, this is some behind-the-scenes progress. (Had to fix up my theme a little, too, for the Twitter archive to work again. Thanks, Elon!)