The many previous versions of this blog always had an actual backend - from Grails, to Django, to Node.js - with an admin panel, database, CMS panel, etc. This meant that the application had to be deployed somewhere (Amazon EC2), the database had to be hosted and managed (Amazon RDS), and new posts required the use of a clunky interface I wrote ages ago and had no desire to update.

This also meant that I had to pay monthly fees to host what is essentially a completely static site - I don’t blog all that much, and when I do, it’s just a simple matter of adding some static content to the database. There really was no reason to be paying for and managing all this infrastructure for such a simple and static site.

In comes Jekyll, a simple website/blogging platform with the flexibility to do everything I need, all while generating a final result that basically boils down to a static website. There’s no need for an application server, database, backend, caching - any of those (potentially) expensive pieces that more dynamic applications require. I figured that once the site was generated, I could simply dump it into Amazon S3, point my domain to the S3 bucket, and be done with it.

The only problem was, how do I port over four years worth of posts without having to do it manually - something I definitely was not going to do.

sql-jekyll-migration

So, I set about writing a tool that would dynamically create Jekyll collections and posts from a SQL database. I knew there were a couple of collections I wanted to migrate, including the posts themselves, so I didn’t want to hard-code table or column names, or have to creating a mapping structure for each collection.

What I wanted, and what I ended up building, was something more dynamic. I decided to write it in Go, which I’ve been using quite a bit lately, and here’s what the final product looks like:

go run migrate.go post ~/Code/kylewbanks.com/_posts title body created_at \
    title=title:preview=preview:created_at=-:body=-:created_at=-:url=permalink:layout=layout

This may seem pretty confusing, but I assure you it’s actually rather simple. I’ve documented everything in the GitHub repository, but I’ll give you a quick rundown now.

The jist of it is that we run the migrate.go program and tell it what table to migrate, where to put the results, and where to find a few key fields. The final argument, the most hideous looking one of the bunch, is simply telling the migration tool to map columns to Jekyll Front Matter, which is essentially a bit of YAML at the top of each file in the collection with some metadata.

Future Work

Right now, the tool only supports Postgres because that’s all I needed at the time. It would be trivial to add MySQL or SQLite support, simply passing in a dialect argument from the command line and adding the driver import to the top of the script should do the trick. I haven’t tested this yet but I don’t foresee any issues and may add support shortly if I get around to it.

Additionally, the arguments are pretty nasty. I’d like to maybe switch it to dynamically find the columns and prompt the user during execution to tell the application if they care about each particular column, and if so where it should map to.

In any case, it worked very well for my needs, and I don’t foresee this being an overly common use case or requirement, so I don’t expect to revisit it unless someone has a particular need.

Contributions and suggestions are always welcome, and can be made via pull request over at the sql-jekyll-migration repository!