Use the SQLite shell

Open a database file from the terminal:

sqlite3 notes.db

You are now inside the shell, and everything you type goes to one of two interpreters. Knowing which one you are talking to saves a lot of confusion.

SQL statements such as SELECT 1; end with a semicolon. If you press enter without one, the shell shows a continuation prompt (...>) and waits. It thinks your statement is not finished. Type the missing ; and press enter.

Dot commands start with a dot, belong to the shell itself, take no semicolon, and must fit on one line. Try .databases, .tables, .schema, and .help.

sqlite> .tables
notes
sqlite> .schema notes
CREATE TABLE notes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL
) STRICT;

.tables lists what exists. .schema prints the exact SQL that created it. That is how you inspect any unfamiliar SQLite file. .databases shows the full path of the open file, which settles any doubt about which database you are actually in.

Make query output readable

The default output is bare values separated by |. Two settings make it much friendlier:

sqlite> .headers on
sqlite> .mode box
sqlite> SELECT id, title FROM notes;
┌────┬───────────────┐
│ id │     title     │
├────┼───────────────┤
│ 1  │ Plan the week │
└────┴───────────────┘

These settings last for the session. Leave with .quit and they reset.

Run a query without entering the shell

You can also pass SQL directly as an argument, which is handy in scripts:

sqlite3 notes.db "SELECT count(*) FROM notes;"
# 1

The shell runs the query, prints the result, and exits.

The classic mistake here is mixing the two languages: typing .tables; (dot command with a semicolon) or SELECT 1 (SQL without one). The first prints an error, the second leaves you stuck at the ...> continuation prompt. If the shell seems to hang, look at the prompt. ...> means it is waiting for your semicolon, and typing ; on its own line finishes the statement.