Last updated: January 16 2025 09:40
SQLite so light,
Where mortals find the way,
Data in stillness.
Dear Reader, this document is a work in progress and is not yet complete. It may contain typos, as well as factual, logical or grammatical errors. :) I’d love it if you could send me your thoughts via email using the name sqlitemortal and the gmail domain!
This website helps me scratch my own itch. Over time, I’ve realized that the scope of what I use SQLite for is limited, and as a result, the number of features I’m familiar with is also limited, which is natural. For this reason, I’ve decided to review things I already know and learn things I don’t know yet using the learning-by-writing method. I’m working on it primarily for myself, but I’ll be happy if you also find it useful for your own needs.
SQLite is serverless. Learn this mantra. When people discuss using it over a network and compare it to server-based database systems, it’s either due to ignorance or trolling. SQLite is not designed to replace or compete with those systems. Currently, there is no competitor to SQLite for its intended use. SQLite is absolutely unique.
SQLite does not compete with client/server databases. SQLite competes with fopen() — D. Richard Hipp.
This website is regularly updated. It’s not something I create and forget. SQLite is evolving, and new features or fixes are constantly being added, which means the content of this website changes as well. The information about the latest update is at the beginning of the page.
To obtain SQLite visit the download secion of the official SQLite website.
You’ll see options for different operating systems. Select the appropriate one based on whether you’re using Windows, macOS, or Linux. For most users, downloading the precompiled binaries is the easiest option. If you are running Linux or macOS SQLite is often pre-installed, but if not you can use your package manager to install it.
The best bet to compile your very own sqlite3 is to download the file
named sqlite-autoconf-[version].tar.gz, and use the autoconf machinery
tar zxvf it, cd into unpacked directory and run the following commands:
./configure --prefix=/home/$USER/app/sqlite3 --enable-editline
make
make install
If everything went smoothly, the directory specified in the --prefix
option of the configure program should have the following structure
and content:
skull: ~ $: tree app/sqlite3/
app/sqlite3/
|-- bin/
| `-- sqlite3*
|-- include/
| |-- sqlite3.h
| `-- sqlite3ext.h
|-- lib/
| |-- libsqlite3.a
| |-- libsqlite3.la*
| |-- libsqlite3.so.8.6*
| `-- pkgconfig/
| `-- sqlite3.pc
`-- share/
`-- man/
`-- man1/
`-- sqlite3.1
7 directories, 8 files
skull: ~ $:
Note: SQLite is statically compiled, to play with it, the only file you need is the sqlite3 executable in bin directory. You can place it anywhere and delete the rest.
Optionally, you can create a symlink to the sqlite3 binary and place it in a directory that is part of your $PATH variable. For example, you can do this as follows:
ln -s /home/$USER/app/sqlite3/bin/sqlite3 /home/$USER/bin/sqlite3
Note: It’s worth noting that the only OS I’ve been using on desktop
and servers since 2017 is OpenBSD.
Therefore, I might be mistaken regarding other operating systems. As
shown in the configure command, I use editline library; on Linux
or macOS, it might be readline, so you will need to use
--enable-readline option.
An SQLite database is a single ordinary file that holds all the data for an application. It organizes the data into tables, which are like lists with rows and columns, making it easy to store and retrieve information. Everything is packed into one file, so there’s no need for a separate system to manage it. You can think of an SQLite database file like a spreadsheet file. It’s a single file that stores all the data in an organized way, with tables similar to sheets in a spreadsheet.
You can perform any operations on this file that you do with other files, such as copying, renaming, deleting it, and just like any other file you send by email or through an instant messenger, the SQLite database file can be easily shared with your friends and colleagues. It works across different operating systems, whether the recipient is using Windows, macOS, or Linux, without any compatibility issues. Sharing the file is as simple as sending any other document.
The program used to create and work with SQLite databases has the surprising name sqlite3 (sqlite3.exe on Windows). It is often referred to as the sqlite shell. This program is used in the terminal, in the command-line interface (CLI). There are also graphical user interfaces (GUIs) available, and personally, I sometimes use a program called SQLite Browser. However, GUIs will not be the focus of this website.
Open your terminal window and issue the following command:
skull: ~ $: sqlite3
On Windows simply run sqlite3.exe
If everything works well sqlite3 program will show a brief banner message and then prompt you to enter commands:
SQLite version 3.47.2 2024-12-07 20:39:59
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
Now, to exit the shell, type .exit and press Enter key. It’s easier than leaving the vi editor for the first time.
sqlite>.exit
Command .exit is so-called dot-command. If you run .help dot-command you’ll see the list of all dot-commands available.
In SQLite, creating or opening a database is essentially the same. If the database does not exist, it is automatically created.
If we omit the database file name, sqlite3 creates a temporary in-memory database, which is destroyed after the sqlite3 shell exits.
There are two ways to create or open a database. The first is by issuing the sqlite3 command with the database name:
skull: ~ $: sqlite3 database1.sq3
The second way is by issuing sqlite3 command without database file name at first:
skull: ~ $: sqlite3
and then issue the dot-command .open with database file name
sqlite> .open database1.sq3
Note: As you may have noticed, I use the “.sq3” extension for database file names. You can use any other extension, or none at all, depending on the naming rules of your operating system. The “.sq3” extension allows me (since it’s a bit unusual) to easily find all SQLite databases on my computer. It also indicates the major SQLite version that created the database… I’m already looking forward to SQLite4 :)
SQLite dot-commands are special commands used within the SQLite command-line interface (CLI) to perform various tasks that aren’t directly related to SQL queries. They begin with a dot (.), and unlike SQL commands, they’re processed by the SQLite shell rather than the database engine itself.
To list available dot-commands open the shell and type .help into command prompt. Shortened output looks like this:
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
.cd DIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
....
sqlite>
If you want to know more about particular command type .help .command into command prompt:
sqlite> .help .backup
.backup ?DB? FILE Backup DB (default "main") to FILE
Options:
--append Use the appendvfs
--async Write to FILE without journal and fsync()
.save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...)
sqlite>
In the SQLite shell, every command must end with a semicolon (;) to signal its end. To execute a command, simply press the enter key. Commands can span multiple lines if needed, but they must still end with a semicolon.
Commands that insert, update, or delete data usually don’t return anything, while commands like SELECT will return data in the form of a result set, typically displayed as rows and columns.
Example of multi-line SQL command input:
sqlite> CREATE TABLE books (
(x1...> id INTEGER PRIMARY KEY,
(x1...> title TEXT,
(x1...> description TEXT,
(x1...> author TEXT
(x1...>);
sqlite>
Note: ...> is called a continuation prompt. (x1…> indicates
that there is one open parenthesis without a corresponding closing
parenthesis.
Example of single-line SQL command input:
sqlite> INSERT INTO books (title, description, author) VALUES ('Book 1 Title', 'Description of the first book.', 'Author 1');
sqlite> INSERT INTO books (title, description, author) VALUES ('Book 2 Title', 'Description of the second book.', 'Author 2');
sqlite> INSERT INTO books (title, description, author) VALUES ('Book 3 Title', 'Description of the third book.', 'Author 3');
sqlite> ....
There are various ways how to display result of a command returning data.
This is when the dot-command .mode comes to the scene. Use this command
without arguments to see current display mode
sqlite> .mode
Use .help mode dot-command to view all available diplay modes with
short description
sqlite> .help .mode
The default display mode is list. To change it run the following command:
sqlite> .mode column
sqlite> select * from books;
id title description author
-- ------------ -------------------------------- --------
1 Book 1 Title Description of the first book. Author 1
2 Book 2 Title Description of the second book. Author 1
3 Book 3 Title Description of the third book. Author 1
...
sqlite>
When you are in SQLite shell sqlite3 reads input directly from keyboard, whether it’s SQL statement or dot-commands.
If you want to execute an SQL from a file while still being able to enter other commands interactively, you can use the .read dot-command. This command accepts a single argument, usually the name of the file containg the input text:
sqlite> .read commands.sql
Note: commands.sql may contain dot-commands
If you want to write output of SELECT statement into file instead of standard output you can use dot-command .output and set mode and column separator for it:
skull: ~ $: sqlite example.sq3
SQLite version 3.47.2 2024-12-07 20:39:59
Enter ".help" for usage hints.
sqlite> .mode list
sqlite> .separator ,
sqlite> .output out.txt
sqlite> select * from books;
sqlite> .exit
skull: ~ $: cat out.txt
1,Book 1 Title,Description of the first book.,Author 1
2,Book 2 Title,Description of the second book.,Author 1
3,Book 3 Title,Description of the third book.,Author 1
4,Book 4 Title,Description of the fourth book.,Author 2
5,Book 5 Title,Description of the fifth book.,Author 2
6,Book 6 Title,Description of the sixth book.,Author 3
7,Book 7 Title,Description of the seventh book.,Author 3
skull: ~ $:
You can store data from a file (commonly binary data) into a single column using the readfile() function. For example, if you want to store an image in a table column, you can do it like this:
sqlite> CREATE TABLE icons(name TEXT, img BLOB);
sqlite> INSERT INTO icons(name, img) VALUES ('book', readfile('book.jpg'));
To extract data (typically BLOB) from a column and save it into a file, you can use the writefile() function. For example:
sqlite> SELECT writefile('book.jpg', img) FROM icons WHERE name='book';
This feature is great! It’s made possible by the edit() function, which allows you to edit the content of a particular column using an external program. For example, I use it when I need to edit markdown files stored in the database. You can invoke it like this:
sqlite> UPDATE md_files SET content=edit(content) WHERE ...;
This SQL command opens your default editor (e.g., vi), where you can change the content. Once you exit the editor, the content of the column for that particular row is updated.
If you want to update an image stored in a column, you can run something like this:
sqlite> UPDATE images SET img=edit(img, 'inkscape') WHERE ...;
You can also use an external program as a viewer. In this case, you simply ignore the return value:
sqlite> SELECT length(edit(img, 'inkscape')) WHERE ...;
Importing data into SQLite
To import data into SQLite, you can use the .import command. This command requires two arguments: the source file to be read and the SQLite table where the data will be inserted.
Example CSV file:
id;title;description;author
1;1984;Dystopian novel by George Orwell;George Orwell
2;Animal Farm;Political satire by George Orwell;George Orwell
3;Brave New World;Dystopian novel by Aldous Huxley;Aldous Huxley
4;The Doors of Perception;Essay by Aldous Huxley;Aldous Huxley
5;Fahrenheit 451;Dystopian novel by Ray Bradbury;Ray Bradbury
...
In this example, the first row contains column names. SQLite can
automatically create the target table based on these column names during
import. If you prefer to create the table manually, you must use the
--skip option with value 1 in the .import command to skip the header row.
sqlite> CREATE TABLE books (
(x1...> id INTEGER PRIMARY KEY,
(x1...> title TEXT,
(x1...> description TEXT,
(x1...> author TEXT
(x1...>);
sqlite>
Now we can import data from a file where the column separator is a
semicolon (;) and the record separator is a newline character (\n).
Since we manually created the table, we use the --skip option with the
.import command to skip the header row.
sqlite> .mode csv
sqlite> .separator ;
sqlite> .import --skip 1 books.csv books
sqlite> .mode column
sqlite> select * from books;
id title description author
-- ----------------------- --------------------------------- -------------------
1 1984 Dystopian novel by George Orwell George Orwell
2 Animal Farm Political satire by George Orwell George Orwell
3 Brave New World Dystopian novel by Aldous Huxley Aldous Huxley
4 The Doors of Perception Essay by Aldous Huxley Aldous Huxley
5 Fahrenheit 451 Dystopian novel by Ray Bradbury Ray Bradbury
...
Exporting data from SQLite
To export data from SQLite, use the .once command. This command requires
a single argument, the file name where the exported data will be written.
Steps for exporting:
.headers command to include column names in the first row..mode to csv..separator command..once command followed by an SQL query to export the desired data.sqlite> .headers on
sqlite> .mode csv
sqlite> .separator |
sqlite> .once books_export.csv
sqlite> select * from books;
All data returned by the SELECT query are now stored in the books_export.csv file, including a header row and using the specified separator.
Exporting a database’s structure and data is useful for future restoration. The export file contains SQL commands to recreate tables and reinsert the data.
If you want to export everything, use the following commands. This will
create a database1.sql file, which can later be used to restore the
entire database. Use the .output dot-command again to restore the
output to the terminal to prevent additional content from being written
to the file.
sqlite> .output database1.sql
sqlite> .dump
sqlite> .output stdout
To export only the table structure, use the .schema dot-command after setting up the output file:
sqlite> .output database1.sql
sqlite> .schema
sqlite> .output stdout
To export only the INSERT statements with data, use the –data-only switch with the .dump dot-command:
sqlite> .output database1.sql
sqlite> .dump --data-only
sqlite> .output stdout
To restore an entire database from a dump file, start by creating a new database. Then, use the .read command to execute the SQL statements from the dump file (e.g., database1.sql). This process recreates the database structure and inserts the data as specified in the dump file.
skull: ~ $: sqlite3 database2.sq3
SQLite version 3.44.2 2023-11-24 11:41:44
Enter ".help" for usage hints.
sqlite> .read database1.sql
sqlite>
Ensure the dump file database1.sql contains all necessary SQL commands for both structure and data. This typically happens when the file was generated using the .dump command without option like –data-only or with .schema dot-command.
The database file being restored to should not already contain conflicting structures or data, as this might result in errors during restoration.
There are certain settings for the SQLite shell that you will use
regularly. These settings can be preconfigured and automatically loaded
every time you start the sqlite3 program. To do this, simply create a
file named .sqliterc in your home directory and add the desired
dot-commands to it. For example:
.mode column
# .mode line
# .headers on
.prompt "sqlite> " " ...> "
Lines starting with the hash symbol (#) are comments and will be ignored by the shell.
The SQLite shell is a simple and powerful tool for working with SQLite databases. You can run SQL queries and use dot-commands to manage your database easily. It’s easy to use, whether you’re a beginner or an experienced developer.
The shell is a broad topic with many features to explore. We’ll come back to some of its tools and tricks as we dive deeper into SQLite. For now, enjoy its simplicity and reliability as you work with your databases!
It’s no surprise that you can use the sqlite3 program in CLI and shell scripts just like any other command-line tool. The basic syntax is:
You can run sqlite3 --help to view all available options. Here are a few
examples of how to use it:
skull: ~ $: sqlite3 database1.sq3 '.backup backup.sq3'
skull: ~ $: sqlite3 -csv database1.sq3 'select * from books' | otherprogram ...
#!/bin/sh
sqlite3 database1.sq3 <<EOF
create table if not exists books (
id integer primary key,
title text,
description text,
author text
);
EOF
#!/bin/sh
sqlite3 database1.sq3 <<EOF
INSERT INTO books (title, description, author) VALUES ('Book 1 Title', 'Description of the first book.', 'Author 1');
INSERT INTO books (title, description, author) VALUES ('Book 2 Title', 'Description of the second book.', 'Author 2');
INSERT INTO books (title, description, author) VALUES ('Book 3 Title', 'Description of the third book.', 'Author 3');
EOF
Selecting data and outputting to a CSV file:
#!/bin.sh
sqlite3 -header -csv database1.sq3 'SELECT * FROM books;' > books.csv
An “SQLite archive” is a file container, much like a ZIP archive or Tarball, but built on an SQLite database. It exemplifies the broader concept that an SQLite database can function as a container, storing numerous smaller data components.
SQLite archive characteristics:
Creating new archive of *.c files in current directory:
skull: ~ $: sqlite3 sources.sqlar -Ac *.c
Adding and updating files in an existing archive
skull: ~ $: sqlite3 sources.sqlar -Au *.h
List files in archive:
skull: ~ $: sqlite3 sources.sqlar -Atv
To extract all files:
skull: ~ $: sqlite3 sources.sqlar -Ax
To view all options and usage hints use only -A option with no
additional arguments.
In addition to SQLite database files, the sqlite3 program can read and
write ZIP archives. Simply provide a ZIP archive filename instead of an
SQLite database filename in the command line or .open command.
sqlite3 will detect the file as a ZIP archive and open it, regardless
of the file extension. This means you can open JAR, DOCX, ODP, or other
ZIP-based files.
A ZIP archive appears as a database with a single table and the following schema:
CREATE TABLE zip(
name, -- Name of the file
mode, -- Unix-style file permissions
mtime, -- Timestamp, seconds since 1970
sz, -- File size after decompression
rawdata, -- Raw compressed file data
data, -- Uncompressed file content
method -- ZIP compression method code
);
To extract data from the ZIP archive you can use writefile() function:
sqlite> sqlite> SELECT writefile(name,content) FROM zip
...> WHERE name LIKE 'docProps/%';
The sqlite3 program is not just a shell; it can be used in CLI and shell scripts for various tasks. Whether it’s reading and writing ZIP archives or automating SQL commands, the versatility of sqlite3 makes it a valuable tool for many use cases. By integrating it into shell scripts, you can simplify tasks and enhance productivity.
SQLite. Reliable. Steady as stone. Used by billions. Time-tested and true.
Key takeaways:
Are you seriously telling me that you don’t check the input data, so now you need a strict type system at the database level?
SQLite’s type system is flexible. Columns adapt to data types, not the reverse. No need for strict definitions; just use column names.
Key points:
SQLite adjusts columns to the data:
Key takeaways:
Introducing fantasy data type, because when our site reaches
the scale of FB, we will need to migrate :)
sqlite> create table t1 (
(x1...> id,
(x1...> name text,
(x1...> age fantasy
(x1...> );
sqlite>
That’s it.
The DEFAULT clause in SQLite simplifies data management by providing predefined values when no explicit input is given. These defaults can be constants, expressions, or special keywords like CURRENTDATE or CURRENTTIMESTAMP, allowing for flexible and automated initialization.
Expressions are evaluated for each inserted row, ensuring context-specific defaults. This feature promotes efficiency and consistency in handling table data, especially for columns with predictable or standard values, minimizing manual input while maintaining data integrity.
INSERT.DEFAULT clause is specified, the column
defaults to NULL.NULL, string, blob, or signed-number.CURRENT_TIME, CURRENT_DATE,
CURRENT_TIMESTAMP.CURRENT_TIME: "HH:MM:SS"CURRENT_DATE: "YYYY-MM-DD"CURRENT_TIMESTAMP: "YYYY-MM-DD HH:MM:SS"Simple example
CREATE TABLE books (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
author TEXT DEFAULT "John Doe",
published_date TEXT DEFAULT CURRENT_DATE,
pages INTEGER,
rating REAL,
created_at TEXT DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now'))
);
INSERT INTO books (title, pages, rating) VALUES('The SQLite book', 520, 4.9);
Heads on :)
I do not use SQLite over a network, and I have never encountered a corrupted database in a real-world scenario that required recovery. In my case, the application and database reside on the same machine and disk drive.
After performing the backup, it’s always a good idea to verify the
integrity of the resulting file to ensure everything is in order, using
PRAGMA integrity_check or PRAGMA quick_check.
Method 1: cp command
If the database is not in use, a simple cp command is sufficient to create a backup. If SQLite operates in WAL mode, there may be additional files related to the database, such as the -wal file or the -shm file.
Method 2: .backup dot-command
If the database is live, I rely on the .backup dot-command. I trust the SQLite author to know best practices for safely handling backups in such situations.
This approach prioritizes simplicity while adhering to SQLite’s built-in mechanisms for data safety.
There are at least two steps involved in performing a backup this way:
skull: ~ $: sqlite3 database1.sq3 '.backup database1.backup.sq3'
skull: ~ $: sqlite3 database1.backup.sq3 'pragma integrity_check'
Of course, the backup can be automated by creating a shell script containing these and other commands, while also checking the return values. For example, PRAGMA integrity_check literally returns the value ‘ok’ if everything went well. :)
Method 3: sqlite3-rsync
sqlite3-rsync is a utility that synchronizes SQLite databases, ensuring the REPLICA matches the ORIGIN as a point-in-time snapshot.
It supports both local and remote databases, handles “live” databases during operation, and uses a bandwidth-efficient protocol. It is ideal for creating consistent database replicas even while ORIGIN is actively used.
sqlite3-rsync is included in SQLite precompiled binaries available on SQLite download page I think.
There is no binary available for OpenBSD, so in my case, I tried to compile it from source. To do so, I needed the Tcl source code, so I downloaded Tcl 9.0 sources. The next steps were as follows:
$ cd ~/tmp
$ git clone https://github.com/sqlite/sqlite.git
$ cd sqlite
$ git checkout sqlite3-rsync
$ ./configure --with-tcl=/home/$USER/Downloads/tcl9.0.1/unix
$ make sqlite3-rsync
Because of some limitations, and also because this tool is relatively new, it’s wise to read the sqlite3-rsync documentation. If you know a simpler way to compile it from source, please let me know. I’d be glad to include it.
Method 4: VACUUM INTO
Additionally, there’s the VACUUM INTO SQL command, which can be used
instead of .backup dot-command for creating vacuumed copy of the live
database.
I personally work with methods 1 and 2. There are many ways and techniques to perform a backup, but I have no experience with them so far, as they depend on the environment in which the database operates.
In regular database systems, when you make a change, the database writes the new data directly into the main database file. This can be slow, especially when many people or programs are using the database at the same time.
WAL mode (Write-Ahead Logging) is a special mode in SQLite that makes things faster and safer.
When you make a change in WAL mode:
SQLite uses Rollback Journal Mode by default. To enable Write-Ahead Logging (WAL) mode, use the following command:
sqlite> PRAGMA journal_mode = WAL;
In scenarios where performance is more critical than durability, you can adjust the synchronous mode for better speed. For example:
sqlite> PRAGMA synchronous = NORMAL;
PRAGMA Synchronous Levels Explained
OFF
NORMAL
FULL
… disadvantages …
SQLite provides basic support for altering tables, such as adding new columns, renaming tables, and renaming columns. However, it lacks direct methods for more complex operations due to its lightweight design, which prioritizes simplicity and performance. As a result, workarounds are often necessary in such cases.
These workarounds involve creating a new table with the desired columns, copying data from the original table into it, deleting the original table, and renaming the new table to match the name of the original one.
The RENAME TO operation renames a table from table-name to
new-table-name. This change is limited to renaming within the same
database.
Key Notes:
ALTER TABLE books RENAME TO bookshelf;
The ADD COLUMN operation adds a new column to the table. The new
column is always added at the end.
Column Constraints:
PRIMARY KEY or UNIQUE constraints.NOT NULL constraint must have a default value.REFERENCES clause must default to NULL.Restrictions:
No default values like CURRENT_TIME or CURRENT_TIMESTAMP are allowed.
ALTER TABLE books ADD COLUMN pages INTEGER;
The RENAME COLUMN TO operation changes a column name from
column-name to new-column-name within the table.
Key Notes:
ALTER TABLE books RENAME COLUMN pages TO total_pages;
… rowid, integer primary key, autoincrementing …
… there is no separate type
what it is
how to do it … explain plan
advantages
disadvantages
FTS4? FTS5 … mention external content tables too
Freedom: The source code is in the public domain. You can do anything you want with it, including using it for commercial purposes.
Minimalism: It’s minimalist both in the sense that it’s small, simple and in the sense that it doesn’t talk when it doesn’t need to.
Creativity: Its properties, which differ from other database systems, or the features that are missing and others might consider weaknesses, force me to be creative.
Speed: SQLite is simply fast. Fastest in the field of the intended use.
Stability: It’s written in ANSI-C, which ensures it doesn’t have to contend with frequent changes to the language specification.
Predictability: Everything above stays true because SQLite has a steady roadmap and the author sticks to his principles without following fashionable trends
It is an iterative process - “write, review, learn, repeat” - until the concept is fully understood and retained!
Another great way to describe the cyclical nature of learning-by-writing is to compare it to the “rinse and repeat” concept, which is well known to people in IT.
You learn something, write about it, reflect on what you’ve written, identify areas that need more work, and then go back to learn more. This process reinforces the knowledge, allowing you to build on it each time you revisit it.