Temporary in-memory database in SQLite

Question

Is it possible somehow to create in-memory database in SQLite and then destroy it just by some query?

I need to do this for unit testing my db layer. So far I've only worked by creating normal SQLite db file and delete if after all tests, but doing it all in memory would be much better.

So is it possible to instanciate database only in memory without writing anything to disc? I can't use just transactions, because I want to create whole new database.

Answer 1

Create it with the filename ":memory:": In-Memory Databases.

It'll cease to exist as soon as the connection to it is closed.

Answer 2

I'd suggest mounting a tmpfs filesystem somewhere (RAM only filesystem) and using that for your unit tests.

Instantiate DB files as normal then blow them away using rm - yet nothing has gone to disk.

(EDIT: Nice - somebody beat me to a correct answer ;) Leaving this here as another option regardless)