SQLite is one of the smallest DBMS available. Unlike other RDBMS solutions like MySQL, PostgreSQL etc. SQLite does not have a Client-Server architecture. SQLite is a embedded into the software using it. The main advantage of this is you can create an application that uses a database without expecting the end user to have a database server.
SQLite is a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine.
SQLite Official Site
SQLite is in no way a competition for MySQL or PostgreSQL as they tend to different needs. In the context of the LAMP platform, it is rare to see SQLite being used in a web server as most servers have MySQL(or PostgreSQL) server running. SQLite is more relevent in the application field where the user may not have a server running.
In client-server model RDBMS like MySQL, PostgreSQL, etc. there will be a server running in the background at all times. For example, take MySQL. It uses a background process 'mysqld'(mysql daemon). When a program(like PHP on Apache) tries to execute an SQL command, this program will communicate with the background process and give the SQL command to the server. The server will then execute it and return the status.
In contrast, SQLite is embedded into the application that uses it. For example amaroK music player, uses SQLite to store its playlist and media library data. This application can read and write to the database using SQL commands without the need for a database server to be running on the user's system.
Some well known applications that use SQLite are...
- amaroK MP3 Player
- Yum - Yellow Dog Updater
- Banshee Music Player
(Well, they are well known if you use linux.)
The features for SQLite include...
- ACID compliant.
- Zero-configuration - no setup or administration needed.
- A complete database is stored in a single file.
- Backing up, Exporting/Importing is very easy
- Supports databases up to 2 terabytes in size.
- Sizes of strings and BLOBs limited only by available memory.
- Very small code footprint.
- Faster than popular client/server database engines for most common operations.
- Easy to use API.
- Self-contained: no external dependencies.
- Open Source