Load on-disk database directly into memory

Question

Using C# and the System.Data.SQLite.dll .NET wrapper (v1.0.117.0). This supports SQLite 3.40.0. The database file is typically under 50 MB, and I'd like to load this into memory. A standard file-backed connection string might be:

Data Source=C:\path\to\myDB.db;Version=3;

Loading a database file into memory can be done in several stages:

1. Create a blank database using the connection string:

   Data Source=:memory:;Version=3;New=True;
   

2. Open a secondary connection (using a standard connection) to the disk file.

3. Read the tables and table schema from the secondary connection.

4. Create all the tables in the in-memory database.

5. Copy data across from the secondary connection into the in-memory database tables.

6. Close the secondary connection.

This works, but is complicated. Am I missing some simple trick? The SQLite documentation on in-memory databases is not clear how to load a database file directly into memory. Is it possible to load "this database file" directly into memory in one step?

Answer 1

I have worked it out.

1. Set up two connections.

2. The connection string for the in-memory database (CONNECTION_1) will be FullUri='file::memory:?cache=shared';.

3. The connection string for the physical on-disk database (CONNECTION_2) will be Data Source=C:\\path\\to\\yourDB.db;Version=3;.

4. Open both connections.

5. Execute the command VACUUM INTO 'file::memory:?cache=shared'; on CONNECTION_2.

6. Close CONNECTION_2.

The in-memory database is deleted when the connection is closed, so keep CONNECTION_1 open for now.