I've been using the CLI's .dump command to dump databases. In general this works great, but I've recently noticed that virtual tables (using FTS5 in my case) get dumped via an insert into sqlite_schema and not the corresponding CREATE VIRTUAL TABLE. When restoring a database this way, the virtual table is not immediately usable. I assume this is because the virtual table is not automatically registered when it is inserted into sqlite_schema directly. A simple workaround to this is to close and re-open the connection, however, I was curious if there was an alternative way to do this that would also work for an in-memory database?

I am adding an example below of a database consisting only of a FTS5 table called "haystack". If I restore the db dump close and re-open the connection, I can run a query like:

SELECT * from haystack;

But the same query would fail with no such table: haystack immediately after restoring the dump.

--- >>> .dump output

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
PRAGMA writable_schema=ON;
INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql)VALUES('table','haystack','haystack',0,'CREATE VIRTUAL TABLE haystack using fts5(needle)');
CREATE TABLE IF NOT EXISTS 'haystack_data'(id INTEGER PRIMARY KEY, block BLOB);
INSERT INTO haystack_data VALUES(1,X'');
INSERT INTO haystack_data VALUES(10,X'00000000000000');
CREATE TABLE IF NOT EXISTS 'haystack_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'haystack_content'(id INTEGER PRIMARY KEY, c0);
CREATE TABLE IF NOT EXISTS 'haystack_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'haystack_config'(k PRIMARY KEY, v) WITHOUT ROWID;
INSERT INTO haystack_config VALUES('version',4);
PRAGMA writable_schema=OFF;
COMMIT;

--- <<< .dump output

--- Add something here to register the virtual table
--- so that the following does not error?

SELECT * FROM haystack;

Is this possible? Or is this completely wrong and there is another preferred approach to dumping and restoring databases with virtual tables?

Thank you!