SQLite Commands: The sqlite3 Shell and Its Dot-Commands (Tested)

Ninad Pathak

October 7, 2026

The sqlite3 shell has its own commands, like .tables and .import, that start with a dot and aren’t SQL, so an SQL reference won’t list them. In this article I’ll walk you through the dot-commands for everyday work, grouped by task, and I’ve run every one.

How to use this page

Each section below covers one task. It opens with a table of the commands for that task, followed by the real output from running them. I created the example database with one table and one index:

sqlite3 shop.db "CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL); CREATE INDEX idx_products_name ON products(name); INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Notebook', 4.25);"

Every dot-command follows two rules, from the rules for dot-commands: “Dot-commands must begin with a ‘.’ at the left margin with no preceding whitespace. Dot-commands must be entirely contained on a single input line.” They don’t end with a semicolon. SQL statements do, and they can span several lines.

Open, run and exit

These commands open the shell and leave it. You can also run one statement without opening it at all:

Task	Command
Open (or create) a database	`sqlite3 shop.db`
Run one statement and exit	`sqlite3 shop.db 'SELECT ...;'`
Open a temporary in-memory database	`sqlite3` with no file name
Save an in-memory database to a file	`.save notes.db`
Exit the shell	`.quit` or `.exit`

Running one statement from your terminal:

$ sqlite3 shop.db 'SELECT name, price FROM products;'
Pen|1.5
Notebook|4.25

With no file name, the shell uses “a transient in-memory database”, which “is deleted when the program exits” (command-line shell docs). .save keeps it:

sqlite> CREATE TABLE notes (body TEXT);
sqlite> .save notes.db
sqlite> .quit
$ sqlite3 notes.db '.tables'
notes

Inspect the schema

These commands show what’s in the database (querying the database schema):

Task	Command
List tables and views	`.tables`
List a table’s indexes	`.indexes products`
Show the CREATE statements	`.schema products`

$ sqlite3 shop.db '.tables'
products
$ sqlite3 shop.db '.indexes products'
idx_products_name
$ sqlite3 shop.db '.schema products'
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL);
CREATE INDEX idx_products_name ON products(name);

Change how results print

.mode chooses the output format (output formats), and .headers on adds column names:

Task	Command
Aligned columns	`.mode column`
Boxed table	`.mode box`
Markdown or JSON	`.mode markdown`, `.mode json`
CSV	`.mode csv`
Column names on	`.headers on`
List every mode	`.help mode`

Here -cmd runs the .mode command before the query, and -header turns on column names:

$ sqlite3 -header -cmd '.mode list' shop.db 'SELECT name, price FROM products;'
name|price
Pen|1.5
Notebook|4.25
$ sqlite3 -header -cmd '.mode column' shop.db 'SELECT name, price FROM products;'
name      price
--------  -----
Pen       1.5
Notebook  4.25
$ sqlite3 -header -cmd '.mode box' shop.db 'SELECT name, price FROM products;'
┌──────────┬───────┐
│   name   │ price │
├──────────┼───────┤
│ Pen      │ 1.5   │
│ Notebook │ 4.25  │
└──────────┴───────┘
$ sqlite3 -header -cmd '.mode markdown' shop.db 'SELECT name, price FROM products;'
|   name   | price |
|----------|-------|
| Pen      | 1.5   |
| Notebook | 4.25  |
$ sqlite3 -header -cmd '.mode json' shop.db 'SELECT name, price FROM products;'
[{"name":"Pen","price":1.5},
{"name":"Notebook","price":4.25}]
$ sqlite3 -header -cmd '.mode csv' shop.db 'SELECT name, price FROM products;'
name,price
Pen,1.5
Notebook,4.25

.help mode lists every mode this version supports:

$ sqlite3 shop.db '.help mode'
.mode ?MODE? ?OPTIONS?   Set output mode
   MODE is one of:
     ascii       Columns/rows delimited by 0x1F and 0x1E
     box         Tables using unicode box-drawing characters
     csv         Comma-separated values
     column      Output in columns.  (See .width)
     html        HTML <table> code
     insert      SQL insert statements for TABLE
     json        Results in a JSON array
     line        One value per line
     list        Values delimited by "|"
     markdown    Markdown table format
     qbox        Shorthand for "box --wrap 60 --quote"
     quote       Escape answers as for SQL
     table       ASCII-art table
     tabs        Tab-separated values
     tcl         TCL list elements
   OPTIONS: (for columnar modes or insert mode):
     --escape T     ctrl-char escape; T is one of: symbol, ascii, off
     --wrap N       Wrap output lines to no longer than N characters
     --wordwrap B   Wrap or not at word boundaries per B (on/off)
     --ww           Shorthand for "--wordwrap 1"
     --quote        Quote output text as SQL literals
     --noquote      Do not quote output text
     TABLE          The name of SQL table used for "insert" mode

Keep settings during a session

Settings such as .headers and .timer last until you change them or quit:

Task	Command
Show run time after each statement	`.timer on`
Turn a setting off	`.timer off`, `.headers off`

The timer figures vary on every run, so they’re shown here as …:

sqlite> .headers on
sqlite> .mode column
sqlite> .timer on
sqlite> SELECT count(*) AS products FROM products;
products
--------
2
Run Time: real … user … sys …

Export and import data

These commands move data in and out as files (redirecting output, importing CSV files):

Task	Command
Save query results as CSV	`sqlite3 -header -csv shop.db 'SELECT ...' > file.csv`
Send only the next result to a file	`.once file.txt`
Import a CSV file	`.import --csv file.csv table`

Exporting to CSV from the terminal:

$ sqlite3 -header -csv shop.db 'SELECT * FROM products;' > products.csv
$ cat products.csv
id,name,price
1,Pen,1.5
2,Notebook,4.25

Inside the shell, .once sends only the next result to a file:

sqlite> .once names.txt
sqlite> SELECT name FROM products ORDER BY id;
$ cat names.txt
Pen
Notebook

Importing that CSV into a new database. If the table doesn’t exist, the first row becomes its column names:

$ sqlite3 new.db '.import --csv products.csv products'
$ sqlite3 -header -column new.db 'SELECT * FROM products;'
id  name      price
--  --------  -----
1   Pen       1.5
2   Notebook  4.25

Back up and restore

.dump writes the whole database as SQL text, which can rebuild it anywhere (converting to a text file). .backup makes a binary copy of the database file:

Task	Command
Dump the database as SQL	`sqlite3 shop.db .dump > shop.sql`
Rebuild from a dump	`sqlite3 restored.db < shop.sql`
Binary backup	`.backup shop-backup.db`

$ sqlite3 shop.db .dump > shop.sql
$ head -5 shop.sql
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL);
INSERT INTO products VALUES(1,'Pen',1.5);
INSERT INTO products VALUES(2,'Notebook',4.25);
$ sqlite3 restored.db < shop.sql
$ sqlite3 restored.db 'SELECT count(*) FROM products;'
2

$ sqlite3 shop.db '.backup shop-backup.db'
$ sqlite3 shop-backup.db 'PRAGMA integrity_check;'
ok

Common mistakes

Watch for these when you start using the shell:

A dot-command with a semicolon or leading spaces. It must start at the left margin, and it takes no ;.

SQL without a semicolon. “If you omit the semicolon, sqlite3 will give you a continuation prompt” and waits for the rest of the statement.
Forgetting that sqlite3 with no file name is temporary. Use .save before you quit, or open a file instead.
Typing SHOW TABLES from MySQL. SQLite’s equivalent is .tables.