SQLite 3 Cheatsheet

Opening and leaving

• Check the SQLite version — Print the version of the sqlite3 shell.
• Open a database — Open a database file, creating it if it does not exist.
• Run a query from the shell — Run one statement and exit.
• Open another database — Switch to a different database file from inside the shell.
• List dot-commands — Show every shell command.
• Quit the shell — Leave the sqlite3 shell and return to your terminal.

Inspecting the database

• List tables — Show the tables in the database.
• Show a table's definition — Print the CREATE statements for a table and its indexes.
• Show the whole schema — Print the CREATE statements for everything.
• List a table's indexes — Show the indexes on a table.
• List a table's columns — Show column names, types and keys with a pragma.
• List a table's foreign keys — Show the foreign keys declared on a table.
• List attached databases — Show the databases and files in use.

Display options

• Show column headers — Print column names above query results.
• Align results in columns — Format results as an aligned table.
• Output JSON — Print query results as JSON.
• Output CSV — Print results as comma-separated values.
• Time each query — Print how long every statement takes.
• Send output to a file — Write results to a file instead of the screen.

Import, export and backup

• Import a CSV file — Load a CSV file into a table, creating the table from the first row if it doesn't exist.
• Export a query to CSV — Write query results to a CSV file with headers.
• Dump the database as SQL — Write the schema and data as SQL statements.
• Restore from a SQL dump — Recreate a database from a dump into a new file.
• Make a binary backup — Copy the database to a file safely, even while it is in use.
• Run a SQL script — Execute the statements in a file.

Pragmas and maintenance

• Enforce foreign keys — Foreign key checks are off by default. Turn them on for each connection.
• Turn on write-ahead logging — Let readers and a writer work at the same time. The setting persists in the file.
• Wait when the database is locked — Retry for up to 5 seconds instead of failing with SQLITE_BUSY.
• Check for corruption — Verify the database file's integrity. It prints ok when everything is fine.
• Shrink the database file — Rebuild the file to reclaim free space.
• Update query planner statistics — Gather statistics so the planner can choose better indexes.
• See a query plan — Show whether a query uses an index.

Everyday SQL

• Create a table — Create a table with an auto-assigned integer key and a default timestamp.
• Add a column — Add a column to an existing table.
• Insert or ignore duplicates — Insert a row, doing nothing if the email already exists.
• Read a value from JSON — Extract a field from a JSON text column.
• Get the current time — Return the current UTC date and time.

Related cheatsheets

• DuckDB 2 cheatsheet — Query CSV, Parquet and DataFrames with SQL from Python or the duckdb command line, without running a server.
• Elasticsearch 9 cheatsheet — curl requests for indexes, documents, searches, bulk loads and cluster health. Security is on by default, so the examples authenticate.
• MongoDB 8 cheatsheet — mongosh shell commands for documents, queries, aggregation, indexes, backups and administration.
• MySQL 8.4 cheatsheet — mysql client, backup and maintenance commands for MySQL 8.4.
• PostgreSQL 18 cheatsheet — psql, backup and maintenance commands for PostgreSQL 18.
• Redis 8 cheatsheet — redis-cli and core commands for keys, data structures and server administration.

Browse all cheatsheets