SQLite Cheatsheet
SQLite command-line reference for opening databases, running SQL, formatting results, importing CSV files, and making backups.
This cheatsheet covers opening SQLite databases, running queries, formatting results, importing CSV files, and making backups with sqlite3. Run sqlite3 commands in your terminal, and enter SQL and dot commands in the SQLite shell.
Open and Inspect
For worked examples, see our sqlite3 command guide .
| Command | Description |
|---|---|
sqlite3 tasks.db | Open a database; create the file on the first write if it is missing |
sqlite3 -readonly tasks.db | Inspect an existing database without allowing writes |
sqlite3 :memory: | Open a database that disappears when the shell exits |
sqlite3 --version | Show the installed SQLite version |
.databases | Show open databases and their file paths |
.tables | List tables and views |
.schema tasks | Show the SQL that defines a table |
.help | Show available dot commands |
.quit | Exit the SQLite shell |
Run SQL
End SQL statements with a semicolon. Dot commands do not need one. Use WHERE to limit which rows an update changes.
| SQL | Description |
|---|---|
CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT NOT NULL, done INTEGER DEFAULT 0); | Create a task table with automatically assigned IDs |
INSERT INTO tasks (title) VALUES ('Check backups'); | Add a task with the default done value of 0 |
SELECT * FROM tasks LIMIT 10; | Preview up to ten rows |
SELECT title FROM tasks WHERE done = 0; | List unfinished tasks |
SELECT count(*) FROM tasks; | Count all rows |
UPDATE tasks SET done = 1 WHERE id = 1; | Mark one task as completed |
Output Formats
Set the mode before changing headers. Column spacing and alignment can vary by SQLite version.
| Command | Description |
|---|---|
.mode list | Separate columns with pipes |
.mode column | Align values in columns |
.mode box | Add table borders |
.mode json | Print results as JSON |
.mode csv | Format results as CSV |
.headers on | Include column names where the mode supports them |
.headers off | Hide column names where the mode supports it |
CSV Import and Export
Imports append rows. Use --skip 1 for a CSV header when the target table already exists. To export, set .mode csv and .headers on before .once. Choose an unused output filename.
| Command | Description |
|---|---|
.import --csv contacts.csv contacts | Import CSV; a new table uses the first row as column names |
.import --csv --skip 1 contacts.csv contacts | Skip the header when importing into an existing table |
.once report.csv | Send the next query result to a file |
SELECT id, title FROM tasks; | Run after .once to export the selected columns |
.output report.csv | Send query results to a file until output is changed |
.output | Send query results back to the terminal |
Transactions and Locks
Back up first and preview the matching rows before deleting. Use ROLLBACK to discard changes or COMMIT to keep them.
| Command | Description |
|---|---|
BEGIN; | Start a transaction |
SELECT * FROM tasks WHERE done = 1; | Preview completed tasks before deletion |
DELETE FROM tasks WHERE done = 1; | Delete completed tasks inside the transaction |
ROLLBACK; | Undo the uncommitted changes |
COMMIT; | Keep the changes; they can no longer be rolled back |
.timeout 5000 | Wait up to five seconds for a lock; does not release another connection’s lock |
Backup and Restore
Run these commands in your terminal. Choose unused backup filenames and restore SQL into a new database. Use .backup for a live database, including committed data still in its WAL file.
| Command | Description |
|---|---|
sqlite3 -readonly tasks.db ".backup backup.db" | Create a consistent database copy |
sqlite3 -readonly tasks.db .dump > backup.sql | Export the database as SQL statements |
sqlite3 -bail restored.db < backup.sql | Restore SQL into a new database and stop on errors |
sqlite3 -readonly backup.db "PRAGMA integrity_check;" | Check database structure; ok means the check passed |