SQLite Forum

Temp database
Login

Temp database

(1) By anonymous on 2021-10-15 08:57:04 [link]

If possible, how do I open a temp database?

I specify  ':memory:' for an in-memory database; what is the  string  for opening/attaching a temporary database?

(2.1) By Stephan Beal (stephan) on 2021-10-15 09:35:50 edited from 2.0 in reply to 1 [link]

> what is the string for opening/attaching a temporary database?

<s>sqlite doesn't, to the best of my knowledge, have any APIs for managing temp databases. It has temp tables:</s>

```
create temp table x ...;
```

Edit: that said, try `""` instead of `":memory:"`. IIRC that works like memory but uses a temp database.

(3) By anonymous on 2021-10-15 09:50:25 in reply to 2.1 [link]

Perfect! Thank you.

(4) By Keith Medcalf (kmedcalf) on 2021-10-16 02:41:49 in reply to 1 [link]

''

That is, you can specify a file name, the special name ':memory:', or an empty string.

Specifying a filename opens that file as a database.  Specifying the special name ':memory:' opens a database that resides purely in memory (as in Volatile RAM).

Specifying nothing for the filename (an empty text string) causes a "temporary file" to be created and then opened as a database.  Once the "temporary file" (database) connection is closed, the "temporary file" is deleted.

A 'named file' database may be accessed simultaneously by multiple connections, however, a ':memory:' or '' (temporary) database may only be exclusively accessed by the connection which created it -- it cannot be accessed by multiple connections.

You will note that *shared-cache* is a method of **emulating** multiple connections, however, there is only one single connection to the database.

(5) By Keith Medcalf (kmedcalf) on 2021-10-16 03:04:58 in reply to 4 [link]

You will note that when you specify a filename ('filename.db' for example) then that file is opened and used as the database.

When you specify a filename ':memory:' then a database exclusive to that connection is created entirely in memory.  No part of it will reside on disk.

If you create a temporary database by specifying a blank filename (ie, '') then the database will be created and treated according to the TEMP_STORE currently set.  If TEMP_STORE is currently set to memory only, then there is no difference between a ':memory:' database and a '' database.

In the default case, a '' (temporary) database resides in a temporary disk file, however, since the disk file is temporary, data will not be written to disk until memory is exhausted.

<https://sqlite.org/pragma.html#pragma_temp_store>

(6) By Keith Medcalf (kmedcalf) on 2021-10-16 03:16:12 in reply to 5 [link]

Also note that if you change TEMP_STORE, then the TEMP database will be cleared, however, ':memory:' and '' databases and attachments will not be cleared.

(7) By anonymous on 2021-10-16 07:50:43 in reply to 5 [link]

Thanks Keith. I need clarification on a some aspects:

1.In a new CLI session, 

> PRAGMA temp_store_directory;

returns nothing. (If I assign to it, it returns what I assigned). How do I find the current/default location?

2.Consider this session:

<code>sqlite> attach ':memory:' as AUX;
sqlite> attach '' as TMP;
sqlite> .mode column
sqlite> select * from pragma_database_list();
seq  name  file
0    main
2    AUX
3    TMP</code>

A. Why do I never find 1 in the seq column? (first column)

B. The file column values is identical for :memory: and '' - why?

3.As with :memory: I can attach as many '' (temp) databases as long as the name/alias is unique. Correct?

(8) By Keith Medcalf (kmedcalf) on 2021-10-16 09:10:52 in reply to 7

> How do I find the current/default location?

This is Operating System dependent.  <https://sqlite.org/tempfiles.html> Section 5

 >  Why do I never find 1 in the seq column? (first column)

seq 0 is always the main database -- the one opened when the connection was created.  
seq 1 is always the temp database -- which may not exist if it has never been used.  
seq 2 and after are the currently attached databases in order of attachment.

 > As with :memory: I can attach as many '' (temp) databases as long as the name/alias is unique. Correct?

Yes.  And each so attached database is unique to the connection.