creation querys of any kind do not work with a file

[NewtTheWolf]

import { DB } from "https://deno.land/x/sqlite/mod.ts";

//works
const db = new DB();

//doesnt work
const db = new DB("./db.db");

db.query(`
  CREATE TABLE fills (
    id         TEXT PRIMARY KEY,
    size       INTEGER NOT NULL,
    price      REAL NOT NULL,
    calculated BOOLEAN
  )
`);
db.query(`
  CREATE TABLE prices (
    id    INTEGER PRIMARY KEY,
    fill  TEXT UNIQUE NOT NULL REFERENCES fills (id),
    total REAL NOT NULL
  )
`);

db.query(`
  CREATE TRIGGER add_prices AFTER INSERT ON fills BEGIN
    UPDATE fills SET calculated = TRUE WHERE id = NEW.id;
    INSERT INTO prices (fill, total) VALUES (NEW.id, NEW.size * NEW.price);
  END
`);

db.query(
  "INSERT INTO fills (id, size, price) VALUES (:id, :size, :price)",
  {
    id: "490068558206992395",
    size: 42,
    price: 19.99,
  },
);

const rows = db.query("SELECT * FROM fills");

console.log(rows)

with an in memory database it works fine

but if you specify a path to a file, unfortunately any update, create, insert etc query does not work, is that normal?

Originally posted by @dyedgreen in https://github.com/dyedgreen/deno-sqlite/issues/171#issuecomment-1015896535

[NewtTheWolf]

only windows atm

[dyedgreen]

Are you passing the required permissions to your deno program (--allow-write and --allow-read)?

[NewtTheWolf]

Are you passing the required permissions to your deno program (--allow-write and --allow-read)?

I have tested the whole thing under linux and it worked fine only under Windows it does not work

my command contains the attribute -A but here is my test command:

deno run -A --unstable --import-map=./import_map.json --no-check .\test.ts

[dyedgreen]

Hm, the issue might be related to Windows in some way then. I don't have a windows machine to test this on unfortunately.

To help narrow down the issue, could you check two things: 1) Does this problem persist if you run without the --unstable flag? 2) If it persists, can you try building the debug WASM (clone the repo, then cd build, then make setup amalgamation debug) and using the local mod.ts to try and reproduce. (The debug build will output lot's of diagnostic information which will hopefully help narrow down the issue)

[dyedgreen]

(In case (1) helps, I feel like the issue #174 might be related; in which case we might want to patch file locking under windows for now?)

[NewtTheWolf]

1. Does this problem persist if you run without the --unstable flag?

then it's fixed, but unfortunately I can't get around using unstable stuff, so that wouldn't be an option

[NewtTheWolf]

(In case (1) helps, I feel like the issue #174 might be related; in which case we might want to patch file locking under windows for now?)

as already written it is because of the unstable flag, so is that fixable or should I look for an alternative first?

so that is the only SQLITE module I would look for another storage option

[dyedgreen]

There is a pretty simple fix we can do to make it work for windows. I’ll throw something together today 😊

[dyedgreen]

Closed by #192