DBAClientX.SQLite 1.0.15

DbaClientX.SQLite

SQLite provider for DbaClientX (Microsoft.Data.Sqlite). Supports non-query, scalar, queries, streaming, transactions, bulk insert. Also includes SQLite maintenance helpers for online database backup, WAL checkpointing, PRAGMA optimize, and graceful shutdown preparation.

• Target Frameworks: net472 (win-x64), netstandard2.1, net8.0, net10.0
• NuGet: DBAClientX.SQLite

On Windows, long database paths use extended-length filenames and the locking-capable win32-longpath VFS so SQLite can also open journal and WAL files. This applies to path, connection-string, session, backup, diagnostics and table-copy entrypoints. An explicit connection-string or file-URI vfs choice takes precedence. File URIs retain mode, cache and native options such as immutable=1. Escaped URI names and ordinary named-memory targets keep distinct shared-memory databases isolated; extended and ordinary drive/UNC aliases share the same destructive-copy and backup guards.

Use SQLite's case-sensitive URI spelling: lowercase file:, option names such as mode, cache and vfs, and values such as memory or shared. FILE:items.db is an ordinary filename on POSIX. Repeated options follow native order: mode=memory&mode=rwc selects a disk database, while mode=ro&mode=rwc is rejected. Explicit connection-string settings override URI options.

Copy guards retain URI-decoded filename spaces and follow the provider's DataDirectory behavior. POSIX maintenance checks use the same native directory-link and .. resolution as the database connection. Diagnostics FullPath and backup result paths report the resolved POSIX filesystem target; aliases such as macOS /var may appear as /private/var. Diagnostics Database retains the caller's input.

Install

dotnet add package DBAClientX.SQLite

Quick examples

Native shared-library hosts on .NET 8 or later can load SQLite from their own deployment directory before opening a connection:

DBAClientX.SQLite.InitializeNativeLibraryDirectory(absoluteDeploymentDirectory);

Deploy the platform's bundled native library (e_sqlite3.dll, libe_sqlite3.so, or libe_sqlite3.dylib) in that trusted directory. This is useful when the host library is deployed separately from the application's executable. The method requires an absolute directory, allows repeated calls for that directory, and rejects switching directories afterward. The loaded library remains available until the process exits; the method does not change the process DLL search path. Ordinary managed applications use the provider's default native-asset resolution.

Query table:

var sq = new DBAClientX.SQLite();
var result = sq.Query(
    database: "app.db",
    query: "SELECT Id, Name FROM Users ORDER BY Id"
);

Stream query (netstandard2.1+/net8.0):

await foreach (DataRow row in sq.QueryStreamAsync(
    database: "app.db",
    query: "SELECT Id, Name FROM Users ORDER BY Id",
    cancellationToken: ct))
{
    // consume row
}

Typed mapped query:

var rows = await sq.QueryAsListAsync(
    database: "app.db",
    query: "SELECT Id, Name FROM Users ORDER BY Id",
    map: row => new UserRow(
        Id: row.GetInt32(row.GetOrdinal("Id")),
        Name: row.GetString(row.GetOrdinal("Name"))),
    cancellationToken: ct);

For larger result sets, use QueryStreamAsync<T> with the same mapper shape to avoid buffering all rows.

Execute against a full connection string without losing provider options:

var connectionString = new SqliteConnectionStringBuilder
{
    DataSource = "app.db",
    Mode = SqliteOpenMode.ReadWrite,
    Cache = SqliteCacheMode.Shared,
    Pooling = false,
    DefaultTimeout = 15
}.ConnectionString;

await sq.ExecuteNonQueryWithConnectionStringAsync(
    connectionString,
    "UPDATE Users SET Name = @name WHERE Id = @id",
    new Dictionary<string, object?> { ["@name"] = "Alice", ["@id"] = 1 },
    cancellationToken: ct);

SQLiteGeneric.GenericExecutors.ExecuteSqlAsync accepts either a path or a full connection string. Full connection strings preserve mode, cache, pooling, timeout, password, foreign-key, trigger, and VFS settings unless an explicit client profile or timeout overrides them. File paths containing = remain valid paths.

Use a connection profile to apply the same settings to commands, sessions, transactions, bulk operations and diagnostics:

var sq = new DBAClientX.SQLite
{
    CommandTimeout = 15,
    ConnectionOptions = new SQLiteConnectionOptions
    {
        Pooling = true,
        BusyTimeoutMs = 1000,
        EnableWriteAheadLogging = true,
        UseNormalSynchronousMode = true,
        EnableForeignKeys = true
    },
    ConfigureConnection = SQLiteUnicodeText.Register
};

Profiles are copied when assigned or retrieved. Configure a client before starting operations; to change future connections, modify a copy and assign it back. A null profile retains ordinary command defaults. OpenDbConnection retains its managed defaults when neither a profile nor explicit options are provided; explicit options on that method take precedence over the profile. Read-only profiles require a file-backed database and skip journal, synchronous and checkpoint write pragmas. Pooling remains controlled by the connection string when the profile's Pooling is null.

An explicit method busy timeout takes precedence over the profile's BusyTimeoutMs, which takes precedence over the client's legacy BusyTimeoutMs. A connection-string timeout is retained when neither the method nor the profile supplies a busy timeout. CommandTimeout applies in seconds to statements and provider-created transaction commands; zero disables it, and ResetCommandTimeout() restores provider, connection-string or callback-configured defaults, including on retained sessions and client transactions. An explicit client timeout takes precedence over a callback's connection default. SQLite lock waits can still block synchronously for a native busy-timeout interval, so choose a busy timeout appropriate to the application's response budget. Native database backups retain their dedicated backup options and unpooled connections.

For table copies, configure both the source and destination adapter as needed. Every adapter connection receives the profile and callback, including schema preflight, snapshot reads and atomic checkpoint commits:

var adapter = new SQLiteTableCopyAdapter("app.db")
{
    CommandTimeout = 15,
    ConnectionOptions = sq.ConnectionOptions,
    ConfigureConnection = SQLiteUnicodeText.Register
};

Register functions or collations used by expression indexes, views or triggers on every connection that accesses them, including external tools. The callback runs after operational pragmas and before the first caller command; it must be thread-safe if the client or adapter opens connections concurrently.

Sessions reuse one connection. A session and its prepared commands execute sequentially; do not run them concurrently or dispose the session during execution. Canceling an async session or prepared command interrupts its running statement outside an explicit transaction. The cancellation registration ends before the connection can be reused. Inside a transaction, SQLite interruption can roll back earlier writes, so cancellation is cooperative and may wait for the provider statement to finish. This includes transactions opened with SQL BEGIN or SAVEPOINT, and batches that start one.

Commands, including result-returning batches and prepared scalars, execute once by default. Set CommandRetryMode = CommandRetryMode.ReplaySafe on a dedicated client only when every command and callback is safe to repeat after partial success. Individual commands inside transactions are never replayed. See Core retry behavior.

Writing many rows in one transaction with a prepared command (the statement is parsed once; each call rebinds values by position):

var sq = new DBAClientX.SQLite();
using var session = sq.OpenSession("app.db");
session.RunInTransaction(tx =>
{
    using var insert = tx.PrepareInsert("Users", "Id", "Name", "Status");
    foreach (var user in users)
    {
        insert.ExecuteNonQuery(user.Id, user.Name, user.Status); // null -> NULL, enum -> integer
    }
});

// Any statement with named parameters, e.g. an insert that returns the new key:
using var addRun = session.Prepare("INSERT INTO Runs (RunId) VALUES ($runId); SELECT last_insert_rowid();", "$runId");
long runKey = (long)addRun.ExecuteScalar("run-42")!;

Graceful shutdown maintenance:

var sq = new DBAClientX.SQLite();
await sq.PrepareForShutdownAsync(
    database: "app.db",
    options: new SqliteShutdownMaintenanceOptions
    {
        CheckpointMode = SqliteCheckpointMode.Truncate,
        OptimizeAfterCheckpoint = true
    });

Backup before maintenance:

var sq = new DBAClientX.SQLite();
sq.BackupDatabase("app.db", "backups/app.db");

Backup of a database that may be written meanwhile (a service's live store), with progress and cancellation:

var sq = new DBAClientX.SQLite();
SqliteBackupResult result = await sq.BackupDatabaseAsync(
    "app.db",
    "backups/app.db",
    SqliteBackupMethod.Auto,
    new SqliteBackupOptions { OverwriteDestination = true, PagesPerStep = 4096 },
    progress: new Progress<SqliteBackupProgress>(p => Console.WriteLine($"{p.PercentComplete:F0}%")),
    cancellationToken);
// result.Method is Snapshot for a WAL database and Incremental otherwise.

BackupDatabase and BackupDatabaseAsync with SqliteBackupMethod.Auto copy a WAL database as a snapshot (BackupDatabaseSnapshotAsync) and any other database step-wise (BackupDatabaseIncrementalAsync), reading the journal mode through SQLite's PRAGMA journal_mode, respecting cancellation and the busy deadline. The snapshot copy holds the database as it was when the backup started and completes while other connections write. While it runs, the WAL keeps what is written meanwhile, and a FULL, RESTART or TRUNCATE checkpoint waits for it (for the checkpointing connection's busy timeout, with writers waiting behind the checkpoint). The step-wise copy releases the source between steps, but every change by another connection restarts it: it suits a database in rollback-journal mode (where a snapshot is a shared lock that keeps writers from committing until the copy ends), writes that pause long enough for a whole copy, or a database that is not being written. Pass SqliteBackupMethod.Snapshot or Incremental to choose. A rollback-journal database that is written continuously needs WAL mode to be backed up online.

PowerShell: Invoke-DbaXSQLiteMaintenance -Database app.db -Action Backup -Destination backups\app.db uses the same choice (-BackupMethod Auto|Snapshot|Incremental), shows progress and stops the copy on Ctrl+C.

Unicode case-insensitive search and sort (SQLite's own lower(), LIKE and NOCASE fold ASCII only):

var sq = new DBAClientX.SQLite { ConfigureConnection = SQLiteUnicodeText.Register };
var rows = await sq.QueryReadOnlyAsListAsync(
    "app.db",
    "SELECT Name FROM Hosts WHERE dbx_lower(Name) = dbx_lower(@name) ORDER BY Name COLLATE DBX_NOCASE",
    reader => reader.GetString(0),
    new Dictionary<string, object?> { ["@name"] = "ŻÓŁW" });

Indexed substring search for large, rarely written tables (FTS5 trigram index kept current by triggers):

await sq.CreateTrigramIndexAsync("app.db", "HostsSearch", "Hosts", "Id", new[] { "Name", "Owner" });
var query = new Query().Select("Id", "Name").From("Hosts");
if (SQLiteTrigramSearch.CanMatch(text))
    query.WhereInRaw(SqlIdentifier.Quote(SqlDialect.SQLite, "Id"), SQLiteTrigramSearch.MatchingKeys("HostsSearch", text));
else
    query.WhereContains("Name", text, TextFolding.Invariant); // needs SQLiteUnicodeText registered, as above

Read-only streaming, with the running statement interrupted when the token is canceled:

await foreach (var row in sq.QueryReadOnlyStreamAsync("app.db", "SELECT Id, Name FROM Hosts", DbaRecordMapper.Values(), cancellationToken: ct))
{
    // one row at a time
}

Prepared column names and declared types, without binding parameter values or evaluating result rows:

using var sqlite = new DBAClientX.SQLite();
var columns = sqlite.GetQueryColumns("app.db", "SELECT Name, Amount FROM Orders LIMIT @count");
foreach (var column in columns)
    Console.WriteLine($"{column.Name}: {column.DeclaredTypeName}");

GetQueryColumns inspects only the first SQL statement and never steps it. Labels retain their order and can be duplicated. Expressions without a declared SQLite type have an empty DeclaredTypeName; their runtime types require execution. Normal connection configuration still runs, and connections and prepared statements are released before the method returns.

See also

• Core mapping + invoker: DBAClientX.Core