Embedded SQL that 'just works'
Almost all modern database systems provide an SQL interface for viewing and manipulating the stored data. Although an ISO standard exists for the language, SQL code is barely portable for a variety of reasons. This puts an application programmer into the unpleasant position to either support several different database back-ends or force the user to go with a specific vendor. Extra fun is added when the user has to run multiple versions of the same product due to broken backward compatibility.
There is a third way, however. You can build your application with an embedded SQL library like SQLite. In several ways, SQLite is completely different to the usual database systems:
If you use an abstract database interface like DBI for Perl, you won't notice much of these things. You will only have to change your code concerning different SQL extensions, which shouldn't cause major trouble.
Databases are created simply by opening them. You can immediately create tables, insert data and all other things you like. A command-line tool is available as well, which is very similar to those accompanying the major database servers.
SQLite offers a pretty robust database model that supports transactions. These are implemented using file locking mechanisms of the operating system. Therefore, if a process wants to write to the database, all other process have to wait for read or write access until the transaction has been completed. This is of course a drawback in an environment where high concurrency is required, but should not be relevant in most situations. Transactions in SQLite fulfill the ACID (Atomic, Consistent, Isolated, Durable) properties.
When playing around with SQLite you will notice that there only few functions available. Some date and time conversions are possible, but mathematical and string functions are nonexistent. This is a downside, but you will probably be able to perform these operations within your application.
To be honest, not every application allows to use an embedded SQL library. Besides high concurrency requirements, very large databases (i.e. more than several GB) or high-volume websites are probably better off with a conventional system. The SQLite documentation offers some insight on when to use it and when not to. But there is no excuse for every tiny web application to require MySQL or PostgreSQL.
To quote the SQLite documentation: "SQLite just works." It is the right choice whenever you can avoid to use a large database server. Especially web developers should do their users a favor and get rid of unnecessary dependencies.