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!)
There have been moments where having HTTP/2 on localhost would have been handy. Over the years I have tried a few things and recently I found a way I liked. I tend to dislike having to install too many things, but for this, it seems impossible to avoid. My instructions are based on macOS Catalina (10.15) (for Safari), but should work for previous versions and future versions as well.
My steps were:
From the Terminal app, generate a self-signed certificate to use for SSL. (maybe change to the directory you’d like to store the certs in first?, in my case “/Users/oelna/.certificates”)
This will create two .pem files (without setting a password for the key, which I found invaluable on a local machine, since I don’t want to enter a password every time I start the server.)
During creation I chose to put in some reasonable values for country name, organization name and common name (“localhost.”).
I tried to figure out a way to do this with the Python built-in HTTP server, but couldn’t, so I begrudgingly installed twisted for Python 3.
pip3 install twisted
Depending on where you saved the .pem files generated earlier, you may have to adjust the paths for them in the following start command for twisted:
twistd -n web --path=. -c /Users/oelna/.certificates/cert.pem -k /Users/oelna/.certificates/key.pem --https=4433
The parameters are pretty self-explanatory: -c is the certificate location, -k is the key file location, –https is the port the server will listen on, and –path is your website root (Most of the time I run the command from the directory the files are in, so I just put . there (=current directory)
Twisted should start up and if it doesn’t fail, your site is ready for you at https://localhost:4433
Safari will complain, when you load the page, but you can click your way through.
Chrome is a completely different beast, though, and I have needed to make a few changes to how the certificates are created.
Chrome The instructions above worked well for me, but for Chrome I needed to go about it a little differently (sadly, more complicated). For this I used the instructions on Stackoverflow.
You need to become your own CA (“certificate authority”) and trust it on your machine. This fake CA will issue a certificate for localhost (which has to include something called subjectAltName). The resulting certificate for localhost also needs to be imported to Keychain and trusted for use with SSL. Let’s do it!
# generate the CA key and cert
openssl genrsa -des3 -out myCA.key 2048
openssl req -x509 -new -nodes -key myCA.key -sha256 -days 825 -out myCA.pem
Next, generate a key and certificate signing request for localhost. (I think I read that when asked for the “common name” for the cert, you should put a period at the end, to make it a fully-qualified domain name (FQDN), like so: “localhost.”). It worked for me. The “days” parameter can’t be larger than 825 on macOS, so that’s what I put.