Skip to content

SQLite Quick Reference Commands

  • Refer to MySQL Quick Reference Commands for general SQL statement examples.
  • Steps prefixed with a "$" (dollar sign) represents the operating system shell prompt. The text after the "$" is to be entered at the shell.
  • Steps prefixed with a "." (period/dot commands) is to be entered at the SQLite shell prompt including the ".".
Task Command Example Notes
Delete Parameter Bindings .parameter clear Remove all parameter bindings.
Delete a Parameter Binding .parameter unset test Remove the parameter "test" from the binding table.
Exit Console .exit Exit the SQLite console.
Exit Console .exit 100 Exit the SQLite console with return code.
Load Extension .load ./myext Load extension library "myext". Learn more.
Print String .print "hello world" Display literal string value (e.g. "Hello World") without double quotes.
Quit Shell .exit Exit the SQLite command line shell.
Quit Shell .q Exit the SQLite command line shell.
Quit Shell .quit Exit the SQLite command line shell.
Read SQL File .read data.sql Read and execute SQL within a file.
Run Shell Command .shell ls Run a command in the operating system shell.
Run Shell Command .shell ls -al Run a command in the operating system shell.
Run Shell Command .system ls Run a command in the operating system shell.

Database

Task Command Example Notes
Backup Database .backup mybackup.db Defaults to working directory if no path is provided.
Backup Database .backup /home/username/Documents/mybackup.db
Create Database $ sqlite3 data.db Create database if file doesn't exist. Console/terminal command.
Change Directory .cd backup/ Change the working directory.
Clone Database .clone test.db Clone data in the existing database to a new database file.
Connect to Database $ sqlite3 data.db Console/terminal command.
Dump Database .dump Dump all database objects. Refer to .output.
Dump Database .dump settings Dump the settings table to stdout. Refer to .output.
Generate sha3sum .sha3sum Generate a SHA-3 hash of database content.
Generate sha3sum .sha3sum --sha3-512 Generate a SHA-3 512-bit hash of database content.
Generate sha3sum .sha3sum table-name Generate a SHA-3 hash of a database table content. Like pattern supported.
Open Database .open data.db Open an existing database while closing the currently open database.
Recover Database .recover Recover as much data as possible from a corrupt database file. Outputs in SQL. Learn More
Recover Database .recover --recovery-db new.db Recover as much data as possible from a corrupt database file. Outputs in SQL and then to database file "new.db".
Restore Database .restore data.db Restore/import the content of a database into current default database.
Save Database .save data.db Save database to another database file.

Maintenance / Diagnostic

Task Command Example Notes
Find Missing Key Indexes .lint fkey-indexes Report potential schema issues by finding misisng foreign key indexes.
Imposter .imposter test settings Create imposter table for "settings" table on index "test". Learn more.
Import Data To Table .import data.csv settings Import the "data.csv" into the table "settings". Refer to .help import.
Run Self Tests .selftest Run tests defined within the SELFTEST table. Learn More

Set

Task Command Example Notes
Set Auto Query Explain .eqp on Enable or disable automatic explain query plan on query commands. Learn more.
Set Auto Query Explain .eqp full Enable automatic explain query plan on query commands with full details. Learn more.
Set Echo .echo on Enable or disable command echo.
Set Output Excel .excel Dump the output of the next command in CSV (comma-separated values).
Set Output .output data.sql Send all output to a desired filename.
Set Output .output stdout Send all output to stdout (console).
Set Output Once .once data.sql Send the next SQL command output only to a file.
Set Testcase Output .testcase Send output to "testcase-out.txt".
Set Prompt .prompt "db> " Change the main prompt to "db> " without double quotes.
Set Prompts .prompt "db> " "Next> Change the main ("db> ") and continuation ("Next> ") prompts.
Set Database Config .dbconfig dqs_ddl off Set database configuration option.
Set Display Mode .mode table Set the display output mode (ascii, box, csv, column, html, insert, json, line, list, markdown, quote, table, tabs, tcl). Refer to .help mode.
Set Explain Format .explain on Enable or disable the explain formatting mode (on/off).
Set Explain Format .explain auto Set the explain formatting mode.
Set File Control .filectrl Set various sqlite3 file control opperations. Refer to .help filectrl.
Set Table Headers .headers on Set table header (column) names (on/off).
Set Changes .changes on Show number of rows changed by SQL (on/off).
Set Scanstats .scanstats on Set sqlite3_stat_scanstatus() metrics on/off.
Set Separator .separator \| \| Set the column and row separators.
Set Stop On Error .bail on Set stop after an error occurs (on/off).
Set Logging .log off Enable or disable logging (off, stderr, stdout).
Set Null Value .nullvalue N/A Set a default string value in place of NULL values.
Set Parameter Binding .parameter set test hello Set the parameter "test" to the value of "hello".
Set Timeout .timeout 100 Try opening locked tables for "100" milliseconds.
Set Timer .timer on Display run time (real, user, system) after each SQL statement (on/off).
Set Trace .trace stdout Output each SQL statement as it is run to desired output and details. See .help trace for details.
Set Column Width .width Set column width to zero (automatic width). Width works with .mode, "box", "column", "table", "markdown".
Set Column Width .width 20 20 40 20 Set each column width measured in characters.

Show

Task Command Example Notes
Show Database Config .dbconfig Display database configuration options.
Show Database Status .dbinfo Display database status information.
Show Databases .databases Display names and files of attached databases.
Show Help .help Display a list of available special dot-commands.
Show Help .help tables Display help for .tables command.
Show Help .help %data% Display help references for "data" using like pattern.
Show Indexes .indexes settings List names of indexes for the "settings" table. Like pattern supported.
Show Parameter Bindings .parameter list Display the current parameter bindings.
Show Tables .tables Show names of all tables.
Show Tables .tables '%test%' Display names of tables matching a like pattern.
Show Settings .show Display the current values for various settings.
Show Suggested Indexes .expert Suggest indexes for the proceeding query.
Show Schema .schema Show the SQL create statements.
Show Schema .schema %test% Show the SQL create statements using a like pattern.
Show Schema .schema --indent Show the SQL create statements using a pretty output.
Show Schema .schema --nosys Show the SQL create statements excluding system objects starting with "sqlite_".
Show Schema/Content .fullschema Display schema and the content of the "sqlite_stat" tables.
Show Stats .stats Show general database statistics.
Show Stats .stats on Show statistics for statements (on/off).
Show Stats .stats stmt Show statement statistics.
Show Stats .stats vmstep Show virtual machine step count only.
Show Test Controls .testctrl Display test control operations that are available to run.
Show Top-level VFS .vfsinfo Display information about top-level VFS.
Show VFS .vfsname Display the name of the VFS stack.
Show VFSes .vfslist Display a list of all available VFSes.

Omission

The following dot commands are not presently included at this time.

  • .archive
  • .auth
  • .binary
  • .check
  • .limit
  • .progress
  • .session
Last update: November 22, 2021