SQLite provides self-contained, light-weight, serverless, transactional file-based storage. It’s suitable for low-volume websites and application storage (no more roll-your-own file storage); see Appropriate Uses for SQLLite.

See the documentation and the (FAQ)[].

On Debian, apt-get install sqlite3. Other packages of interest: php-sqlite3 ruby-sqlite3 sqlite3-pcre.

Create a database from a SQL file: sqlite3 test.db < test.sql, where test.sql is something like:

    note TEXT,
    tag TEXT,
    note INTEGER,
    FOREIGN KEY(note) REFERENCES notes(id)
INSERT INTO notes VALUES(1, 'My first note!', '2015-07-20 16:26:16');
INSERT INTO notes VALUES(2, 'My second note.', '2015-07-21 12:53:19');
INSERT INTO notes VALUES(3, 'Yet another note. :\', '2015-07-23 18:13:42');
INSERT INTO tags VALUES('firsts', 1);
INSERT INTO tags VALUES('excited', 1);
INSERT INTO tags VALUES('remember', 1);
INSERT INTO tags VALUES('excited', 2);
INSERT INTO tags VALUES('bored', 3);

Connect to the database CLI: sqlite3 test.db.

Back up the database: sqlite3 test.db .dump > test.db.backup.

Shrink a database: sqlite3 test.db VACUUM. It creates a temporary file to do the shrink, so it will use more disk space until the VACUUM process completes.

Quick command pipeline use:

$ sqlite3 test.db 'SELECT * FROM notes LIMIT 1;' | awk -F'|' '{printf "%s\n%s\n", $3, $2}'
2015-07-20 16:26:16
My first note!

CLI dot functions

The CLI has a number of useful dot functions. See

sqlite> .databases
sqlite> .tables
sqlite> .schema
sqlite> .schema _tablename_

Dump the database to a text file:

$ echo '.dump' | sqlite3 mydb | gzip -c > mydb.dump.gz

Export a table to CSV:

sqlite> .header on
sqlite> .mode csv
sqlite> .once /tmp/foo.csv
sqlite> SELECT * FROM mytable;
sqlite> .system /tmp/foo.csv

SQL in SQLite

Data types: INTEGER, REAL, TEXT, BLOB, or NULL. Any other times (like DATETIME) are an alias to one of these basic types.

Columns declared as PRIMARY KEY will auto-increment.

SQLite supports C-style multiline comments (/* Comment */) and SQL-style single-line comments (-- A comment.).

SQLite understandsa a variety of date-time formats (stored in TEXT fields), including “YYYY-MM-DD HH:MM:SS”.

See SQL As Understood By SQLite.

Customize the Client

Add options in your ~/.sqliterc file.

.headers on
.mode column
.nullvalue ยค
.prompt "> "
.timer on

Import CSV Data

SQLite has a mode for importing CSV data.

$  sqlite3 airports.db
sqlite> .mode csv
sqlite> .separator ","
sqlite> .import airports.csv airports
sqlite> .schema

SQLite assumes the first line of input contains column headers, which it uses to name table columns for the database schema.

Attach Multiple Databases at the Same Time

$  sqlite3
sqlite> ATTACH 'airports.db' AS airport;
sqlite> ATTACH 'urls.db' AS urls;
sqlite> .databases
sqlite> SELECT COUNT(*) FROM urls.urls;