SQLite Tutorial (10): In-memory database and temporary database

1. Memory database:

In SQLite, databases are usually stored in disk files. However, in some cases, we can keep the database in memory at all times. The most commonly used method is to pass the database file name parameter ":memory:" when calling sqlite3_open(), such as:
 

After calling the above function, no disk files will be generated, instead, a new database is successfully created in pure memory. Since it is not persisted, the database disappears immediately after the current database connection is closed. It should be noted that although multiple database connections can create in-memory databases through the above method, they are different databases and have no relationship with each other. In fact, we can also attach the in-memory database like other ordinary databases through the Attach command, such as:
 
   
2. Temporary database:

When calling the sqlite3_open() function or executing the ATTACH command, if the database file parameter passes an empty string, a new temporary file will be created as the underlying file of the temporary database, such as:
 

or
 
Very similar to in-memory databases, the temporary databases created by the two database connections are also independent. After the connection is closed, the temporary database will automatically disappear and its underlying files will be automatically deleted.
Although disk files are created to store data information in temporary databases, temporary databases will actually reside in memory like in-memory databases. The only difference is that when the amount of data in the temporary database is too large, in order to ensure that more memory can be used for other operations, SQLite will write some of the data in the temporary database to the disk file, while the in-memory database will always store the data in memory.