PRAGMA Settings

The SqlitePragmaSettings class provides comprehensive PRAGMA configuration for controlling SQLite database behavior including journaling, caching, and synchronization.

Built-in Presets

Default

Balanced settings for general-purpose applications:

• WAL journal mode for concurrent reads
• NORMAL synchronous mode
• 64MB cache, foreign keys enabled, incremental auto-vacuum

HighPerformance

Maximum speed with reduced safety guarantees:

var settings = SqlitePragmaSettings.HighPerformance;
var connection = new SqliteConnection("Data Source=myapp.db");
await connection.OpenAsync();
await settings.ApplyToConnectionAsync(connection);

^{snippet source | anchor}

• WAL journal mode, OFF synchronous mode
• 128MB cache, no auto-vacuum overhead

DANGER

HighPerformance uses synchronous = OFF, which risks database corruption on power loss or OS crash. Use only when data can be regenerated.

HighSafety

Maximum durability and data integrity:

var settings = SqlitePragmaSettings.HighSafety;
var connection = new SqliteConnection("Data Source=myapp.db");
await connection.OpenAsync();
await settings.ApplyToConnectionAsync(connection);

^{snippet source | anchor}

• WAL journal mode, FULL synchronous mode
• Secure delete enabled, full auto-vacuum, 10-second busy timeout

Settings Reference

Setting	Default	Description
JournalMode	WAL	Transaction logging. WAL allows concurrent readers with a single writer
Synchronous	NORMAL	Disk sync frequency. FULL = safest, NORMAL = balanced, OFF = fastest
CacheSize	-64000	Cache in KiB (negative) or pages (positive). -64000 = 64MB
TempStore	MEMORY	Where temporary tables are stored
MmapSize	268435456	Memory-mapped I/O size in bytes (256MB)
PageSize	4096	Database page size. Must be set before database creation
ForeignKeys	true	Enable foreign key constraint enforcement
AutoVacuum	INCREMENTAL	Automatic space reclamation strategy
BusyTimeout	5000	Milliseconds to wait when database is locked
SecureDelete	false	Overwrite deleted data with zeros
WalAutoCheckpoint	null	Pages before auto-checkpoint (WAL mode only)

Custom Configuration

var settings = new SqlitePragmaSettings
{
    JournalMode = JournalMode.WAL,
    Synchronous = SynchronousMode.NORMAL,
    CacheSize = -32000, // 32MB
    ForeignKeys = true,
    BusyTimeout = 5000,
    WalAutoCheckpoint = 1000
};
var connection = new SqliteConnection("Data Source=myapp.db");
await connection.OpenAsync();
await settings.ApplyToConnectionAsync(connection);

^{snippet source | anchor}

Applying to an Existing Connection

var settings = SqlitePragmaSettings.Default;
var connection = new SqliteConnection("Data Source=myapp.db");
await connection.OpenAsync();
await settings.ApplyToConnectionAsync(connection);

^{snippet source | anchor}

Generating a SQL Script

var settings = SqlitePragmaSettings.Default;
Console.WriteLine(settings.ToSqlScript());

// -- SQLite PRAGMA Settings
// PRAGMA journal_mode = WAL;
// PRAGMA synchronous = NORMAL;
// PRAGMA cache_size = -64000;
// ...

^{snippet source | anchor}

Important Notes

• PRAGMA settings are per-connection and must be applied every time a connection opens
• Some settings (page_size, auto_vacuum) can only be set before the database is created
• WAL mode does not work with in-memory databases (:memory:)
• Foreign key constraints are disabled by default in SQLite and must be explicitly enabled
• Use SqliteHelper for consistent PRAGMA application across your application