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

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):

LanguageDriver
Cthe SQLite library itself, header sqlite3.h
JavaJDBC driver org.xerial:sqlite-jdbc
Godatabase/sql with modernc.org/sqlite or github.com/mattn/go-sqlite3
Node.jsbuilt-in node:sqlite since Node 22.5, or better-sqlite3
Rustrusqlite
C#Microsoft.Data.Sqlite

When to choose a server database

Then use a database server such as PostgreSQL or MySQL.

Article author: Klavdiya Kondakova