A Case For SQLite

May 09, 2006

I believe SQLite is the under appreciated and easily forgotten database for Rails development. Most people immediately go for MySQL when beginning a new Rails application without a second thought. Some others (like the Planet Argon guys) are big on Postgresql. But little do you hear about a little file based database called SQLite. I believe many Rails applications, especially those that are primarily used as data stores, would benefit from using SQLite as their database. It gives you all the power of SQL queries, while keeping the simplicity and speed of flat file data stores.

Simplicity

SQLite is simple to set up and use. It has no access control, and does not require a username or password to access. The database is also a single file that can be stored with the application itself and added to source control. All you have to do is add something like this to your database.yml file and you’re ready to go:

development:
   adapter: sqlite3
   database: db/development.sqlite3

If you decide you’d like to use your current development database as a set of base data for production, it’s just as simple as a copy/paste, no data dumping needed like you’d have to with MySQL. I’ve also found it to be extremely reliable and I’ve had no issues with it so far.

Speed

SQLite is extremely fast. It’s a very small engine written in C, and has no client/server network overhead like larger databases. Since it is always doing basic file reads and writes it will only be limited by the physical limitations of the computer hardware.

When to use SQLite

SQLite is great for applications such as Typo or other content based sites that just need a simple way to store data and content. Any sites that have a high amount of visitors but only a few actual users will see immediate benefits from using SQLite as their database. I use it for this site and have been very pleased with the performance.

Limitations

Since SQLite uses file read/write locks it only allows one operation at a time. For this reason it wouldn’t be wise to use SQLite for something like Basecamp or other sites that have a large amount of users.

Also, since all transactions are stored in memory doing operations on very large datasets is not advised.

But for most Rails applications SQLite will perform just as well, if not better than most client/server databases.

Final Notes

If you need to install SQLite on your development machine I’ve written a short tutorial on how to build SQLite.