Everyone knows about open source database giants such as MySQL
and PostgreSQL; these free SQL database servers are commonly used by Web applications
and other programs. For some uses, however, large database systems like this
are overkill. If you need a simple, lightweight SQL database, look no further
than SQLite.

SQLite is an embeddable database system that uses flat files. It
does not need to be started, stopped, configured, or managed like other SQL
databases. It is lightweight, fast, and compact. And it works completely out of
the box without any configuration. Many Linux vendors are shipping SQLite, so
you can either download and compile from source or install packages from your
Linux vendor.

To create a new database, simply use:

$ sqlite foo.db

This creates the database file foo.db in the current
directory. This will also fire up SQLite and allow you to start using SQL
statements. For example:

sqlite> CREATE TABLE config(id int(5), name varchar(16),
values text, PRIMARY KEY (id));
sqlite> INSERT INTO config (id, name, value) VALUES (1, 'foo', 'some text');
sqlite >SELECT * FROM config;
1|foo|some text
sqlite> .quit

As you can see, it works just like any other big SQL database.
Some of the commands are different; for example, use .quit to quit the program, .databases
to see what databases are available, and .help
to get a help listing. The SQL syntax is nearly identical to what you would
find in other big SQL database programs.

When you quit the program, there is no server hanging around
eating up CPU and memory. To manipulate the database again, simply reconnect.
For large Web applications or programs involving multiple users making changes
at the same time, SQLite may not be the best thing to use. But for simple, single-user,
day-to-day use, SQLite will most likely do what you need.

Delivered each Tuesday, TechRepublic’s free Linux NetNote provides tips, articles, and other resources to help you hone your Linux skills. Automatically sign up today!