skip to content

In SQLite, how do :memory: and temporary databases differ from a file database, and what goes wrong when tests rely on them?

level: middleimportance: should knowfreq 45%

answer

  1. private to one connection
  2. gone when the connection closes
  3. empty filename can spill to disk
  4. no WAL for in-memory databases
  5. a leading slash shares a memdb

basics

~20 s

Opening :memory: creates a RAM-only database private to that connection and deleted when it closes; an empty filename creates a private temporary database that may spill to a temp file. Tests break when a second connection expects the same data.

solid answer

~50 s

A `:memory:` database never touches disk, and every connection that opens `:memory:` gets its own, independent database, which vanishes when that connection closes. An empty filename (`""`) creates a **temporary** database: also private and deleted on close, but SQLite may flush parts of it to a temp file under memory pressure. Tests go wrong in three ways. A connection pool opens a second connection and finds no tables, or the pool closes an idle connection and the data disappears. A `:memory:` database can only use `journal_mode` MEMORY or OFF and always uses exclusive locking, so tests never run in WAL mode, never meet a second process and rarely exercise `SQLITE_BUSY` handling. And a test on SQLite proves little about a different production engine's dialect. To share one in-memory database between connections in a process, name it: `file:/testdb?vfs=memdb` (shared since 3.36.0) or the older shared-cache URI.

code

sql · 6 lines
sql
ATTACH DATABASE ':memory:' AS scratch;   -- pure in-memory, private to this connection
ATTACH DATABASE '' AS spill;             -- temporary database, may spill to a temp file
CREATE TABLE scratch.fixtures(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO scratch.fixtures(name) VALUES ('alpha'), ('beta');
PRAGMA scratch.journal_mode = WAL;       -- returns memory: the request is ignored
SELECT name, file FROM pragma_database_list;  -- both attached databases show an empty file name

go deeper

for a junior

Know that :memory: lives only in RAM, belongs to one connection and disappears when that connection closes.

for a middle

Explain the empty-filename temporary database, why journal_mode is stuck at MEMORY or OFF, and how a named memdb or shared-cache URI lets connections in one process share data.

for a senior

Spot the test-suite failures before they happen: pooled connections seeing empty schemas, data vanishing when a pool closes idle connections, and concurrency paths left untested.

for a principal

Set the team's test policy: in-memory databases for fast unit tests of SQLite code, real files for concurrency and failure tests, and the production engine itself where production is not SQLite.

## Three kinds of database a connection can open SQLite normally stores a database in one ordinary file, but the filename you pass to `sqlite3_open()`, `sqlite3_open_v2()` or `ATTACH` can ask for something else. | Filename | What you get | Lifetime | Who can see it | |---|---|---|---| | `app.db` | A database in that file | Until the file is deleted | Any connection, any process on the host | | `:memory:` | A **pure in-memory** database; no file is opened | Until this connection closes | Only this connection | | `""` (empty string) | A **temporary** database backed by a temp file | Until this connection closes | Only this connection | The `:memory:` name must be exactly that: `./:memory:` creates an ordinary file with that odd name. The temporary database usually lives in the page cache too, so in practice it behaves almost like `:memory:`. The one difference SQLite documents is that a `:memory:` database must stay in RAM, while parts of a temporary database can be written to disk when it grows or memory gets tight. `PRAGMA temp_store` and the `SQLITE_TEMP_STORE` compile-time option can force temporary storage to stay in memory. ## What changes when a database is not a file - **Each open is a new database.** Two connections that both open `:memory:` get two independent databases; a table created through one is invisible to the other. - **Closing destroys it.** There is nothing to reopen later. Persist an in-memory database with the online backup API, `VACUUM INTO`, or, in the `sqlite3` shell, the `.save` command; those mechanics belong to the material on SQLite's files and backup. - **Journal mode is fixed.** SQLite's documentation states that an in-memory database's `journal_mode` is either MEMORY or OFF and cannot be changed; a request for WAL is ignored. A temporary database refuses WAL too. - **Locking is fixed.** A `:memory:` or temporary database always uses exclusive locking mode, because no other connection can reach it. - **No crash durability.** A power loss or process crash takes the data with it, by design. ## Sharing one in-memory database between connections A test suite or a connection pool often needs several connections to see the same in-memory data. SQLite offers two named forms, both written as **URI filenames**. URI filenames are disabled by default for backwards compatibility; enable them with the `SQLITE_OPEN_URI` flag to `sqlite3_open_v2()`, `sqlite3_config(SQLITE_CONFIG_URI, 1)` or the `SQLITE_USE_URI=1` compile-time option. The `sqlite3` shell enables them itself. 1. **The memdb VFS**: `file:/testdb?vfs=memdb`. Since 3.36.0, connections in the same process that use the same name share one database, provided the name **starts with `/`**; a name without the slash gives each connection its own separate copy. 2. **Shared cache**: `file::memory:?cache=shared`, or a named form such as `file:memdb1?mode=memory&cache=shared`. This works, but SQLite's documentation calls shared-cache mode obsolete and discourages its use. Either way, every sharing connection must be **in the same process**, and the database is deleted when the last of them closes. The name is matched exactly, so two test suites that pick different names stay isolated from each other. ## Where tests built on in-memory databases mislead - **Pool size greater than one.** Each pooled connection opening `:memory:` sees an empty schema, which surfaces as "no such table" only in some tests. - **Idle connections closed by the pool.** The data vanishes between tests without an error. - **Concurrency goes untested.** None of these in-memory forms runs in WAL mode and no second process can join, so readers beside a writer and multi-process locking never appear and busy handling is rarely exercised; tests pass that would fail against a file database under load. - **A different production engine.** SQLite's own guidance endorses it as a stand-in for demos and testing, but its typing and SQL dialect differ from client-server engines, so a green suite on SQLite says less about production on another engine. ## Good uses - **Fast, isolated unit tests** of code that really runs on SQLite, one fresh database per test. - **Scratch work inside a program**: loading data into an in-memory database to sort, join and filter it with SQL instead of hand-written loops, as SQLite's guidance on internal databases suggests. - **Attached scratch space** beside a file database, for intermediate results that should never be written to the main file. For tests of WAL behaviour, busy handling or file-level failures, use a real file in a temporary directory instead, and delete it when the test finishes.

  • Your test suite opens a pool of four connections to ':memory:' and some tests fail with 'no such table'. Why, and what is the fix?
    Each pooled connection that opens `:memory:` creates its own empty database, so a table created through one connection does not exist on the others. Either cap the pool at one connection, or give every connection the same shared name, such as `file:/testdb?vfs=memdb` with URI filenames enabled, and keep at least one connection open for the life of the test so the database is not deleted.
  • How do you get a test's in-memory SQLite database onto disk to inspect it after a failure?
    From the test's own connection, copy it with the online backup API or run `VACUUM INTO 'failed-test.db'`, before that connection closes. Both write a consistent copy to a file without changing the in-memory database, and the file can then be opened in the `sqlite3` shell. The reverse direction, loading a database image into memory, is `sqlite3_deserialize()`.

  • Every connection that opens ':memory:' in a process sees the same database.
  • An in-memory database can use WAL to test concurrent readers.
  • A temporary database from an empty filename survives closing the connection.
  • A shared in-memory database can be opened from a second process.
  • Tests passing on an in-memory SQLite prove the SQL works on any engine.

Assumes SQLite 3.53; the memdb VFS has let connections in one process share a named in-memory database since 3.36.0, so an older platform-bundled SQLite needs the shared-cache URI instead.