possible strange issue with an ATTACHed database

[punkish]

I want to query about a behavior of ATTACHed db that I've just encountered and find a bit strange.

1. import Database from 'better-sqlite3'; // v 8.4.0
2. const db1 = new Database('./db1.sqlite');
3. const db2 = new Database('./db2.sqlite');
4. db1.pragma('foreign_keys = ON');
5. db2.pragma('foreign_keys = ON');
6. db2.prepare(ATTACH DATABASE './db1.sqlite' AS db1).run();
7. create tables in db1 // pseudo code here on for brevity
8. create tables in db2
9. create a TEMPORARY TRIGGER AFTER INSERT ON TABLE in db2 that references a table in attached db1
10. insert data in db1
11. insert data in db2

// 11 fails with the error

SqliteError: no such table: db1.\<table in db1\>

Basically, the TEMPORARY TRIGGER fails to fire because it can't find the ATTACHEd db1.

However, if I run any query before creating the TEMPORARY TRIGGER, it all works. So, imagine a step between #8 and 9 above like so

8a. select * from db1.<table from db1>

Even though there is no data in any table in db1 yet, now, everything works. It is almost as if a query was needed to actually make an ATTACHed db really get attached? I've asked this question on the SQLite forum as well, but could this be a quirk of better-sqlite3?

[punkish]

I have confirmed that the above behavior happens only with better-sqlite3. If I run plain SQL, everything just works, and I don't need to run a useless query to kickstart the ATTACHed database. Unless this behavior is by design, it seems like a bug.

Here is a gist with the script that shows the bug in action. Run the script as is and it will fail. Uncomment lines 165-166 (see line 160 for notes) and the script will succeed.

https://gist.github.com/punkish/684f925d49f74e4dab52e264a1c0259b

[neoxpert]

Do you have some example code prepared that will run all of this within a native sqlite3 context (executable)? Have you tried narrowing down the issue by reducing the implementation to a critical minimum that is able to reproduce this issue?

If I run all the commands within your example gist the following code works without throwing an error:

const Database = require('better-sqlite3');
const mat = new Database('/tmp/mat.sqlite');
mat.exec(`attach database '/tmp/geo.sqlite' as geo;`);

mat.exec(`
INSERT INTO materialCitations (materialCitation, treatments_id, longitude, latitude) 
     VALUES ('one materialCitation', '1', -5,  10);
`);