SQLite

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format.

Usage

Usage: sqlite3 [OPTIONS] FILENAME [SQL] FILENAME is the name of an SQLite database. A new database is created if the file does not previously exist.

Options

Option	Description
-append	append the database to the end of the file
-ascii	set output mode to ‘ascii’
-bail	stop after hitting an error
-batch	force batch I/O
-box	set output mode to ‘box’
-column	set output mode to ‘column’
-cmd COMMAND	run “COMMAND” before reading stdin
-csv	set output mode to ‘csv’
-deserialize	open the database using sqlite3_deserialize()
-echo	print inputs before execution
-init FILENAME	read/process named file
-[no]header	turn headers on or off
-hexkey KEY	hexadecimal encryption key
-html	set output mode to HTML
-interactive	force interactive I/O
-key KEY	raw encryption key
-json	set output mode to ‘json’
-line	set output mode to ‘line’
-list	set output mode to ‘list’
-lookaside SIZE N	use N entries of SZ bytes for lookaside memory
-markdown	set output mode to ‘markdown’
-maxsize N	maximum size for a --deserialize database
-memtrace	trace all memory allocations and deallocations
-newline SEP	set output row separator. Default: \n
-nofollow	refuse to open symbolic links to database files
-nonce STRING	set the safe-mode escape nonce
-nullvalue TEXT	set text string for NULL values. Default ”
-pagecache SIZE N	use N slots of SZ bytes each for page cache memory
-quote	set output mode to ‘quote’
-readonly	open the database read-only
-safe	enable safe-mode
-separator SEP	set output column separator. Default: ""
-stats	print memory stats before each finalize
-textkey PASSPHRASE	text to be hashed into the encryption key
-table	set output mode to ‘table’
-tabs	set output mode to ‘tabs’
-version	show SQLite version

Commands

The most important commands inside the SQLite3 Shell:

• .backup ?DB? FILE: Backup DB (default “main”) to FILE
• .bail on|off: Stop after hitting an error. Default OFF
• .binary on|off: Turn binary output on or off. Default OFF
• .cd DIRECTORY: Change the working directory to DIRECTORY
• .changes on|off: Show number of rows changed by SQL
• .check GLOB: Fail if output since .testcase does not match
• .clone NEWDB: Clone data into NEWDB from the existing database
• .databases: List names and files of attached databases
• .dbinfo ?DB?: Show status information about the database
• .dump ?OBJECTS?: Render database content as SQL
• .echo on|off: Turn command echo on or off
• .excel: Display the output of next command in spreadsheet
• .exit ?CODE?: Exit this program with return-code CODE
• .headers on|off: Turn display of headers on or off
• .help ?-all? ?PATTERN?: Show help text for PATTERN
• .hex-rekey OLD NEW NEW: Change the encryption key using hexadecimal
• .import FILE TABLE: Import data from FILE into TABLE
• .imposter INDEX TABLE: Create imposter table TABLE on index INDEX
• .indexes ?TABLE?: Show names of indexes
• .limit ?LIMIT? ?VAL?: Display or change the value of an SQLITE_LIMIT
• .lint OPTIONS: Report potential schema issues.
• .log FILE|off: Turn logging on or off. FILE can be stderr/stdout
• .nullvalue STRING: Use STRING in place of NULL values
• .once ?OPTIONS? ?FILE?: Output for the next SQL command only to FILE
• .open ?OPTIONS? ?FILE?: Close existing database and reopen FILE
• .output ?FILE?: Send output to FILE or stdout if FILE is omitted
• .print STRING...: Print literal STRING
• .quit: Exit this program
• .read FILE: Read input from FILE or command output
• .rekey OLD NEW NEW: Change the encryption key
• .recover: Recover as much data as possible from corrupt db.
• .restore ?DB? FILE: Restore content of DB (default “main”) from FILE
• .save ?OPTIONS? FILE: Write database to FILE (an alias for .backup …)
• .schema ?PATTERN?: Show the CREATE statements matching PATTERN
• .selftest ?OPTIONS?: Run tests defined in the SELFTEST table
• .separator COL ?ROW?: Change the column and row separators
• .sha3sum ...: Compute a SHA3 hash of database content
• .shell CMD ARGS...: Run CMD ARGS… in a system shell
• .system CMD ARGS...: Run CMD ARGS… in a system shell
• .show: Show the current values for various settings
• .stats ?ARG? : Show stats or turn stats on or off
• .tables ?TABLE? : List names of tables matching LIKE pattern TABLE
• .text-rekey OLD NEW NEW: Change the encryption key using hexadecimal
• .width NUM1 NUM2 ...: Set minimum column widths for columnar output