c# - Sqlite encryption in mutithreadind context

Question

Here is the background: I have an .net 5.0 Api, on a local network, consumed by a maximum of 15 people simultaneously (via a mobile application or a WPF application).

The DB behind this API is a SQLite database (and before I'm asked to switch to another DBMS, assume that this is not possible).

For the moment, everything works perfectly with quite respectable performances (even when stressing the system with a hundred clients connected at the same time).

Now my goal is to be able to encrypt the database by changing the bundle (typically SQLitePCLRaw.bundle_e_sqlcipher). So I made the necessary modifications, and the big problem is that opening a connection is very very slow. Client-side performance is not acceptable.

After reading on the net, it is advised to leave the connection open so as not to waste any more time with the encryption/decryption of the base (roughly).

So I created a service in my api that creates a SQLite connection and leaves it open at all times :

public class SqliteConnectionService : ISqliteConnectionService
{
    public SqliteConnection Connection { get; set; }

    public async Task Initialize()
    {
        if (Connection == null)
        {
            var dbPath = $"{Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData)}\Toto\";

            if (!Directory.Exists(dbPath))
                Directory.CreateDirectory(dbPath);

            var connectionBuilder = new SqliteConnectionStringBuilder
            {
                DataSource = Path.Combine(dbPath, "Data"),
                Password = "123456"
            };

            Connection = new SqliteConnection(connectionBuilder.ConnectionString);
            await Connection.OpenAsync();

            //using (var command = Connection.CreateCommand()) 
            //{
            //    command.CommandText = "PRAGMA busy_timeout = 600;";
            //    await command.ExecuteNonQueryAsync();
            //}

            Batteries_V2.Init();
        }
    }
}

DbContext side :

protected override async void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    await _sqliteConnectionService.Initialize();

    optionsBuilder.UseSqlite(_sqliteConnectionService.Connection);
}

And there, no more performance concerns, on the other hand I very often get

SQLite Error 5 error: 'database is locked For more information on this error code see https://www.sqlite.org/rescode.html'.

This seems logical, since I only have one connection open in a context where calls can be made concurrently.

In short, whether I leave the connection open all the time, or open and close a connection with each request results in a user experience that is not at all acceptable (a very slow application in one case, and repeated errors in the other).

So I'd like to know your opinion; is it possible to use an encrypted SQLite database in such a context, and if so, how?

question from:https://stackoverflow.com/questions/65935315/sqlite-encryption-in-mutithreadind-context

Answer 1

Waitting for answers