SQLite PRAGMA and Administration Cheatsheet

SQLite•Permalink

High-signal PRAGMA commands for foreign keys, cache, synchronous settings, page size, temp storage, health checks, and general SQLite administration.

View

Standard Detailed Compact

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## Integrity and connection behavior

Enable foreign key enforcement

↗#

PRAGMA foreign_keys = ON;

# Turn on FK checks for the current connection.

Check current foreign key setting

↗#

PRAGMA foreign_keys;

# Read whether FK enforcement is enabled.

Find foreign key violations

↗#

PRAGMA foreign_key_check;

# Report rows that violate FK constraints.

Run a quick integrity check

↗#

PRAGMA quick_check;

# Perform a lighter-weight consistency scan.

## Performance and file settings

Inspect page size

↗#

PRAGMA page_size;

# Read the database page size.

Set cache size

↗#

PRAGMA cache_size = -20000;

# Adjust the number of pages or kibibytes used for page cache.

Set synchronous mode

↗#

PRAGMA synchronous = NORMAL;

# Control durability vs speed tradeoffs.

Control temporary storage location

↗#

PRAGMA temp_store = MEMORY;

# Choose file-backed or memory temp storage.

Track application schema version

↗#

PRAGMA user_version;
PRAGMA user_version = 12;

# Read or set the user-defined schema version number.

More in SQLite

SQLite Triggers, Views, and Generated Columns Cheatsheet

CREATE VIEW, CREATE TRIGGER, audit logging, derived values, generated columns, and automation patterns in SQLite.

SQLite Full-Text Search (FTS5) Cheatsheet

FTS5 virtual tables, MATCH queries, prefix search, phrase search, bm25 ranking, snippets, and highlighting patterns for SQLite search features.

SQLite JSON Functions Cheatsheet

json_extract, json_set, json_each, JSON operators, generated columns, and practical JSON query/update patterns in SQLite.

SQLite Import, Export, and Backup Cheatsheet

CSV import/export, SQL dumps, VACUUM INTO, backup copies, and repeatable data movement patterns for SQLite databases.

SQLite Transactions, WAL, and Locking Cheatsheet

BEGIN modes, COMMIT and ROLLBACK, savepoints, WAL mode, busy timeouts, and concurrency patterns for SQLite applications.

SQLite Indexes and Query Planner Cheatsheet

CREATE INDEX patterns, covering and partial indexes, EXPLAIN QUERY PLAN, ANALYZE, and practical tuning tips for SQLite queries.

Recommended next

No recommendations yet.

Browse all sheets →