Site Moved

This site has been moved to a new location - Bin-Blog. All new post will appear at the new location.

Bin-Blog

SQLite

SQLite Logo

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.)

Features

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

Filed Under...

1 Comment:

Jay Godse said...

Hi Binny.

In this post you said,

"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."


SQLite is, in fact, a great competitor for MySQL and PostgreSQL for many applications using the LAMP platform. That is because most (e.g. 99.9 %) web applications never reach the traffic levels needed to justify a server-based database such as PostgreSQL or MySQL.

If you are running your web application as a CGI, or in an application server, your application can make direct calls to SQLite that give the functionality of a relational database, but with the performance and programming semantics of using fopen/fread/fwrite/fclose on local flat files. This generally holds true as long as you don't exceed about 1 request per second (although the SQLite folks run their system at 10 requests per second just fine).

Check out my page on SQLite at Squidoo for a different perspective on SQLite.