Permission problem with v2.1.0

[millette]

deno run d2.ts -A

Adding the proper import line

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

based on https://dyedgreen.github.io/deno-sqlite/playground/

With

const db = new DB(); // no filename

I get this error:

error: Uncaught SqliteError: unable to close due to unfinalized statements or unfinished backups
    return new SqliteError(msg, code);
           ^
    at DB._error (https://deno.land/x/sqlite@v2.1.0/src/db.ts:251:12)
    at DB.close (https://deno.land/x/sqlite@v2.1.0/src/db.ts:228:18)
    at file:///home/millette/ghfols/d2.ts:22:4

With

const db = new DB("rarara.db"); // with filename

It's this error:

error: Uncaught PermissionDenied: read access to "rarara.db", run again with the --allow-read flag
    at unwrapResponse ($deno$/ops/dispatch_json.ts:43:11)
    at Object.sendSync ($deno$/ops/dispatch_json.ts:72:10)
    at Object.openSync ($deno$/ops/fs/open.ts:20:10)
    at Object.openSync ($deno$/files.ts:28:15)
    at js_open (https://deno.land/x/sqlite@v2.1.0/build/vfs.js:30:14)
    at denoOpen (<anonymous>:0:3285)
    at sqlite3BtreeOpen (<anonymous>:0:140239)
    at openDatabase (<anonymous>:0:136332)
    at sqlite3_open (<anonymous>:0:135515)
    at open (<anonymous>:0:2665)

Running with all permissions (after trying --allow-read and --allow-write seperately:

deno run d2.ts -A

deno 1.0.4
v8 8.4.300
typescript 3.9.2

P.S.: the playground works as expected, with or without a db filename.

[dyedgreen]

Thanks for opening the issue! I can't seem to reproduce the error for opening an in-memory database, running:

// file.js
import { DB } from "https://deno.land/x/sqlite@v2.1.0/mod.ts";
const db = new DB();
db.close();

deno run file.js

works and exits with no error.

Could you provide the full script you're running? Based on the error it looks like you made some queries and have not closed them (called .done() or iterated over all results). You can also always force the connection to be closed using db.close(true), (see the documentation for details).

The second error is expected, opening a database file requires both read and write permissions for the file being opened.

[millette]

I added db.close(true) and it works with no filename but still fails when given one.

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

const db = new DB();
db.query("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, email TEXT)");

const names = ["Peter Parker", "Clark Kent", "Bruce Wane"];

// Run a simple query
for (const name of names)
  db.query(
    "INSERT INTO people (name, email) VALUES (?, ?)",
    [name, `${name.replace(/\s+/g, ".").toLowerCase()}@deno.land`]
  );

// Query the results
let data = db.query("SELECT * FROM people");

// Note: The console supports directly displaying queried rows (consuming the row)
console.log("Results:", data);

// Close connection
db.close(true);

If I use const db = new DB("blabla.db"); instead I get the error

error: Uncaught PermissionDenied: read access to "blabla.db", run again with the --allow-read flag
    at unwrapResponse ($deno$/ops/dispatch_json.ts:43:11)
    at Object.sendSync ($deno$/ops/dispatch_json.ts:72:10)
    at Object.openSync ($deno$/ops/fs/open.ts:20:10)
    at Object.openSync ($deno$/files.ts:28:15)
    at js_open (https://deno.land/x/sqlite@v2.1.0/build/vfs.js:30:14)
    at denoOpen (<anonymous>:0:3285)
    at sqlite3BtreeOpen (<anonymous>:0:140239)
    at openDatabase (<anonymous>:0:136332)
    at sqlite3_open (<anonymous>:0:135515)
    at open (<anonymous>:0:2665)

Running with deno run d2.ts -A

[millette]

Also works if I end the script with

data.done();

// Close connection
db.close(); // no true

But again, the filename is giving a permission problem either -A or both allow-read/allow-write.

[dyedgreen]

Ah, I see! You are logging the returned rows object to console, which will not consume the rows iterator. Thus the database transaction remains open, which causes the issue. The logging function provided in the playground will automatically consume the iterator to produce the pretty tables.

To resolve the issue you'd need to close the transaction, either by using it's iterator or by calling .done():

// ...
for (const row of data)
  console.log(row);
// or
data.done();
// ...

[dyedgreen]

Yes, using a file will require both read and write permission on the file, since SQLite will need write-access to the file when you make any queries changing the database. Opening a database in read-only mode is currently not supported. However if you are concerned with security you can grant the permission on that file only.

If you require read-only access options, please open a separate issue with a feature request 😄

[millette]

It's not really a feature request. I just can't get it to run when using

const db = new DB("bobo.db");

Whatever permissions I give to deno. I can open another issue if you prefer. It was working fine with 1.0.0 - but I'm really just staring with deno (although I understand the permission concepts).

[dyedgreen]

Yeah, there was a change compared with the 1.0.0 version, which means that now the module can make much better guarantees for persisting transactions to disk. But on the flip side can no-longer open files in read-only mode. Your code will run if you run with the options:

deno run --allow-read --allow-write dt2.ts

Also: As far as I know you need to pass the permission flags before the file being run, otherwise they are interpreted as arguments to the script:

Running with deno run d2.ts -A

Should be deno run -A d2.ts

[millette]

Yikes, sorry!!

deno run -A d2.ts # YES, it works
deno run d2.ts -A # NOPE, I wasted your time

Thanks again!

[dyedgreen]

No worries, happy to help! 😃