SQLite CLI: Using the sqlite3 Command Line Shell

What the CLI Is

sqlite3 is the official command line shell that ships with SQLite. It opens a database file, gives you a prompt, and lets you run SQL against it. No server, no login, no config — just a binary and a file.

You'll use it for quick inspections, one-off queries, schema dumps, and imports. It's the fastest way to poke at a database without writing application code.

$ sqlite3 mydata.db
SQLite version 3.45.0
Enter ".help" for usage hints.
sqlite>

If mydata.db doesn't exist yet, SQLite will create it as soon as you make your first change. Running sqlite3 with no filename opens a throwaway in-memory database.

Running SQL at the Prompt

Once you're inside the shell, type SQL and end every statement with a semicolon. The shell waits for that semicolon — if you hit Enter without one, it just shows a continuation prompt.

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER
);

INSERT INTO books (title, year) VALUES ('Dune', 1965);
INSERT INTO books (title, year) VALUES ('Neuromancer', 1984);

SELECT * FROM books;

The semicolon is the signal "I'm done, run this." Forget it and the shell will sit there with a ...> prompt, waiting. Type ; on a new line to finish.

Dot Commands Are Not SQL

Anything that starts with a . is a dot command — an instruction to the shell itself, not to the SQL engine. They don't take semicolons and they only work inside the sqlite3 shell.

sqlite> .tables
books

sqlite> .schema books
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER
);

sqlite> .help

The handful you'll use constantly:

• .tables — list every table in the current database.
• .schema [table] — show the CREATE TABLE statement for one table or all of them.
• .databases — list attached database files.
• .mode <format> — change how query results are displayed.
• .headers on — show column names above query results.
• .read file.sql — execute a SQL file.
• .exit or .quit — leave the shell.

.help prints the full list. There are dozens, but you'll lean on maybe ten of them.

Making Output Readable

The default output is pipe-separated and has no headers — fine for scripts, ugly for reading. Two dot commands fix that:

sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM books;
id  title        year
--  -----------  ----
1   Dune         1965
2   Neuromancer  1984

Other useful modes:

• .mode box — draws a Unicode-bordered table. Pretty for screenshots.
• .mode csv — comma-separated, good for piping into other tools.
• .mode json — JSON output, one object per row.
• .mode line — one column per line, handy when rows are wide.

Settings reset every time you launch the shell. To make them permanent, drop them into a ~/.sqliterc file and sqlite3 will load it on startup.

Importing and Exporting CSV

The CLI has built-in CSV support. Switch to CSV mode, then .import a file into a table:

sqlite> .mode csv
sqlite> .import authors.csv authors
sqlite> SELECT * FROM authors LIMIT 3;

If the table doesn't exist, .import creates one using the first row as column names. If it does exist, the first row is treated as data unless you pass --skip 1.

Going the other direction, redirect output to a file:

sqlite> .headers on
sqlite> .mode csv
sqlite> .output books.csv
sqlite> SELECT * FROM books;
sqlite> .output stdout

.output filename sends every following query result to that file until you switch back with .output stdout. It's the same mechanism whether you're exporting CSV, JSON, or a raw schema dump.

Running SQL Files

For anything longer than a few statements, put the SQL in a file and run it. Inside the shell:

sqlite> .read setup.sql

Or from your normal terminal, without entering the shell at all:

$ sqlite3 mydata.db < setup.sql
$ sqlite3 mydata.db "SELECT count(*) FROM books;"

The second form runs a single query and exits — perfect for shell scripts or quick checks. Combine it with .mode settings via the -cmd flag if you need formatted output:

$ sqlite3 -header -column mydata.db "SELECT * FROM books;"

Inspecting an Unknown Database

When someone hands you a .db file and you have no idea what's in it, this is the routine:

$ sqlite3 mystery.db
sqlite> .tables
sqlite> .schema
sqlite> .indexes
sqlite> SELECT count(*) FROM some_table;

.schema with no argument dumps every CREATE TABLE, CREATE INDEX, and CREATE TRIGGER in the file. .indexes lists indexes per table. Five minutes in the shell tells you more about a database than any README usually will.

For a complete textual snapshot you can re-create elsewhere, use .dump:

sqlite> .output backup.sql
sqlite> .dump
sqlite> .output stdout

That writes a SQL script that fully recreates the database — schema and data — when fed back through sqlite3.

Leaving the Shell

Three ways out, all equivalent:

sqlite> .exit
sqlite> .quit

Or press Ctrl+D on macOS and Linux (Ctrl+Z then Enter on Windows). Your changes are already saved — SQLite commits each statement as it runs unless you've started a transaction explicitly.

Next: GUI Tools for SQLite

The CLI is fast and scriptable, but sometimes you want to click through tables, sort columns, and edit rows visually. There are a few solid GUI tools for SQLite — that's the next page.