sqlite3 — The SQLite Command-Line Tool

sqlite3 is the official command-line tool for SQLite – a serverless, file-based SQL database that stores an entire database in a single file. You need no server, no configuration and no running service: you simply open a .db file and get to work. Alongside plain SQL, the tool offers so-called dot-commands like .tables, .schema and .mode that let you inspect the database, control the output format and import or export data. This guide walks you through the commands you reach for in development, testing and embedded use.

Open & Create

sqlite3 <database> — Open a database (creates if not exists).

sqlite3 myapp.db

sqlite3 :memory: — Create an in-memory database.

sqlite3 :memory:

sqlite3 <database> '<query>' — Execute a query and exit.

sqlite3 myapp.db 'SELECT count(*) FROM users;'

sqlite3 <database> < <file> — Execute SQL from a file.

sqlite3 myapp.db < schema.sql

Dot-Commands — Info

.databases — List attached databases and their file paths.

.databases

.tables — List all tables in the current database.

.tables

.tables <pattern> — List tables matching a pattern.

.tables user%

.schema <table> — Show the CREATE statement for a table.

.schema users

.schema — Show CREATE statements for all tables.

.schema

.indexes <table> — Show indexes for a table.

.indexes users

Dot-Commands — Output

.mode <mode> — Set output mode (column, csv, json, table, line, etc.).

.mode table

.headers on — Show column headers in output.

.headers on

.output <file> — Send query output to a file.

.output results.txt

.output — Reset output back to stdout.

.output

.once <file> — Send next query output to a file (one-time).

.once export.csv

Import & Export

Import CSV data into a table.

.mode csv
.import <file> <table>

Export a table to CSV.

.mode csv
.headers on
.output <file>
SELECT * FROM <table>;

.dump — Export entire database as SQL statements.

.output backup.sql
.dump

.dump <table> — Export a specific table as SQL.

.dump users

.read <file> — Execute SQL commands from a file.

.read migrations/001.sql

Backup & Utility

.backup <file> — Create a backup of the database.

.backup /tmp/myapp-backup.db

.clone <file> — Clone the current database to a new file.

.clone myapp-copy.db

VACUUM; — Compact the database file (reclaim free space).

VACUUM;

PRAGMA integrity_check; — Check database integrity.

PRAGMA integrity_check;

PRAGMA table_info(<table>); — Show column details for a table.

PRAGMA table_info(users);

.quit — Exit sqlite3.

.quit

Common Patterns

sqlite3 <db> -header -csv '<query>' — One-liner CSV export.

sqlite3 myapp.db -header -csv 'SELECT * FROM users' > users.csv

sqlite3 <db> -json '<query>' — One-liner JSON export.

sqlite3 myapp.db -json 'SELECT * FROM users'

sqlite3 <db> '.dump' | sqlite3 <newdb> — Clone a database via dump and restore.

sqlite3 prod.db '.dump' | sqlite3 dev.db

sqlite3 <db> 'SELECT count(*) FROM <table>;' — Quick row count from command line.

sqlite3 myapp.db 'SELECT count(*) FROM users;'

Conclusion

sqlite3 packs a complete SQL database into a single file – and bundles the command line right along with it – making it ideal for development, testing, small tools and embedded use. The dot-commands handle the routine work: use .schema and .tables to get your bearings, .mode and .output to control export formats, and .dump or .backup to capture the whole dataset. Be careful with writing commands: a DROP or a DELETE without a WHERE is immediate and irreversible. For consistent backups, use .backup rather than copying an open file, and mind the file permissions of the .db file – anyone who can read it reads all of your data.