paulgorman.org

SQLite

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:

CREATE TABLE notes(
    id INTEGER PRIMARY KEY,
    note TEXT,
    created DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE tags(
    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 https://www.sqlite.org/cli.html

sqlite> .databases
sqlite> .tables
sqlite> .schema
sqlite> .schema tablename
sqlite> 
sqlite> 

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

See SQL As Understood By SQLite.