How do I completely clear a SQLite3 database without deleting the database file?

sql, sqlite

Solution

This works without deleting the file and without closing the db connection:

PRAGMA writable_schema = 1;
DELETE FROM sqlite_master;
PRAGMA writable_schema = 0;
VACUUM;
PRAGMA integrity_check;

Another option, if possible to call the C API directly, is by using the `SQLITE_DBCONFIG_RESET_DATABASE`:

sqlite3_db_config(db, SQLITE_DBCONFIG_RESET_DATABASE, 1, 0);
sqlite3_exec(db, "VACUUM", 0, 0, 0);
sqlite3_db_config(db, SQLITE_DBCONFIG_RESET_DATABASE, 0, 0);

Here is the reference

Problem

For unit testing purposes I need to completely reset/clear SQLite3 databases. All databases are created in memory rather than on the file system when running the test suite so I can't delete any files. Additionally, several instances of a class will be referencing the database simultaneously, so I can't just create a new database in memory and assign it to a variable. Currently my workaround for clearing a database is to read all the table names from `sqlite_master` and drop them. This is not the same as completely clearing the database though, since meta data and other things I don't understand will probably remain. Is there a clean and simple way, like a single query, to clear a SQLite3 database? If not, what would have to be done to an existing database to make it identical to a completely new database? In case it's relevant, I'm using Ruby 2.0.0 with sqlite3-ruby version 1.3.7 and SQLite3 version 3.8.2.

Original source