Discover how to use and probe a SQLite database
Data Diver
![© Lead Image © Leo Blanchette, 123RF.com © Lead Image © Leo Blanchette, 123RF.com](/var/linux_magazin/storage/images/issues/2016/186/sqlite-tutorial/po-26213-123rf-leo_blanchette_123rf-bot_with_code_resized.png/669111-1-eng-US/PO-26213-123RF-Leo_Blanchette_123RF-Bot_with_code_resized.png_medium.png)
© Lead Image © Leo Blanchette, 123RF.com
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).
![](/var/linux_magazin/storage/images/issues/2016/186/sqlite-tutorial/figure-1/669114-1-eng-US/Figure-1_large.png)
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.
![Learn More](https://www.linux-magazine.com/var/linux_magazin/storage/images/media/linux-magazine-eng-us/images/misc/learn-more/834592-1-eng-US/Learn-More_medium.png)
News
-
NVIDIA Released Driver for Upcoming NVIDIA 560 GPU for Linux
Not only has NVIDIA released the driver for its upcoming CPU series, it's the first release that defaults to using open-source GPU kernel modules.
-
OpenMandriva Lx 24.07 Released
If you’re into rolling release Linux distributions, OpenMandriva ROME has a new snapshot with a new kernel.
-
Kernel 6.10 Available for General Usage
Linus Torvalds has released the 6.10 kernel and it includes significant performance increases for Intel Core hybrid systems and more.
-
TUXEDO Computers Releases InfinityBook Pro 14 Gen9 Laptop
Sporting either AMD or Intel CPUs, the TUXEDO InfinityBook Pro 14 is an extremely compact, lightweight, sturdy powerhouse.
-
Google Extends Support for Linux Kernels Used for Android
Because the LTS Linux kernel releases are so important to Android, Google has decided to extend the support period beyond that offered by the kernel development team.
-
Linux Mint 22 Stable Delayed
If you're anxious about getting your hands on the stable release of Linux Mint 22, it looks as if you're going to have to wait a bit longer.
-
Nitrux 3.5.1 Available for Install
The latest version of the immutable, systemd-free distribution includes an updated kernel and NVIDIA driver.
-
Debian 12.6 Released with Plenty of Bug Fixes and Updates
The sixth update to Debian "Bookworm" is all about security mitigations and making adjustments for some "serious problems."
-
Canonical Offers 12-Year LTS for Open Source Docker Images
Canonical is expanding its LTS offering to reach beyond the DEB packages with a new distro-less Docker image.
-
Plasma Desktop 6.1 Released with Several Enhancements
If you're a fan of Plasma Desktop, you should be excited about this new point release.