SQLite Cheatsheet - SQLite Database Command Reference

For developers who need a zero-config, single-file, fast-enough embedded database. SQLite needs no server or credentials; a single .db file moves anywhere. The trade-off is that only one writer is allowed at a time across the whole file, so concurrent writes need WAL mode and a busy timeout. By the end you can create databases and tables, constrain data with indexes and constraints, import and export CSV, and enable WAL to improve concurrent read/write behavior.

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

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

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

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.