Hello, I am Alex. For the past year I’ve been chained in a dungeon, fed on a steady diet of MySQL and PHP. [Ed. note: We do let him out once a month to play with some CSS.] I built the Doghouse infrastructure with these technologies. Recently I started learning a wonderful and beautiful programming language called Ruby. If you have any interest whatsoever in programming languages I really recommend giving Ruby a try (more on that in a future post). I’m currently building the future Doghouse moderator section with Ruby On Rails. Working with Rails and the pedias I have had a chance to look more closely at SQLite, so I thought I’d write a short post about it.
I have a new-found respect for SQLite.
As you know, SQLite is the database ‘engine’ on which the pedias are based. It is Apple’s default database schema. Being a Mysql user for a number of years now I wasn’t really interested in this seemingly less capable new-comer. But, lately, I’ve had a lof of dealings with Ruby on Rails, whose default database engine is also SQLite and have found I really like it for certain tasks.
For those of you who are only interested in a Graphical Editor to be able to manipulate your pedia data, skip to the GUI tools below.
It’s not for every project, especially ones that require a lot of inserts or updates to your database. But it’s perfect for portable applications and web sites that rely mostly on reading and not writing data. I read somewhere that the rule of thumb is that if your database takes less than 100,000 (100K) hits a day, then you’re find going with SQLite. This is, obviously, a big simplification, but not a bad starting point.
It’s extremely easy to set up, lightweight and very portable since the whole database consists of a single file and there are no server connections to worry about. It offers all the simplicity of a file-driven database (Excel, CSV file, Access, etc) with none of the shortcomings, namely it allows for relational databases as well as constraints and triggers.
You can find lots of good articles on SQLite usability and Mysql vs SQLite comparisons so I won’t bother, instead I’ll write about the Graphical Tools you can use to manipulate these databases.
GUI Tools
So, on to the most important thing, the GUIs or Graphical Tools for manipulating SQLite databases. Sure, you can use the terminal and issue all sorts of weird commands to manipulate your databases, but c’mon, who doesn’t love a GUI?
Sequel Pro
http://www.sequelpro.com/
This is the best tool for Mysql databases on the planet, no exceptions. And it’s free. If you work with Mysql databases, do yourself a favour and get it now. And do us all a favour and donate something to the developers to keep them upgrading this wonderful product.
It doesn’t yet have support for SQLite but it’s in the works. I just had to include it here because it’s so great and I work with it everyday monitoring and tweaking Doghouse performance.
Navicat Premium
http://itunes.apple.com/us/app/navicat-premium-essentials/id466416967?mt=12
This is a capable but very pricey option. Though there is a sale of the Premium Essentials http://itunes.apple.com/us/app/navicat-premium-essentials/id466416967?mt=12 right now in the App Store.
It’s not that great-looking but will do pretty much anything you need and has the advantage of also tackling Mysql databases so if you use both Mysql and SQLite like we do, you only need one tool.
I don’t very much like the interface or the way the tables are laid out or the fact that each table opens up a new window, making for a very cluttered workspace.
Not my tool of choice, but capable.
Base
http://itunes.apple.com/us/app/base/id402383384?mt=12
This is a nice-looking SQLite manager. Much nicer-looking than Navicat, albeit with less features.
The interface is simple and very nice when reading database contents.
However, when editing the database such as adding or altering tables and fields I find the interface strange and confusing. Things are hidden away in weirdly-placed pop-up menus. Almost as if it was initially intended only for reading databases, then the editing features were bolted on as an afterthought and the developer wasn’t quite sure where to put them.
I like it a lot more than Navicat Premium Essentials but, unfortunately, it’s more expensive, almost three times the price (now that Navicat Premium Essentials is on sale).
I think this is a nice app, especially for reading data. If it were a little cheaper I’d buy it, but I’m not paying $30 for something I don’t fully like.
SQLVue
http://itunes.apple.com/us/app/sqlvue/id426397771?mt=12
They don’t have a demo version on the App Store (this really pisses me off) but if you hunt around the Internet you can find a 7-day demo on their site:
http://www.logicalvue.com//Download/SQLVueTrial.dmg
I think this tool is ok, capable and simple for SQLite databases. The problem is I tried their tool to convert a Mysql database to SQLite called SQLite Migrator and could not get it to work. The SQLite Migrator relies on Apple’s ODBC Administrator app (no longer supplied with OS X, but you can download it separately) and ODBC Administrator in itself is not simple to configure. You first have to find an ODBC driver (from where?) for the connection you want to use, then get that working inside ODBC Administrator, create a new connection with that driver and to the database of your choice, then go back to SQLite Migrator and try to connect to this new connection you’ve created in ODBC Administrator. If that works, then you can (I assume) start dealing with your migration issues.
I’m sorry, but for a tool that costs $50 I expect it to be a little easier to create a connection just to get started. The days of ODBC connectors on the desktop are long gone (there’s a reason Apple doesn’t include this tool anymore, because it was just gathering dust and taking up space in your hard drive) and this is a step in the wrong direction.
So this experience left a really bitter taste in my mouth so I can’t quite like their SQLVue product.
All these tools (except Sequel Pro) seem to suffer from opening a new window for every task. If you are looking at a table structure and want to edit it you get a new window just to add or modify a field. This makes it confusing and cluttered.
SQLite Database Browser
http://sourceforge.net/projects/sqlitebrowser/
Lastly, there is this tool. It’s free so that’s great news, but it’s buggy, crashy, hideous and hasn’t been update since 2008 as far as I can tell. It is a port of a Windows app and it shows.
For very ocassional use this tool might be OK. But if you want to deal with SQLite databases and keep your sanity, I’d recommend using one of the other alternatives.
Conclusion
Well, it’s tough to say who the winner really is. Until Sequel Pro brings out support for SQLite I think you’re better off going with Base or Navicat Premium Essentials. The latter is not great, but I think that for $10 (on sale) it’s probably your best option at this point, though I will say that personally I use Base.
I would also like to add that you can use these tools on the Pedias by pointing them to the database at ~/Library/Application Support/Xpedia/Database.Xpd (where X is book, cd, dvd or game). Sqlite is also the backend of choice for any large Core Data program (XML and binary are also options with Core Data) so it might be useful for any number of Mac programs out there based on Core Data.