Route 19

For some reason I have an aversion to running MySQL for a simple site (like this one), where I don’t see the value in giving the database half of my server’s RAM for such a limited role.

Sure, it has it’s place in a heavy read/write project, but for a single user making infrequent updates to a well cached, low traffic site I can’t justify it.

Enter SQLite.

I like to keep my VPS lean and simple, with Ubuntu 6.06 defaults via APT. That means PHP doesn’t support SQLite 3 out of the box, which is fine by me because neither does CodeIgniter. It’s not that difficult to upgrade to PHP 5.2 or SQLite 3 (PDO), but I’ll wait for Ubuntu’s next LTS to get there. For now, version 2 does the trick.

UPDATE: I’ve since moved my VPS to Ubuntu 8.04 and PHP 5.2, but I’m still running SQLite 2 because of some issues with CI 1.6.x and PDO. I still haven’t found a good reason to force an upgrade to SQLite 3.

Updating the database on a live site is sometimes as easy as uploading a small database file (20kb before this entry), and it’s maintained in Subversion as well. Another bonus is the MAMP server I use for local development has a built in web interface for SQLite (similar to phpMyAdmin). If you prefer an application, SQLite Manager is the best I’ve been able to find.

So this site is running on my VPS, using Lighttpd, PHP 5.2, SQLite 2 and CodeIgniter 1.6.2 until I see a reason to do otherwise.

config/database.php:
$active_group = "sqlite"; $active_record = TRUE; $db['sqlite']['hostname'] = "localhost"; $db['sqlite']['username'] = ""; $db['sqlite']['password'] = ""; $db['sqlite']['database'] = "path/to/database.sqlite2"; $db['sqlite']['dbdriver'] = "sqlite"; $db['sqlite']['dbprefix'] = ""; $db['sqlite']['pconnect'] = FALSE; $db['sqlite']['db_debug'] = TRUE; $db['sqlite']['cache_on'] = FALSE; $db['sqlite']['cachedir'] = ""; $db['sqlite']['char_set'] = "utf8"; $db['sqlite']['dbcollat'] = "utf8_general_ci";

Don’t forget to change the permissions on the database file and it’s parent directory so it can be written to for inserts and updates.

UPDATE II: I’m now testing SQLite3 with CodeIgniter 1.6.x, and plan on switching soon.