SQLite - PRAGMA

Configuring SQLite with PRAGMA

The PRAGMA command is a special SQLite-specific command used to query and modify the internal settings of the database engine. These settings can control everything from performance and durability to data integrity rules. Think of them as runtime configuration options for your database connection.

Syntax

To query the current value of a setting:

PRAGMA setting_name;

To change a setting for the current connection:

PRAGMA setting_name = value;

Note: PRAGMA settings are typically connection-specific. You should re-apply critical settings every time you open a new connection to the database.

Essential PRAGMAs for Modern Applications

While there are dozens of PRAGMAs, a few are critical for building robust and performant applications.

Data Integrity

PRAGMA foreign_keys

Controls the enforcement of foreign key constraints. For historical reasons, this is OFF by default. It is highly recommended to enable it for any application that relies on relational integrity.

-- Recommended: Enable for every connection to ensure data integrity.
PRAGMA foreign_keys = ON;

Performance and Concurrency

PRAGMA journal_mode

This is one of the most important performance settings. It controls how SQLite handles transaction rollbacks and crash recovery.

• DELETE (Default): The rollback journal is deleted at the end of each transaction. Safe but can be slow.
• WAL (Write-Ahead Log): Highly Recommended for most applications. WAL mode significantly improves concurrency (readers don’t block writers, and vice-versa) and is often much faster. Changes are written to a separate -wal file first.
• MEMORY: Keeps the journal in memory. Fast, but transaction is lost if the application crashes.
• OFF: Disables the rollback journal completely. Very fast, but a crash or power loss can corrupt the database. Use with extreme caution.

-- Set the journal mode to Write-Ahead Log for better performance and concurrency.
PRAGMA journal_mode = WAL;

PRAGMA synchronous

Controls how carefully SQLite writes data to the disk to ensure it’s saved permanently. There is a trade-off between safety and speed.

• FULL (2): The most secure. Guarantees data is written to the disk before continuing. Can be slow.
• NORMAL (1): The default when using WAL mode. Syncs at critical moments, offering a great balance of safety and performance.
• OFF (0): SQLite passes data to the OS but doesn’t wait for it to be written to disk. Much faster but data can be lost in a crash.

-- When using WAL mode, NORMAL is a safe and performant default.
PRAGMA synchronous = NORMAL;

PRAGMA cache_size

Sets the size of the in-memory page cache. The default is 2000 pages. A larger cache can improve performance for read-heavy workloads on large databases, at the cost of higher memory usage. A negative value -N means a target cache size of N kibibytes.

-- Set the cache size to 64 MiB (65536 KiB).
PRAGMA cache_size = -65536;

Database Inspection & Debugging

PRAGMA database_list: Lists all attached databases, including the main database and any temporary ones.

PRAGMA foreign_key_check: Checks the database for any foreign key violations. Returns one row for each violation found. Excellent for validating data integrity.

PRAGMA integrity_check: Performs a comprehensive check of the entire database for corruption. Can be slow on large databases.

PRAGMA table_info(table_name): Returns one row for each column in the specified table, detailing its name, type, and constraints.

PRAGMA index_list(table_name): Lists all indexes associated with a table.

Practical Example: The Recommended Setup

For a new application, a good starting point is to execute the following PRAGMAs right after opening a database connection:

-- 1. Set Write-Ahead Logging for better concurrency and performance.
PRAGMA journal_mode = WAL;

-- 2. Set synchronous mode to NORMAL for a good balance of safety and speed.
PRAGMA synchronous = NORMAL;

-- 3. Enforce foreign key constraints for data integrity.
PRAGMA foreign_keys = ON;

-- 4. Set a busy timeout to handle concurrent write attempts gracefully.
PRAGMA busy_timeout = 5000; -- Wait 5000 milliseconds (5 seconds)