The SQLite console, also known as the command line interface or CLI, is a powerful tool for working with SQLite databases. While many people, myself included, often prefer a graphical environment, there are a lot of things that can be done quickly in the console with its collection of commands. There are dozens of commands covering everything from file management to software configuration but, in this overview, I want to quickly go over some of the commands that you might be most likely to use on a regular basis.

This post mainly covers the standard Windows package of SQLite, version 3.50.0. These commands are not supported in the DB Browser for SQLite application and many of them will only function on the main database that has been loaded with the .open command, not attached databases.

Getting Help

Remember that you can use the .help command to get a full list of available commands or more detail on a specific command.

In the example below, typing .help open or .help .open will give you more detail on the .open command.

As you can see in the screenshot above, many of these commands have additional switches you can use like the –zip switch for the .open command which will open SQLite databases stored in ZIP files. The .help -all command will show all the help files on all available commands.

Database connections

In addition to the .open command for opening the primary database, additional databases can be open at the same time using the attach database command.  While working on my recent SQLite book project, I usually had all three sample databases loaded for reference and I have a SQLite script that will open them all with one command.

/*"C:\\Data\\SQLite\\openscript.txt"*/
.open "C:\\Data\\SQLite\\chinook.db"
attach database "C:\\Data\\SQLite\\northwind.db" as northwind;
attach database "C:\\Data\\SQLite\\sqlite-sakila.db" as sakila;
.databases

Notice that the first line in the above code is commented out with the /*  */ characters. I use this line to copy the path into the .read command which will read the entire file into the command console and run any commands it contains.

The .databases command will show all the databases currently loaded on the current connection. As shown above, these commands can be used as part of a script in SQLite.

The .tables command will show a list of the available tables and views in all databases, open and attached.

The detach database command will unload specific databases using the names assigned to them when they were attached.

detach database northwind;

The database that was loaded with the .open command cannot be detached.  Using the .open command to open another database will replace the currently open database and detach any other databases.

The SQLite console also has the ability to maintain up to 10 independent connections, on each of which multiple databases can be opened or attached.  The .connection command (.conn for  short) followed by a number between 0 and 9 will either create or switch to another connection. 

Using the .conn command on its own will show a list of currently open connections.  The .conn close [#] command will close the specified connection, providing it is not currently the active connection. The screenshot above is a demonstration of opening and closing multiple connections.

As shown in the above example, SQLite has one connection when the console is opened which shows the transient memory database until a database file is opened. SQLite will not create a new connection if there is an existing connection without a database file opened on it.  Attempting to do so will re-number the existing connection that doesn’t have an open file.

The .exit and .quit commands are almost the same and will close the console and any open connections. The .exit command can be used to return a code between 0 and 255 to the operating system or whatever script activated the console.

.exit 1

The .cd command will change the working directory of the SQLite console and can be used to avoid having to repeatedly type path names when loading databases.

.cd "C:\\Data\\SQLite\\"

Remember that the backslashes in Windows paths must be escaped as shown above by using a second backslash.

Database file management

There are two commands for copying the current database to another file. The .backup (or .save) command will make a backup of the database at the specified location while the .clone command will make the copy and then immediately switch the database connection to the new copy.  Both commands are pretty straightforward:

.backup "chinook_back.db"

.clone "chinook_clone.db"

The .clone command’s output is fairly detailed as it copies tables to the new file one at a time and typically reports an error when encountering the sqlite_stat1 table but this will not actually cause a problem with the new database as this table is automatically generated by the ANALYZE command.

The .clone command also will not affect the status of any connections to attached databases.

The .save command is an alias for the .backup command and works the same.

The .restore command will restore a backup file either as the main database file or into a database alias that was previously created for an attached database. This command will not otherwise affect connections to attached databases. By default, the database will be restored to the main database connection if no alias is specified.

.restore northwind "northwind_bkup.db"

.restore "chinook_bkup.db"

In the event that a database file is corrupted, SQLite has a .recover command that will create a SQL script file that can re-create the database. The .recover command would generally be run from the OS prompt by calling SQLite, specifying the file and the command.

> sqlite3 northwind_corrupt.db .recover > northwind.sql

This file is then used to create a new database in SQLite from command line.

> sqlite3 northwind_recovery.db < northwind.sql

There is an issue in the way the .recover command creates the SQL file in that it doesn’t always save it with UTF-8 encoding. SQLite requires a UTF-8 encoded text file to import to a new database as shown above. To fix this, you might need to open the SQL file with a text editor like Notepad++, change the encoding to UTF-8 and re-save it before trying to create the recovered database.

Database objects

The .indexes command shows the names of all the indexes on a particular table in the main database which can be handy when evaluating and tracing problems with query performance.

The .schema command will show the CREATE statements for a specific object in the database. You can also use wildcards to define a pattern for the object names.

.schema 'categor%'

The .parameter command can be used to store parameters for use within the CLI. This is mainly useful when running SQL scripts that will use a particular value more than once and to assign a description to that value.

.parameter set @CompanyName 'Wilman Kala'

SELECT * FROM Customers WHERE Company = @CompanyName;

Parameters must start with one of the following: “@”, “$”, “?” or “:” and they are case-sensitive. Any existing parameters are stored as key / value pairs in a table called sqlite_parameters which only exists if there are currently defined parameters.  The following commands are available to manage the list of parameters.

.parameter list	Show list of currently set parameters.
.parameter set PARAM VALUE	Set a new parameter.
.parameter unset PARAM	Remmove specific parameter.
.parameter clear	Clear all parameters.

Query statistics and formatting

The .changes command can be used to activate or deactivate the reporting of the number of rows changed by action queries on the current connection.

These statistics show the changes for the most recent SQL statement and the total number of changes for the connection.  It will show a change of 1 even for SELECT queries, regardless of the number of rows returned, but doesn’t add this to the total.

The .eqp command can be turned ON or OFF and activates an explanation of the query plan used for each SQL statement issued in the console.

.eqp ON

This can be useful for determining the efficiency of queries and optimizing those that will be used frequently. The .eqp command also has a FULL setting that will provide a complete description of the query plan although it is very long, even for a simple query like the one shown above. This command can also be used by adding the full words EXPLAIN QUERY PLAN before a query.

The .explain command changes the format of the .eqp command output. When it’s set to ON, the output will be formatted as a table. When OFF, the output is in a column-based format. The default option is AUTO.

While .eqp explains the plan for the query, the .scanstats command will activate the explanation of what queries actually did. This command can be turned ON or OFF to show the explanation after each statement issued.

The .stats command can be turned ON and OFF to show the complete statistics for each query run including origin details on each column and the amount of memory used during the operation. It’s probably much more data than you’ll usually need but it’s worth trying out to see some of the information available for optimizing queries.

The .timer command can be turned ON and OFF to display the actual time that it took a specific query to run.

The .headers command can be set to ON or OFF to either show or hide the column title headers from the query output. Depending on the setting of the .mode command, the headers might show regardless.

The .width command can set specific widths of query columns in characters. The following example would set the widths for the first three columns of future queries to 15 characters each.

.width 15 15 15

Using the .width command with no options will reset the widths to auto for all columns.  Using negative values will right-justify the columns.

Finally, the .show command will show a listing of many of the commands listed here and their current settings.

Query output

The .mode command is one of the most useful in SQLite as it provides a lot of control over how query results are displayed in the console and elsewhere. As of version 3.50.0, there are 15 different display modes for query results including box mode, which is used in many of the screenshots here, csv for comma-delimited text and many others.

.mode ascii --wrap 15

The example above will use ascii mode and wrap any column values longer than 15 characters. Calling the help command will provide a list of other modes and options available.

.help mode

The .nullvalue command substitutes a specific string for any null values in all queries run within the console. This is helpful when running a lot of NULL-infested queries that don’t have the necessary SQL commands to replace the null values within the queries themselves.

.nullvalue "N/A"

.nullvalue ""

The .nullvalue command must be used with an empty string to deactivate the substitution. Unlike some commands, just using .nullvalue alone won’t change anything.

The .output command will send any output from the console to the specified external file instead of the console.

.output output.txt

Using .output again without specifying a file will return the output to the console itself.

The .once command will output the results of the next command only to a file, maintaining whatever formatting has been set by the .mode command. Both .output and .once have options switches to send the query results to a web browser or to a CSV file for Excel.

.once output.csv -x

SQLite has a .log command that is supposed to log everything that happens in the session, including commands and prompts, but it appears to be broken. I’ve tested it in both Windows and Fedora Linux and it creates the specified log file on disk but never writes anything to it, even when I explicitly issue the following commands:

.log output.txt

.log on

The other way to capture an entire session that works on both Windows and Linux is to redirect the session output from the command line, maybe with a script.

sqlite3 < script.sql > session.log

This will capture the output of any script commands to the log file. If no script is supplied, the SQLite console loads silently in the background, processes and logs the output of any commands.

The .trace command outputs the text of the queries only to the specified file. It does not output results or console commands.  The output is only written once the .trace off command is issued, ending the output. This is helpful for tracking the SQL statements issued during a session.

There are a few commands to output the results of only the next command to a file. The .excel command will output the results of the next command or statement to a CSV file which will be automatically opened in whatever program is set to open CSV files on the system. The .www command will output the results to an HTML file which will then be opened in the default web browser. 

The .echo ON command can be used to instruct the console to repeat any commands entered by the user. This can be useful for documenting actions if the console output is being redirected to a log with any of the commands shown above.

Output of the results to a webpage using the .www command.

The .crlf command can be turned ON or OFF to use Windows-style line endings and fix problems with how text is formatted when output to a file

The .print command can be used to print specific text to the console or output. This would generally be used as part of a script to provide explanations of the current operation being performed or other status.

.print 'Inserting product records …'

Database import and export

SQLite has a .dbtotxt command that will quickly dump the database file to hexadecimal format. This can be used for debugging or diff analysis.  One of the other output commands can be used first to direct the dump to a specific file.

.once output.txt

.dbtotxt

The .dump command will dump the specified database objects or the entire database to a SQL script including the CREATE statements for the objects themselves and any INSERT statements needed to insert the data found in them. Multiple objects can be specified either separately or with wildcards as shown in the following examples:

.output dump.sql

.dump artists albums

.dump invoice%

The .dump command only works on the main database, not attached databases or their objects.

The .import command will import a text file into a new table in the main database.

.import new_data.csv NewTable

The .import command has options like –skip to skip the first specified number of rows in the text file and –ascii to import data that is not encoded as UTF-8 and might contain special characters.

Additional commands

The .shell and .system commands are the same and can be used to run OS commands from within the CLI.  For example, .shell cls will clear the console screen of previous commands and output.

The .version command will show the version number and release date of the version of SQLite that you are currently running.

For more details on the SQLite CLI commands, see the official online documentation at SQLite.org.