SQLite DOT(.) COMMANDS

Description

The SQLite provides a simple command-line utility named sqlite3 which allows the user to execute SQL statements manually against an SQLite database. This article is a brief introduction of sqlite3 dot ( . ) commands.

• A dot-command must begin with the "." at the left margin with no preceding whitespace.
• The dot-command must be entirely contained on a single input line.
• A dot-command cannot occur in the middle of an ordinary SQL statement. In other words, a dot-command cannot occur at a continuation prompt.
• Dot-commands do not recognize comments.

Here are the dot(.) commands

backup ?DB? FILE

This command is used to backup database (default "main") to a file.

Here is the example.

Sample Output:

D:\sqlite>sqlite3 medical.db
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             D:\sqlite\medical.db

sqlite> .tables
doctornew   doctors     speciality  table_a     table_b     visits

sqlite> SELECT * FROM doctornew;
210|Dr. John Linga|MD
211|Dr. Peter Hall|MBBS
212|Dr. Ke Gee|MD
213|Dr. Pat Fay|MD

sqlite> .backup D:/sqlite/backup/medical_bkup

In the above example, we explain how the .backup command execute. Here we have open the database medical. We have shown that the list of database and the list of tables, and the records of one table doctornew by the SELECT statement. Now we took a backup of the database main into file medical_bkup under D:/sqlite/backup folder.

Here we have shown the created database.

.restore ?DB? FILE

This command is used to restore content of database(default "main") from the backup file.

Here is the example.

Sample Output:

D:\sqlite>sqlite3 medical.db
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             D:\sqlite\medical.db

sqlite> .tables
doctornew   doctors     speciality  table_a     table_b     visits

sqlite> SELECT * FROM doctornew;
210|Dr. John Linga|MD
211|Dr. Peter Hall|MBBS
212|Dr. Ke Gee|MD
213|Dr. Pat Fay|MD

sqlite> .backup D:/sqlite/backup/medical_bkup

A backup file medical_bkup created into D:/sqlite/backup folder. Here for you, we have deleted all the records from the doctornew table.

sqlite> DELETE FROM doctornew;

sqlite> SLECT * FROM doctornew;

No records found in the doctornew table. Now we have restored the medical_bkup file.

Sample Output:

sqlite> .restore D:/sqlite/backup/medical_bkup
sqlite> .tables
doctornew   doctors     speciality  table_a     table_b     visits

sqlite> SELECT * FROM doctornew;
210|Dr. John Linga|MD
211|Dr. Peter Hall|MBBS
212|Dr. Ke Gee|MD
213|Dr. Pat Fay|MD
sqlite>

.bail on|off

Stop after hitting an error. Default OFF

.clone NEWDB

This command is used to clone data into another database from the existing database

Here is the example.

Sample Output:

D:\sqlite>sqlite3 medical1.db
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.
sqlite> .open d:/sqlite/backup/medical1.db
sqlite> .tables
doctors     speciality  table_a     table_b     visits
sqlite> .clone d:/sqlite/backup/medical2.db
doctors... done
speciality... done
visits... done
table_a... done
table_b... done
sqlite_autoindex_doctors_1... done

In the above example the database medical1.db have been copied in the same folder by medical2.db.

.databases

This command lists names and files of attached databases.

Here is the example.

Sample Output:

sqlite> .databases
seq  name             file
---  ---------------  ------------------------
0    main             D:\sqlite\medical.db
2    hrdb             D:\sqlite\hr

.dump ?TABLE?

Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE.

Here is the example.

Sample Output:

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE table1(
id integer,
descrip char(10));
INSERT INTO "table1" VALUES(1,'descrip1');
INSERT INTO "table1" VALUES(2,'descrip2');
CREATE TABLE table2(
id integer,
descrip2 char(20));
INSERT INTO "table2" VALUES(1,'descrip3');
INSERT INTO "table2" VALUES(2,'descrip4');
COMMIT;

Here in the above the .dump command dumps the total database.

Sample Output:

sqlite> .dump table1
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE table1(
id integer,
descrip char(10));
INSERT INTO "table1" VALUES(1,'descrip1');
INSERT INTO "table1" VALUES(2,'descrip2');
COMMIT;
sqlite>

The above example shows a specific table table1 have been used along with .dump command to dump only the specific table.

.echo on|off

Turn command echo on or off

Here is the example.

Sample Output:

sqlite> .echo on
sqlite> .tables
.tables
doctors speciality table_a table_b visits
sqlite> .echo off
sqlite> .tables
doctors     speciality  table_a     table_b     visits

The above example shows, when .echo on then the command is also showing along with the results.

.eqp on|off

This command is used to Enable or disables automatic explain of a query plan.

Here is the example.

Sample Output:

sqlite> .eqp on

sqlite> select * from doctors;
--EQP-- 0,0,0,SCAN TABLE doctors
210|Dr. John Linga|MD
211|Dr. Peter Hall|MBBS
212|Dr. Ke Gee|MD
213|Dr. Pat Fay|MD

sqlite> .eqp off

sqlite> select * from doctors;
210|Dr. John Linga|MD
211|Dr. Peter Hall|MBBS
212|Dr. Ke Gee|MD
213|Dr. Pat Fay|MD

.exit

Exit this program

.explain ?on|off?

The ".explain" dot command can be used to turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on.

Sample Output:

sqlite> .explain
.explain
sqlite> explain select * from table1;
explain select * from table1;
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------
0     Init           0     9     0                    00
1     OpenRead       0     2     0     2              00
2     Rewind         0     7     0                    00
3     Column         0     0     1                    00
4     Column         0     1     2                    00
5     ResultRow      1     2     0                    00
6     Next           0     3     0                    01
7     Close          0     0     0                    00
8     Halt           0     0     0                    00
9     Transaction    0     0     3     0              01
10    TableLock      0     2     0     table1         00
11    Goto           0     1     0                    00

.headers on|off

Turn display of headers on or off

The column labels that appear on the first two lines of output can be turned on and off using the ".header" dot command. In the following example, the column labels are off.

Sample Output:

sqlite> .headers off
sqlite> SELECT * FROM doctors;
210         Dr. John Linga  MD
211         Dr. Peter Hall  MBBS
212         Dr. Ke Gee      MD
213         Dr. Pat Fay     MD

Here in the above example, no header have shown for doctors table. If we want to see the header of the columns of the table, the following command has to be executed.

Sample Output:

sqlite> .headers on
sqlite> SELECT * FROM doctors;
doctor_id   doctor_name     degree
----------  --------------  ----------
210         Dr. John Linga  MD
211         Dr. Peter Hall  MBBS
212         Dr. Ke Gee      MD
213         Dr. Pat Fay     MD

.help

Show this message

.import FILE TABLE

This command is used to import data from a file into a table.

The ".import" command is used to import CSV data into an SQLite table. The ".import" command takes two arguments which are the name of the disk file from which CSV data is to be read and the name of the SQLite table into which the CSV data is to be inserted.

Note that it is important to set the "mode" to "csv" before running the ".import" command.

Here is the example.

Sample Output:

sqlite> .tables
doctors     speciality  table_a     table_b     visits

sqlite> .mode csv
sqlite> .import D:/sqlite/backup/tempdoctor.csv doctornew

sqlite> .tables
doctornew   doctors     speciality  table_a     table_b     visits

sqlite> .mode column
sqlite> SELECT * FROM doctornew;
doctor_id   doctor_name     degree
----------  --------------  ----------
210         Dr. John Linga  MD
211         Dr. Peter Hall  MBBS
212         Dr. Ke Gee      MD
213         Dr. Pat Fay     MD

In the above example, the first .table command shows the list of tables. Then .mode cvs change the mode of a file into csv. Then the csv file tempdoctor.csv under directory D:/sqlite/backup/ have been imported into file doctornew. The second .tables command shows the doctornew table in table list and after that, the select statement shows the records of the table.

.indices ?TABLE?

Show names of all indices If TABLE specified, only show indices for tables matching LIKE pattern TABLE.

Here is the example.

sqlite> .indices doctors
sqlite_autoindex_doctors_1

In the above example, an auto index has created for the table doctors. If we want to see the indexes for a specific table the above command has to execute.

.load FILE ?ENTRY?

Load an extension library

.log FILE|off

Turn logging on or off. FILE can be stderr/stdout

.mode MODE ?TABLE?

The sqlite3 program is able to show the results of a query in eight different formats: "csv", "column", "html", "insert", "line", "list", "tabs", and "tcl". You can use the ".mode" dot command to switch between these output formats.

The default output mode is "list". In list mode, each record of a query result is written on one line of output and each column within that record is separated by a specific separator string. The default separator is a pipe symbol ("|").

Set output mode where MODE is one of:

Sample Output:

csv      Comma-separated values
column   Left-aligned columns.  (See .width)
html     HTML <table> code
insert   SQL insert statements for TABLE
line     One value per line
list     Values delimited by .separator string
tabs     Tab-separated values
tcl      TCL list elements

Here is the example

Sample Output:

sqlite> select * from table1;
select * from table1;
1|descrip1
2|descrip2

Suppose .separator command is used to change the separator.

Sample Output:

sqlite> .separator ", "
.separator ", "
sqlite> select * from table1;
select * from table1;
1, descrip1
2, descrip2

If the mode set in "line", each column in a row of the database is shown on a line by itself. Each line consists of the column name, an equal sign, and the column data. The records are separated by a blank line.

Sample Output:

sqlite> .mode line
.mode line
sqlite> select * from table1;
select * from table1;
     id = 1
descrip = descrip1

     id = 2
descrip = descrip2

If mode set in column, each record is shown on a separate line with the data aligned in columns

Sample Output:

sqlite> .mode column
.mode column
sqlite> select * from table1;
select * from table1;
1           descrip1
2           descrip2

If you want to see the records with column header, the .header command have to use. The default header is off.

Sample Output:

sqlite> .header on
.header on
sqlite> select * from table1;
select * from table1;
id          descrip
----------  ----------
1           descrip1
2           descrip2

In insert mode, the output is formatted to look like SQL INSERT statements. You can use insert mode to generate text that can later be used to input data into a different database. When specifying insert mode, you have to give an extra argument which is the name of the table to be inserted into.

Sample Output:

sqlite> .mode insert table1
.mode insert table1
sqlite> select * from table1;
select * from table1;
INSERT INTO table1 VALUES(1,'descrip1');
INSERT INTO table1 VALUES(2,'descrip2');

Here is the example to set mode in tabs.

Sample Output:

sqlite> .mode tabs
.mode tabs
sqlite> select * from table1;
select * from table1;
id      descrip
1       descrip1
2       descrip2

Here is the example to set mode in csv

Sample Output:

sqlite> .mode csv
.mode csv
sqlite> select * from table1;
select * from table1;
id,descrip
1,descrip1
2,descrip2

Here is the example to set mode in html

Sample Output:

sqlite> .mode html
.mode html
sqlite>

sqlite> select * from table1;
select * from table1;
<TR><TH>id</TH>
<TH>descrip</TH>
</TR>
<TR><TD>1</TD>
<TD>descrip1</TD>
</TR>
<TR><TD>2</TD>
<TD>descrip2</TD>
</TR>

Here is the example to set mode in tcl

Sample Output:

sqlite> .mode tcl
.mode tcl
sqlite> select * from table1;
select * from table1;
"id" "descrip"
"1" "descrip1"
"2" "descrip2"

.nullvalue STRING

This command is used to replace the NULL values with a specific string.

Here is the example.

Sample Output:

sqlite> SELECT * FROM visits;
doctor_id   patient_name  vdate
----------  ------------  ----------
210         Julia Nayer   2013-10-15
214         TJ Olson      2013-10-14
215         John Seo      2013-10-15
212         James Marlow  2013-10-16
212         Jason Mallin  2013-10-12
216                       2013-10-27

The above table shows the patient_name column of the last row has a null value. If you want to replace the null value by specified string the following commands have to be executed.

Sample Output:

sqlite> .nullvalue 'Not Blank'
sqlite> SELECT * FROM visits;
doctor_id   patient_name  vdate
----------  ------------  ----------
210         Julia Nayer   2013-10-15
214         TJ Olson      2013-10-14
215         John Seo      2013-10-15
212         James Marlow  2013-10-16
212         Jason Mallin  2013-10-12
216         Not Blank     2013-10-27

.once FILENAME

This command is used to send the output of the next SQL command only to named file instead of being printed on the console.

Here is the example.

Sample Output:

sqlite> .header on
sqlite> .mode csv
sqlite> .once d:/sqlite/backup/tempdoctor.csv
sqlite> SELECT * FROM doctors;

The above example shows that the file tempdoctor.csv have created in the specified folder. The .header command turn on the header, the .mode csv command changed the mode in csv.

.open ?FILENAME?

Close existing database and reopen FILENAME

Sample Output:

sqlite> .open d:/sqlite/backup/medical1.db
sqlite> .databases
seq  name             file
---  ---------------  ------------------------------------
0    main             d:\sqlite\backup\medical1.db
sqlite> .tables
doctors     speciality  table_a     table_b     visits

Here in the above example the opened database have closed and reopened the new database medical1.db.

.output ?FILENAME?

This command sends output to a file or stdout.

Here is the example.

If we want to send the output of the command into file test1.txt in d:/sqlite/backup directory the following command can be used.

Sample Output:

sqlite> .output d:/sqlite/backup/test1.txt
sqlite> SELECT * FROM doctors;

.print STRING...

This command is used to Print literal STRING

Here is the example.

Sample Output:

sqlite> .print "This command is used to print the text."
This command is used to print the text.

.prompt MAIN CONTINUE

This command replaces the standard prompts.

Here is the example

Sample Output:

sqlite> .prompt sqlite#
sqlite#

.quit

Exit this program

.read FILENAME

Execute SQL in FILENAME

.save FILE

Write in-memory database into FILE

After creating a new database and tables under this database using the default in-memory storage, the database can be saved into a disk file using the ".save" command:

Here is the example.

Sample Output:

D:\sqlite>sqlite3
SQLite version 3.8.5 2014-06-04 14:06:34
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open medical.db
sqlite> .tables
doctors     speciality  table_a     table_b     visits
sqlite> .save D:/sqlite/backup/medical1.db

In the above example, at first we open an existing database medical which contain 5 tables. Then the database have been saved into the location D:/sqlite/backup by name medical1.db. Later we can use the database by .open command.

.schema ?TABLE?

Show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE.

The .schema command is used to see database schema information. This gives return the create command that was used to create the tables. If you created an index on any columns then this will also show up.

Here is the example.

Sample Output:

sqlite> .schema
CREATE TABLE table1(
id integer,
descrip char(10));
CREATE TABLE table2(
id integer,
descrip2 char(20));
CREATE INDEX id_index ON table1(id);

Here in the following example, the table name has been specified.

sqlite> .schema table1
CREATE TABLE table1(
id integer,
descrip char(10));

.separator STRING

Change separator used by output mode and .import

You can use the ".separator" dot command to change the separator for list mode. For example, to change the separator to a comma and space, you could do this

Sample Output:

sqlite> .mode list
sqlite> .header on
sqlite> .separator ','
sqlite> SELECT * FROM doctors;
doctor_id,doctor_name,degree
210,Dr. John Linga,MD
211,Dr. Peter Hall,MBBS
212,Dr. Ke Gee,MD
213,Dr. Pat Fay,MD

.shell CMD ARGS...

Run CMD ARGS... in a system shell

.show

Show the current values for various settings

Here is the example.

Sample Output:

sqlite> .show
     echo: off
      eqp: off
  explain: off
  headers: on
     mode: column
nullvalue: ""
   output: stdout
separator: "|"
    stats: on
    width:

.stats on|off

Turn stats on or off

Here is the example.

Sample Output:

sqlite> .stats on
sqlite> SELECT * FROM doctors;
doctor_id   doctor_name     degree
----------  --------------  ----------
210         Dr. John Linga  MD
211         Dr. Peter Hall  MBBS
212         Dr. Ke Gee      MD
213         Dr. Pat Fay     MD
Memory Used:                         75640 (max 82424) bytes
Number of Outstanding Allocations:   133 (max 152)
Number of Pcache Overflow Bytes:     3376 (max 6600) bytes
Number of Scratch Overflow Bytes:    0 (max 0) bytes
Largest Allocation:                  64000 bytes
Largest Pcache Allocation:           1176 bytes
Largest Scratch Allocation:          0 bytes
Lookaside Slots Used:                8 (max 58)
Successful lookaside attempts:       247
Lookaside failures due to size:      48
Lookaside failures due to OOM:       0
Pager Heap Usage:                    3912 bytes
Page cache hits:                     2
Page cache misses:                   0
Page cache writes:                   0
Schema Heap Usage:                   2864 bytes
Statement Heap/Lookaside Usage:      2368 bytes
Fullscan Steps:                      3
Sort Operations:                     0
Autoindex Inserts:                   0
Virtual Machine Steps:               28

.system CMD ARGS...

Run CMD ARGS... in a system shell

The .system command works like .once command and in more this will typically bring up a spreadsheet program to display the CSV file.

Here is the example

Sample Output:

sqlite> .mode csv
sqlite> .once D:/sqlite/backup/newdoctor1.csv
sqlite> select * from doctors;
sqlite> .system D:/sqlite/backup/newdoctor1.csv
sqlite>

The line .mode csv change the mode in csv, the line ".once D:/sqlite/backup/newdoctor1.csv" causes all query output to go into the named file instead of being printed on the console, that is the CSV content to be written into a file named "D:/sqlite/backup/newdoctor1.csv". The final line ".system D:/sqlite/backup/newdoctor1.csv" has the same effect as double-clicking on the D:/sqlite/backup/newdoctor1.csv file in windows, shown below.

.tables ?TABLE?

List names of tables If TABLE specified, only list tables matching LIKE pattern TABLE.

Sample Output:

sqlite> .tables
doctors     speciality  table_a     table_b     visits

.timeout MS

Try opening locked tables for MS milliseconds

.timer on|off

This command turns SQL timer on or off. By default, the timer is off.

Here is the example.

Sample Output:

sqlite> .timer on
sqlite> SELECT * FROM doctors;
doctor_id   doctor_name     degree
----------  --------------  ----------
210         Dr. John Linga  MD
211         Dr. Peter Hall  MBBS
212         Dr. Ke Gee      MD
213         Dr. Pat Fay     MD
Run Time: real 0.003 user 0.000000 sys 0.000000
sqlite> .timer off
sqlite> SELECT * FROM doctors;
doctor_id   doctor_name     degree
----------  --------------  ----------
210         Dr. John Linga  MD
211         Dr. Peter Hall  MBBS
212         Dr. Ke Gee      MD
213         Dr. Pat Fay     MD

In the above example, the line indicated by red color have appeared because the .timer is on.

.trace FILE|off

Output each SQL statement as it is run

.vfsname ?AUX?

Print the name of the VFS stack

.width NUM1 NUM2 ...

By default, each column is between 1 and 10 characters wide, depending on the column header name and the width of the first column of data. Data that is too wide to fit in a column is truncated. You can adjust the column widths using the ".width" command. Like this:

Sample Output:

sqlite> .width 15,20,10
sqlite> select * from doctors;
doctor_id        doctor_name     degree
---------------  --------------  --------
210              Dr. John Linga  MD
211              Dr. Peter Hall  MBBS
212              Dr. Ke Gee      MD
213              Dr. Pat Fay     MD

The ".width" command in the example above sets the width of the first column to 15 and the width of the second column to20 and the third is 10. You can change the specific column width and the remaking width can be unaltered.

If you specify a column a width of 0, then the width of the column is automatically adjusted to be the maximum of three numbers: 10, the width of the header, and the width of the first row of data.

A negative column width can be specified to get right-justified columns.

Previous: Download, installation and getting started
Next: Attach, Detach Database