How do I create a sqllite3 in-memory database?

Question

One of the appropriate uses for sqlite3 is "in-memory databases". This sounds like a really useful tool for my C++ applications. Does anyone have an example of how this is done in C or C++? I'm specifically looking for a canonical way to slurp several flat-files into an in-memory database, then do some joins.

Answer 1

Just open the file :memory: and that should do it (at least it does in PHP).

You mention that you want to read in several flat files and do joins on them. If it's possible to store the flat files as SQLite databases, you can work directly with both by attaching one to the other:

ATTACH foo.db AS foo

Then refer to the tables in foo like so:

SELECT * FROM foo.users

This way you can do your joins without the need for creating an in-memory database.

Answer 2

It's actually quite easy. Just specify ':memory:' as the database filename when opening a database using the C/C++ API. It's a special constant that the engine will recognize. The same actually works for other languages such as Python or Ruby, since they typically just wrap the C/C++ API. See http://sqlite.org/c3ref/open.html for complete details.

Answer 3

If you want SQLite to not use temporary files as journals, e.g, you don't want any file activity other than when you manually requests a connect, disconnect, attach or detach. Then use the following two pragmas at runtime after you connect to your ":memory:" database.

PRAGMA temp_store=MEMORY;
PRAGMA journal_mode=MEMORY;

From the docs.