SQLite Cheat Sheet

A quick reference for SQLite's CLI, SQL syntax, type affinity, pragmas, and backup commands for embedded, file-based databases.

1 PageBeginnerMar 2, 2026

CLI Basics

Working with the sqlite3 shell.

bash

sqlite3 mydb.db.tables               # list tables.schema users         # show CREATE TABLE for users.headers on           # show column headers.mode column          # aligned column output.quit

SQL Basics

Creating and querying tables.

sql

CREATE TABLE users (  id INTEGER PRIMARY KEY AUTOINCREMENT,  email TEXT NOT NULL UNIQUE,  created_at TEXT DEFAULT CURRENT_TIMESTAMP);INSERT INTO users (email) VALUES ('a@b.com');SELECT * FROM users WHERE email = 'a@b.com';PRAGMA table_info(users);

Type Affinity & Pragmas

Column types and common pragmas.

INTEGER- whole number; INTEGER PRIMARY KEY aliases the internal rowid
TEXT- UTF-8, UTF-16BE, or UTF-16LE string
REAL- 8-byte floating point number
BLOB- raw binary data, stored exactly as input
PRAGMA foreign_keys = ON- enables FK constraint enforcement (off by default)
PRAGMA journal_mode = WAL- enables write-ahead logging for better concurrency

Backup & Attach

Copying and combining databases.

sql

.backup main backup.dbATTACH DATABASE 'other.db' AS other;SELECT * FROM other.users;VACUUM;   -- rebuilds the file, reclaiming free space

Pro Tip

SQLite uses dynamic type affinity rather than strict typing — any column can normally store any value type unless the table is declared STRICT (available in SQLite 3.37+).

Was this cheat sheet helpful?

MySQL Cheat Sheet

Redis Cheat Sheet