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!
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
Data types: INTEGER, REAL, TEXT, BLOB, or NULL. Any other times (like DATETIME) are an alias to one of these basic types.
https://www.sqlite.org/datatype3.html
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.
Add options in your ~/.sqliterc file.
.headers on
.mode column
.nullvalue ¤
.prompt "> "
.timer on
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.
$ sqlite3
sqlite> ATTACH 'airports.db' AS airport;
sqlite> ATTACH 'urls.db' AS urls;
sqlite> .databases
sqlite> SELECT COUNT(*) FROM urls.urls;
🐚 $ sqlite3 test-ticket.db
sqlite> .tables
comments tickets
sqlite> .schema comments
CREATE TABLE `comments` (
`id` int(10) NOT NULL PRIMARY KEY,
`posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`author` varchar(50) DEFAULT NULL,
`message` longtext,
`ticket_id` int(10) NOT NULL DEFAULT '0',
`pin` tinyint(1) NOT NULL DEFAULT '0'
);
sqlite> .schema tickets
CREATE TABLE `tickets` (
`id` int(10) NOT NULL PRIMARY KEY,
`status` INTEGER DEFAULT NULL,
`opened` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`severity` tinyint(4) NOT NULL DEFAULT '3',
`owner` varchar(50) DEFAULT NULL,
`headline` varchar(255) DEFAULT NULL
);
sqlite> CREATE VIRTUAL TABLE fts USING fts5(ticket, headline, message);
sqlite> INSERT INTO fts SELECT tickets.id, tickets.headline, comments.message FROM tickets LEFT JOIN comments ON tickets.id = comments.ticket_id;
sqlite> SELECT * FROM fts WHERE fts MATCH 'spinning' ORDER BY rank;
5656|Upgrade older workstation spinning hard drives to SSD|Maybe more than half the main office staff still have workstations with spinning disks.\r\nSome of the workstations are getting a little old, and SSD's might extend their productive lives significantly.\r\n\r\n(I started looking at this because Sally complained that her computer is slow. I could hear her drive struggling.)
5656|Upgrade older workstation spinning hard drives to SSD|Replaced Nancy's drive.
5656|Upgrade older workstation spinning hard drives to SSD|Replaced Beth's drive with an SSD.
[…]
Populating the FTS5 table as seen above can significantly increase the database size. External or contentless tables are an alternative. See 4.4. External Content and Contentless Tables in https://www.sqlite.org/fts5.html.
Using a view for the search content is often best:
sqlite> CREATE VIEW IF NOT EXISTS ftsview (rowid, ticket, headline, message) AS SELECT comments.rowid, tickets.id, tickets.headline, comments.message FROM tickets LEFT JOIN comments ON tickets.id = comments.ticket_id;
sqlite> CREATE VIRTUAL TABLE fts USING fts5(ticket, headline, message, content='ftsview', content_rowid=rowid);
sqlite> INSERT INTO fts(fts) VALUES('rebuild');
(SQLite returned no results until the rebuild. Why?)
(And, after the rebuild, this does not seem to actually save any disk space in this case. Hmph.)