Open a database file
Start the sqlite3 shell with a file-backed database.
bashANYsqlite3opendatabase
bash
sqlite3 app.dbNotes
Creates the file if it does not exist and opens an interactive shell session.
SQLite CLI and Shell Cheatsheet
sqlite3 shell commands for opening databases, inspecting schema, formatting output, importing CSV, and scripting common interactive workflows.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Open and inspect databases
Start sqlite3, open files, inspect databases, and discover schema objects.
Open an in-memory database
Start a temporary database that lives only for the session.
bashANYsqlite3memorytesting
bash
sqlite3 :memory:Notes
Useful for quick experiments, demos, and throwaway SQL tests.
List attached databases
Show the main database and any attached files.
textANYsqlite3databasesattach
text
.databasesNotes
Displays the database aliases and file paths known to the current connection.
text
.tablesNotes
Fast way to discover tables and virtual tables from the shell.
text
.schemaNotes
Good for inspecting tables, indexes, triggers, and views.
Show schema for one table
Print the CREATE statement for a specific object.
textANYsqlite3schematable
text
.schema usersNotes
Limits output to matching objects, which is easier to read in larger databases.
Show schema plus sqlite_stat tables
Include internal objects useful for advanced debugging.
textANYsqlite3fullschemaplanner
text
.fullschemaNotes
Helpful when investigating query-planner behavior and generated internal objects.
text
.helpNotes
Useful when working on a new machine or less-familiar sqlite3 version.
Output, import, and export
Format results, run scripts, import CSV, and dump databases.
text
.headers onNotes
Often paired with `.mode column` for human-readable query results.
text
.mode columnNotes
Great for interactive browsing in a terminal.
text
.mode csvNotes
Useful before redirecting or writing query output to a file.
text
.import ./users.csv usersNotes
Make sure the target table exists first or use a staging table for cleanup.
text
.read ./schema.sqlNotes
Useful for schema initialization, migrations, or repeatable demos.
text
.dumpNotes
Helpful for backups, migrations, and reproducible fixtures.
Write the next query result to a file
Send one result set to a file without changing subsequent output.
textANYsqlite3onceoutput
text
.once users.csvNotes
Combine with `.mode csv` and a `SELECT` statement to export a query result.
Attempt recovery from a damaged database
Use the shell recovery command.
textANYsqlite3recovercorruption
text
.recoverNotes
Can salvage data from a corrupt database into SQL output for rebuilding.
More in SQLite
SQLite Triggers, Views, and Generated Columns Cheatsheet
CREATE VIEW, CREATE TRIGGER, audit logging, derived values, generated columns, and automation patterns in SQLite.
SQLite Full-Text Search (FTS5) Cheatsheet
FTS5 virtual tables, MATCH queries, prefix search, phrase search, bm25 ranking, snippets, and highlighting patterns for SQLite search features.
SQLite PRAGMA and Administration Cheatsheet
High-signal PRAGMA commands for foreign keys, cache, synchronous settings, page size, temp storage, health checks, and general SQLite administration.
SQLite JSON Functions Cheatsheet
json_extract, json_set, json_each, JSON operators, generated columns, and practical JSON query/update patterns in SQLite.
SQLite Import, Export, and Backup Cheatsheet
CSV import/export, SQL dumps, VACUUM INTO, backup copies, and repeatable data movement patterns for SQLite databases.
SQLite Transactions, WAL, and Locking Cheatsheet
BEGIN modes, COMMIT and ROLLBACK, savepoints, WAL mode, busy timeouts, and concurrency patterns for SQLite applications.