Temporary in-memory database in SQLite
Create it with the filename ":memory:": In-Memory Databases.
It'll cease to exist as soon as the connection to it is closed.
As an alternative to in memory databases, you can create a SQLite temporary database by using an empty string for the filename. It will be deleted when the connection is closed. The advantage over an in-memory database is your databases are not limited to available memory.
Alternatively, you can create your database in a temp file and let the operating system clean it up. This has the advantage of being accessible for inspection.
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)