Cannot open Temporary Database

[jolee11]

Hello sir,

It seems that
require('better-sqlite3')('')
just open :memory: database instead of "Temporary Databases" !

From https://www.sqlite.org/inmemorydb.html :

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

Thank you very much!
Best regards,
jo

[Prinzhorn]

Related: a5fe469

What you are saying is that currently it behaves exactly like in-memory but you'd expect to see a temporary file to be create? Because the temporary database should be able to outgrow the memory, correct?

[jolee11]

Thank you for your reply!

I guess the problem is not about seeing the temp file or how memory is used!

The current behavior of better-sqlite3 is:
using either ':memory:' or '' are exactly the same.
i.e. to use ':memory:' when calling sqlite3 API.

But, as the sqlite3 document says, ':memory:' and '' are different!

And, the behavior of sqlite3 creating database with '' is what I really wanted.

Thank you very much!
Best regards,
jo

[JoshuaWise]

This is simply not true. better-sqlite3 passes your exact database name to the underlying SQLite3 library, unchanged (besides trimming whitespace). When you use "" in better-sqlite3 it does indeed open a temporary database. There are two reasons you may be confused:

• SQLite3 does not actually create a temporary file for temporary databases until it determines you don't have enough memory to store the whole database in memory. This is described at the bottom of this page in the SQLite3 docs.
• better-sqlite3 exposed a property called .memory on database objects that you create, and that property is true for both in-memory and temporary databases. Perhaps a better name for that property would be .temporary or .anonymous or .ephemeral.

[Prinzhorn]

* This is described at the bottom of [this page](https://www.sqlite.org/inmemorydb.html) in the SQLite3 docs.

I was just skimming through this page back then and was interpreting "Even though a disk file is allocated for each temporary database" differently. It sounds like SQLite would immediately create a temporary file (seems reasonable to ensure fs permissions), but only write to it when needed. And I couldn't find said file (even after specifying the tmp folder using env vars I couldn't find the file). Thanks for clarifying.

[Prinzhorn]

In case anyone comes across this. https://sqlite.org/forum/forumpost/62f9d03a80?t=h&unf

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

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.

Emphasis mine.

So that makes it hard to actually "see" the temporary file, because it's not necessarily actually in the file system. So even though the SQLite docs say "a disk file is allocated for each temporary database" it does not mean it's actually there because OS smart.