SQLite In-Memory Databases

19 Jul 2015

There are 3 ways of creating in-memory databases in SQLite:

Use :memory: filename to create distinct in-memory database. Every :memory: database is distinct from every other. For example:

rc = sqlite3_open(":memory:", &db);
// or,
rc = sqlite3_open("file::memory:", &db);

Or,

ATTACH DATABASE ':memory:' AS aux1;
// or,
ATTACH DATABASE 'file::memory:' AS aux1;

The same in-memory database can be shared/opened by two or more connections as follows:

rc = sqlite3_open("file::memory:?cache=shared", &db);

Or,

ATTACH DATABASE 'file::memory:?cache=shared' AS aux1;

Create two or more distinct but still shareable in-memory databases by using named in-memory database:

rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db);

Or,

ATTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS aux1;

When an in-memory database is named in this way, it will only share its cache with another connection that uses exactly the same name.

REFERENCE