Learning about the SQLite Command Line Interface.
SQLite provides a Command Line Interface (CLI) program named
sqlite3. And it’s already installed on most operating systems.
The CLI can be run with or without command line options (flags).
When a flag is provided, it must be prefixed with
--. For example,
--version do the same thing:
sqlite3 is run without flags, it will connect to a temporary in-memory database (which will be deleted on exit) in interactive mode:
When in interactive mode, the prompt is
sqlite> and it reads text input from the keyboard:
- SQL statements.
- Dot commands like
.open (where some dot commands also accept flags).
But it’s also possible to redirect
sqlite3 I/O (input/output) to:
To see how to use the CLI (and print all available CLI flags):
To print all available dot commands (in interactive mode):
To see how to use a dot command (in interactive mode), and print available dot command flags, run
.help DOT_COMMAND. For example:
Open a database
When a filename is provided to the
sqlite3 command, it will either create a new database or open an existing database in interactive mode:
In interactive mode, a connection to a new or existing database can always be created via the
.open dot command. And to connect to a temporary in-memory database, use
:memory: as the database file name.
To destroy any data in an existing database run
.open -new FILENAME. For example:
To open a database in read-only mode use the
This also works in interactive mode:
Databases and schemas
To see all databases in interactive mode:
To see all tables (including attached databases) in interactive mode:
To see all indexes in interactive mode:
To see the complete schema of the database (including attached databases) in interactive mode:
Read SQL statements from a file
In interactive mode the
.read dot command can be used to read SQL statements (and dot commands) from a file:
If the argument to
.read begins with the pipe symbol (
|), then instead of opening the argument as a file, it runs the argument as a command, and uses the output of that command as its input. This can be useful to run scripts that generate SQL.
Write results to a file
sqlite3 sends all output to “standard output”, but this can be changed via the
.once dot commands in interactive mode.
To output all query results to a file:
To do the above just once, use the
.once dot command instead.
If the argument to
.once begins with the pipe symbol (
|), then it runs the argument as a command, and the output is sent to that command.
Load file content into a table column
readfile() function loads file content as a
BLOB in interactive mode. For example:
Write a table column to a file
writefile() function writes a column value to a file in interactive mode. For example:
Import CSV into table
To import a CSV file into a table in interactive mode:
And to import into a table not part of the “main” database the
-schema flag can be used. This specifies that the table is part of another “schema” (useful for attached databases or to import into a temporary table).
Export results to CSV
To export results to a CSV file in interactive mode:
Dump and restore a database
Dump (converts entire database content into a single UTF-8 text file):
.sqliterc resource file can be created in the “home directory” to configure dot command settings. For example to change the output format for all queries:
After creating the
.sqliterc file, it will be loaded on startup:
It’s possible to “bypass” interactive mode and run SQL statements directly when using the
sqlite3 command via the last argument:
And by using CLI flags like
-cmd it’s possible to shorten certain actions.
One-line import and query CSV
One-line export results to CSV
Thanks for reading!
If you have ideas how to improve this post, let me know on GitHub.
Post last updated