This guide is for users wishing to migrate their WriteFreely instance from a SQL database to SQLite3. The steps involved can be used for any similar migration.

Why?

The reason I decided to move my database was that I run it as a single user instance for myself only. I don’t have thousands of posts, nor will I, and I am interested in running a few other services on the same little server. Most of them use or support PostgreSQL, while WriteFreely currently supports SQL or SQLite3. I don’t really want to run more than one database instance if I don’t have to.

Dump Your DB

We will start by dumping the existing SQL database. Then make a copy of that to work on, this way you have a backup if some issue comes up.

On the existing server run as root: mysqldump --complete-insert=TRUE --extended=insert=FALSE --all-databases > dump.sql.

Then copy that down to your local machine: scp hostname:/path/to/dump.sql ..

The copy on the server will remain as a backup, do not overwrite it.

Create the New DB

WriteFreely comes with schema files for both supported databases. The command syntax is sqlite3 new-database.extension < sqlite.sql.

On your local machine run: sqlite3 writefreely.sqlite3 < sqlite.sql.

Convert the Dump

MySQL and SQLite are not perfectly cross compatible, so a little script to convert the dump is required.

I tried to do this myself using sed, vim and even vscode with no luck.

I found an awesome script at https://github.com/dumblob/mysql2sqlite which worked perfect.

On your local machine, assuming a nix based system, curl https://raw.githubusercontent.com/dumblob/mysql2sqlite/master/mysql2sqlite. Then make it executable chmod +x mysql2sqlite.

Populate the Database

mysql2sqlite dump.sql | sqlite3 writefreely.sqlite3. You may get a few warnings, I did but they seemed not to affect the resulting database.

Change Your Server Over

Copy the new database file up scp writefreely.sqlite3 hostname:/path/to/writefreely/..

Then log in to your server and as root:

• Edit your config file to use sqlite3

[database]
type     = sqlite3
filename = writefreely.sqlite3

• You can leave the other database settings as a backup until you confirm it is working.
• Restart your writefreely instance, i.e. systemctl restart writefreely.service.
• Check for errors with journalctl -f -u writefreely

That’s it, if all went well you can stop and uninstall mysql. Also, fun fact, the first half of this post survived the migration.