Can't create a Sqlite.Net In Memory Database with Shared Cache when using Dapper

Question

How can I create an in-memory Sqlite database with shared cache?

If I use the connectionstring mentioned in MS Docs for Sqlite In-Memory

Data Source=InMemorySample;Mode=Memory;Cache=Shared

Then I find that the in-memory database is not shared (I know this because the database creation script runs but tests report 'no such table' errors).

How do I make Sqlite in-memory shared cache work?

Reproduction

For me, this code throws "no such table" with either of the alleged in-memory connection strings, but works with a file-backed db.

void Main()
{
    // Works fine with file-backed db.
    if (File.Exists("temp.db")) File.Delete("temp.db");
    CreateAndSelectAndWriteLine("Data Source=temp.db");

    //Fails with either of the MS suggested datasource values
    CreateAndSelectAndWriteLine("Data Source=:memory:");
    CreateAndSelectAndWriteLine(
        "Data Source=InMemorySample;Mode=Memory;Cache=Shared");
}

void CreateAndSelectAndWriteLine(string connectionString)
{
    using var conn = new SqliteConnection(connectionString);

    conn.Execute("Create Table HereIAm(id int)");

    Console.WriteLine("------------------------------------------");
    Console.WriteLine(connectionString);
    try
    {
        Console.WriteLine(
         conn.Query<int>("Select * from HereIAm")
             .Count());
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
    }
    Console.WriteLine("-----------------------------------------");

flows that create more than one instance of a DbContext can't succeed. that's a warning flag. A DbContext is a multi-entity Unit-of-Work, so what does flow mean here? Why would there be more than one UoW per scenario/use-case/flow/domain? In any case, we can't guess what's wrong with code that isn't posted. As the docs say The database persists as long as at least one connection to it remains open. They don't say that Cache=Shared will keep connections open indefinitely. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 10 at 12:19
You can keep the in-memory database alive between tests using a test fixture that creates a connection before any other tests start and closes it after they complete. SQLite is an embedded database, which means it's run by your own application and only exists while that runs. the in-memory database is not shared ... tests still report 'no such table that's not what "shared" means with SQLite. Normally, there can be only one connection per database, period. Shared means that multiple connections will be allowed, but even then, there can be only one writer. You need to keep a connection — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 10 at 12:24
You can keep the database alive in a single unit test if you open an SQLite connection (using the same connection string, with the same name) when the test starts, without actually using it. This will keep the database alive until the test ends and that connections gets disposed. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 10 at 12:32
You forgot to open the connection. It seems that you are using the Dapper, which seems to open and close(??) the connection. — Guru Stron
– Guru Stron, Commented Nov 10 at 14:18
That's not why it failed. The question's code does not maintain a single open connection throughout its execution. That connection should be opened in Main, not CreateAndSelectAndWriteLine — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Nov 10 at 15:08

Guru Stron · Accepted Answer · 2025-11-10 14:27:35Z

First of all - Use of shared-cache is discouraged:

Shared-cache mode is an obsolete feature. The use of shared-cache mode is discouraged.

Next point to take into account is that the database which uses Mode=Memory and Cache=Shared persists as long as at least one connection to it remains open (source. So you need to have at least one connection persisting for the whole test run. I.e. open connection on the start of the test run and save it until the end without closing. See the sample. Somewhat minimal example can be the following:

const string connectionString = "Data Source=InMemorySample;Mode=Memory;Cache=Shared";

var connection = new SqliteConnection(connectionString); // store this connection without closing/disposing
connection.Open();

var createCommand = connection.CreateCommand();
createCommand.CommandText = """CREATE TABLE data ( value TEXT)""";
createCommand.ExecuteNonQuery();

// connection.Close(); // <--- TRY closing the connection and see the results
using (var firstConnection = new SqliteConnection(connectionString))
{
    firstConnection.Open();

    var updateCommand = firstConnection.CreateCommand();
    updateCommand.CommandText = """INSERT INTO data VALUES ('Hello, memory!')""";
    updateCommand.ExecuteNonQuery();
}

using (var secondConnection = new SqliteConnection(connectionString))
{
    secondConnection.Open();
    var queryCommand = secondConnection.CreateCommand();
    queryCommand.CommandText = """SELECT * FROM data""";
    var value = (string)queryCommand.ExecuteScalar();
    Console.WriteLine(value);
}

But since you are saving the connection for the whole test run you can just follow the approach from the Testing without your production database system: SQLite in-memory doc:

// Create and open a connection. 
_connection = new SqliteConnection("Filename=:memory:");
_connection.Open();

// Create and store options with connection to use in tests
_contextOptions = new DbContextOptionsBuilder<BloggingContext>()
    .UseSqlite(_connection)
    .Options;

// Create the schema and seed some data
using (var context = new BloggingContext(_contextOptions))
{
    context.Database.EnsureCreated();
}

// somewhere in test
using (var context = new BloggingContext(_contextOptions))
{
    // use context in another test with the same options
}

UPD

Regarding your minimal reproducible example - you need to open the connection for in-memory connection strings:

void CreateAndSelectAndWriteLine(string connectionString)
{
    using var conn = new SqliteConnection(connectionString);
    conn.Open(); // <--- HERE
    // ...
}

Which makes your code working (it seems that you are using Dapper which will open and close connection if it was not opened before, resulting in the in-memory db being scrapped).

And, for posterity: I had failed to notice that Dapper would close connections. So that's more or less the underlying reason for my problem.

Collectives™ on Stack Overflow

Can't create a Sqlite.Net In Memory Database with Shared Cache when using Dapper

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related