sqlite3 command-line tool

Everybody knows about sqlite3 command-line tool:

$ sqlite3 some.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .schema pages
CREATE TABLE pages (
    url VARCHAR NOT NULL,
    html TEXT,
    PRIMARY KEY (url)
);

Actually, this is its interactive mode of operation. There is batch one, too. Availability of batch mode is less known (people do not read man pages nowadays, unfortunately).

So, for instance, you can pipe some SQL statement to stdin of sqlite3 command-line utility:

$ echo "SELECT COUNT(url) FROM pages WHERE html != '';" | sqlite3 some.db
130899493

But there is much simpler way to do this:

$ sqlite3 some.db "SELECT COUNT(url) FROM pages WHERE html != '';"
130899493

Also sqlite3 is able to display query results as HTML, CSV, and easy to parse “value per line” format (in addition to “list with separator” format, which is default):

$ sqlite3 -line some.db "SELECT COUNT(url) FROM pages WHERE html != '';"
COUNT(url) = 130899493

Plus, it can echo SQL statement right before its result:

$ sqlite3 -echo some.db "SELECT COUNT(url) FROM pages WHERE html != '';"
SELECT COUNT(url) FROM pages WHERE html != '';
130899493

Options may be combined, of course:

$ sqlite3 -echo -line some.db "SELECT COUNT(url) FROM pages WHERE html != '';"
SELECT COUNT(url) FROM pages WHERE html != '';
COUNT(url) = 130899493

See man sqlite3 for more information on boring command-line options of sqlite3 utility :)