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#
- if ~/.sqliterc exists, it is processed
- if -init path/to/.myinitrc is passed to sqlite3:
- ~/.sqliterc is not processed
- path/to/.myinitrc is processed
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#
sqlite backup
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#
- Official Documentation
- Programming Interfaces