Using SQLite for Simple Database Storage
(Page 1 of 6 )
Web applications frequently need a persistent data store, and a database is often the most appropriate choice. Although some users have succeeded in using a file-based database engine such as Access, getting full SQL compliance usually involves a database engine. For a large number of applications, this adds unnecessary complexity. SQLite is a good compromise between simplicity and full SQL compliance.
SQLite can be downloaded for free from http://www.sqlite.org. This site contains full documentation, binary versions for Linux and Windows, and wrappers for a very wide variety of languages.
Features
SQLite is a disk-based database engine. The entire database resides in a single disk file. Access control is provided by the operating system's own security.
Most of SQL92 is supported, including views, sub queries and triggers. The database is transaction compliant, although any transaction locks the entire database until it has completed. It's easier to list the features that it doesn't support. It doesn't support foreign key constraints, alter table statements, writable views, right and full outer joins, and the grant and revoke statements. These are only the features that I use (or would use) regularly. A complete list is available as part of the documentation.
The one thing that isn't supported that might confuse people is data typing. As far as SQLite is concerned, all data types are strings. As a C and C++ programmer, I revolted at this idea until I had a chance to put it into use. As long as access to the database is controlled by the program, and you design uniform interfaces, the consistency checks provided by data typing are not as necessary. For a language like PHP, which has a fairly flexible concept of data typing, the typeless database is pretty much unnoticed.
SQLite is natively accessible through a C interface library that is remarkably easy to use. It is also natively supported by PHP 5, and via an add-on module to PHP 4. Wrapper libraries make it accessible to TCL, a COM interface makes it available for Visual Basic, ASP, ADO.Net, and the latest beta of Zeos Database Objects from http://www.zeoslib.net make SQLite available as a native database type to Delphi users.
The database engine also allows multiple simultaneous users.
Concurrency support is somewhat weak, because locking occurs on the entire database using the operating system's file locking support.
When to Use SQLite
Because it doesn't require a database engine which has to be installed and maintained, SQLite is great for any situation where SQL support is necessary but simplicity is desired.
If the application is to have multiple users, SQLite is a solid choice if most access is reading. Because of the locking issues, multi user applications with a high ratio of writes to reads and a heavy user load are not a good match with SQLite. For a situation like that, a server database engine is preferred.
This combination of factors make SQLite a good choice for a lot of web applications, which make heavy use of persistent storage, but don't need to support a lot of interactive users at one time. Guest book and user preference tools are good candidates because they typically require a very small number of writes per user and a whole lot of reads.
Next: SQLite in Action >>
More Database Articles
More By Clay Dowling
/ 24 
