Entity Framework Core and SQLite: 'attempt to write a readonly database'

Question

I am migrating a Xamarin app to MAUI and I had issue with the database.

I have two database plaintext and encrypted, if add password to my app plaintext transform to encrypted and if I remove password encrypted transform to plaintext.

The app starts with plaintext and it works normally, than I transform to encrypted and it also works normally, then I transform back to plaintext and I am adding some data; first entry is getting added normally, but with second and third, I am getting this exception

Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 8: 'attempt to write a readonly database'.>

 at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)  
 at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()  
 at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)  
 at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()  
 at Microsoft.Data.Sqlite.SqliteCommand.ExecuteScalar()  
 at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteScalar(RelationalCommandParameterObject parameterObject)  
 at Microsoft.EntityFrameworkCore.Sqlite.Migrations.Internal.SqliteHistoryRepository.AcquireDatabaseLock()  
 at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)  
 at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)  
 at Database.Context.EncryptedDbContext.Create() in Database/Context/EncryptedDbContext.cs:line 130  
 at Database.Utils.UnitOfWork..ctor() in Database/Utils/UnitOfWork.cs:line 28  
 at Database.Module.DataServices.DataServiceBase`2.<Update>d__6[[Features.Accounts.Entities.Account, Features, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null],[Database.Entities.AccountEntity, Database, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]].MoveNext() in Module/DataServices/DataServiceBase.cs:line 44

This is my setup, connection with plaintext I am getting here:

private SqliteConnection GetConnection(string databasePath)
{
    var connection = new SqliteConnection($"Filename={databasePath};Mode=ReadWriteCreate;");
    connection.Open();

    return connection;
}

Connection with encrypted I am getting here:

private SqliteConnection GetConnection(string databasePath)
{
    var connection = new SqliteConnection($"Filename={databasePath};Mode=ReadWriteCreate;");
    connection.Open();
            
    var command = connection.CreateCommand();
    command.CommandText = "SELECT quote($password);";
    command.Parameters.AddWithValue("$password", Password);

    var quotedPassword = (string) command.ExecuteScalar();
            
    command.CommandText = "PRAGMA key = " + quotedPassword;
    command.Parameters.Clear();

    command.ExecuteNonQuery();
            
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = "PRAGMA cipher_compatibility = 3;";
        cmd.ExecuteNonQuery();
    }

    return connection;
}

Here I am transform database from plaintext to encrypted:

private SqliteConnection GetEncryptedConnection(string plainTextDbPath, string encryptedDbPath)
{   
    var connection = new SqliteConnection($"Filename={plainTextDbPath};Mode=ReadWriteCreate;");
    connection.Open();
            
    var attachCommand = connection.CreateCommand();
    attachCommand.CommandText = $"ATTACH DATABASE '{encryptedDbPath}' AS {Encrypted} KEY '{Password}';";
    attachCommand.ExecuteNonQuery();
            
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = $"PRAGMA {Encrypted}.cipher_compatibility = 3;";
        cmd.ExecuteNonQuery();
    }

    attachCommand.CommandText = $"SELECT sqlcipher_export('{Encrypted}');";
    attachCommand.ExecuteNonQuery();

    attachCommand.CommandText = $"DETACH DATABASE {Encrypted};";
    attachCommand.ExecuteNonQuery();

    return connection;
}

Here I am transforming the database from encrypted back to plaintext:

private SqliteConnection GetDecryptedConnection(string plainTextDbPath, string encryptedDbPath)
{                
    var connection = new SqliteConnection($"Filename={encryptedDbPath};Mode=ReadWriteCreate");
    connection.Open();
            
    var command = connection.CreateCommand();
    command.CommandText = $"PRAGMA key = '{_password}'";

    command.ExecuteNonQuery();
            
    using (var cmd = connection.CreateCommand())
    {
        cmd.CommandText = "PRAGMA cipher_compatibility = 3;";
        cmd.ExecuteNonQuery();
    }
            
    command.CommandText = $"ATTACH DATABASE '{plainTextDbPath}' AS {PlainText} KEY '';";
    command.ExecuteNonQuery();

    command.CommandText = $"SELECT sqlcipher_export('{PlainText}');";
    command.ExecuteScalar();

    command.CommandText = $"DETACH DATABASE {PlainText};";
    command.ExecuteNonQuery();

    return connection;
}

I know that is no issue with accessing the filesystem, and also not really sure if it is migration issue, also this issue same on Android and iOS.

Where is your db file located? Is it part of your app bundle, or stored in a writable user folder? — Jason
– Jason, Commented Apr 17 at 3:29
@Jason it is stored in a writable user folder in SpecialFolder.MyDocuments for ios and in SpecialFolder.UserProfile for andoid — Bohdan
– Bohdan, Commented Apr 17 at 4:40
It's not obvious whether correct cleanup is being done on both SqliteConnection and SqliteCommand. They have Close and Dispose methods that you're not calling in your examples. In the case of Dispose, you can avoid calling it if you were to wrap your SqliteCommand object in a using statement. See learn.microsoft.com/en-us/dotnet/csharp/language-reference/… — Stephen Quan
– Stephen Quan, Commented May 1 at 8:53

Bohdan · Accepted Answer · 2025-04-30 16:58:27Z

0

It was issue with pooling on EF Core, so just disabling it in my connection strings helped me

var connection = new SqliteConnection($"Filename{databasePath};Mode=ReadWriteCreate;Pooling=False");

answered Apr 30 at 16:58

Bohdan

314 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework Core and SQLite: 'attempt to write a readonly database'

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related