Features and Limitations

In this section we will look at the key features of SQLite and some of its limitations. The nature of SQLite makes it an ideal choice for quite a number of tasks, but it's not suitable for everything.

It is important to decide whether SQLiteor any other database engine for that matteris the right choice for your application before committing to a particular technology.

Speed

SQLite is extremely efficient, benefiting from a highly optimized internal architecture and a small memory footprint. Because SQLite is not a client/server database, the overheads of running a database daemon and socket communication are eliminated.

The published speed comparison at http://www.sqlite.org/speed.html compares SQLite to both MySQL and PostgreSQL. It finds that SQLite can perform up to 20 times faster than PostgreSQL and more than twice as fast as MySQL for common operations.

These tests were performed with default installations of each database, and although it is possible to tune the MySQL and PostgreSQL servers for slightly better performance in a given environment, SQLite does not require any such optimization.

The tests found that SQLite is significantly slower than the other databases only on the operations to create an index and to drop a table. However, slowness in these areas will not affect performance on a production database.

Portability

Because SQLite databases are stored as single files on the filesystem, they are very portable indeed. A database can be copied from one file to another, even across different operating systems. This means that for a cross-platform distribution you just need to concentrate on making your code portableeven when a populated database is to be shipped with the application.

SQLite has no external dependencies. The SQLite library is self-contained, so the only system requirement to run an application with an embedded SQLite database is the SQLite library itself. Because SQLite can be freely distributed, you can always ensure that this is present.

Security

SQLite databases are stored to the filesystem and access control is performed by the underlying operating system based on that file's permission settings.

Though SQLite can be accessed by processes running as different users if the correct file permissions are set, the database engine does not detect which user is performing a particular operation.

The advantage of this is one of administrative simplicitythere is no need to set up a complex user grants scheme. Any user who has access to read the database file is able to access the database tables and records. Likewise, in a shared environment, users are able to create their own SQLite databases to their file space without any involvement from the system administrators.

The disadvantage comes when you want to control permissions at a more finely grained level. There is no GRANT operation that would allow access to particular tables to one set of users but not others. If users have read access, they are able to read the entire database, and if they have write access, you have to be sure of their competence and trustworthiness with the data!

SQL Implementation

SQLite supports a large subset of the ANSI SQL-92 standard. Some features have a limited implementation and a few features are not supported at all.

For example, atomic transactions are available but cannot be nested; simple subqueries are possible but correlated subqueries are not; triggers can fire for each row but not for each statement; views are available but are read-only.

The list of current limitations is maintained at http://www.sqlite.org/omitted.html with the items at the top of the list indicating which items are most likely to be added to SQLite first.

In the vast majority of cases, none of the limitations of SQLite will cause problems when developing your application. For those that you need to work around, the benefits of using a fast, portable embedded database will almost certainly outweigh the cost of the workaround.

Customization

The SQLite library includes a very powerful mechanism for adding user-defined functions to the SQL command set. Custom functions can even be written in many of the supported language APIs, not just C/C++.

Both simple functions and aggregating functions can be added this way and, as the hard work is done in the application code, there is no need to rebuild the SQLite library to add new functionality.

Additionally, as the SQLite source code is public domain, you are free to examine and modify it as you see fit. If SQLite is missing a feature that you absolutely must have, why not add it yourself and give something back to the community?

Supported APIs

SQLite now has extensive support for other programming languages through APIs that use the underlying C/C++ interface to communicate with SQLite database files.

C/C++

The core interface is implemented as a single library called libsqlite.so on Linux/Unix systems and sqlite.dll on Windows.

Only the SQLite library is required to allow all users to create their own databases, so very little administration is required to add SQL database capability to a shared system.

PHP

Support for SQLite in PHP has been available for a while, but since the release of PHP 5, it has been shipped with the standard distribution.

Traditionally, PHP and MySQL have gone hand in hand as the interface and back end for dynamic web sites, but it is expected that many more web hosting providers will offer SQLite as PHP 5 gains popularity. From the host's point of view, it is much simpler to administer than a client/server database as there are no complex permissions to manage and database files will be already counted in the disk quota.

Note

SQLite has proved itself to work very well with low- to medium-traffic web sites. As a very rough guideline, if you are expecting over 100,000 hits per dayand in practice, only a small number of web sites will have this level of trafficyou should consider how much database work is done by the web scripts and think about doing some kind of stress testing before committing to SQLite as your back end.

Perl

Perl allows access to SQLite through the Database Interface (DBI) module. This makes it very easy for existing Perl developers to use SQLite within their scripts. The DBI provides an abstraction layer to the Database Driver (DBD), so the same command set is used to access many different types of underlying databases.

The DBD::SQLite driver further allows access to the capabilities of SQLite that the Perl DBI does not allow. Although this does not create a Perl application that can be easily ported to another database back end, it does allow access to the powerful user-defined functions feature.

Tcl

The Tcl interface for SQLite is shipped as part of the SQLite distribution as a library that is imported into Tcl to activate the extensions.

Using Tcl and Tk together with SQLite, you have a platform that is ideal for rapid development of portable graphical user applications.

Other Programming Languages

SQLite has APIs for many other programming languages, including Java, .NET, Smalltalk, and Ruby. As more languages become supported, they are added to the list at http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers.

Scalability

The downside of using a single file to store databases is that SQLite is not as scalable as many client/server database systems.

The SQLite engine can address database files of up to 2TB in size; however, the restriction on the size of a database is more likely to be enforced by your operating system. In many cases, the size limit on a single file is 2GB.

File locking in SQLite is very coarse-grained. When a write operation takes place, the entire file is locked so that no other process can open it for reading or writing. Larger RDBMSs implement locking at the table or row level so that other processes are able to carry on working unless they are trying to access a specific piece of locked data.

Therefore, if you have a database that is likely to involve massive database files or a high frequency of slow write operations, SQLite may not be suitable and you should consider an RDBMS that is designed and tuned for multiple-user access.