SQLite Cheatsheet - SQLite Database Command Reference

Essential SQLite embedded database commands for the complete workflow from database creation to data import/export. Copy directly for your projects.

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

Back to Databases

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