Enterprise Software

Learn more about how you can use SQLite

SQLite is an SQL database engine that is self-contained, does not have a server process, and does not require configuration. Vincent Danen shows you how to get started with it.

SQLite is an SQL database engine that is self-contained, does not have a server process, and does not require configuration. In short, SQLite is a portable, embeddable, and very cool SQL system that is used by many applications. Some of these include Adobe's Photoshop Lightroom, Dropbox, Firefox, Thunderbird, and a number of Apple applications for OS X including Apple Mail and Safari.

With SQLite you can embed an SQL database into your application, or use it for storage with your application.

Knowing what applications use SQLite can also be used as a means of seeing what data is stored by the application, and allows for manipulation of that data outside of the application. For instance, because a lot of information stored by Firefox is in SQLite databases, we can interact with them using the SQLite command-line client.

For example, you can look at what has been downloaded in Firefox from the command-line:

$ cd ~/.mozilla/firefox/flsyw2mc.default/
$ sqlite3 downloads.sqlite
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
moz_downloads
sqlite> select * from moz_downloads;
4|jswat-4.5-installer.jar|http://jswat.googlecode.com/files/jswat-4.5-installer.jar|file:///tmp/jswat-4.5-installer.jar||1266700860703160|1266700886146783|1|||15034184|15034184|application/x-java-archive||4|0
5|Komodo-IDE-5.2.4-37659-linux-libcpp6-x86_64.tar.gz|http://downloads.activestate.com/Komodo/releases/5.2.4/Komodo-IDE-5.2.4-37659-linux-libcpp6-x86_64.tar.gz|file:///home/vdanen/Downloads/Komodo-IDE-5.2.4-37659-linux-libcpp6-x86_64.tar.gz||1273726172588991|1273726212529285|1|http://www.activestate.com/komodo/downloads/||51453178|51453178|application/x-gzip||0|0

What we have done here is loaded the downloads.sqlite database file that is part of the Firefox profile. The .tables command is used to see what tables are in this database — useful if we have no idea what the file contains or what its structure is. Once the table names are known, standard SQL queries are used to look at it. In this case, we can see that there have been five downloads to-date with this Firefox profile; we can see what they are and where they came from. This does not, however, give us headers for the columns, which doesn't give as much meaning to the output.

The .schema command can be used to display the structure of the table:

sqlite> .schema
CREATE TABLE moz_downloads (id INTEGER PRIMARY KEY, name TEXT, source TEXT, target TEXT, tempPath TEXT, startTime INTEGER, endTime INTEGER, state INTEGER, referrer TEXT, entityID TEXT, currBytes INTEGER NOT NULL DEFAULT 0, maxBytes INTEGER NOT NULL DEFAULT -1, mimeType TEXT, preferredApplication TEXT, preferredAction INTEGER NOT NULL DEFAULT 0, autoResume INTEGER NOT NULL DEFAULT 0);

And with this information, more sensible queries can be made:

sqlite> select name,source from moz_downloads;
jswat-4.5-installer.jar|http://jswat.googlecode.com/files/jswat-4.5-installer.jar
Komodo-IDE-5.2.4-37659-linux-libcpp6-x86_64.tar.gz|http://downloads.activestate.com/Komodo/releases/5.2.4/Komodo-IDE-5.2.4-37659-linux-libcpp6-x86_64.tar.gz

In fact, this can be done in on the command-line in a way that is useful for scripting:

$ sqlite3 downloads.sqlite "select name from moz_downloads;"
jswat-4.5-installer.jar
Komodo-IDE-5.2.4-37659-linux-libcpp6-x86_64.tar.gz

Creating an SQLite database is simple:

$ qlite3 test.sqlite
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
sqlite> CREATE TABLE test (id INTEGER PRIMARY KEY, name TEXT, number INTEGER NOT NULL DEFAULT 0);
sqlite> .tables
test
sqlite> insert into test (id, name, number) VALUES (1, 'something cool', 12);
sqlite> insert into test (id, name) VALUES (2, 'something else');
sqlite> select * from test;
1|something cool|12
2|something else|0

To create and initialize a new database, simply provide the name of the file to store it in. In this case it is test.sqlite. The file name does not matter; it can be .sqlite or .db or even .whatever. The first .tables command showed that no tables were defined, then the next SQL statement created the "test" table, and a subsequent SQL statement inserted data. Also note that you can have more than one table per database.

If your database needs are light, SQLite is perfect. It has a proven track-record, and a number of high-profile applications use it. It is also very lightweight and fast. Most SQL features are available, although there are a very few commands that are not, such as full ALTER TABLE support and authentication-related commands such as GRANT and REVOKE (the latter are pretty redundant with SQLite). Otherwise, SQLite supports the features of SQL92. As well, the database is fully portable and can be used on any operating system that has a build of SQLite available.

In short, if you ever felt like you needed an SQL database to store information, data or binary blobs of "stuff," give SQLite a look. It definitely wins in the overhead department against the likes of MySQL and PostgreSQL.

About

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.

Editor's Picks