SQLite In-memory database

With libraries, samples etc that are in the FourJs Genero GitHub repository such as fgl_zoom, we need to run the program against a database.  However we want the program to run out of the box, we don’t want you to have to configure to your own databases or to have to cater for database differences not catered for by the ODI layer   We want the repository to be as simple as possible so that it can be downloaded, compiled, and executed in 3 simple steps. ¹.

A technique I use is to take advantage of the fact that SQLite has a built-in in-memory database.  That is rather than having the SQLite database stored in an ordinary disk file, it is stored in memory.  As documented in the SQLite documentation, this is achieved with a special database filename of “:memory:”.  For our Genero configuration this simply means that when specifying a connection to a SQLite database to also use this special filename of “:memory:” as we have mentioned here in our documentation.

So you will see this in a lot of my repositories I do this with the following line of code

CONNECT TO ":memory:+driver='dbmsqt'"

I am telling the CONNECT statement to use the SQLite database driver (dbmsqt) and instead of a filename, I am using the special filename of “:memory:”.  From there I can then issue CREATE TABLE, INSERT INTO, LOAD etc to populate my in-memory test database as required.

You will see in my fgl_zoom repository, in fgl_zoom_test.4gl at line 56 at time of writing, there is the CONNECT statement using the :memory: reserved word, and then inside fgl_zoom_testdata.4gl  I am creating tables and populating this in-memory database.

This in-memory database is only accessible by the process running it, but for your purposes that maybe all that is required, and the fact that you know no one can access this data or impact the performance of this database might be advantageous.

You may find other uses for this in-memory database.  Suggestions include

• an alternative to a temp table in a physical database.
• reduce load or network connections to physical database.
• testing, do your testing starting from a known position without worrying about other test processes impacting your database and your test process impacting the database for the next test.
• demonstrations, do your demonstrations against a database in memory with your known data-set knowing that no-one else will be touching your database during your demonstration.
• teaching, have your training exercises performed using this database in memory knowing that one student can not change another students data.
• to do SQL type SELECT’s and UPDATE’s on an array, it maybe quicker to load the array into a table in this in-memory database, and do your operation there

Final point, if you see “:memory:” in a database connection to a SQLite database you now know what it means and you can stop looking for a file named “:memory:” 🙂

⁽¹⁾With setting up test data, one of the additional things I try to avoid is dates greater than the 12th on any given month so that the same data can be loaded into a DMY or MDY database as per https://en.wikipedia.org/wiki/Date_format_by_country