SQLite Forum

Where is the temporary database file stored?

Where is the temporary database file stored?

(1) By Joe Enos (jtenos) on 2021-04-30 04:05:33 [source]

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?

(2) By Larry Brasfield (larrybr) on 2021-04-30 04:27:27 in reply to 1 [link] [source]

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.

(3.1) By Keith Medcalf (kmedcalf) on 2021-04-30 05:15:19 edited from 3.0 in reply to 1 [link] [source]

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

(4) By Joe Enos (jtenos) on 2021-04-30 05:21:50 in reply to 2 [link] [source]

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.

(5) By Joe Enos (jtenos) on 2021-04-30 05:24:33 in reply to 3.1 [link] [source]

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.

(6) By Keith Medcalf (kmedcalf) on 2021-04-30 05:27:37 in reply to 3.1 [link] [source]

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

(7) By Joe Enos (jtenos) on 2021-04-30 05:29:23 in reply to 6 [link] [source]

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


(8) By David Raymond (dvdraymond) on 2021-04-30 17:29:53 in reply to 1 [link] [source]

Temporary Files Used By SQLite Section 5 has the info I think you're looking for.

Temporary File Storage Locations

The directory or folder in which temporary files are created is determined by the OS-specific VFS.

On unix-like systems, directories are searched in the following order:

    1. The directory set by PRAGMA temp_store_directory or by the sqlite3_temp_directory global variable
    2. The SQLITE_TMPDIR environment variable
    3. The TMPDIR environment variable
    4. /var/tmp
    5. /usr/tmp
    6. /tmp
    7. The current working directory (".") 

The first of the above that is found to exist and have the write and execute bits set is used. The final "." fallback is important for some applications that use SQLite inside of chroot jails that do not have the standard temporary file locations available.

On Windows systems, folders are searched in the following order:

    1. The folder set by PRAGMA temp_store_directory or by the sqlite3_temp_directory global variable
    2. The folder returned by the GetTempPath() system interface. 

SQLite itself does not pay any attention to environment variables in this case, though presumably the GetTempPath() system call does. The search algorithm is different for CYGWIN builds. Check the source code for details.

(9) By Kees Nuyt (knu) on 2021-05-01 17:00:24 in reply to 5 [link] [source]

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