Unable to query or insert into SQLite table after loading SpatiaLite extension

[tmessaoudi]

Title: Unable to query or insert into SQLite table after loading SpatiaLite extension

Description:

I am encountering an issue while trying to use the denodrivers/sqlite3 library to interact with an SQLite database with the SpatiaLite extension. Although I can successfully load the SpatiaLite extension and perform simple spatial queries, I am unable to query or insert into a regular table, and the script crashes without showing any error.

Steps to Reproduce

1. Environment:

   • OS: Ubuntu 24.04
   • Deno version: 2.x (also tested with 1.46.3)
   • SQLite version: 3.45.1
   • Installed packages:
     • sqlite3 installed via sudo apt install sqlite3
     • libsqlite3-mod-spatialite installed via sudo apt install libsqlite3-mod-spatialite

2. SQLite setup:

   • Created a SQLite database using sqlite3 test.db
   • Loaded the SpatiaLite extension with .load mod_spatialite
   • Initialized the SpatiaLite metadata with SELECT InitSpatialMetaData();
   • Created a simple table with this schema:

     CREATE TABLE buildings (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       settlement_id INTEGER,
       building_type_id INTEGER,
       production_method_id INTEGER
     );

3. Deno setup:

   • Initialized the project with deno init
   • Installed the SQLite driver using deno install jsr:@db/sqlite

4. Deno script (main.ts):

   import { Database } from '@db/sqlite';
   
   const db = new Database("test.db", { enableLoadExtension: true, create: false });
   
   db.loadExtension("mod_spatialite");
   
   const [version] = db.prepare("select sqlite_version()").value<[string]>()!;
   
   console.log(version); // Outputs: 3.45.1
   
   const spl = db.prepare("SELECT ASGEOJSON(ST_GeomFromText('MULTIPOINT(1.2345 2.3456)')) as geojson;").all();
   console.log(spl); // Outputs: [{ geojson: '{"type":"MultiPoint","coordinates":[[1.2345,2.3456]]}' }]
   
   // The following part crashes without any error or output
   const stmt = db.prepare("INSERT INTO buildings (settlement_id, building_type_id, production_method_id) VALUES (1, 1, 1) RETURNING *;");
   const buildingCreated = stmt.all();
   console.log(buildingCreated); // This is never reached
   
   db.close();
   

5. Running the script:

• Command: deno run --allow-all --watch main.ts
• The output is as follows:

3.45.1
[{ geojson: '{"type":"MultiPoint","coordinates":[[1.2345,2.3456]]}' }]

6. Problem:

• The insert query (or even a simple select query on the buildings table) doesn't execute, and the script crashes without showing any errors.
• I expected the insert query to work and output the inserted row.
• The script also doesn’t continue to "watch" as expected, even though I used the --watch flag.

Expected Behavior

• The query INSERT INTO buildings (settlement_id, building_type_id, production_method_id) VALUES (1, 1, 1) should insert the row into the buildings table and return the inserted data.
• The script should continue to "watch" for changes, but instead, it crashes after printing the spatial query result.

Actual Behavior

The spatial query works as expected, but as soon as I try to query or insert into the buildings table, the script crashes silently with no output and no error message.

Questions:

1. Could there be a compatibility issue between the @db/sqlite driver and the loaded SpatiaLite extension?
2. Is there a known issue with denodrivers/sqlite3 when handling insert queries into non-spatial tables after loading SpatiaLite?
3. Is there a way to capture or debug errors when the script crashes without error messages?

Additional Information:

• The issue occurs with both Deno 2.x and 1.46.3, so it's not specific to a particular Deno version.
• Every query work well when using sqlite3 cli