SQLite As A Production Database (No, Really)

There's a certain stigma surrounding Sqlite. It seems that many developers will happily use it to prototype a project, but wouldn't think of deploying on anything less than Postgres (or MySQL, if you swing that way). I say this partially because I used to feel this way myself. I've run more low traffic web apps off of a Postgres server than I'd care to admit now that I've discovered the inherent convenience and manageability of a file-based SQLite database.

With SQLite an entire python web application fits within a single directory on the disk. This makes backup, testing and maintenance easier than you can likely imagine. SQLite also requires next to no setup, and most likely, is already running on your server.

Recently a user came across a bug in one of my web applications. In order to reproduce the bug, I didn't have to set up a test database and populate it with dummy data. Instead I merely copied the actual production database file along with the application files and within minutes had an identical copy of the application, data and all, running on my development machine.

There are trade-offs with SQLite and the developers make this very clear. Concurrent writes are an issue (I'd wager that actually very few web sites depend upon a large number of concurrent database writes) and the types system can throw traditional database developers for a twist. All the same, as the development community comes to grips with the fact that scripting languages can solve real problems as well or better (when you factor in time-to-market) than compiled languages, I think projects such as SQLite and CouchDB will finally motivate developers to begin looking for data persistence solutions in places other than monolithic relational database servers.

"SQLite isn't trying to replace Oracle, it's trying to replace 'fopen'" - D. Richard Hipp, creator of SQLite

- FLOSS Weekly Podcast with D. Richard Hipp, the creator of SQLite.