SQLite for Ruby on Rails Applications Revisited

So you are thinking of using SQLite?

This article has been revised from its once thought permanent grave. This article is for those of you who are considering using SQLite for your apps.

Are you confused about what database to use?

A lot of confusion seems to be floating around in regards to the different databases to choose from when developing a Ruby on Rails application. The truth is, making these decisions can be critical depending on the planned size of your application. The challenge is that there is so much information floating around, some good, some bad, that it's easy to be mislead or simply overwhelmed by it all. I'm not promising this article will solve those problems, but what I am going to do is attempt to help you understand the costs and beneifts to using SQLite as the database engine for your application.
Currently the latest release of SQLite is Version 3.3.5. It was release April 5th, 2006 and fixes a fair number of bugs as well as providing new features and performance enhancements from previous versions.

Pros of using SQLite

  • It is a zero-configuration SQL database engine. This means there is no configuration setup required by you whatsoever.
  • Sources are in the public domain. It is completely FREE and can be used for any purpose whatsoever.
  • the main engine is written in C (speed and efficiency rank high for C applications)
  • Access the databases without the need of a server process running at the same time.
  • Accessing the database is much faster than than several DBMS on the market, due to the fact that there is no client-server negotation taking place. In fact, SQLite is entirely self-contained and requires no extternal dependencies.
  • it doesn't put all data in memory (yet you can do that if you want, just use ':memory:' as filename
  • It's very cool for small databased application, because you do not have to start an external DBMS. It exists essentially as a file.
  • Implements almost all of SQL92
  • SQLite is compact. The entire library footprint is less than 250kb
  • The database files can scale up to 2 terabytes (2^41 bytes).
  • It's extensible in a very easy way via several applications
  • Sizes of strings and BLOBs are limited only by available memory. This means you can have some pretty big strings i'd say!

Cons of using SQLite

  • SQLite only supports the basic types NULL, INTEGER, FLOAT, TEXT and BLOB. While this is sometimes considered a feature, some database designs that would prefer using date's and various other types might find this to be a drawback. Blogs for example might benefit from using a database such as MySQL or PostgreSQL.
  • If you want to use other types like DATE and TIME in, you need to use its "types mode" which can be confusing for users.
  • It locks the entire database to perform a write, so its not going to offer much in the concurrency department due to this file level locking.
  • There's no concept of "users" allowed to access the database. Since all of the data is stored in a single file, the permissions pertain to the file for regulating access. If a script is going to perform read or write accesses for the file, a sql instruction can be executed on the database itself. You'd therefor protect your database by controlling your file permissions.
  • Some SQL queries are not supported. Here's the list.
  • It is not truly a good option for giant databases. While they do support 2 terabytes, the memory required to run such a database would chew up quite a bit of ram. The SQLite faq's will say that 256 bytes of ram are needed for each MB of space in the database file.

Bug or Not?

SQLite has no data enforce rules for datatype constraints. What this essentially means is that you can put a string variable into a field marked to be an integer and you can even make the string enormous in size.

About:

  • SQLite organizes each database in a .db file
  • Columns marked to be the Primary KEY are required to be of type integer.

When to use SQLite:

  • Websites that get hits in the range of ~100,000/day, although this is considered to be a conservative estimate.
  • Embedded devices and applications, such as PDAs and cell-phones
  • Applications such as record keeping programs, analysis tools and CAD packages.
  • A replacement for actual ad hoc disk files
  • Temporary Databases
  • Demos and testing of applications

When not to use SQLite:

  • Client/Server Applications - Due to the latency associated with network filesystems, the performance limitations on SQLite make it a less than optimal choice for client/server applications.
  • Extremely large datasets: Due to the consequence mentioned earlier, SQLite needs 256 bytes of RAM for every 1MB used by the database.
  • High concurrency - Due to the read/write locking of the entire database file, if a process is being read, all other processes are prevented from writing any part of the database.

A few problems...

If you have a high traffic site or plan on having one in the future, I must re-emphasize strongly that you consider postgreSQL or MySQL. If you choose SQLite and your site explodes, the db may inhibit you from organic growth due to database locking requests. This can become tragic and truly choke your system.

Conclusion

When deciding on what Database engine you are going to use for your Rails applications, SQLite offers an excellent high performance fast database for non high concurrency applications. While it is not your best pick for client/server databases, it does offer an excellent alternative for standalone applications where speedy queries is a high priority. If your website or application is small or medium sized, SQLite may just be the very best pick for you. Hopefully this article helps you in making that decision.