Using an in memory DB

OpenAF comes with the H2 database embeeded. The main objective is, of course, to interact with H2 databases. But it also provides other perks (e.g. like the MVStore). One of them is an “in-memory” H2 database.

Why would you want or need an “in-memory” database? There are a lot of uses that we won’t elaborate now but you can read them in wikipedia. But since H2 is also a relational database engine it can be helpful even if it just for testing.

Creating

Creating the H2 in-memory database it’s easy. Just provide a name and execute:

> var db = createDBInMem("testDB");

Why a name? Because you can have several databases providing you have the memory capacity for them. The OpenAF’s createDBInMem function returns an already instatiated db object that you can now use.

Using it

> db.u("CREATE TABLE test (id NUMBER(10), desc VARCHAR2(255))");
0
> db.q("SELECT * FROM test");
{
  "results": []
}

Let’s insert some data:

> db.us("INSERT INTO test (id, desc) VALUES (?, ?)", [ 0, "Result 0" ]);
1
> db.q("SELECT * FROM test")
{
  "results": [
    {
      "ID": 0,
      "DESC": "Result 0"
    }
  ]
}

Let’s make it a hundred dummy data records:

> var ar = []; for(var ii = 1; ii < 99; ii++) { ar.push([ ii, String("Result " + ii)]); }
98
> db.usArray("INSERT INTO test (id, desc) VALUES (?, ?)", ar)
98
> db.commit();
> 
> db.q("SELECT COUNT(1) c FROM test")
{
  "results": [
    {
      "C": "99"
    }
  ]
}

Persisting

If you exit OpenAF the in-memory database will, of course, lose all it’s data. But what if you want to persist it? There are some helper functions for that: persistDBInMem and loadDBInMem:

> persistDBInMem(db, "test.sql");

Then on another OpenAF execution:

> var db = createDBInMem("testDB");
> loadDBInMem(db, "test.sql")
5
> db.q("select count(1) C from test")
{
  "results": [
    {
      "C": "99"
    }
  ]
}

Note: the generated files are SQL files. There isn’t any intention of supporting large volumes of data databases.