Use SQLite for a simple, lightweight database option

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s