According to the In-memory documentation:

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.

I'm trying this out, on a Mac, and I've tried with Python and with Deno. I'll use ":memory:" as the name, and "" as the name, and they seem to do the same thing. I don't see a file created on disk in the current directory or in /tmp. Where else should I be looking to see this file get created and automatically deleted?

From a little further in the same doc you linked, on how a memory DB and temporary DB are not the same: "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."

It is unclear why you want to find the temporary DB file, (which may not be written according to that quote), since it is not persistent past the connection's lifetime. The file backing is logically like a portion of the swap device, something which users hardly need be aware of.

Thanks. It does say "a new temporary file is created" - emphasis on "is" rather than "may be" - unless of course I disagree with the documentation on the meaning of the word "is" (throwback to the 90s).

The reason I'm asking is I do want to see it in action in testing, before I use it for real. For example, suppose this actually doesn't work with the library I'm using for whatever reason, and an empty string actually writes fully to memory rather than to disk - if I tried using this on something that's too big, it would crash (I assume) rather than spill over to disk. If I can watch it as it's happening, then I can be confident it'll work with larger sets of data.

I suppose I can just do some bigger testing with a few GB of dummy data, and keep an eye on the /tmp directory and the system monitor for RAM usage. I figured I'd confirm that /tmp is right first.

A database ":memory:" exists only in the process virtual private address space and is never stored on disk (except, of course, that virtual memory may in fact live in the swap file when a page is not mapped into V:R address space). The cache_size setting does not limit (has no effect on) the size of the database in memory. It is limited only by the virtual address space that the process can allocate (or the heap allocation limit for sqlite3's use, which is configurable, and effective only if memory management is in effect).

A database with a zero-length name ("") has a virtual private address space size limit that is equal to the cache_size setting. Once the entire number of pages specified in the cache_size are used, then a temporary file will be allocated in the location specified for the process to create temporary files, wherever that happens to be on the Operating System in question, in order to "spill pages" that will not fit in the specified page cache all at the same time. Once this happens, the empty-name database is indistinguishable from a normal database that live in the filesystem EXCEPT the name, and the fact that the database file is "EXCLUSIVE" and may not be accessed by anything save the connection which created it.

This file is a temporary file that will be deleted by the Operating System when the process ends or the connection closes (and assumes that the Operating System works properly -- if it does not, then the "temporary file" may be left behind if the Operating System fails to wipe-its-arse properly -- for example, Microsoft Operating Systems are known to require lots of help managing temporary files and has a tendency to leave turds scattered about with wild abandon).

Thanks. So it sounds like you're also saying that the temporary file is only generated when it needs to be, rather than every time.

I guess I'll just dig in and make a big data set and see if I can watch /tmp and see anything happen.

... that the temporary file is only generated when it needs to be, rather than every time.

Not only that. In Operating Systems that allow it (Unix, Linux) SQLite will often unlink(3) temp files immediately after they are created.

The inode and space allocation will exist as long as SQLite has the file open, but the directory entry will vanish in less than the blink of an eye.

Note: pragma max_page_count will limit the number of pages in both a ":memory:" and "" database.

Awesome, I'll give that a try, might make things easier to test.

Thanks