SQLite Database Cheat Sheet
Quick reference guide for SQLite: Dot commands, schema definition, pragmas, and CLI flags.
SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured SQL database engine embedded directly into applications.
Essential `sqlite3` CLI Dot Commands
Dot commands control the SQLite command-line tool environment (not SQL statements).
| Dot Command | Purpose / Action |
|---|---|
sqlite3 app.db | Open or create an SQLite database file |
.tables | List all tables in current database |
.schema table_name | Print CREATE TABLE statement for table |
.mode column / .mode json | Set output formatting mode |
.headers on | Display column headers in query output |
.import file.csv table | Import data from CSV file into table |
.dump table_name | Export database or table as SQL script |
.exit / .quit | Exit sqlite3 interactive session |
Data Types & Table Definition
SQLite uses dynamic type affinity: TEXT, NUMERIC, INTEGER, REAL, BLOB.
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL,
score REAL DEFAULT 0.0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Index creation
CREATE INDEX idx_users_email ON users(email);
Useful SQLite Pragmas
Pragmas tweak SQLite runtime options and inspect internal state.
-- Enable Foreign Key constraints enforcement
PRAGMA foreign_keys = ON;
-- Optimize Write-Ahead Logging (WAL) performance mode
PRAGMA journal_mode = WAL;
-- Check database integrity
PRAGMA integrity_check;
-- List all indexes for a table
PRAGMA index_list('users');
Upsert & Conflict Handling (`ON CONFLICT`)
INSERT INTO users (id, username, email)
VALUES (1, 'john_doe', 'john@example.com')
ON CONFLICT(id) DO UPDATE SET
email = excluded.email,
created_at = CURRENT_TIMESTAMP;
Common Pitfalls & Tips
[!WARNING] Foreign Key constraint checking is disabled by default in SQLite for backwards compatibility! You must run
PRAGMA foreign_keys = ON;upon establishing every new database connection.
[!TIP] Use
PRAGMA journal_mode = WAL;(Write-Ahead Logging) in multi-threaded or web server apps to allow concurrent readers while a write operation is active.