Artifact
f6a248f29112000dccfe7b4476de321745f9bae9:
- File
pages/inmemorydb.in
— part of check-in
[c8dc1e13d7]
at
2009-02-05 02:33:57
on branch trunk
— Enhancements to the SQL language documentation.
(user:
drh
size: 3107)
<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:", &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>
<b>ATTACH DATABASE ':memory:' AS aux1;</b>
</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>
<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("", &db);
</pre></blockquote>
<blockquote><b>
ATTACH DATABASE '' AS aux2;
</b></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>