Applications very often save data. Whether your users are creating simple text documents, complex graphic layouts, game progress, or an intricate list of customers and order numbers, software usually implies that there's data being generated. There are many ways to store data for repeated use. You can dump text to configuration formats such as INI or
YAML
, XML, or JSON, or you can write out raw binary data, or you can store data in a structured database. SQLite is a self-contained, lightweight database that makes it easy to create, parse and query, modify, and transport data.
SQLite has been dedicated to the
public domain
, which
technically means it is not copyrighted and therefore requires no license
. Should you require a license, you can
purchase a Warranty of Title
. SQLite is immensely common, with an estimated 1 *trillion* SQLite databases in active use (that's counting multiple databases on every Android and iOS device, every macOS and Windows 10 computer, most Linux systems, within every Webkit-based web browser, modern TV sets and automotive multimedia systems, and countless other software applications).
In summary, it's a reliable and simple system to use for storing and organizing data.
Installing {#_installing}
You probably already have SQLite libraries on your system, but for you to use it directly you need its command-line tools installed. On Linux, you probably already have these tools installed. The command it provides is `sqlite3` (not just `sqlite`).
If you don't have SQLite installed on Linux or BSD, you can install it from your software repository or ports tree, or
download and install it
from source code or as a compiled binary.
On macOS or Windows, you can download and install SQLite tools from
sqlite.org
.
Using SQLite {#_using_sqlite}
It's common to interact with a database through a programming langauge. For this reason, there are SQLite interfaces (or "bindings") for Java, Python, Lua, PHP, Ruby, C++, and many many others. However, before using these libraries, it helps to have an understanding of what's actually happening with the database engine, and why your choice of a database is significant. This article introduces you to SQLite and the `sqlite3` command so you can get familiar with the basics of how this database handles data.
Interacting with SQLite {#_interacting_with_sqlite}
You can interact with it using the `sqlite3` command. This provides an interactive shell so you can view and update your databases.
----[source,bash] $ sqlite3 SQLite version 3.34.0 2020-12-01 16:14:00 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite>
This places you in an SQLite subshell, and so your prompt is now an SQLite prompt.
Your usual Bash commands don't work here.
You must use SQLite commands.
To see a list of SQLite commands, type `.help`:
----[source,bash]
sqlite> .help
.archive ... Manage SQL archives
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.cd DIRECTORY Change the working directory to DIRECTORY
[...]
Some of these commands are binary, while others require unique arguments (like filenames, paths, and so on). These are administrative commands for your SQLite shell, and are not database queries. Databases take queries in Structured Query Language (SQL), and many SQLite queries are the same as what you may already know from the [MySQL and MariaDB databases]. However, data types and functions differ, so pay close attention to minor differences if you're familiar with another database.
Creating a database {#_creating_a_database}
When launching SQLite, you can either open a prompt in memory, or you can provide a database to open:
----[source,bash] $ sqlite3 mydatabase.db
If you have no database yet, you can create one at the SQLite prompt:
----[source,bash]
sqlite> .open mydatabase.db
You now have an empty file on your harddrive, ready to be used as an SQLite database. The file extension `.db` is arbitrary. You can also use `.sqlite`, or whatever you want.
Creating a table {#_creating_a_table}
Databases contain *tables*, which can be visualized as a spreadsheet. There's a series of rows (called *records* in a database) and columns. The intersection of a row and a column is called a *field*.
The Structured Query Language (SQL) is named after what it provides: a method to inquire about the contents of a database in a predictable and consistent syntax in order to receive useful results. SQL reads a lot like an ordinary English sentence, if not a little robotic. Currently your database is empty, devoid of any tables.
You can create one with the `CREATE` query. It's useful to combine this with the `IF NOT EXISTS` statement, which prevents SQLite from clobbering an existing table.
You can't create an empty table in SQLite, so before trying a `CREATE` statement, you must think about what kind of data you anticipate the table will be used to store. In this example, I'll create a table called `member` with these columns:
- The date and time of data entry.
Unique ID {#_unique_id}
It's always good to be able to refer to a record by a unique number, and luckily SQLite recognizes this and does it automatically for you in a column called `rowid`.
No SQL statement is required to create this field.
Data types {#_data_types}
For my example table, I'm creating a `name` column to hold TEXT data. To prevent a record from being created without data in a specific field specified, you can add the `NOT NULL` directive.
The SQL to create this field is: `name TEXT NOT NULL`.
There are five data types (actually **storage classes**) in SQLite:
- REAL: a floating point (unlimited decimal places) number.
- BLOB: binary data (for instance, a `.jpeg` or `.webp` image).
Date and time stamp {#_date_and_time_stamp}
SQLite includes a convienient date and timestamp function. It is not a data type itself, but a function in SQLite that generates either a string or integer, depending on your desired format. In this example, I leave it as the default.
The SQL to create this field is: `datestamp DATETIME DEFAULT CURRENT_TIMESTAMP`.
Table creation SQL {#_table_creation_sql}
The full SQL for creating this example table in SQLite:
----[source,bash] sqlite> CREATE TABLE ...> IF NOT EXISTS ...> member (name TEXT NOT NULL, ...> datestamp DATETIME DEFAULT CURRENT_TIMESTAMP
In this code sample, I pressed **Return** after the logical clauses of the statement to make it easier to read.
SQLite won't run your command unless it terminate with a semi-colon (**;**).
You can verify that the table has been created with the SQLite command `.tables`:
----[source,bash]
sqlite> .tables
member
View all columns in a table {#_view_all_columns_in_a_table}
You can verify what columns and rows a table contains with the `PRAGMA` statement:
----[source,bash] sqlite> PRAGMA table_info(member); 0|name|TEXT|1||0 1|datestamp|CURRENT_TIMESTAMP|0||0
== Data entry
You can populate your new table with some sample data using the `INSERT` SQL keyword:
----[source,sql]
> INSERT INTO member (name) VALUES ('Alice');
> INSERT INTO member (name) VALUES ('Bob');
> INSERT INTO member (name) VALUES ('Carol');
> INSERT INTO member (name) VALUES ('David');
Verify the data in the table:
----[source,sql] > SELECT * FROM member; Alice|2020-12-15 22:39:00 Bob|2020-12-15 22:39:02 Carol|2020-12-15 22:39:05 David|2020-12-15 22:39:07
=== Adding multiple rows at once
Now create a second table:
----[source,sql]
> CREATE table IF NOT EXISTS linux (
...> distro TEXT NOT NULL)
Populate it with some sample data, this time using a little `VALUES` shortcut so you can add multiple rows in just one command. The `VALUES` keyword expects a list in parentheses, but can take multiple lists separated by commas:
----[source,sql] > INSERT INTO linux (distro) ...> VALUES ('Slackware'), ('RHEL'), ...> ('Fedora'),('Debian');
== Altering a table
You now have two tables, but as yet there's no relationship between the two.
They each contain independent data, but feasibly you might have a need to associate a member of the first table to a specific item listed in the second.
To do that, you can create a new column for the first table that corresponds to something in the second.
Because both tables were designed with unique identifiers (automatically, thanks to SQLite), the easiest way to connect them is to use the `rowid` field of one as a selector for the other.
Create a new column in the first table to represent a value in the second table:
----[source,sql]
> ALTER TABLE member ADD os INT;
Using the unique IDs of the `linux` table, assign a distribution to each member. Because the records already exist, you use the `UPDATE` SQL keyword rather than `INSERT`. Specifically, you want to select one row and then update the value of one column. Syntactically, this is expressed a little in reverse, with the update happening first and the selection matching last:
----[source,sql] > UPDATE member SET os=1 WHERE name='Alice';
Repeat this process for the other names in the `member` table, just to populate it with data.
For variety, assign 3 different distributions across the 4 rows (doubling up on one).
== Joining tables
Now that these two tables relate to one another, you can use SQL to display the associated data.
There are many kinds of joins in databases, but you can try them all once you know the basics.
Here's a basic join to correlate the values found in the `os` field of the `member` table to the `id` field of the `linux` table:
----[source,sql]
> SELECT * FROM member INNER JOIN linux ON member.os=linux.rowid;
Alice|2020-12-15 22:39:00|1|Slackware
Bob|2020-12-15 22:39:02|3|Fedora
Carol|2020-12-15 22:39:05|3|Fedora
David|2020-12-15 22:39:07|4|Debian
The `os` and `id` fields form the join.
You can imagine, in a graphical application, that the `os` field might be set by a drop-down menu, the values for which are drawn from the contents of the `distro` field of the `linux` table. By using separate tables for unique but related sets of data, you ensure consistency and validity of data, and thanks to SQL, you have the ability to associate them dynamically later.
Learning more {#_learning_more}
SQLite is an infinitely useful self-contained, portable, open source database. Learning to use it interactively is a great first step toward managing it for web applications or using it through programming language libraries.
If you enjoy SQLite, you might also try
Fossil
by the same author, Dr. Richard Hipp.