When Not to Choose SQLite

There is no hard-and-fast rule that determines whether SQLite is the right choice for your application. It is a robust, fast database engine that implements as much SQL as you are likely to need, but in some situations there are probably better choices.

In this section we will look at some circumstances that require features that SQLite cannot provide, followed by some examples of situations where SQLite is a very good choice indeed.

When SQLite Is Probably Wrong

First the bad news: The following situations may be better handled by another database system.

Network or Client/Server Applications

SQLite is not well suited for multiuser access over a network. SQLite can read from and write to its databases on a network share, but performance will take a hit because of the high latency levels that are usually found on a network filesystem.

File locking can often be patchy across a network, which could lead to two SQLite processes writing to the database at the same time, inevitably causing corruption.

A client/server RDBMS avoids these issues because all filesystem access is performed by the server after its requests have been received using a network protocol.

High-Volume Websites

For the vast majority of websites SQLite is a good choice. However, some sites receive so much traffic that their database components would be better suited to a client/server RDBMS.

How to quantify high traffic volume is the million-dollar question. The traffic level itself is not as much of an issue as the number of database reads and, more importantly, writes that are performed when a typical visitor does some surfing around.

Because web servers can send multiple requests simultaneously, if the database is frequently locked these processes will often be hanging around waiting for each other to finish writing before they can do their own thing.

The last thing your busy website needs is a database bottleneck, so you should consider a client/server RDBMS with more finely grained database locking.

High Concurrency

Similar to the reasons that a high-volume website may not be suitable for SQLite, a multiprocess or multithreaded application that performs a large number of database accesses may run into file-locking issues that could be avoided in an RDBMS that implements locking on smaller subsets of the database.

Again, quantifying a high level of concurrency is difficult, but this is something you should consider if your application will attempt to perform many concurrent database operations.

Each lock on the database file may only last for a few milliseconds, but there are still times where this will be too long for the application to work efficiently.

When SQLite Is Probably Right

Now for the good news: There are many applications for which SQLite is a great choice.

Websites

We have already mentioned that SQLite is usually a great choice for database-driven websites. With the inclusion of SQLite support in PHP 5, it is likely to challenge MySQL as the de facto database back end for PHP scripts.

Embedded Devices

Being small and requiring virtually no administration makes SQLite ideal for hardware applications that require database storage without the need for human intervention. In addition to working on Embedded Linux and Windows CE, SQLite has been also ported to Palm OS.

Ad-hoc File Storage

If a program writes data to a file on disk, it can write it to a SQLite database just as easily. If you need to write application data to disk, why not use an SQL database rather than devising a custom file format? SQLite has a further advantage over, for instance, XML data files in that the whole file does not have to be read into memory before specific elements can be readSQLite provides fast random access to the data.

Internal Data Manipulation

When an application has to perform operations on data held in internal data structures, it can be easier, and often quicker, to load that data into an in-memory SQLite database. Then it can be manipulated using SQL commands and returned to the program.