Home → Databases & APIs → SQLite Database

SQLite Database Cheat Sheet

Last Updated: November 21, 2025

Data Types

Storage Class Description Example Types
NULL Null value NULL
INTEGER Signed integer INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, INT2, INT8
REAL Floating point REAL, DOUBLE, FLOAT
TEXT Text string (UTF-8/16) TEXT, VARCHAR, CHAR, CLOB
BLOB Binary data BLOB (images, files, etc.)

Table Operations

Command Description Example
CREATE TABLE Create new table CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
DROP TABLE Delete table DROP TABLE users;
ALTER TABLE ADD Add column ALTER TABLE users ADD COLUMN age INTEGER;
ALTER TABLE RENAME Rename table ALTER TABLE users RENAME TO customers;
ALTER TABLE RENAME COLUMN Rename column ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE DROP Drop column (v3.35+) ALTER TABLE users DROP COLUMN age;
CREATE TEMP TABLE Temporary table CREATE TEMP TABLE temp_data (id INT, val TEXT);

Constraints

Constraint Purpose Example
PRIMARY KEY Unique row identifier id INTEGER PRIMARY KEY
AUTOINCREMENT Auto-increment ID id INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL Column cannot be null name TEXT NOT NULL
UNIQUE All values must be unique email TEXT UNIQUE
CHECK Value must satisfy condition age INTEGER CHECK(age >= 18)
DEFAULT Default value status TEXT DEFAULT 'active'
FOREIGN KEY Reference another table FOREIGN KEY (user_id) REFERENCES users(id)
COLLATE Text comparison rules name TEXT COLLATE NOCASE

INSERT Operations

Command Description Example
INSERT INTO Insert single row INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
INSERT multiple Insert multiple rows INSERT INTO users VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob');
INSERT OR REPLACE Insert or update if exists INSERT OR REPLACE INTO users (id, name) VALUES (1, 'John');
INSERT OR IGNORE Insert, skip if exists INSERT OR IGNORE INTO users (email) VALUES ('john@example.com');
INSERT default values Insert with defaults INSERT INTO users DEFAULT VALUES;
INSERT FROM SELECT Insert from query results INSERT INTO archive SELECT * FROM users WHERE active = 0;

SELECT Queries

Command Description Example
SELECT Basic select SELECT * FROM users;
SELECT columns Select specific columns SELECT name, email FROM users;
WHERE Filter rows SELECT * FROM users WHERE age > 18;
AND / OR Combine conditions SELECT * FROM users WHERE age > 18 AND status = 'active';
LIKE Pattern matching SELECT * FROM users WHERE name LIKE 'John%';
IN Match any in list SELECT * FROM users WHERE status IN ('active', 'pending');
BETWEEN Range matching SELECT * FROM users WHERE age BETWEEN 18 AND 65;
IS NULL Check for null SELECT * FROM users WHERE email IS NULL;
ORDER BY Sort results SELECT * FROM users ORDER BY name ASC;
LIMIT Limit results SELECT * FROM users LIMIT 10;
OFFSET Skip rows SELECT * FROM users LIMIT 10 OFFSET 20;
DISTINCT Unique values only SELECT DISTINCT status FROM users;
AS Column alias SELECT name AS full_name FROM users;

UPDATE & DELETE

Command Description Example
UPDATE Update rows UPDATE users SET age = 30 WHERE id = 1;
UPDATE multiple Update multiple columns UPDATE users SET age = 30, status = 'active' WHERE id = 1;
DELETE Delete rows DELETE FROM users WHERE id = 1;
DELETE all Delete all rows DELETE FROM users;
TRUNCATE (DELETE) Fast delete all (SQLite uses DELETE) DELETE FROM users; VACUUM;

JOIN Operations

JOIN Type Description Example
INNER JOIN Match in both tables SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id;
LEFT JOIN All from left + matches SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;
CROSS JOIN Cartesian product SELECT * FROM users CROSS JOIN categories;
SELF JOIN Join table to itself SELECT a.name, b.name FROM users a, users b WHERE a.manager_id = b.id;
Multiple JOINs Join multiple tables SELECT * FROM users u JOIN orders o ON u.id = o.user_id JOIN products p ON o.product_id = p.id;

Aggregate Functions

Function Description Example
COUNT() Count rows SELECT COUNT(*) FROM users;
SUM() Sum values SELECT SUM(price) FROM orders;
AVG() Average value SELECT AVG(age) FROM users;
MIN() Minimum value SELECT MIN(price) FROM products;
MAX() Maximum value SELECT MAX(price) FROM products;
GROUP_CONCAT() Concatenate values SELECT GROUP_CONCAT(name, ', ') FROM users;
GROUP BY Group results SELECT status, COUNT(*) FROM users GROUP BY status;
HAVING Filter groups SELECT status, COUNT(*) FROM users GROUP BY status HAVING COUNT(*) > 5;

String Functions

Function Description Example
LENGTH() String length SELECT LENGTH(name) FROM users;
UPPER() Convert to uppercase SELECT UPPER(name) FROM users;
LOWER() Convert to lowercase SELECT LOWER(email) FROM users;
TRIM() Remove whitespace SELECT TRIM(name) FROM users;
SUBSTR() Extract substring SELECT SUBSTR(name, 1, 5) FROM users;
REPLACE() Replace text SELECT REPLACE(email, '@', '[at]') FROM users;
CONCAT (||) Concatenate strings SELECT first_name || ' ' || last_name FROM users;
INSTR() Find substring position SELECT INSTR(email, '@') FROM users;

Date & Time Functions

Function Description Example
DATE() Extract date SELECT DATE('now');
TIME() Extract time SELECT TIME('now');
DATETIME() Date and time SELECT DATETIME('now');
JULIANDAY() Julian day number SELECT JULIANDAY('now');
STRFTIME() Format date/time SELECT STRFTIME('%Y-%m-%d', 'now');
DATE modifiers Add/subtract time SELECT DATE('now', '+7 days');
Start of... Round to period SELECT DATE('now', 'start of month');

Index Operations

Command Description Example
CREATE INDEX Create index CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX Unique index CREATE UNIQUE INDEX idx_users_email ON users(email);
Composite index Index multiple columns CREATE INDEX idx_name_age ON users(name, age);
DROP INDEX Delete index DROP INDEX idx_users_email;
REINDEX Rebuild index REINDEX idx_users_email;
Partial index Index with WHERE clause CREATE INDEX idx_active ON users(email) WHERE status='active';

Transactions

Command Description Example
BEGIN Start transaction BEGIN TRANSACTION;
COMMIT Save changes COMMIT;
ROLLBACK Undo changes ROLLBACK;
SAVEPOINT Create savepoint SAVEPOINT sp1;
RELEASE Remove savepoint RELEASE sp1;
ROLLBACK TO Rollback to savepoint ROLLBACK TO sp1;

CLI Commands

Command Description Example
sqlite3 db.db Open database sqlite3 myapp.db
.tables List all tables .tables
.schema Show table schemas .schema users
.indices Show indexes .indices users
.databases List databases .databases
.headers on Show column headers .headers on
.mode Set output mode .mode column or .mode csv
.output Redirect output to file .output results.txt
.import Import CSV data .import data.csv users
.dump Dump database as SQL .dump > backup.sql
.backup Backup database .backup backup.db
.restore Restore database .restore backup.db
.read Execute SQL from file .read script.sql
.exit Exit SQLite .exit or .quit
.help Show help .help

Optimization Tips

Technique Description Example
Create indexes Speed up WHERE, JOIN, ORDER BY CREATE INDEX idx_name ON users(name);
Use EXPLAIN Analyze query execution EXPLAIN QUERY PLAN SELECT * FROM users WHERE name = 'John';
ANALYZE Update statistics ANALYZE;
VACUUM Rebuild database, reclaim space VACUUM;
Use transactions Batch multiple INSERTs BEGIN; INSERT...; INSERT...; COMMIT;
Prepared statements Reuse compiled queries Use parameterized queries in your language
Limit SELECT * Only select needed columns SELECT id, name FROM users not SELECT *
Use PRAGMA Configure performance settings PRAGMA cache_size = 10000;
Partial indexes Index subset of data CREATE INDEX idx ON users(email) WHERE active=1;

Useful PRAGMA Commands

Command Description Example
PRAGMA table_info Show table structure PRAGMA table_info(users);
PRAGMA foreign_keys Enable foreign keys PRAGMA foreign_keys = ON;
PRAGMA journal_mode Set journal mode PRAGMA journal_mode = WAL;
PRAGMA synchronous Set sync mode PRAGMA synchronous = NORMAL;
PRAGMA cache_size Set cache size PRAGMA cache_size = 10000;
PRAGMA database_list List attached databases PRAGMA database_list;
PRAGMA user_version Get/set DB version PRAGMA user_version = 1;
PRAGMA integrity_check Check DB integrity PRAGMA integrity_check;

Common Use Cases

Use Case Why SQLite
Mobile Apps (iOS/Android) Built-in, lightweight, no server needed. Default DB for iOS Core Data
Desktop Applications Zero configuration, single file database, cross-platform
Browser Storage WebSQL (deprecated) and IndexedDB alternatives use SQLite concepts
Embedded Systems Small footprint, no dependencies, runs on IoT devices
Testing Fast in-memory databases for unit tests
Prototyping Quick setup, no server configuration, easy migration later
Data Analysis Query CSV/JSON files with SQL, lightweight alternative to database servers
Application File Format Better than custom formats - readable, queryable, documented
Local Cache Cache API responses, offline-first apps
Configuration Storage Store app settings with version control and schema
💡 Pro Tips:
  • SQLite database is a single file - easy to backup, copy, and version control
  • Use WAL mode (Write-Ahead Logging) for better concurrent read/write: PRAGMA journal_mode=WAL
  • Wrap bulk INSERTs in transactions - can be 10-100x faster
  • INTEGER PRIMARY KEY is an alias for rowid - most efficient index
  • Use EXPLAIN QUERY PLAN to see if your queries are using indexes
  • Unlike other DBs, SQLite is dynamically typed - column types are suggestions
  • Maximum database size: 281 terabytes (theoretical), practical limit depends on filesystem
  • VACUUM command rebuilds DB file - reclaims space after deletes
  • Use :memory: as database name for in-memory database (perfect for testing)
  • ATTACH DATABASE to query multiple SQLite files in one session
  • SQLite reads are concurrent, but writes are serialized (one at a time)
  • Perfect for read-heavy workloads - not ideal for high-volume concurrent writes

Related Cheat Sheets

Browse all Databases & APIs →

← Back to Databases & APIs | Browse all categories | View all cheat sheets