The sqlite3 command line tool
last modified July 6, 2020
In this part of the SQLite tutorial, we cover the
sqlite3 command line tool.
The sqlite3 tool
The sqlite3 tool is a terminal based frontend to the SQLite library that
can evaluate queries interactively and display the results in multiple formats.
It can also be used within scripts.
On the terminal screen, we see the following prompt of the sqlite3 tool:
$ sqlite3 SQLite version 3.16.2 2017-01-06 16:32:41 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database.
The .help command is one of the meta commands of the sqlite3 tool; it lists
all meta commands. The .exit and the .quit commands
exit the sqlite3 session. We can also use the Ctrl+D key combination to
quit sqlite3. The .databases command shows the attached databases.
The .tables command lists the available tables.
The Ctrl+L clears the screen and Ctrl+U clears the current line. (When built with readline library.)
Create database with sqlite3
The complete SQLite database is stored in a single cross-platform disk file. We use
the sqlite3 command line tool to create a new database file.
$ sqlite3 test.db
Here we create a new test.db database. If the file exists,
it is opened.
Basic sqlite3 meta commands
Next we describe some of the meta commands of the sqlite3 tool.
sqlite> .tables Authors Cars Friends Reservations Books Customers Orders
The .tables command shows the available tables.
sqlite> SELECT * FROM Friends; 1|Jane|F 2|Thomas|M 3|Franklin|M 4|Elisabeth|F 5|Mary|F 6|Lucy|F 7|Jack|M
Here we get the output of a SELECT statement. By default, the
output mode is line and the separator is |.
sqlite> .separator : sqlite> SELECT * FROM Friends; 1:Jane:F 2:Thomas:M 3:Franklin:M 4:Elisabeth:F 5:Mary:F 6:Lucy:F 7:Jack:M
Here we have used a new colon separator.
There are several other output modes available. The following example shows the
column output mode.
sqlite> .mode column sqlite> .headers on sqlite> SELECT * FROM Friends; Id Name Sex ---------- ---------- ---------- 1 Jane F 2 Thomas M 3 Franklin M 4 Elisabeth F 5 Mary F 6 Lucy F 7 Jack M
This example shows how data is formatted in sqlite's column mode.
The .headers command has also been used to show column headers.
By default, the headers are hidden.
The .width command adjusts the size of the columns.
(This meta command is relevant only for tables in the column mode.)
sqlite> SELECT Name, Title FROM Authors NATURAL JOIN Books; Name Title ----------- ---------- Jane Austen Emma Leo Tolstoy War and Pe Joseph Hell Catch XII Charles Dic David Copp Joseph Hell Good as Go Leo Tolstoy Anna Karen
The column widths are not wide enough to display all data correctly.
sqlite> .width 15 18 sqlite> SELECT Name, Title FROM Authors NATURAL JOIN Books; Name Title --------------- ------------------ Jane Austen Emma Leo Tolstoy War and Peace Joseph Heller Catch XII Charles Dickens David Copperfield Joseph Heller Good as Gold Leo Tolstoy Anna Karenia
Here we change the column widths. The first column will be 15 characters wide and the second 18.
sqlite> .shell clear
The .shell command executes system programs. In our
case, we clear the screen with the clear command.
(The equivalent on Windows is cls.)
sqlite> .show
echo: off
eqp: off
explain: auto
headers: on
mode: column
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "\n"
stats: off
width: 15 18
filename: testdb
The .show command lists various settings. These include the
output mode, the separator used in the list mode, and whether the
headers are on.
sqlite> .schema Cars CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
The .schema command shows the structure of the table.
It gives the DDL SQL to create the table.
It is possible to change the prompt of the sqlite3 with
the .prompt command.
sqlite> .prompt "> " ". " > SELECT * FROM Cars . LIMIT 2; Id Name Price ---------- ---------- ---------- 1 Audi 52642 2 Mercedes 57127 >
There are two prompts. One is the main prompt and the other is
a continuation prompt. The default main prompt is sqlite>
and the default continuation prompt is ...>.
Executing SQL from the shell
We can execute SQL commands from the shell.
$ sqlite3 test.db "SELECT * FROM Cars;" 1|Audi|52642 2|Mercedes|57127 3|Skoda|9000 4|Volvo|29000 5|Bentley|350000 6|Citroen|21000 7|Hummer|41400 8|Volkswagen|21600
Here we have non-interactively executed a SELECT statement;
all cars from the Cars table were selected.
Dumping tables
It is possible to dump tables in SQL format to the disk. This way we can easily save the structure and the data of a database table.
We have the Cars table.
sqlite> SELECT * FROM Cars; Id Name Price ---------- ---------- ---------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
We are going to use the .dump command to
dump the table.
sqlite> .dump Cars PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER); INSERT INTO "Cars" VALUES(1,'Audi',52642); INSERT INTO "Cars" VALUES(2,'Mercedes',57127); INSERT INTO "Cars" VALUES(3,'Skoda',9000); INSERT INTO "Cars" VALUES(4,'Volvo',29000); INSERT INTO "Cars" VALUES(5,'Bentley',350000); INSERT INTO "Cars" VALUES(6,'Citroen',21000); INSERT INTO "Cars" VALUES(7,'Hummer',41400); INSERT INTO "Cars" VALUES(8,'Volkswagen',21600); COMMIT;
The .dump command shows us the SQL necessary to
create the Cars table.
sqlite> .output cars2.sql sqlite> .dump Cars
We can also redirect the output to a file. The .output
command will redirect the output to the cars2.sql file.
$ cat cars2.sql PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER); INSERT INTO "Cars" VALUES(1,'Audi',52642); ...
We show the contents of the cars2.sql file with the cat
command.
Reading SQL
We can read SQL statements from a file name with the .read command.
sqlite> .tables Authors Cars Friends Reservations Books Customers Orders sqlite> DROP TABLE Cars; sqlite> .tables Authors Customers Orders Books Friends Reservations sqlite> .read cars.sql sqlite> .tables Authors Cars Friends Reservations Books Customers Orders sqlite> SELECT * FROM Cars WHERE Id=1; Id Name Price ---------- ---------- ---------- 1 Audi 52642
Here we have executed a series of commands. We drop the table and
read it from cars.sql.
The .sqlite_history file
The commands and statements are archived in the .sqlite_history file.
The file is located in the home directory.
$ tail -5 ~/.sqlite_history .tables SELECT * FROM Cars; .mode column .headers on .show
Using the tail command, we show the last five
entries.
Resource file
The sqlite3 tool has a resource file called .sqliterc.
It is located in the home directory. If there is no such file, we can simply create it.
The resouce file can contain meta commands, or regular SQL statements.
However, we should avoid using SQL in the file.
$ cat .sqliterc .mode column .headers on .nullvalue NULL
Here is a simple example of a resource file. It has three meta commands.
With a resource file, we do not have to execute meta commands all over again
when we start the sqlite3 tool. They will be executed
automatically at the start of the tool.
$ sqlite3 test.db -- Loading resources from /home/janbodnar/.sqliterc SQLite version 3.16.2 2017-01-06 16:32:41 Enter ".help" for usage hints.
We have a message saying that the tool loaded resources upon the beginning.
Command line options
The tool has several command line options. They mostly duplicate the meta commands. Note that command line options overwrite the resource file meta commands.
$ sqlite3 --help -- Loading resources from /home/janbodnar/.sqliterc 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 include: -ascii set output mode to 'ascii' -bail stop after hitting an error -batch force batch I/O -column set output mode to 'column' -cmd COMMAND run "COMMAND" before reading stdin -csv set output mode to 'csv' -echo print commands before execution -init FILENAME read/process named file -[no]header turn headers on or off -help show this message -html set output mode to HTML -interactive force interactive I/O -line set output mode to 'line' -list set output mode to 'list' -lookaside SIZE N use N entries of SZ bytes for lookaside memory -mmap N default mmap size set to N -newline SEP set output row separator. Default: '\n' -nullvalue TEXT set text string for NULL values. Default '' -pagecache SIZE N use N slots of SZ bytes each for page cache memory -scratch SIZE N use N slots of SZ bytes each for scratch memory -separator SEP set output column separator. Default: '|' -stats print memory stats before each finalize -version show SQLite version -vfs NAME use NAME as the default VFS
The --help option gives us the list of all available options with
a brief description.
$ sqlite3 -echo -line -noheader test.db -- Loading resources from /home/janbodnar/.sqliterc SQLite version 3.16.2 2017-01-06 16:32:41 Enter ".help" for usage hints. sqlite> SELECT * FROM Cars LIMIT 2; SELECT * FROM Cars LIMIT 2; Id = 1 Name = Audi Price = 52642 Id = 2 Name = Mercedes Price = 57127
We start the sqlite3 tool with -echo,
-line, and -noheader options. The SELECT
statement is repeated/echoed after being launched. The ouput is in the line
mode and we have no headers displayed.
$ sqlite3 -version -- Loading resources from /home/janbodnar/.sqliterc 3.16.2 2017-01-06 16:32:41 a65a62893ca8319e89e48b8a38cf8a59c69a8209
With the -version option we get the sqlite3's version.
$ sqlite3 -html test.db -- Loading resources from /home/janbodnar/.sqliterc SQLite version 3.16.2 2017-01-06 16:32:41 Enter ".help" for usage hints. sqlite> SELECT * FROM Cars LIMIT 2; <TR><TH>Id</TH> <TH>Name</TH> <TH>Price</TH> </TR> <TR><TD>1</TD> <TD>Audi</TD> <TD>52642</TD> </TR> <TR><TD>2</TD> <TD>Mercedes</TD> <TD>57127</TD> </TR>
With the -html option, we can output the results as simple HTML tables.
In this part of the SQLite tutorial, we worked with the sqlite3 command
line tool. We have described various meta commands, shown how to dump tables, and
read SQL from files. We mentioned the sqlite's history and resource files.