Pragma Settings for SQLite

[shlatchz]

The following code

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var connectionStringBuilder = new SqliteConnectionStringBuilder() { DataSource = Constants.DataDBPath  };
    var connectionString = connectionStringBuilder.ToString();
    var connection = new SQLiteConnection() { ConnectionString = connectionString };
    optionsBuilder.UseSqlite(connection);
}

cannot receive pragma settings such as journalmode or syncmode.
How can I apply the pragma settings to the db?

I tried:

using (var context = new DataDBContext())
{
    var connection = context.Database.GetDbConnection();
    connection.Open();
    var command = connection.CreateCommand();
    command.CommandText = "PRAGMA synchronous=OFF; PRAGMA journal_mode=OFF; PRAGMA count_changes=OFF; PRAGMA temp_store=OFF; PRAGMA page_size=65536; PRAGMA cache_size=-16777216;";
    command.ExecuteNonQuery();
}

but it didn't help.

[ErikEJ]

You need to do it in the same context as where you are doing the EF data access

[shlatchz]

Can you please elaborate?
Are you saying that I have to do it each time I modify the DB?

[rowanmiller]

@natemcmaster can you confirm?

[rowanmiller]

@natemcmaster ping 😄

[natemcmaster]

@shlatchz if you want to issue custom pragma statements, you need to do this every time you open a connection. Changing values via pragma statements do not persist across calls to sqlite_open_v2. At the moment, we don't have API in Microsoft.Data.Sqlite.SqlConnection to support executing something arbitrary (such as issuing pragma statements) everytime a connection opens.

Moving up a layer in the stack. EntityFramework Core has a class that wraps SqliteConnection. This wrapper handles opening/closing SqliteConnection and has its own logic for issuing pragma statements when connections open. See Microsoft.EntityFrameworkCore.Storage.Internal.SqliteRelationalConnection. EF lets you pass an connection object instead of a connection string if you want to manage the lifecycle of opening/closing a connection yourself. UseSqlite(DbConnection connection).

[natemcmaster]

Remarking for triage: what is the work here? Add API to Microsoft.Data.Sqlite or EF?

[rowanmiller]

@natemcmaster this is mostly to investigate and work out what we should do. It seems like we may want a way to easily configure pragma settings when you setup the context to target SQLite. Or it may be a more general "connection preamble" feature. It's in the 1.0.1 milestone to look at post-RTM though

[natemcmaster]

I have this given this some consideration and prototyped some designs today. My current thinking leads me to believe we should consider this a duplicate of the lifecycle hooks feature (#626). Most designs I came up with end up working essentially like a general connection preamble feature. This general feature has bigger implications than just SQLite pragmas, so we should probably gather for some design work on this first.

[rowanmiller]

Closing this issue as the idea of connection preamble is being tracked as part of the bigger Lifecycle Hooks feature (#626).

[rowanmiller]

BTW just to make it explicit - we are not closing this because we aren't planning to implement it. We very much agree this is super useful, it's just already tracked as part of #626.