SQLite

SQLite is a free database engine that can be build into programs. There is also a command line tool. The data is stored in local files. It is not intended for multi-user or as web-server back-end (but probably can manage these, too), but for easily interfacing from programs. It can be used from Shell scripts, C and C++ programs, Python programs and many more. Many Web-Toolkits (like Django) use it for quickly starting to develop without having to install a database server.

SQL

The SQL dialect is a bit limited, but for those knowing PostgreSQL, MySQL or Oracle, it is very easy to adapt. The most important difference is SQLite ignoring data types: it does not matter what type a column is, it holds all sorts of data.

CREATE TABLE demo (ID INTEGER PRIMARY KEY, name TEXT, age INTEGER);
INSERT INTO TABLE demo (name, age) VALUES ('Peter', 42);
UPDATE demo SET (age=41) WHERE name = 'Peter';
INSERT INTO demo (name, age) VALUES ('Tom', 20);
DELETE FROM demo WHERE age > 100;
SELECT * from demo;
SELECT name, age FROM demo WHERE age > 16 ORDER BY age;

If the first column is of the exact data type INTEGER PRIMARY KEY, it will be an alias for rowid. This is the only column, that holds only integer and automatically increments to a new value for every new row. Commands must be terminated by a semicolon, the case does not matter. Strings are given in single quotes.

The SQL dialect is documented on the web site.

Command line tool

The command line interface sqlite3 can be used to interactively control the database file or to check what your program has done. The file must be given on the command line.

$ sqlite3 test.db
sqlite> .schema         # shows the existing tables and their structure
sqlite> .mode column    # switch to a nicer display mode, csv is also possible
sqlite> .header on      # show column headers
sqlite> select * from demo;
sqlite> .quit

Use .help to show what interal commands are understood.

Global settings can be given in a file ~/.sqliterc, e.g.

.mode column
.header on

Interfacing from Shell scripts

A query can be given on the command line:

$ sqlite3 test.db "select * from demo;";

The format can be configured to columns (for viewing), csv (for importing data to spreadsheets), HTML and some more.

To avoid stumbling over global settings for human readability, scripts should always set the output format according.

Further reading

How SQLite itself sees its strengths and weaknesses: Appropriate Uses For SQLite

social