denodrivers / denodrivers/sqlite3
Unable to query or insert into SQLite table after loading SpatiaLite extension
- Dominant language
- TypeScript
- Stars
- 312
- Forks
- 24
- PR merge metrics
- No merged PRs in 30d
Description
### **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:
```sql
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):**
```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
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.