At minimum, always invoke sqlite with "PRAGMA foreign_keys = ON;" as follows:
sqlite3 -header -cmd "PRAGMA foreign_keys = ON;" </path/to/sqlite3.db>
where "</path/to/sqlite3.db>" is an actual path to the SQLite3 database file.
In the case where one finds themselves needing to connect to the SQLite3 database from the command-line interface (CLI), there are a number of options and commands the user can run to ensure certain SQLite3 features are turned on to make using the database a better experience.
To see the list of available options, on your CLI, type:
sqlite3 --help
Some options I have found handy are the "-header", "-init" and "-cmd" options.
The "-header" option turns on the table headers, so that when you query, your query results include the table columns names.
The "-init" option lets you have multiple, frequently used dot commands, and certain initialisation statements in a file that you can load at the invocation of SQLite3.
The "-cmd" allows us to run certain commands that activate/deactivate specific SQLite3 features.
One of the important features of databases in general is the use of foreign keys to link data in multiple tables. SQLite3 has this feature, but it is
We need this feature turned on for our SQLite3 databases, thus we invoke SQLite3 with the "PRAGMA foreign_key=ON;" directive as follows:
sqlite3 -cmd "PRAGMA foreign_keys = ON;" </path/to/sqlite3.db>
where "</path/to/sqlite3.db>" is an actual path to the SQLite3 database file.
The following dot commands help enhance the outputs of your queries:
To turn the table headers on, use the the "-header" option at invocation:
sqlite3 -header </path/to/sqlite3.db>
or the ".headers on" dot command:
sqlite3 </path/to/sqlite3.db> SQLite version 3.40.0 2022-11-16 12:10:08 Enter ".help" for usage hints. sqlite> .headers on ︙
You can set the output modes to make the output of your queries more readable. You do this with the appropriate options at the CLI invocation, or using the ".mode" dot command.
To see a list of available modes, do:
$ sqlite3
SQLite version 3.40.0 2022-11-16 12:10:08
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .help mode
.mode MODE ?OPTIONS? Set output mode
MODE is one of:
ascii Columns/rows delimited by 0x1F and 0x1E
box Tables using unicode box-drawing characters
csv Comma-separated values
column Output in columns. (See .width)
︙
I have found the "box", "table" and "markdown" modes to be nice for outputs intended for users to read.
Outputs such as "json", "csv", "tsv" and the like are useful for use with scripts.