SQLite Reference

Kip Landergren

(Updated: )

My cheat sheet for SQLite and sqlite3 covering common commands and helpful documentation links.

Contents#

Behavior#

booleans#

SQLite has no BOOLEAN type.

Use INTEGER type instead:

0 false
1 true

sqlite3#

Init File#

Example:

sqlite3 -init .myinitrc foo.sqlite3

Selected Options#

to see all:

sqlite3 --help
-csv set output mode to 'csv'
-[no]header turn headers on or off

dot-commands (meta-commands)#

.headers on turns on column headers
.help
.help TOPIC
.mode column aligns output to column
.read FILE path relative from sqlite3 invocation (not database location)
.schema ?PATTERN? describe table equivalent \dt

SQLite documentation’s full list of dot-commands. SQLITE3(1) man page refers to these as “meta-commands”.

Executing SQL#

directly:

sqlite3 my-db.sqlite3 "select * from t_foo;"

from file:

sqlite3 my-db.sqlite3 < path/to/some-file.sql

Examples#

sqlite3 database.db ".backup backup.db"

Gotchas#

Why am I getting a non-null constraint on my primary key column on insert?#

To get SQLite’s autoincrementing behavior, ensure your schema uses exactly the declaration:

id_col_name INTEGER NOT NULL PRIMARY KEY

and not, for example:

id_col_name INT8 NOT NULL PRIMARY KEY

Frequently Asked Questions (FAQs)#

How can I tell what sqlite3 was compiled with?#

sqlite> pragma compile_options;

How can I check whether sqlite3 was built with fts5?#

Look for ENABLE_FTS5 in this output:

sqlite> pragma compile_options;

Resources#