Background

As part of making a C# project Linux compatible I had to change System.Data.SQLite dependency to Microsoft.Data.Sqlite to support encrypted databases in Linux. Since SQLite doesn't support encrypting database files by default Microsoft.Data.Sqlite is also not supporting it, instead it supports using modified versions of SQLite like SQLCipher, SEE or SQLiteCrypt to support encryption.(For more information read here.)

This comes with some limitations when working with encrypted databases. One of the limitations I countered was that I could not load an encrypted Sqlite database into memory. With non-encrypted databases it was as simple as opening a connection to the database and then creating a shareable in-memory database, and at the end backing up the database into the in-memory database.

var connectionString = new SqliteConnectionStringBuilder("Data Source=sampleDatabase.db")
{
    Mode = SqliteOpenMode.ReadWriteCreate
}.ToString();
var connection = new SqliteConnection(connectionString);
connection.Open();

var inMemConnectionString = new SqliteConnectionStringBuilder("Data Source=InMemorySample;Mode=Memory;Cache=Shared")
{
    Mode = SqliteOpenMode.ReadWriteCreate
}.ToString();
var inMemConnection = new SqliteConnection( inMemConnectionString );
inMemConnection.Open();

connection.BackupDatabase(inMemConnection, connection.Database, connection.Database);

Problem

Doing this with an encrypted database will throw SqliteException with following message:
backup is not supported with encrypted databases.

Solution

The solution is to attach a shareable in-memory database with password as empty string to the encrypted database and then use sqlcipher_export to decrypt the data into the in-memory database and at the end to detach it. Afterward you can use the shareable in-memory database. Note that encrypted in-memory databases are not supported.

var connectionString = new SqliteConnectionStringBuilder("Data Source=encrypted.db")
{
    Mode = SqliteOpenMode.ReadWriteCreate,
    Password = "somePassword"
}.ToString();
var connection = new SqliteConnection(connectionString);
connection.Open();

var query = "ATTACH DATABASE 'file:InMemorySample?mode=memory&cache=shared' AS inMemDb KEY ''; SELECT sqlcipher_export('inMemDb'); DETACH DATABASE inMemDb;";
using var cmd = new SqliteCommand(query, connection);
var res = cmd.ExecuteNonQuery();

connection.Close();

var inMemConnectionString = new SqliteConnectionStringBuilder("Data Source=InMemorySample;Mode=Memory;Cache=Shared")
{
    Mode = SqliteOpenMode.ReadWriteCreate
}.ToString();
var inMemConnection = new SqliteConnection( inMemConnectionString );
inMemConnection.Open();