SQLite3: an Embedded SQL Database
SQLite is a database engine that runs inside your program as a library: there is no server to start, no users and no configuration. A whole database, with its tables, indexes and data, is one ordinary file. sqlite3 is version 3 of that file format and also the name of its command-line shell.
This page works with the shell and then opens the same database from two programming languages. If you write Python, the article SQLite3 Python shows the Python module step by step. Tested on Ubuntu 24.04.5 LTS with sqlite3 3.45.1, Python 3 and PHP 8.2.
What SQLite is
- One database is one file. Copy the file and you have copied the database.
- No server process: the program reads and writes the file through the SQLite library.
- Transactions: a change is written completely or not at all, also after a crash or a power cut.
- Many readers, one writer: while a transaction writes, other writers wait.
That makes SQLite the usual choice for data that belongs to one program: browsers and phone apps keep their settings and history in it, desktop programs their documents, tests their temporary data, and small websites their content.
Install the sqlite3 shell
sudo apt install sqlite3
sqlite3 --version
3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1 (64-bit)
The package is only the shell. Programs use the SQLite library, which most languages ship together with their database driver, so a program works without the shell.
Create a database and a table
sqlite3 creates the database file on the first write. SQL after the file name runs at once:
sqlite3 shop.db "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL);"
sqlite3 shop.db "INSERT INTO items (name, price) VALUES ('Keyboard', 49.9), ('Mouse', 19.5), ('Monitor', 189);"
A column declared INTEGER PRIMARY KEY numbers the rows by itself. The database is now a small file:
ls -l shop.db
-rw-r--r-- 1 andrei andrei 8192 Nov 2 23:28 shop.db
Without SQL after the file name, sqlite3 shop.db opens an interactive shell with the prompt sqlite>. Type SQL ending with a semicolon, and .quit to leave.
Query and output formats
sqlite3 shop.db 'SELECT * FROM items;'
1|Keyboard|49.9 2|Mouse|19.5 3|Monitor|189.0
By default the columns are separated by |. -header -column adds names and aligns them:
sqlite3 -header -column shop.db 'SELECT * FROM items;'
id name price -- -------- ----- 1 Keyboard 49.9 2 Mouse 19.5 3 Monitor 189.0
-box draws a table:
sqlite3 -box shop.db 'SELECT name, price FROM items WHERE price < 100;'
┌──────────┬───────┐ │ name │ price │ ├──────────┼───────┤ │ Keyboard │ 49.9 │ │ Mouse │ 19.5 │ └──────────┴───────┘
and -json prints rows for scripts:
sqlite3 -json shop.db 'SELECT name, price FROM items WHERE price > 50;'
[{"name":"Monitor","price":189.0}, {"name":"Webcam","price":59.8999999999999985}]
This output comes from the end of the page, after the CSV import added the webcam. 59.9 shows up as 59.8999999999999985 because REAL is a binary floating-point number: it stores the nearest binary value, and JSON output and .dump print all of its digits. For money, store cents in an INTEGER column.
Tables and schema
sqlite3 shop.db .tables
items
sqlite3 shop.db '.schema items'
CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL);
Commands that start with a dot belong to the shell, not to SQL. In the interactive shell they work the same way, and .help lists all of them.
Import and export CSV
Two new items arrive in a CSV file with a header line:
cat new_items.csv
name,price Headset,35 Webcam,59.9
Import it into a new table. sqlite3 takes the column names from the first line:
sqlite3 shop.db '.import --csv new_items.csv staging'
sqlite3 shop.db '.schema staging'
CREATE TABLE IF NOT EXISTS "staging"( "name" TEXT, "price" TEXT);
Every column of an imported table is TEXT. Copy the rows into the real table, where they get their types and an id, then drop the staging table:
sqlite3 shop.db 'INSERT INTO items (name, price) SELECT name, price FROM staging; DROP TABLE staging;'
sqlite3 -header -column shop.db 'SELECT * FROM items;'
id name price -- -------- ----- 1 Keyboard 49.9 2 Mouse 19.5 3 Monitor 189.0 4 Headset 35.0 5 Webcam 59.9
Two traps of a direct import: the file has two columns and items three, and with --skip 1 into a table that does not exist yet, sqlite3 skips the header and takes the first data row as the column names.
Export works the other way round:
sqlite3 -csv -header shop.db 'SELECT * FROM items;' > items.csv
cat items.csv
id,name,price 1,Keyboard,49.9 2,Mouse,19.5 3,Monitor,189.0 4,Headset,35.0 5,Webcam,59.9
Dump, restore and backup
.dump writes the whole database as SQL text:
sqlite3 shop.db .dump > shop.sql
cat shop.sql
PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL); INSERT INTO items VALUES(1,'Keyboard',49.8999999999999985); INSERT INTO items VALUES(2,'Mouse',19.5); INSERT INTO items VALUES(3,'Monitor',189.0); INSERT INTO items VALUES(4,'Headset',35.0); INSERT INTO items VALUES(5,'Webcam',59.8999999999999985); COMMIT;
Feed it to sqlite3 to build a new database from it:
sqlite3 restored.db < shop.sql
sqlite3 restored.db 'SELECT count(*) FROM items;'
5
.backup copies a database safely even while another program uses it; a plain cp is safe only when nothing writes to the file. Check the copy afterwards:
sqlite3 shop.db ".backup 'shop-backup.db'"
sqlite3 shop-backup.db 'PRAGMA integrity_check;'
ok
SQLite from programming languages
Any language with a SQLite driver opens the same file. Both programs below read shop.db from the steps above and print the items under 50.
Python: the module sqlite3 is part of the standard library.
vi read_items.py
import sqlite3 con = sqlite3.connect("shop.db") for name, price in con.execute("SELECT name, price FROM items WHERE price < ?", (50,)): print(name, price) con.close()
python3 read_items.py
Keyboard 49.9 Mouse 19.5 Headset 35.0
PHP: PDO with the pdo_sqlite extension.
vi read_items.php
<?php $db = new PDO('sqlite:shop.db'); $query = $db->prepare('SELECT name, price FROM items WHERE price < ?'); $query->execute([50]); foreach ($query as $row) { echo $row['name'], ' ', $row['price'], PHP_EOL; }
php read_items.php
Keyboard 49.9 Mouse 19.5 Headset 35
The same rows, with one difference: PHP prints the float 35.0 as 35. Other languages use these drivers (not tested on this page):
| Language | Driver |
|---|---|
| C | the SQLite library itself, header sqlite3.h |
| Java | JDBC driver org.xerial:sqlite-jdbc |
| Go | database/sql with modernc.org/sqlite or github.com/mattn/go-sqlite3 |
| Node.js | built-in node:sqlite since Node 22.5, or better-sqlite3 |
| Rust | rusqlite |
| C# | Microsoft.Data.Sqlite |
When to choose a server database
- Many programs or users write at the same time: SQLite lets one writer at a time.
- The database sits on a network share: file locking over NFS or SMB is not reliable, and the file can be damaged.
- Several servers share one database, or you need accounts and permissions per user.
Then use a database server such as PostgreSQL or MySQL.
Article author: Klavdiya Kondakova