The sqlite3 command-line shell

The shell is the fastest way to explore a SQLite file, fix data or import a CSV.

The sqlite3 program is SQLite's interactive shell. sqlite3 shop.db opens (or creates) that file; sqlite3 alone opens a temporary in-memory database. Type SQL statements ending with ;.

Commands that start with a dot are handled by the shell itself, not SQLite's SQL engine:

.tables lists tables; .schema product shows the CREATE statement.
.headers on and .mode box (or column, table, markdown, json, csv) make output readable.
.import --csv data.csv product loads a CSV; if the table doesn't exist, the first row becomes column names.
.once out.csv sends the next query's output to a file; .output redirects until reset.
.read file.sql runs a script; .dump writes the whole database as SQL text, a simple backup.
.open other.db switches databases; .quit exits; .help lists everything.

You can also run one-off commands from the shell: sqlite3 shop.db "SELECT count(*) FROM product;", or pipe a script in with sqlite3 shop.db < schema.sql.

GUI tools such as DB Browser for SQLite and the database panels in many IDEs work on the same file. Note that many operating systems ship an old sqlite3 binary; download a current one from sqlite.org if a newer feature is missing.

Example

$ sqlite3 shop.db
sqlite> CREATE TABLE product (id INTEGER PRIMARY KEY, name TEXT, price REAL);
sqlite> INSERT INTO product (name, price) VALUES ('pen', 1.5), ('book', 12);
sqlite> .headers on
sqlite> .mode box
sqlite> SELECT * FROM product;
┌────┬──────┬───────┐
│ id │ name │ price │
├────┼──────┼───────┤
│ 1  │ pen  │ 1.5   │
│ 2  │ book │ 12.0  │
└────┴──────┴───────┘
sqlite> .tables
product
sqlite> .import --csv more_products.csv product
sqlite> .quit

$ sqlite3 shop.db .dump > backup.sql      # whole database as SQL text

Remember

sqlite3 file.db opens or creates a database
Dot-commands (.tables, .schema, .mode, .import) belong to the shell
.dump exports everything as SQL

Watch out for

Running .import into an existing table whose first CSV row is a header, which then becomes a data row (use --skip 1).

Check yourself

What does .schema product do?

Official docs: sqlite.org/cli.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks