Loading and persisting an In-Memory SQLite database with EF Core

Question

I'm doing some research for a new project and I'm trying to determine if it's possible (and advisable) to load a SQLite database into memory, perform CRUD operations against it, and persist it back out.

I've seen many examples of utilizing SQLite databases (in memory) for unit testing, and in all of those examples, the data is just trashed in the end - this is NOT what I wish to do.

I'm going to likely use Microsoft SQL Server to manage the overall site data and act as a storage engine (users and credentials and their associated SQLite databases, etc).

When a user selects a SQLite database in the UI, I would like to load it into memory on the server, allow the user to operate against it, and then persist it back to the storage engine (SQL Server) without needing to save a .db file to the filesystem.

I'm comfortable with aspects of EF Core + SQL Server and SQLite (against the filesystem). But what's new to me is the idea of operating against a SQLite database in memory.

So my questions are:

• Is this possible with EF Core?
• If so, how would I configure my SQLite DbContext class to accomplish this?
• Are there any major downsides to this?

Thank you

Answer 1

Is this possible with EF Core?

Yes. Why not? SQLite essentially treats in-memory database the same as any other. There are particular considerations, but you essentially insert and query data in the same way.

If so, how would I configure my SQLite DbContext class to accomplish this?

Once again, refer to documentation. You would need to provide a specific connection string. (Not to discourage SO questions, but if you're going to research and test this you really should research information available on sqlite.org. It is has great, thorough documentation--at least compared to many open-source projects... sometimes a bit scattered, but still accessible.)

Perhaps more complicated than specifying an appropriate connection string is actually loading an existing database file into memory. The default, basic behavior is to only create an empty database in memory. There are multiple ways to load the data, and this question has some useful answers.

Are there any major downsides to this?

You have apparently already identified some of the downsides, but probably no more than any project which needs to merge/synchronize databases. There is no short answer to that question and it is much too broad for Stack Overflow.

You specifically mention syncing data to an SQL Server without saving the data to a disk file. You will certainly have to perform a series of queries form sqlite, massage the data into a corresponding update statement for SQL Server, then execute that on the server. Perhaps there are third party tools to do that same thing for file-based databases, but I suspect that you'd end up performing the same operation with a disc file anyway.