March 31st, 2008
I’ve dumped MySQL for the majority of my projects. For years the LAMP stack has assumed that MySQL occupies the database role. I’ve finally turned off my MySQL server and have had SQLite take its place. I think more people should be doing this.
MySQL has been the database workhorse on the back-end of most of my projects for the last few years. It has performed admirably, and was in every case up to the tasks I threw its way. The change to SQLite might therefore not make much sense without further explanation.
A quick primer on SQLite can be found on Wikipedia and SQLite’s own about page, but in a nutshell: SQLite is a self-contained, open source, file-based database that doesn’t require a client-server architecture nor any configuration. The beauty of SQLite is that the entire database functionality can be included in an application — it therefore does not need to be explicitly installed on the host system.
PHP — my scripting language of choice — has included an SQLite module since PHP 5. Most relatively up-to-date hosting solutions will therefore be SQLite capable. Its ubiquity also extends to operating systems: it’s a Core Data persistent storage option, it is used by the Firefox web browser, Skype, Symbian phones, and of course the iPod Touch and iPhone. A number of popular programming languages have bindings for SQLite, like C, C++, Objective-C and Java.
Industry adoption alone is perhaps not enough of an incentive to make the switch. For me the advantages over MySQL can be summed up as follows: SQLite gives me a mobile, easily implemented relational database without the hassle and overhead of a client-server model, for free.
The majority of projects I work on are centered around a simple CMS model. Content, in the form of articles and user submitted items, are dumped into a central database (usually on the same machine that is serving the parsed HTML) and manipulated directly on the server. Getting a database up and running for any particular project involves a number of steps, depending on the hosting solution, that are invariably run through the command line and web-based software like PHPMyAdmin.
Once the database is created, and I have portions of the program running which access content in said database, I usually go about expanding the existing tables as needed. All the while a MySQL process and server needs to be running, permissions need to be set and appropriate server configurations need to be made. PHPMyAdmin, if not already installed, also needs to be configured. That’s a lot of work for a simple place to store some pretty standard data.
SQLite does away with all that, and instead presents me with two steps: create a database file (using the command-line sqlite3 utility, or via the program itself) and make sure it is read and writable by the program. Done.
If I’m only testing certain functionality without actually needing a persistent store of data, I need not create a database file at all, but simply tell the SQLite connect function to make a temporary memory-based database.
The resulting database is a single file somewhere on your server. You have the choice of putting this file anywhere — for security’s sake, place it outside your www root directory. You also know that you can move your program from one server to the other and automatically have your database migrate with you. There’s no SQL dumps and imports to do. Just move the file.
This might seem like a minor advantage, but consider the web developer that frequently backs up their projects. Backing up the application also backs up the state of the database, again, without SQL dumps. Because it’s a simple flat file, if you’re using version control like CVS or Subversion, checking in and checking out your database from an earlier date is entirely possible.
There’s no doubt that SQLite is gaining ground incredibly fast. A number of high profile deployments, together with software like Firefox, Skype and Mac OS 10.4, have garnered it significant attention. Embedded developers especially crave its simplicity and light-weight design. The advantages mentioned above are especially important to me, but are not exhaustive, and do not imply that for certain high-use applications MySQL is not the better choice.
The best way to get started with SQLite is, of course, to try it. There’s a ‘Getting Started’ tutorial on the SQLite website. If you’d like to know more about SQLite, its history and development, I recommend you listen to Randal Schwartz and Leo Laporte’s interview with D. Richard Hipp — SQLite’s creator and developer.