Discover how to use and probe a SQLite database
Data Diver
Several databases likely reside on your desktop and smartphone, and it is easy to manage the data in these files or to create similar databases yourself.
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).
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.
Buy this article as PDF
(incl. VAT)
Buy Linux Magazine
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs
Subscribe to our ADMIN Newsletters
Support Our Work
Linux Magazine content is made possible with support from readers like you. Please consider contributing when you’ve found an article to be beneficial.
News
-
Canonical Releases Ubuntu 24.04
After a brief pause because of the XZ vulnerability, Ubuntu 24.04 is now available for install.
-
Linux Servers Targeted by Akira Ransomware
A group of bad actors who have already extorted $42 million have their sights set on the Linux platform.
-
TUXEDO Computers Unveils Linux Laptop Featuring AMD Ryzen CPU
This latest release is the first laptop to include the new CPU from Ryzen and Linux preinstalled.
-
XZ Gets the All-Clear
The back door xz vulnerability has been officially reverted for Fedora 40 and versions 38 and 39 were never affected.
-
Canonical Collaborates with Qualcomm on New Venture
This new joint effort is geared toward bringing Ubuntu and Ubuntu Core to Qualcomm-powered devices.
-
Kodi 21.0 Open-Source Entertainment Hub Released
After a year of development, the award-winning Kodi cross-platform, media center software is now available with many new additions and improvements.
-
Linux Usage Increases in Two Key Areas
If market share is your thing, you'll be happy to know that Linux is on the rise in two areas that, if they keep climbing, could have serious meaning for Linux's future.
-
Vulnerability Discovered in xz Libraries
An urgent alert for Fedora 40 has been posted and users should pay attention.
-
Canonical Bumps LTS Support to 12 years
If you're worried that your Ubuntu LTS release won't be supported long enough to last, Canonical has a surprise for you in the form of 12 years of security coverage.
-
Fedora 40 Beta Released Soon
With the official release of Fedora 40 coming in April, it's almost time to download the beta and see what's new.