Use SQLite for a simple, lightweight database option

by Vincent Danen | Sep 23, 2005 7:00:00 AM

Tags: Storage, Databases, Programming languages, Linux, Vincent Danen, database, SQLite, SQL, SQL database, Linux Tips Newsletter

Takeaway: Learn about open source SQLite, a simple, compact database option suitable for applications that don't require large database systems.

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:

<code>

$ sqlite foo.db

</code>

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:

<code>

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

</code>

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!

Print/View all Posts Comments on this article

it's not badJaqui  | 09/27/05

I never knew about sql liteDanLM  | 09/27/05

Ok, I think sqlite can fully fit your requests....3gmeet@...  | 04/04/06

Transactionsgracedman  | 09/27/05

It's a container.mgordon@...  | 09/27/05

orJaqui  | 09/28/05

Use it with OPenOfficeBadger47  | 09/28/05