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
sqlite> select * from moz_downloads;

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;

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

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


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.


Since no one else has posted a message, I thought that asking for your assitance might be worthwhile, but maybe you do not know anything about the SDB utility. Frankly, after downloading and unpacking the software into a folder (C:\Installs\Unpack\SQLite Browser), I have discovered only two things: (1) The "browser" will not run if I move the folder to another directory, such as C:\Program Files -- which fact makes no sense to me; and, (2) When SDB does run, launched from the directory into which the files were "unpacked", then it displays blank fields on each of the three "tabs" labeled "Database Structure", "Browse Data", and "Execute SQL". After I use the icon for "Open Database File" and select one for Firefox in C:\Documents & Settings\UserAccount\Application Data\Mozilla ....\useridentity\cookies.sqlite, the title bar of SQLite Database Browser changes to show the pathname of that file, but it still does not display any data. The Help feature consists of "What's This" and "About", neither of which tell the user anything about how to persuade the program to display anything useful or informative. There is no mention of any documentation being available and I doubt that there is any. If Firefox or I.E. ran like this "browser", no one would use them. What next? With respect to your article: Quote: [i]"For example, you can look at what has been downloaded in Firefox from the command-line:"[/i] The phrase "you can" is banned from professional technical writing, but maybe you aren't being paid for the article. The purpose of "documentation" is [b]not[/b] to tell the reader what they [i]can[/i] do, it is to tell the reader [i]how[/i] to do what it is possible to do with the software. If the reader could do it, then why would they be reading your advice and instruction? If [i]you[/i] can't understand that, then you shouldn't bother to do it at all. The editors here at TechRepublic don't actually edit any copy, do they?? But perhaps I digress. Which "command line" would that be? For Linux? For the SQLite Database Browser (not that it appears to have one itself)? What OS command shell produces a CLI prompt that begins with a dollar $ign? It looks vaguely familiar, but memory fails me. Not that the answer is likely to do me any good at all, since I do not speak SQL. So I don't know any SQL commands, if that is what you mean, let alone recognize the line on which they are to be entered. Of course, I suppose that not having anything pertaining to SQL [i]per se[/i] installed and running on the computer might have something to do with that too.

Editor's Picks