Does in memory db behave the same as a normal db

(1) By Cecil (CecilWesterhof) on 2020-07-07 11:12:26 [link] [source]

I am testing a db application. The best would be to create the database for every test. I timed the creation of a normal test database. This takes about two seconds.
Currently I do not have many tests, so that would not be a problem. But when the number of tests would grow it could be.
I expect that an in memory database would be a lot faster. But can I assume that an in memory database behaves the same as a normal database? Otherwise it would be not very useful.

It is less important as I thought. When using transactions creating the database takes just a eight of a second.

(2) By Larry Brasfield (LarryBrasfield) on 2020-07-07 12:46:40 in reply to 1 [link] [source]

The in-memory or on-disk character of the database is implemented in a way that is not visible to the rest of the library, (except by name of the VFS that stores pages.)

As you might expect, the 'D' part of the ACID feature essentially vanishes for the in-memory DB. Also, many multiple-client access scenarios vanish.

(3) By Cecil (CecilWesterhof) on 2020-07-07 12:59:43 in reply to 2 [link] [source]

I expected that, but as I 'always' say:
"Better a check to many, as a check to little." ;-)

The D is not important: it is only for the test. And at the moment the multiple client is also not important for me at the moment.
I cross that bridge when I get there/

Thanks for the feedback.

(6) By Cecil (CecilWesterhof) on 2020-07-07 14:12:08 in reply to 3 [link] [source]

By using an in memory db it takes a hundredth of the time. A real big improvement. :-D

It pays to think a little. Two little changes and a performance increase 0f 1.600.

(4.1) By Stephan Beal (stephan) on 2020-07-07 13:15:50 edited from 4.0 in reply to 2 [source]

The in-memory or on-disk character of the database is implemented in a way that is not visible to the rest of the library,

Related trivia: unlike on-disk databases, using the db name ":memory:" multiple times in the same app instance opens up a different in-memory db instance each time. IIRC there's a way to tell sqlite3 to use shared memory, such that all open() calls get the same in-memory db, but i don't recall the specifics.

A brief demo:

s2sh2> const sA = new sqlite3(":memory:"), sB = new sqlite3(":memory:")
s2sh2> sA.exec("create table tA(a,b,c)")
s2sh2> sA.selectRows("select * from sqlite_master")
result: array@0x55b0e6dcc220[scope=#1 ref#=0] ==> [{
    "name": "tA",
    "rootpage": 2,
    "sql": "CREATE TABLE tA(a,b,c)",
    "tbl_name": "tA",
    "type": "table"
  }]
s2sh2> sB.selectRows("select * from sqlite_master")
result: array@0x55b0e6dcc280[scope=#1 ref#=0] ==> [] // <== empty array

(Edit: typos. See Gunther's response for the shared cache solution.)

(5) By Gunter Hick (gunter_hick) on 2020-07-07 13:11:46 in reply to 4.0 [link] [source]

https://sqlite.org/inmemorydb.html

"In-memory databases are allowed to use shared cache if they are opened using a URI filename. If the unadorned ":memory:" name is used to specify the in-memory database, then that database always has a private cache and is this only visible to the database connection that originally opened it. However, the same in-memory database can be opened by two or more database connections as follows:

rc = sqlite3_open("file::memory:?cache=shared", &db);
Or,
ATTACH DATABASE 'file::memory:?cache=shared' AS aux1;
This allows separate database connections to share the same in-memory database. Of course, all database connections sharing the in-memory database need to be in the same process. The database is automatically deleted and memory is reclaimed when the last connection to the database closes.

If two or more distinct but shareable in-memory databases are needed in a single process, then the mode=memory query parameter can be used with a URI filename to create a named in-memory database:

rc = sqlite3_open("file:memdb1?mode=memory&cache=shared", &db);
Or,
ATTACH DATABASE 'file:memdb1?mode=memory&cache=shared' AS aux1;
When an in-memory database is named in this way, it will only share its cache with another connection that uses exactly the same name."