Temporary Tables, In-Memory Databases, and the temp Schema

Learning outcomes

“Temporary” and “in memory” sound similar, but SQLite uses them for different scopes. A TEMP table is an object in a connection-private temp schema. An entire :memory: database is a database whose main storage exists only for the lifetime of its connection. Mixing those concepts leads to disappearing data and confusing tests.

:memory: replaces the main database with connection-lifetime state

If the database filename is exactly :memory:, SQLite creates a private in-memory database for that connection. Nothing is written to a normal database file, and the database disappears when the connection closes.

sqlite3 :memory:CREATE TABLE demo (    id INTEGER PRIMARY KEY,    note TEXT NOT NULL);INSERT INTO demo(note) VALUES ('exists only in this connection');SELECT * FROM demo;.databases.quit-- Start a new process/connection:sqlite3 :memory:.tables

The second session has no demo table. That is not data loss; it is the requested lifetime. This makes :memory: excellent for isolated tests and scratch computation where persistence would be undesirable.

A TEMP table is a schema choice, not a synonym for RAM

CREATE TEMP TABLE creates an object in the connection's temp schema. That schema is private to the connection and destroyed when the connection closes. SQLite may back temporary structures with memory or temporary files depending on build/runtime configuration, so “TEMP table” describes visibility and lifetime more reliably than physical medium.

sqlite3 chapter03_scope.dbCREATE TABLE main.persistent_note (    id INTEGER PRIMARY KEY,    note TEXT NOT NULL);CREATE TEMP TABLE scratch_note (    id INTEGER,    note TEXT);INSERT INTO main.persistent_note(note) VALUES ('survives reconnect');INSERT INTO temp.scratch_note VALUES (1, 'connection only');SELECT * FROM main.persistent_note;SELECT * FROM temp.scratch_note;PRAGMA temp_store;.databases

PRAGMA temp_store reports a storage policy setting; compile-time option SQLITE_TEMP_STORE can influence the effective behavior. Do not change it just to complete this lesson. The essential guarantee is that the temp schema is connection-private and disappears on close.

main, temp, and attached databases are schemas on one connection

An SQLite connection can address more than one database. The primary database is main. The connection-private temporary database is temp. ATTACH DATABASE adds another database under a name you choose.

ATTACH DATABASE 'chapter03_archive.db' AS archive;CREATE TABLE archive.archived_note (    archive_id INTEGER PRIMARY KEY,    note TEXT NOT NULL);SELECT 'main' AS schema_name, nameFROM main.sqlite_schemaWHERE type='table'UNION ALLSELECT 'temp', nameFROM temp.sqlite_schemaWHERE type='table'UNION ALLSELECT 'archive', nameFROM archive.sqlite_schemaWHERE type='table'ORDER BY schema_name, name;DETACH DATABASE archive;

Schema qualification becomes important when the same object name could exist in more than one database. It also makes scripts self-documenting: main.device means durable application state, while temp.stage_device announces a connection-scoped staging object.

Named/shared in-memory databases are a specialized same-process technique

SQLite supports URI filenames such as file:memdb1?mode=memory&cache=shared. Two connections can share that named in-memory database only when URI processing is enabled, the URI name matches, and the connections are in the same process participating in the shared cache. The database disappears when the last participating connection closes.

# Application-level concept, not two independent shell processes:connection A -> file:fieldnotes_mem?mode=memory&cache=sharedconnection B -> file:fieldnotes_mem?mode=memory&cache=sharedsame process + same URI name + shared cache              |              v       one named in-memory database              |      deleted after last close

Do not use two separate terminal sqlite3 processes as proof of shared memory; separate processes do not share this in-memory database. Also record URI support/flags in application code instead of assuming every host wrapper enables URI processing identically.

Persistence matrix: choose by required lifetime

Lab: prove what survives by closing the connection

This lab intentionally uses both a disk-backed main database and a TEMP table. The close/reopen step is the test.

sqlite3 chapter03_scope.dbDROP TABLE IF EXISTS main.survival_test;CREATE TABLE main.survival_test (    id INTEGER PRIMARY KEY,    note TEXT NOT NULL);CREATE TEMP TABLE disappearance_test (    id INTEGER,    note TEXT);INSERT INTO main.survival_test(note) VALUES ('disk-backed main');INSERT INTO temp.disappearance_test VALUES (1, 'temp schema');SELECT * FROM main.survival_test;SELECT * FROM temp.disappearance_test;.quitsqlite3 chapter03_scope.dbSELECT * FROM main.survival_test;SELECT * FROM temp.disappearance_test;

After reconnect, main.survival_test returns its row because the table and row are in the database file. The query against temp.disappearance_test should fail with “no such table” because the original connection's temp schema was destroyed.

sqlite3 :memory:CREATE TABLE main.memory_only(id INTEGER PRIMARY KEY, note TEXT);INSERT INTO memory_only(note) VALUES ('gone after close');SELECT COUNT(*) FROM memory_only;.quitsqlite3 :memory:SELECT COUNT(*) FROM memory_only;

The second :memory: connection should report that memory_only does not exist. This experiment proves the difference between a durable file, a temporary schema, and an in-memory main database.

Failure patterns and safe corrections

Summary and bridge to deliberate schema design

You can now separate three independent design questions: what key organizes rows, which schema owns an object, and how long the database/object lives. That vocabulary is enough to design the FieldNotes schema deliberately rather than copy generic SQL DDL. Lesson 5 combines rowid choices, a composite WITHOUT ROWID table, constraints, and introspection into a reusable schema review.

References

• SQLite — In-Memory Databases.
• SQLite — ATTACH DATABASE.
• SQLite — URI Filenames.
• SQLite — Temporary Files / TEMP Databases.
• SQLite — Shared-Cache Mode.
• SQLite — PRAGMA temp_store.