Data Diver

SQLite [1] (pronounced sequel-lite or S-Q-L-lite) is a public domain, embedded, relational database engine that runs on everything from smartphones to mainframes. If you use Linux or any other modern operating system, chances are good that you are already using at least one SQLite database. That alone is reason enough to learn the basics of SQLite, and it is in your interest to know not just how to back up that data, but how to generate, process, and analyze it in ways that would not be possible with other applications.

Moreover, you can install SQLite with almost zero configuration or manual work and then run it without root privileges. Finally, despite its simplicity, SQLite can handle even huge quantities of data, which means it may even help you on the job someday.

What You Will Learn

In this tutorial, I explain what SQLite is, discuss how it works, and look at a few practical ways in which to use it. Although I show only a few quick examples of actual database queries, you can find plenty of those at the SQLite website or from online cheat sheets. Instead, I focus on basic SQLite management, concepts, components, where you can find SQLite data on your desktop or smartphone, and why you might want to process it. Basic knowledge of the command line and shell scripts is all you need to take advantage of this tutorial.

Although I provide pointers to SQLite graphical interfaces, I mostly focus on what you can do at the SQLite command prompt, partly because the prompt is the only SQLite interface that is reliably available on every platform. The main reason, though, is that you can automate what you type a lot more easily than what you click.

Relational Databases

Relational databases are highly structured archives of data stored in one or more files in a low-level binary format. The data is stored in tables of rows and columns (Figure 1). Each column contains data fields of the same type (e.g., title, author, publication date, or ISBN number in a library database), and each row (or record) groups all the data referring to the same entity (e.g., all data about a specific book).

Figure 1: Like all other relational databases, SQLite organizes its data in tables (shown in the SQLite DB Browser). Each column is devoted to one type of data, and each row holds all data related to a specific object (here, a digital photograph).

The low-level textual commands to create, alter, or filter tables and records are written in a structured query language (SQL), which varies slightly from database engine to database engine. Although graphical interfaces hide SQL commands from the user, they are still used to talk with the database engine.

Features and Use Cases

SQLite is easy to manage and run because its database structure is very simple and it does not use the client-server model. Instead of requiring a server that always waits for requests from clients, SQLite is an ordinary program that launches only when needed. The core code fits everywhere because, even though it can manage terabytes of data, it has a very small memory footprint and no external dependencies.

Although executable SQLite programs are specific to an operating system, each SQLite database is a single, 100 percent cross-platform file that always has the same format. You can back it up as you do any other file or put it on a USB key for access from any computer, without the need for administrative privileges. If you want a read-only database, you just remove write permission from the corresponding files.

All of these features taken together mean that the SQLite database format is explicitly promoted and ready for use, both as a searchable archival format (but more sophisticated than TAR or ZIP files) and as a general-purpose application file format (much like a DOC, ODT, or PDF file). Rather than developing a unique file format and all the code to handle it, developers can include the SQLite library in their source and use it to store everything their program needs in a SQLite database, from configuration to user data.

Now that I have explained what SQLite is, I will look at how it uses the SQL language. The main concepts and tools you need to know, which are much easier to understand than their names suggest, are pragmas, metacommands, data types, commands, operators, and functions.