CheatSQLite CLI Shell for SQLite
Light · Postcard
102 × 152 mm · PDF
Dark · Postcard
102 × 152 mm · PDF
Ink saver · Postcard
102 × 152 mm · PDF
Light · A4 landscape
297 × 210 mm · PDF
Dark · A4 landscape
297 × 210 mm · PDF
Ink saver · A4 landscape
297 × 210 mm · PDF
Light · A3 landscape
420 × 297 mm · PDF
Dark · A3 landscape
420 × 297 mm · PDF
Ink saver · A3 landscape
420 × 297 mm · PDF
Start here
sqlite3 <file.db>
Open or create a database
.tablesList tables
.schema <table>
Show CREATE statements
.mode boxReadable table output
.headers onShow column names
.import --csv <file> <table>
Load a CSV
.backup <file>
Binary copy of the database
.quitLeave the shell
Concepts
- Input file, SQL arg, stdin
- Shell dot-commands + SQL
- Formatter box, csv, json...
- Output stdout, file or pipe
Open and run
sqlite3 <file.db>
Open (or create) a database
sqlite3 <file.db> "<sql>"
Run one statement and exit
sqlite3 <file.db> < <script.sql>
Run a SQL script
.read <script.sql>
Run a script from inside the shell
.open <file.db>
Switch to another database file
--new wipes an existing file without asking
sqlite3 -readonly <file.db>
Open without write access
.helpList all dot-commands
.quitExit the shell
.shell <cmd>
Run a shell command
.cd <dir>Change working directory
sqlite3 -bail -batch <file.db>
Stop at the first error
sqlite3 -safe <file.db>
Disable file access and shell escapes
Not a full sandbox
sqlite3 -init <file>
Run a startup script first
.timeout <ms>
Wait for a locked database
Output modes
.mode boxUnicode box table
.mode columnAligned plain columns
.mode tableASCII table
.mode markdownMarkdown table
.mode csvComma separated values
.mode jsonJSON array of objects
.mode lineOne column per line
.mode listDefault pipe-separated
.mode tabsTab separated values
Does not re-import cleanly if values hold double quotes
.mode insert <table>
Rows as INSERT statements
.mode quoteSQL-quoted values
.headers onPrint column names
.nullvalue <text>
Show NULL as text
.separator <col> <row>
Set delimiters
.width <n> <n>
Fixed column widths
.showShow current settings
Inspect schema
.tablesList tables and views
.tables <pattern>
Filter with a LIKE pattern
.schemaAll CREATE statements
.schema <table>
CREATE statements for one table
.schema --indent
Pretty-printed schema
.indexes <table>
List indexes
.databasesAttached databases and files
.dbinfoPage size, counts, schema cookie
.fullschemaSchema plus sqlite_stat data
Statistics may expose sampled data values
.lint fkey-indexes
Foreign keys lacking an index
.expertSuggest indexes for next query
Experimental
pragma table_info(<table>);
Columns and types
Import and export
.import --csv <file> <table>
Load CSV into a table
A missing table is created from the header row
.import --csv --skip 1 <file> <table>
Load into an existing table
Without --skip the header becomes a data row
.once <file>
Send next result to a file
.output <file>
Send all results to a file
.outputBack to stdout
.once | <cmd>
Pipe next result to a command
.once -xOpen next result in a spreadsheet
select readfile('<path>');
Read a file as a value
select writefile('<path>',
<blob>);
Write a value to a file
.print <text>
Print a literal line
Backup and recover
.backup <file>
Binary copy of main database
Overwrites an existing file silently
.restore <file>
Load a backup into this database
.save <file>
Write database to a file
.dumpWhole database as SQL text
.dump <table>
One table as SQL text
sqlite3 <old.db> .dump | sqlite3 <new.db>
Rebuild into a fresh file
.recoverSalvage data from a corrupt file
Orphaned rows land in lost_and_found
.clone <newdb>
Copy content into a new file
.sha3sumHash of database content
pragma integrity_check;
Check for corruption
Debug and tune
.timer onTime each statement
.eqp onShow query plan automatically
explain query plan <sql>;
Plan for one statement
.stats onMemory and I/O statistics
.changes onReport rows changed
.echo onEcho commands as they run
.trace stdoutPrint each executed statement
.load <extension>
Load an extension library
.parameter set <name> <value>
Bind a named parameter
.versionShow library version info
Recipes
sqlite3 app.db# inside the shell:.import --csv people.csv people.schema peopleselect count(*) from people;sqlite3 -header -csv app.db "select * from people" > people.csv# or inside the shell:.headers on.mode csv.once people.csvselect * from people;sqlite3 app.db ".backup 'snap.db'"sqlite3 snap.db "pragma integrity_check;".tables.mode box.headers on.schema --indentselect * from <table> limit 5;
