I’ve been hard at work these days, and to relax I decided to develop a small application/side project. As with many applications, I needed a reliable, persistent store, so I went with the most reliable, easy to set up, performant, and generally awesome store I knew: SQLite.

However, since this was a very experimental prototype, I got tired of the frequent schema changes and wished I had something that didn’t dictate a schema, but that I could still query quickly. MongoDB is, theoretically, a very good fit for this, but I’m not very fond of it due to my previous bad experiences. I’m not sure how far along it’s come, but it’s not my first choice these days, and I figured I could very easily hack a schemaless layer over SQLite.

After pontificating for a bit, I realized I could just store my data (any sort of arbitrary data) in a blob column, and not care about what’s in it. Querying would be trivial by loading each and every field from the DB and checking if all the properties are there, but this would be very slow. If, however, I dump the fields I care about (and their values) on another table, then I could index them and query them very easily and quickly, and an INNER JOIN between the index and data tables would give me the actual data blob very quickly. Data that didn’t have one of the properties searched for wouldn’t match anyway, so they could be omitted from the indexes.

After starting to work on this, my good friend Iain showed me a post that mentioned that this is the exact method FriendFeed used over MySQL to turn their MySQL instance schemaless, for easier creation of indexes, updating, etc. Postgres, of course, has the hstore, so it doesn’t need any of this hackery.

One day later, I had something that works quite well, and I call it goatfish. It’s small, fast, very easy to use (just have your data class inherit from goatfish.Model, and you’re set), and, since it uses SQLite, it’s embeddable.

Its documentation can be found at github pages, and it works reasonably well for rapid prototyping. If anyone would like to contribute, I would be grateful if you added tests (tests that fail when they shouldn’t are a bonus), and generally any sort of feedback/request is appreciated.

I hope you find goatfish useful!