hd_keywords {in-memory database} {in-memory databases} {memory}

In-Memory Databases

An SQLite database is normally stored in a single ordinary disk file. However, in certain circumstances, the database might be stored in memory.

The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename ":memory:". In other words, instead of passing the name of a real disk file into one of the [sqlite3_open()], [sqlite3_open16()], or [sqlite3_open_v2()] functions, pass in the string ":memory:". For example:

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

When this is done, no disk file is opened. Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

The special filename ":memory:" can be used anywhere that a database filename is permitted. For example, it can be used as the filename in an [ATTACH] command:

ATTACH DATABASE ':memory:' AS aux1;

Note that in order for the special ":memory:" name to apply and to create a pure in-memory database, there must be no additional text in the filename. Thus, a disk-based database can be created in a file by prepending a pathname, like this: "./:memory:".

hd_fragment temp_db {temporary tables} {temporary databases}

Temporary Databases

When the name of the database file handed to [sqlite3_open()] or to [ATTACH] is an empty string, then a new temporary file is created to hold the database.

rc = sqlite3_open("", &db);
ATTACH DATABASE '' AS aux2;

A different temporary file is created each time, so that just like as with the special ":memory:" string, two database connections to temporary databases each have their own private database. Temporary databases are automatically deleted when the connection that created them closes.

Even though a disk file is allocated for each temporary database, in practice the temporary database usually resides in the in-memory pager cache and hence is very little difference between a pure in-memory database created by ":memory:" and a temporary database created by an empty filename. The sole difference is that a ":memory:" database must remain in memory at all times whereas parts of a temporary database might be flushed to disk if database becomes large or if SQLite comes under memory pressure.

The previous paragraphs describe the behavior of temporary databases under the default SQLite configuration. An application can use the [temp_store pragma] and the [SQLITE_TEMP_STORE] compile-time parameter to force temporary databases to behave as pure in-memory databases, if desired.