SqliteHelper

The SqliteHelper static class simplifies connection creation with automatic PRAGMA configuration and provides a factory for the SqliteMigrator.

Creating Connections

Basic Connection with Default PRAGMAs

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

// Apply default PRAGMA settings (WAL mode, NORMAL sync, 64MB cache, foreign keys enabled)
await SqlitePragmaSettings.Default.ApplyToConnectionAsync(connection);

^{snippet source | anchor}

This opens the connection and applies SqlitePragmaSettings.Default (WAL mode, NORMAL sync, 64MB cache, foreign keys enabled).

Custom PRAGMA Configuration

Pass an Action<SqlitePragmaSettings> to configure specific settings:

var settings = new SqlitePragmaSettings
{
    JournalMode = JournalMode.WAL,
    ForeignKeys = true,
    CacheSize = -64000 // 64MB
};
var connection = new SqliteConnection("Data Source=myapp.db");
await connection.OpenAsync();
await settings.ApplyToConnectionAsync(connection);

^{snippet source | anchor}

Using Presets

// High performance (reduced safety)
var highPerfConn = new SqliteConnection("Data Source=myapp.db");
await highPerfConn.OpenAsync();
await SqlitePragmaSettings.HighPerformance.ApplyToConnectionAsync(highPerfConn);

// High safety (maximum durability)
var highSafetyConn = new SqliteConnection("Data Source=myapp.db");
await highSafetyConn.OpenAsync();
await SqlitePragmaSettings.HighSafety.ApplyToConnectionAsync(highSafetyConn);

^{snippet source | anchor}

See PRAGMA Settings for details on each preset.

Creating a Migrator

Use CreateMigrator() to get a SqliteMigrator instance for DDL generation:

var migrator = new SqliteMigrator();

var table = new Table("users");
table.AddColumn<int>("id").AsPrimaryKey().AutoIncrement();
table.AddColumn<string>("name").NotNull();

var writer = new StringWriter();
table.WriteCreateStatement(migrator, writer);
Console.WriteLine(writer.ToString());

^{snippet source | anchor}

Connection String Examples

// In-memory database (lost when connection closes)
var inMemory = "Data Source=:memory:";

// File-based database
var fileBased = "Data Source=myapp.db";

// Shared cache for multiple connections to the same in-memory database
var sharedCache = "Data Source=myapp;Mode=Memory;Cache=Shared";

// Read-only access
var readOnly = "Data Source=myapp.db;Mode=ReadOnly";

^{snippet source | anchor}

Method Reference

Method	Description
CreateConnectionAsync(string, Action<SqlitePragmaSettings>?, CancellationToken?)	Opens a SqliteConnection with PRAGMA settings applied
CreateMigrator()	Returns a new SqliteMigrator for DDL generation

Recommended Usage

Configure PRAGMAs through SqliteHelper rather than in connection strings or with raw SQL. This provides type safety, validation, and consistent settings across your application:

// Preferred: type-safe PRAGMA configuration
var settings = new SqlitePragmaSettings
{
    JournalMode = JournalMode.WAL,
    Synchronous = SynchronousMode.NORMAL,
    ForeignKeys = true
};
var connection = new SqliteConnection("Data Source=myapp.db");
await connection.OpenAsync();
await settings.ApplyToConnectionAsync(connection);

// Avoid: raw PRAGMA statements
// var cmd = connection.CreateCommand();
// cmd.CommandText = "PRAGMA journal_mode = WAL; PRAGMA foreign_keys = ON;";

^{snippet source | anchor}