SQLite Common Commands - CodeSwordsman

Frequent sqlite3 dot commands, transactions and PRAGMAs, import/export and backups, indexing and integrity checks for local and embedded use.

SQLite 3Updated 2026-10-3024 commands

Connection & Dot Commands

Open a database file, creating it when missing

sqlite3 app.db

List every table, equivalent to querying sqlite_master

.tables

Show the CREATE statement for a table; omit the name to dump all objects

.schema orders

Show column headers in query output

.headers on

Align output into columns; use .mode csv for exports

.mode column

Exit the sqlite3 shell

.quit

Start a transaction so a batch of writes commits or rolls back atomically

begin;

Commit the transaction; rollback discards all uncommitted changes

commit;

Switch to WAL mode for better read/write concurrency and less write blocking

pragma journal_mode=wal;

WAL creates extra -wal and -shm files that must be handled when copying the database

A typical paginated query

select id, name from orders where status = 1 order by id desc limit 20;

Count rows; expensive on large databases

select count(*) from orders;

Print a full SQL backup to stdout, which can be redirected into a .sql file

.dump

Back up the database online, producing a consistent copy while it is in use

.backup /backup/app.db

Import a CSV file into a table; set .mode csv and .separator first

.import data.csv orders

Send subsequent query results to a file; .output stdout switches back

.output /tmp/orders.csv

Execute a SQL script for initialization or restore

.read init.sql

Create a composite index covering the common user-and-time query

create index idx_orders_user on orders(user_id, created_at);

List the indexes defined on a table

.indexes orders

Add a column; SQLite supports only a limited set of schema changes

alter table orders add column remark text;

Rebuild the database file to reclaim space after deletions

vacuum;

Verify database file integrity, reporting explicit errors when damaged

pragma integrity_check;

Check whether a query uses an index and how it scans

explain query plan select * from orders where user_id = 42;

Let SQLite optimize internal structures using current statistics

pragma optimize;

Follow up with analyze to refresh statistics used by the query planner

Show the page size; deciding it before creating the database avoids later migrations

pragma page_size;