SQLite Cheatsheet - SQLite Database Command Reference

Q: How do I back up and restore a SQLite database from the command line?

For a backup, run .output dump.sql to redirect the next output to a file, then .dump to write the whole database as SQL text; restore it later with .read dump.sql. To bring in a CSV use .import --csv data.csv my_table, and the same .output trick exports a table to CSV. Once a file bloats, VACUUM; reclaims free pages and shrinks it, while ANALYZE; refreshes the statistics the optimizer uses to pick indexes. Run PRAGMA integrity_check; whenever you suspect corruption.

Q: Which columns deserve an index, and what does a bad index cost?

Index the columns you filter, sort and join on frequently: CREATE INDEX idx_users_name ON users(name). When uniqueness is the real goal use CREATE UNIQUE INDEX idx_users_email ON email, which doubles as a data constraint. The cost is on writes — every INSERT and UPDATE has to maintain the index and it consumes disk, so do not index columns you never query. Use PRAGMA index_list(users); to audit what exists and DROP INDEX IF EXISTS idx_users_name; to clear the useless ones. Note that CREATE TABLE backup AS SELECT * FROM users copies data only — indexes are not inherited and must be recreated.

Q: I keep hitting "database is locked" with concurrent writers — what should I change?

Switch the journal mode first: PRAGMA journal_mode=WAL; allows readers and a writer to proceed together and is the single biggest win for concurrency. Wrap write batches in a transaction — BEGIN TRANSACTION;, run several INSERTs, then COMMIT; — so you take the lock once instead of per statement. When only part of a long transaction is bad, mark a point with SAVEPOINT sp1;, recover with ROLLBACK TO sp1;, and finish the savepoint with RELEASE SAVEPOINT sp1;, or discard everything with ROLLBACK;. Scratch data belongs in CREATE TEMP TABLE, which is dropped when the session ends and stays out of the main lock.

The choice among embedded databases is narrow: when you want zero operations, a single file you can copy, and backups that amount to copying that file, SQLite is almost always right, at the cost of allowing only one writer at a time. This table groups everything from connecting and basics through table operations, data manipulation, query techniques, indexes and constraints, transactions and locking, import and export with tools, and utility functions and PRAGMA, so the path runs from creating a schema all the way to WAL mode and CSV round-trips. The deciding factor is the concurrency model. If writes come from multiple processes or threads, PRAGMA journal_mode=WAL plus a sensible busy_timeout is mandatory; for a single-process script the better investment is defining types and constraints in the schema so bad data fails at insert time. Reach for it when adding local persistence to a CLI, running a migration, doing one-off analysis, or loading a CSV for querying. The boundary against Postgres or MySQL is plain: with no server process there is no network layer, and the bottleneck is usually writes rather than queries.

Databases·52 commands·Last updated 2026-07-21

Typical Use Case

For developers who need lightweight local storage: embedded data in desktop and mobile apps, scripts and automation jobs, test fixtures, browser-side data, or small read-heavy server configuration. SQLite has no server process and no setup — one file is the database, and copying it is the migration. Three layers are covered: DML and query technique including upsert, GROUP BY with HAVING, joins and aggregates; indexes and constraints, meaning unique indexes, foreign keys and CHECK, which are the only real lever on how the database behaves once the data grows; and operational work, covering WAL journal mode, dumping a backup, vacuuming space and integrity checking.

Database Connection & Basics 6

sqlite3 file.db

Open or create a SQLite database file

.open file.db

Open a database file from within the sqlite3 CLI

.databases

List all open database connections

.tables

List all tables in the current database

.quit

Exit the sqlite3 CLI

.help

Show help for all available dot commands

Table Operations 6

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

Create table with columns and constraints

DROP TABLE IF EXISTS users;

Drop table if it exists

ALTER TABLE users ADD COLUMN email TEXT;

Add a new column

CREATE TABLE backup AS SELECT * FROM users;

Create a new table from query results

CREATE TEMP TABLE temp_data (id INT);

Create a temporary table, auto-deleted after session

.schema users

View the CREATE TABLE statement for a table

Data CRUD 7

INSERT INTO users (name, age) VALUES ('Tom', 25);

Insert a single record

INSERT INTO users (name, age) VALUES ('Tom', 25), ('Jerry', 30);

Batch insert multiple records

SELECT * FROM users;

Query all records

UPDATE users SET age = 26 WHERE name = 'Tom';

Update records matching condition

DELETE FROM users WHERE name = 'Tom';

Delete records matching condition

REPLACE INTO users (id, name, age) VALUES (1, 'Tom', 26);

Insert or replace based on primary key or unique constraint

INSERT INTO users (id, name) VALUES (1, 'Tom') ON CONFLICT(id) DO UPDATE SET name = excluded.name;

UPSERT: update on conflict, insert otherwise

Advanced Queries 8

SELECT * FROM users WHERE age > 18;

WHERE condition filtering

SELECT * FROM users ORDER BY age DESC, name ASC;

Multi-field sorting

SELECT status, COUNT(*) FROM users GROUP BY status;

GROUP BY aggregation

SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 5;

HAVING filter on grouped results

SELECT * FROM users LIMIT 10 OFFSET 20;

Pagination: limit results and skip first 20

SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;

INNER JOIN between tables

SELECT DISTINCT city FROM users;

Deduplicate query results

SELECT COUNT(*), AVG(age), MAX(age), MIN(age) FROM users;

Aggregate functions: count, avg, max, min

Indexes & Constraints 6

CREATE INDEX idx_users_name ON users(name);

Create index to speed up queries

CREATE UNIQUE INDEX idx_users_email ON users(email);

Create unique index

DROP INDEX IF EXISTS idx_users_name;

Drop an index

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, age INT DEFAULT 0);

Column-level constraints: PK, UNIQUE, NOT NULL, DEFAULT

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INT REFERENCES users(id));

Define foreign key constraint

CREATE TABLE products (id INT, price REAL CHECK(price > 0));

CHECK constraint to restrict value range

Transactions & Locking 7

BEGIN TRANSACTION;

Start a transaction

COMMIT;

Commit transaction, persist all changes

ROLLBACK;

Rollback transaction, undo all uncommitted changes

SAVEPOINT sp1;

Set a savepoint for partial rollback

ROLLBACK TO sp1;

Rollback to a specific savepoint

RELEASE SAVEPOINT sp1;

Release a savepoint

PRAGMA journal_mode=WAL;

Set WAL mode for better concurrent read/write performance

Import/Export & Tools 6

.import --csv data.csv my_table

Import CSV file into a table

.output dump.sql

Redirect output to a file

.dump

Export entire database as SQL text

.read dump.sql

Execute SQL file (restore backup)

VACUUM;

Reclaim free space, reduce database file size

ANALYZE;

Update statistics to help query optimizer

Utility Functions & PRAGMA 6

PRAGMA table_info(users);

View column information of a table

PRAGMA index_list(users);

View index list of a table

PRAGMA integrity_check;

Check database integrity

SELECT datetime('now');

Get current date and time

SELECT strftime('%Y-%m-%d', 'now');

Format date output

SELECT length('hello'), typeof(42), randomblob(16);

Utility functions: length, typeof, randomblob

Parameter matrix

Parameter	Effect	Example
`.open <file>`	Opens or creates a database file.	`sqlite3 file.db`
`.tables / .databases`	Lists the tables in the current database / open connections.	`.tables`
`.schema <table>`	Shows the CREATE TABLE statement.	`.schema users`
`CREATE TABLE ... PRIMARY KEY`	Defines the primary key and column-level constraints.	`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL, age INT DEFAULT 0);`
`REFERENCES users(id)`	Foreign key constraint.	`CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INT REFERENCES users(id));`
`CHECK(price > 0)`	Restricts a column's range of values.	`CREATE TABLE products (id INT, price REAL CHECK(price > 0));`
`ON CONFLICT DO UPDATE`	UPSERT: update on conflict, insert otherwise.	`INSERT INTO users (id, name) VALUES (1, 'Tom') ON CONFLICT(id) DO UPDATE SET name = excluded.name;`
`REPLACE INTO`	Inserts or replaces the whole row by primary key or unique constraint.	`REPLACE INTO users (id, name, age) VALUES (1, 'Tom', 26);`
`GROUP BY + HAVING`	Filters groups after group-wise aggregation.	`SELECT status, COUNT() FROM users GROUP BY status HAVING COUNT() > 5;`
`LIMIT / OFFSET`	Pagination: limit the number of rows and skip the leading ones.	`SELECT * FROM users LIMIT 10 OFFSET 20;`
`JOIN ... ON`	Join query (INNER JOIN).	`SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;`
`CREATE [UNIQUE] INDEX`	Creates an index / unique index for speed and deduplication.	`CREATE UNIQUE INDEX idx_users_email ON users(email);`
`PRAGMA journal_mode=WAL`	Switches to WAL mode to improve concurrent read/write performance.	`PRAGMA journal_mode=WAL;`
`BEGIN / COMMIT / ROLLBACK`	Transaction boundary control.	`BEGIN TRANSACTION;`
`SAVEPOINT / ROLLBACK TO`	Savepoints and partial rollback.	`ROLLBACK TO sp1;`
`.import --csv`	Imports a CSV as a table.	`.import --csv data.csv my_table`
`.dump / .read`	Exports the whole database as SQL / replays a SQL file to restore.	`.dump`
`VACUUM / ANALYZE`	Reclaims free space / refreshes statistics.	`VACUUM;`
`PRAGMA integrity_check`	Checks database file integrity.	`PRAGMA integrity_check;`

Common pitfalls

SymptomThe database file keeps growing, yet deleting a large amount of data leaves its size unchanged.

CauseDeletion only marks pages as free; it does not shrink the file automatically.

FixRun VACUUM; to reclaim free space; it rewrites the whole database, so reserve disk headroom.

SymptomQueries get slower as data grows, and the query plan does not use an index.

CauseStale statistics make the optimizer decide on outdated row counts; or the query conditions do not match the indexed columns.

FixRun ANALYZE; first to refresh statistics, then use PRAGMA index_list(users) to confirm an index really exists on the target column.

SymptomWrites report database is locked, or reads frequently time out under concurrency.

CauseIn the default rollback-journal mode reads and writes exclude each other, and long transactions hold the lock the whole time.

FixRun PRAGMA journal_mode=WAL; to switch to WAL; wrap batch operations in a transaction and release with COMMIT as early as possible.

SymptomA bulk import fails halfway, leaving data in a half-finished state.

CauseMultiple statements were not wrapped in a transaction, so the earlier part is committed and cannot be rolled back.

FixStart with BEGIN TRANSACTION;, use ROLLBACK; on error and COMMIT; on success; for finer granularity use SAVEPOINT sp1; and ROLLBACK TO sp1;.

SymptomWriting a query with syntax migrated from another SQL dialect fails outright.

CauseSQLite supports most of SQL-92 but does not support RIGHT JOIN or FULL JOIN.

FixRewrite as LEFT JOIN, or achieve the same result with a subquery/UNION combination.

SymptomRestoring a backup reports a syntax error and only half the file is restored.

Cause.read executes the export file statement by statement; the file contains an unsupported statement or inconsistent encoding.

FixFirst run PRAGMA integrity_check; to confirm the database itself is intact, then replay the whole file with sqlite3 new.db < dump.sql and watch for error lines.

SymptomDot commands appear to have no effect when run alongside DDL.

Cause.tables, .schema, .import, .output and the like are sqlite3 CLI-only dot commands and cannot be placed in a SQL script or code string for the database engine to execute.

FixRun dot commands in the sqlite3 interactive shell, or switch to the equivalent SQL statement in a script file.

Troubleshooting

1Confirm file integrity.
PRAGMA integrity_check;
An output of ok means the file structure is intact; if error lines appear, salvage data with .dump first, then repair.
2Inspect the table structure and actual CREATE statement.
.schema users
The CREATE statement reveals whether constraints and index definitions match the design.
3Inspect column info and the index list.
PRAGMA table_info(users); PRAGMA index_list(users);
The former confirms column types and nullability, the latter whether the query-condition columns already have indexes.
4Aggregate groups to locate anomalous data.
SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 5;
The output directly shows which groups cluster abnormally; HAVING filters groups here, not rows.
5Export the whole database as SQL for backup.
.output dump.sql && .dump
Redirect with .output then .dump; the resulting dump.sql is a replayable backup text.
6Switch to WAL and confirm it took effect.
PRAGMA journal_mode=WAL; PRAGMA integrity_check;
The first statement should return wal, then run an integrity check to confirm the database still reads and writes fine after the switch.

Command Examples

Dump the database before touching the schema

.output dump.sql
.dump
.open file.db
.read dump.sql

.output redirects everything that follows into a file, so an immediately following .dump writes the whole database to dump.sql. Do this before altering tables or adding indexes: SQLite has no system-level snapshot, so a dropped column can only be recovered from this file. Replaying it with .read into an empty database restores everything.

Output

dump.sql  // the database is exported as replayable SQL text

Wrap a bulk edit in a transaction and roll back on failure

BEGIN TRANSACTION;
UPDATE users SET age = age + 1 WHERE age < 18;
SELECT COUNT(*) FROM users WHERE age = 18;
COMMIT;

An UPDATE or DELETE missing its WHERE clause rewrites the whole table. Inside a transaction, run the SELECT first and read the count before COMMIT — if it is not what you expected, ROLLBACK puts the database back exactly as it was.

Group, then filter out small groups with HAVING

SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 1;

WHERE filters rows before grouping; HAVING filters groups after. To find "statuses with more than one record" you must use HAVING — putting the same condition in WHERE only drops the offending rows, leaving COUNT at 1.

Output

status | COUNT(*)
active | 2

UPSERT keeps repeated script runs idempotent

INSERT INTO users (id, name) VALUES (1, 'Tom') ON CONFLICT(id) DO UPDATE SET name = excluded.name;

Contrast with REPLACE INTO, which deletes then re-inserts — cascading deletes can wipe related rows. ON CONFLICT DO UPDATE updates in place and touches only the columns you name. For migration and import scripts, UPSERT is far safer than REPLACE.

Diagnosing a slow query: columns, indexes, query plan

PRAGMA table_info(users);
PRAGMA index_list(users);
ANALYZE;

table_info reveals column types and defaults — many mysterious "insert fails" cases are type or constraint problems. index_list confirms the index actually exists. ANALYZE refreshes the statistics the optimizer needs. After these three, a slow query is almost always down to a missing index or stale statistics.

Output

cid | name | type | notnull | dflt_value | pk
0 | id | INTEGER | 0 | NULL | 1
1 | name | TEXT | 1 | NULL | 0
2 | age | INT | 0 | 0 | 0

Savepoints: roll back one section of a long transaction

BEGIN TRANSACTION;
SAVEPOINT sp1;
DELETE FROM users WHERE name = 'Bob';
ROLLBACK TO sp1;
RELEASE SAVEPOINT sp1;
COMMIT;

SELECT COUNT(*) after the DELETE still returns 3: ROLLBACK TO sp1 undoes only what happened after the savepoint and leaves everything before it intact. That is the smallest rollback unit for "lose one batch, not the whole job" processing of large data sets.

Common Pitfalls

An UPDATE or DELETE missing its WHERE clause rewrites or drops the whole table. SQLite has no system snapshot, so recovery means replaying a .dump backup — run the matching SELECT first and check the count.
REPLACE INTO deletes before it inserts when the primary key conflicts, so cascading deletes can wipe rows in related tables with no way back. Prefer ON CONFLICT DO UPDATE.
SQLite does not enforce foreign keys by default (PRAGMA foreign_keys reports 0), so REFERENCES alone lets bad rows in. Enable it on the same connection inside migration scripts and re-verify.
Without WAL, concurrent writers block each other and you get "database is locked" — long transactions make it far worse. Use PRAGMA journal_mode=WAL in production, but remember it creates -wal/-shm sidecar files that a .db-only backup will miss.
Running .read dump.sql against a database that already has data silently appends and duplicates rows without erroring. Restore into an empty database.
.import --csv relies on the CSV column order matching the table; reorder a column and the data lands silently in the wrong fields. Spot-check with SELECT right after importing.
When an UPDATE assigns a value whose type disagrees with the column declaration, SQLite dynamic typing can store a number as text. Check PRAGMA table_info rather than eyeballing whether the value looks right.
VACUUM only runs without an active transaction and rewrites the entire file — on a large database it locks the DB for a long time and needs roughly twice the free disk space. Stop writers first.

Tips

SQLite is a zero-configuration database, no server process needed
Use .dump for backup, VACUUM to reclaim space
PRAGMA journal_mode=WAL significantly improves concurrent performance
SQLite supports most SQL-92 standards but not RIGHT/FULL JOIN
The .import command imports CSV files directly into tables

FAQ

Should I use REPLACE INTO or an UPSERT when a row already exists?

REPLACE INTO is a delete-then-insert: on a primary key or unique conflict it drops the old row and inserts a new one, firing delete and insert behaviour and any cascading foreign keys, so it is only safe when you truly mean to overwrite the whole row. An UPSERT is finer grained: INSERT INTO users (id, name) VALUES (1, 'Tom') ON CONFLICT(id) DO UPDATE SET name = excluded.name updates only the columns you list (excluded holds the incoming values) and otherwise leaves the existing row intact. If any existing field must survive, use the UPSERT.

How do I back up and restore a SQLite database from the command line?

For a backup, run .output dump.sql to redirect the next output to a file, then .dump to write the whole database as SQL text; restore it later with .read dump.sql. To bring in a CSV use .import --csv data.csv my_table, and the same .output trick exports a table to CSV. Once a file bloats, VACUUM; reclaims free pages and shrinks it, while ANALYZE; refreshes the statistics the optimizer uses to pick indexes. Run PRAGMA integrity_check; whenever you suspect corruption.

Which columns deserve an index, and what does a bad index cost?

Index the columns you filter, sort and join on frequently: CREATE INDEX idx_users_name ON users(name). When uniqueness is the real goal use CREATE UNIQUE INDEX idx_users_email ON email, which doubles as a data constraint. The cost is on writes — every INSERT and UPDATE has to maintain the index and it consumes disk, so do not index columns you never query. Use PRAGMA index_list(users); to audit what exists and DROP INDEX IF EXISTS idx_users_name; to clear the useless ones. Note that CREATE TABLE backup AS SELECT * FROM users copies data only — indexes are not inherited and must be recreated.

What is the difference between WHERE and HAVING, and how do they combine with GROUP BY?

WHERE filters raw rows before grouping and may only reference ordinary columns, never aggregates. HAVING filters after the grouping and aggregation step and requires GROUP BY. A canonical report is SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 5, which keeps only the statuses with more than five members. When you need both filters, the efficient order is WHERE to shrink the data, then GROUP BY, then HAVING on the aggregated result. For multi-column sorting use ORDER BY age DESC, name ASC.

I keep hitting "database is locked" with concurrent writers — what should I change?

Switch the journal mode first: PRAGMA journal_mode=WAL; allows readers and a writer to proceed together and is the single biggest win for concurrency. Wrap write batches in a transaction — BEGIN TRANSACTION;, run several INSERTs, then COMMIT; — so you take the lock once instead of per statement. When only part of a long transaction is bad, mark a point with SAVEPOINT sp1;, recover with ROLLBACK TO sp1;, and finish the savepoint with RELEASE SAVEPOINT sp1;, or discard everything with ROLLBACK;. Scratch data belongs in CREATE TEMP TABLE, which is dropped when the session ends and stays out of the main lock.

Official References

Each command links to its official documentation below, so you can verify the latest usage and read deeper.

Official Docs

Maintained by LaoHand

Publicly updated on Jul 21, 2026, continuously proofread against official docs.

Contact Us

Wrong command or description? Send us corrections, business inquiries or product feedback by email.

Typical Use Case

Database Connection & Basics 6

Table Operations 6

Data CRUD 7

Advanced Queries 8

Indexes & Constraints 6

Transactions & Locking 7

Import/Export & Tools 6

Utility Functions & PRAGMA 6

Parameter matrix

Common pitfalls

Troubleshooting

Command Examples

Dump the database before touching the schema

Wrap a bulk edit in a transaction and roll back on failure

Group, then filter out small groups with HAVING

UPSERT keeps repeated script runs idempotent

Diagnosing a slow query: columns, indexes, query plan

Savepoints: roll back one section of a long transaction

Common Pitfalls

Tips

FAQ

Related Cheatsheets

Share

Official References

Maintained by LaoHand

Contact Us