SQLite Tools

— Morten @ 18:31

As an instructor teaching Microsoft courses on a regular basis, I often get asked to compose a custom schedule covering just what this particular customer finds interesting or worthwhile. As a result, I spend an amazing amount of time juggling with schedules and modules to make it fit without breaking the semi-natural order in which the topics are best delivered.

Instead of keep on doing this every time, I’ve decided it’s time for a CoursePlanner software program to assist me in this process. Obviously this needs a WPF front-end, a simple domain model, and a relational backend store. I just cant bring myself to storing useful data in any other way, and 10 wild horses couldn’t convince me that DataSets are good for anything but drag’n’drop demos.

I have been a long time user of the excellent embeddable open-source database SQLite, which in itself never ceases to impress me with its simplicity, speed and functionality. As this is going to be a simple client application, SQLite is a perfect match. It requires no installation of services or other nonsense to get the job done – simply add two dll files and your drink is ready.

After some fiddling with the domain model entities I was ready to create the database. I remember writing a quick schema and data editor in Delphi 6, which must have been in 2002 or so. It worked great and took about 2 days to write, plus another day or two adding niceties or fixing UI quirks. Apart from me not having the source anymore (or rather, me not using Delphi anymore), I figured someone else must have done something that inevitably would be better, smarter and nicer than my 5-year old 2-days hack of an editor..

First stop was the SQLite home page, where they keep a decent list of tools, plus some googling to make sure I wasn’t missing anything obvious. About 40 tabs and 15 downloads later I was all set to try out a mix of trial, shareware and open-source tools for SQLite. I decided to skip all the ones that looked discontinued or unmaintained (like, last blog post in 2005), as they’re unlikely to work with SQLite 3, but as there were plenty of others I felt optimistic about finding a decent tool.

Imagine listening to Bolero from start to finish. First, because this is a rather lengthy number it lets me get on with installing and testing. Second, it rises in volume from the inaudible at first all the way up to deafening at the end, and this correlates precisely with my rising frustration during testing. It’s quite encroyable, but there is simply no really good SQLite management tool out there!

To spare others some of the evaluation effort, let me summarize some of my experiences:

  • SQLite3Explorer (freeware). This looks like my 5-year old editor, but is usable for data editing.
  • SQLite Studio 1.0.4 (shareware). It works for data entry, but the UI looks like it was designed for Windows 3.1. This is not a compliment, but the fact that it works makes it worth mentioning.
  • PlaneDisaster.NET (freeware). At least the name is appropriate. I think you have to know Lotus developers to find equally poor UI designers.
  • SQLite Maestro (trialware). This tool looks nicer than most but is fairly horrible in the way it tries to make even the simplest task rather cumbersome. It took me 10 minutes just to find the screen where I could see table data, and another 10 trying to add and insert rows, before giving up in disgust. On the surface it looks like it can do a lot, but in practice it is not at all a pleasant or productive experience. The schema editor was the only reasonably decent thing. As a bonus the company appears to be selling a whole suite of equally useless tools for a wide range of databases. If they can make a living I guess most people are less picky than me.
  • SQLite Expert Professional (shareware). This is another prime example of how not to design your UI. It’s not at all intuitive or pleasant to work with. It also appears to be somewhat buggy, as it took me about 10 minutes to provoke an uncaught exception that exited the program. One click saved there.
  • SQLite2007 Pro Enterprise Manager (freeware edition available). Certainly above average for data entry/editing but not good for much else. Doesn’t seem to feature a schema editor.
  • SQLiteMan (freeware). This is actually pretty decent and so far my favorite. It’s not particularly advanced, but at least the UI is clean and to the point. Stuff works. It reminds me of what I once wrote myself. I’m happy.

If you’re reading this post and know of a good tool that I didn’t mention, please drop a comment. If you’re involved in the development of any of the above tools, forgive me for any insult given. In return, I shall forgive you for trying out a career as software developer. If you have received praise, know that you have a talent few apparently possess.