With the advent on Drupal 7, using SQLite as a database backend became a new option. While this ushered in a period of code updates for modules written specifically against MySQL, the Database API provided a clear way to make your code be database agnostic.
At CommonPlaces, we use Drupal fairly often on a number of sites. In the past, you could only migrate data from a MySQL database. However, now in Drupal 7, things have changed to include a new alternative in SQLite. But with all these technical changes, I think a lot of the advantages and use cases for SQLite were glossed over. Perhaps the reason is that for the most part... it just keeps working with little outward change.
For those not familiar with SQLite, it is a library that implements a self-contained, serverless, database engine. Its usage is so wide spread, it is hard to name an industry or technology where it is not utilized.
In terms of Drupal, it means that your data is stored in a single file and that your installation just got that much easier. There is no messing around with having an additional database service running and consuming resources.
As a developer, it means I can get up and running quickly, but also keep portability in terms of hosting offerings. During development I can move the entire site from a server to a laptop, without needing to duplicate the MySQL setup at every point or change settings. This is especially true if you want to do offline development and do not have MySQL installed. Likewise, backups of a single site are also just as simple as backing up your web site directory. You know the code and the data from that moment are in sync.
So why would anyone use anything else then? Well... Performance, familiarity, a different level of control, requirements of integration with outside systems. While SQLite holds a lot of promise, it is not meant to be a replacement for MySQL, it is a different offering.
Small and medium sites with reasonable levels of traffic could host from SQLite without any issue. Often the decision is made to move to MySQL prior to public launch, as more hosts are familiar with that type of segmentation and the related support workflows.
Still, it remains a viable choice for many circumstances. Down the road, it will be exciting to see which other database backends become viable candidates for Drupal sites.
Have any questions about SQLite? Contact us!