SQLite3 CLI

SQLite3 CLI cheatsheet — query and manage .db files from terminal. .tables, .schema, SELECT, INSERT, .dump, .import. Lightweight embedded database with a powerful CLI.

6 min read

What it is

The SQLite3 command-line interface (CLI) is a powerful tool for interacting with SQLite databases directly from your terminal, allowing you to create, query, and manage .db files.

Installation

Linux:

sudo apt update && sudo apt install sqlite3  # Debian/Ubuntu
sudo yum install sqlite3                   # Fedora/CentOS/RHEL
sudo pacman -S sqlite                     # Arch Linux

macOS: SQLite3 is usually pre-installed. You can install or upgrade it using Homebrew:

brew install sqlite

Windows: Download the precompiled binaries from the SQLite Download Page. Extract the sqlite3.exe file and add its directory to your system’s PATH environment variable, or run it from the directory where you extracted it.

Core Concepts

Database File (.db): SQLite stores entire databases (tables, indexes, views, etc.) in a single file. You can specify a database file when launching the sqlite3 command. If it doesn’t exist, it will be created.
SQL Commands: The sqlite3 CLI executes standard SQL commands.
.commands (Dot Commands): These are special commands specific to the SQLite CLI, used for managing the database, viewing schema, and controlling the CLI’s behavior. They start with a dot (.) and are not SQL.

Commands / Usage

Opening and Creating Databases

Open an existing database or create a new one:
```
sqlite3 mydatabase.db
```
This opens mydatabase.db. If it doesn’t exist, it’s created. You’ll see the sqlite> prompt.
Open in memory (temporary database):
```
sqlite3 :memory:
```
This creates a temporary database entirely in RAM. It’s lost when you exit sqlite3.

Executing SQL Queries

Create a table and insert data:

sqlite3 mydatabase.db "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT); INSERT INTO users (name) VALUES ('Alice');"

Executes SQL commands directly without entering the interactive prompt.

Select data from a table:
```
sqlite3 mydatabase.db "SELECT name FROM users WHERE id = 1;"
```
Retrieves the name of the user with ID 1.

Interactive CLI Dot Commands

Database Management

.open FILENAME: Close the current database and open FILENAME.
```
.open another_database.db
```
.databases: List all databases currently attached.
```
.databases
```
Output might look like:
```
main: mydatabase.db
temp: :memory:
```
.close: Close the current database connection.
```
.close
```
.backup ?DB? FILE: Backup database DB (default main) into FILE.
```
.backup main backup.db
```
.restore ?DB? FILE: Restore database DB (default main) from FILE. Warning: This overwrites data.
```
.restore main restored.db
```

Schema and Table Information

.schema ?PATTERN?: Show the SQL statements used to create objects matching PATTERN.
```
.schema users
```
Output:
```
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
```
.tables ?PATTERN?: List tables and views matching PATTERN.
```
.tables
```
Output:
```
users
```
.indices ?TABLE_NAME?: List available indexes on TABLE_NAME.
```
.indices users
```
.table: Show a help message describing the .table command (lists tables, views, and indexes).
```
.table
```
.columns ?TABLE_NAME?: List columns for TABLE_NAME.
```
.columns users
```
Output:
```
id  INTEGER
name  TEXT
```

Query Execution and Output Formatting

.mode MODE: Set output mode (csv, column, html, insert, json, line, list, null, tabs, tcl).
```
.mode csv
SELECT * FROM users;
```
Output:
```
1,"Alice"
```
.headers ON|OFF: Toggle display of column names in query results.
```
.headers on
SELECT * FROM users;
```
Output:
```
id|name
1|Alice
```
.width COL1,...: Set column widths for column mode.
```
.mode column
.width 5,20
SELECT * FROM users;
```
.read FILENAME: Execute commands from FILENAME.
```
.read load_data.sql
```
.once FILENAME: Send subsequent query output to FILENAME instead of stdout.
```
.once output.txt
SELECT * FROM users;
.output stdout
```
.output FILENAME: Redirect all CLI output (including errors and dot commands) to FILENAME. stdout or stderr can be used to reset.
```
.output log.txt
SELECT * FROM users;
.output stdout
```

CLI Configuration and Help

.help: Show a list of available dot commands.
```
.help
```
.help CMD: Show help for a specific dot command CMD.
```
.help schema
```

.prompt: Show current prompt strings.

.prompt

Output:

Old prompt: !>
New prompt: sqlite>

.prompt NEW_PROMPT: Change the default prompt.
```
.prompt DB_PROMPT>
```
The prompt will change to DB_PROMPT>.
.timeout MS: Set the timeout in milliseconds for how long a query will wait for a lock to be released.
```
.timeout 5000
```
.changes: Show the number of database rows modified by the last INSERT, UPDATE, or DELETE.
```
INSERT INTO users (name) VALUES ('Bob');
SELECT changes(); -- SQL function
```
Or using the dot command after a modification:
```
.changes
```

Importing and Exporting Data

Importing CSV data:
```
sqlite3 mydatabase.db ".mode csv" ".import data.csv users"
```
Assumes data.csv has columns matching the users table.

Exporting table to CSV:

sqlite3 mydatabase.db ".mode csv" ".headers on" "SELECT * FROM users;" > users.csv

Exporting schema to SQL file:

sqlite3 mydatabase.db ".schema" > schema.sql

Exporting data as INSERT statements:

sqlite3 mydatabase.db ".mode insert users" "SELECT * FROM users;" > inserts.sql

Common Patterns

Quickly view table contents:

sqlite3 mydatabase.db "SELECT * FROM my_table LIMIT 10;"

Importing data from a file:
```
sqlite3 mydatabase.db < import_script.sql
```
Where import_script.sql contains SQL statements and potentially dot commands.

Piping SQL query output to another command:

sqlite3 mydatabase.db "SELECT name FROM users;" | grep "Alice"

Creating a temporary database for testing:

sqlite3 :memory: <<EOF
CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT);
INSERT INTO test (value) VALUES ('hello');
SELECT * FROM test;
.quit
EOF

Getting the schema of a remote database (if accessible via network file share or mounted volume):
```
sqlite3 /path/to/remote/database.db ".schema"
```

Counting rows in a table:

sqlite3 mydatabase.db "SELECT COUNT(*) FROM my_table;"

Checking if a table exists:

sqlite3 mydatabase.db "SELECT name FROM sqlite_master WHERE type='table' AND name='my_table';"

Gotchas

Case Sensitivity: Table and column names are generally case-insensitive in SQLite, but string comparisons within queries depend on the collation defined for the column.
Data Types: SQLite uses dynamic typing. While you can declare types like INTEGER, TEXT, REAL, BLOB, SQLite often stores them in one of five type affinity categories (NULL, INTEGER, TEXT, REAL, BLOB). This flexibility can sometimes mask type-related issues.
.import Behavior: The .import command expects the input file to contain data in a format that matches the table’s structure. If the CSV has different columns or order, it can lead to unexpected results or errors. It typically inserts data row by row.
.restore is Destructive: Using .restore will overwrite the entire existing database file. Be very careful.
Locking: SQLite uses file locking. If multiple processes try to write to the same database file simultaneously without proper coordination, you can encounter database is locked errors. The .timeout command can help mitigate this by waiting for locks to be released.
Dot Commands vs. SQL: Remember that .commands are for the CLI itself, while SQL statements are for the database engine. You cannot mix them directly in the same line unless using shell redirection or specific shell features like echo "SQL" | sqlite3 db.db.