Documentation Source Text

Artifact [d4675c11f0]
Login

Artifact d4675c11f0c83f6517356072dcd7eef1fe72defa:


<title>In-Memory Databases</title>
<tcl>hd_keywords {in-memory database} {in-memory databases} {memory}</tcl>

<h1 align="center">In-Memory Databases</h1>

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

<p>The most common way to force an SQLite database to exist purely 
in memory is to open the database using the special filename
"<b>:memory:</b>".  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:</p>

<blockquote><pre>
rc = sqlite3_open(":memory:", &amp;db);
</pre></blockquote>

<p>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.</p>

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

<blockquote><pre>
ATTACH DATABASE ':memory:' AS aux1;
</pre></blockquote>

<p>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:".</p>

<p>The special ":memory:" filename also works when using [URI filenames].
For example:

<blockquote><pre>
rc = sqlite3_open("file::memory:", &amp;db);
</pre></blockquote>

Or,

<blockquote><pre>
ATTACH DATABASE 'file::memory:' AS aux1;
</pre></blockquote>

<tcl>hd_fragment sharedmemdb {in-memory shared cache database}</tcl>
<h2>In-memory Databases And Shared Cache</h2>

<p>In-memory databases are allowed to use [shared cache] if they are
opened using a [URI filename].  If the unadorned ":memory:" name is used
to specify the in-memory database, then that database always has a private
cache and is this only visible to the database connection that originally
opened it.  However, the same in-memory database can be opened by two or
more database connections as follows:

<blockquote><pre>
rc = sqlite3_open("file::memory:?cache=shared", &amp;db);
</pre></blockquote>

Or,

<blockquote><pre>
ATTACH DATABASE 'file::memory:?cache=shared' AS aux1;
</pre></blockquote>

<p>This allows separate database connections to share the same
in-memory database.  Of course, all database connections sharing the
in-memory database need to be in the same process.  The database is
automatically deleted and memory is reclaimed when the last connection
to the database closes.

<p>If two or more distinct but shareable in-memory databases are needed
in a single process, then the [coreqp | mode=memory] query parameter can
be used with a [URI filename] to create a named in-memory database:

<blockquote><pre>
rc = sqlite3_open("file:memdb1?mode=memory&amp;cache=shared", &amp;db);
</pre></blockquote>

Or,

<blockquote><pre>
ATTACH DATABASE 'file:memdb1?mode=memory&amp;cache=shared' AS aux1;
</pre></blockquote>

<p>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.



<tcl>hd_fragment temp_db {temporary tables} {temporary databases}</tcl>
<h2>Temporary Databases</h2>

<p>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.</p>

<blockquote><pre>
rc = sqlite3_open("", &amp;db);
</pre></blockquote>

<blockquote><pre>
ATTACH DATABASE '' AS aux2;
</pre></blockquote>

<p>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.</p>

<p>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.</p>

<p>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.
</p>