What problem are you trying to solve?
PRAGMA synchronous is scoped to a native SQLite connection and is not persisted in the database. Applications that require a specific mode, such as NORMAL for a WAL database, must execute the PRAGMA for every physical connection.
Microsoft.Data.Sqlite pooling makes this awkward. After SqliteConnection.Open(), an application cannot tell whether Microsoft.Data.Sqlite created a new native connection or leased an already-configured one from the pool. The reliable application-level workaround is therefore to execute the PRAGMA after every logical open:
var connection = new SqliteConnection(connectionString);
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = "PRAGMA synchronous = NORMAL;";
command.ExecuteNonQuery();
This adds boilerplate and an extra command to every open. It is also easy for one connection-opening path to omit the configuration.
The connection string already supports analogous connection-scoped configuration such as Foreign Keys=True. Microsoft.Data.Sqlite reapplies that setting when a connection is opened, including when a physical connection is obtained from the pool.
This request is narrower than #5024, which was redirected to general EF lifecycle interception, and #13826, which addressed preserving configuration when reopening the same SqliteConnection. Neither provides a connection-string option that consistently configures all physical connections in a Microsoft.Data.Sqlite pool.
Observed with Microsoft.Data.Sqlite.Core 10.0.10.
Describe the solution you'd like
Add a Synchronous connection-string keyword and corresponding strongly typed SqliteConnectionStringBuilder.Synchronous property, for example:
var connectionString = new SqliteConnectionStringBuilder
{
DataSource = databasePath,
ForeignKeys = true,
Synchronous = SqliteSynchronousMode.Normal
}.ToString();The supported values could map to SQLite's modes:
Off / 0Normal / 1Full / 2Extra / 3
When specified, Microsoft.Data.Sqlite would apply PRAGMA synchronous after obtaining the native connection during Open(), similar to the existing Foreign Keys behavior. When omitted, the provider would preserve SQLite's default behavior.
Including the value in the connection string would also naturally partition pools whose connections require different synchronous modes.
What problem are you trying to solve?
PRAGMA synchronousis scoped to a native SQLite connection and is not persisted in the database. Applications that require a specific mode, such asNORMALfor a WAL database, must execute the PRAGMA for every physical connection.Microsoft.Data.Sqlite pooling makes this awkward. After
SqliteConnection.Open(), an application cannot tell whether Microsoft.Data.Sqlite created a new native connection or leased an already-configured one from the pool. The reliable application-level workaround is therefore to execute the PRAGMA after every logical open:This adds boilerplate and an extra command to every open. It is also easy for one connection-opening path to omit the configuration.
The connection string already supports analogous connection-scoped configuration such as
Foreign Keys=True. Microsoft.Data.Sqlite reapplies that setting when a connection is opened, including when a physical connection is obtained from the pool.This request is narrower than #5024, which was redirected to general EF lifecycle interception, and #13826, which addressed preserving configuration when reopening the same
SqliteConnection. Neither provides a connection-string option that consistently configures all physical connections in a Microsoft.Data.Sqlite pool.Observed with
Microsoft.Data.Sqlite.Core10.0.10.Describe the solution you'd like
Add a
Synchronousconnection-string keyword and corresponding strongly typedSqliteConnectionStringBuilder.Synchronousproperty, for example:The supported values could map to SQLite's modes:
Off/0Normal/1Full/2Extra/3When specified, Microsoft.Data.Sqlite would apply
PRAGMA synchronousafter obtaining the native connection duringOpen(), similar to the existingForeign Keysbehavior. When omitted, the provider would preserve SQLite's default behavior.Including the value in the connection string would also naturally partition pools whose connections require different synchronous modes.