Blogging software news: The advantages of building your site on SQLite

Most of us bloggers like to write, and when it comes to technical matters, we rely on whatever our blogging platform suggests. Whether we use WordPress, Drupal, Joomla, or any other, we trust what the installer says. In fact, if your site runs on a hosted platform, such as WordPress.com or Blogger, you don’t have a choice in the matter. But if you’re hosting your own installation, then you have to make some choices during installation. One of these choices is what database system to use. Most content management systems out there support multiple options, and default to MySQL or MS SQL, depending whether you’re on a Unix or Windows host. However, sometimes it’s worth changing that. If you’re building your own custom web app, or have someo! ne do one for you, again it’s worth considering what database you’re going to use. Here a few reasons why you may want to go with SQLite instead of the traditional choices.

First, what is SQLite and how does it differ from MySQL or other relational databases? MySQL and most other SQL based systems run in a client-server model, that means you have an actual server running, that contains your data, and your web site is a client of that server. This has several implications. The first is complexity. Having a second, separate server running adds steps in the deployment process, and requires more resources. In most cases, if your web host is modern and the package you’re installing is well done, then this doesn’t mean much to you. You’ll get a MySQL server provided as part of your hosting, and you simply select a username and password, then put this into the installation process. However, if your host isn’t providing that, then you need to install the database, and make sure it’s running. SQLite however is based on a file system structure. Your database is literally one file, and all the ! data is kept inside of it. That means there’s no second server that needs to run, and no username/password to assign to it. The file security is based on your own account permissions.

This brings us to the second point, reliability. Using a RDMS, or relational database, means you need both your web site, and your database server, to be up in order for people to view your content. That’s 50% more chances for failures. If either goes down, then the whole system is down as well. And in many cases, when it comes to cheap web hosts, while your site may be unique to you, the database that you are provided with is shared among many people, and is often slower and less reliable. SQLite doesn’t rely on a server at all, so all you need is for the web server to be up, and that’s all.

Finally, the third advantage of using a SQLite setup is backups. It may seem like a minor thing, but it isn’t. Even if your web host says it’s doing daily backups, most techies will recommend that you still do backups of your own. But making a backup of a MySQL server can be a pain. You need to issue commands to ‘dump’ the data into a file, and then extract permissions, before backing that. Worse, when it comes to restoring, many people find themselves having large issues getting whatever new server they are moving to, to accept the dump that they previously made. With SQLite, it’s dead simple, you just copy the file wherever you want.

Overall, SQLite is a great solution for small and medium sites, and is something you could consider when you build your own site, or even when installing a blogging platform. There is one case where SQLite proves to not be a great choice, and that’s for very large sites. If you plan on making a site for a multi-national company, then you may want to pick something else, because SQLite is restrained by disk I/O access times, and also can’t be load-balanced as easily. Otherwise, give this free, open-source database a try.