SQLite shell

Command line invocations

sqlite3 [options] db-filename [SQL statement]

`-A ARGS…`	run `.archive ARGS` and exit
`-append`	append the database to the end of the file
`-bail`	stop after hitting an error
`-batch`	force batch I/O
`-cmd COMMAND`	run `COMMAND` before reading stdin
`-echo`	print commands before execution
`-init FILENAME`	read/process named file
`-[no]header`	turn headers on or off
`-help`	show this message
`-interactive`	force interactive I/O
`-lookaside SIZE N`	use N entries of SZ bytes for lookaside memory
`-markdown`	set output mode to markdown.
`-memtrace`	trace all memory allocations and deallocations
`-mmap N`	default mmap size set to N
`-newline SEP`	set output row separator (default: `\n`).
`-nofollow`	refuse to open symbolic links to database files
`-nullvalue TEXT`	set text string for NULL values. Default is the empty string (`''`).
`-pagecache SIZE N`	use N slots of SZ bytes each for page cache memory
`-readonly`	open the database read-only
`-separator SEP`	set output column separator. Default: `\|`
`-stats`	print memory stats before each finalize
`-table`	set output mode to table. With this setting, SQLite needs to process the entire resultset before it outputs it in order to determine the individual result column's widths.
`-version`	show SQLite version. Compare with the SQLite shell dot command `.version`. The version is also printed when the SQLite shell is started.
`-vfs NAME`	use NAME as the default VFS
`-zip`	open the file as a ZIP Archive

The following options set the output mode (i. e. format): -ascii, -box, -column, -csv, -html, -json, -line, -list, -quote

An SQL script can be redirected into the SQLite executable, for example in PowerShell like so

get-content sql-script.sql | sqlite3.exe

See also the -cmd COMMAND command line option and the executescript() method of Python's sqlite3 module.

$ sqlite3 my.db  'select … from …' .exit

Run a script

$ sqlite3 my.db  '.read myScript.sql' .exit

Dot commands modify some of the shell's setting or execute a non-SQL action.

These commands are called dot commands because they start with a dot.

Some interesting dot commands, imho, include:

`.open some.db`, `.open --new some.db`	`--new` deletes contents of `some.db` if it exists.
`.read filename`	execute SQL statements in `filename`.
`.exit`, `.quit`	to terminate an SQLite session
`.shell cmd`	executes `cmd` in a shell.
`.headers on`, `.headers off`
`.mode`	specifices the format in which a result set is returned. It recognizes the following formats: `csv`, `column`, `html`, `insert`, `line`, `list` (which is the default), `quote`, `tabs`, `tcl` and `box`. When using `.mode column` (which produces a tabular output), the widths of the individual columns might be adjusted with `.width n m n o …` (where `n`, `m` …) specifiy the column widths s in characters.
`.print Message`
`.output file.txt`	sends output to `file.txt`
`.dump`	to export and import a database.
`.timer on`, `.timer off`
`.show`	Report the values for some settings such as col- and row separator, the filename of the opened database etc.
`.changes on`	automatically calls `changes()` and `total_changes()` after each statement.
`.eqp on`, `.eqp off`	Turn on/off automatic explain query plan mode. If turned on, the shell will automatically run an `explain query plan` for each statement entered and display the plan.
`.version`	Prints the version of the engine, zlib and the compiler with which the engine was compiled. Compare with the command line option `--version`

Commands that can be used to find schema object or describe tables include:

.header on
.width 2 30 10 4 4
.mode columns
pragma table_info('table_name');

.testcase

.imposter

.load to load a DLL or a shared object in order to add more functionality to SQLite.

.scanstats