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
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
sqlite3 my-db.sqlite3 < path/to/some-file.sql
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