SqliteError: file is not a database

[andykais]

So I have a database file (created by better-sqlite3 over in nodejs land) that I cannot seem to open with this library. I am able to read it from better-sqlite3, the linux sqlite3 cli (3.38.2 2022-03-16) and https://deno.land/x/sqlite3. Any ideas why this wasm library is failing? Is it perhaps because this library was compiled with an older version of sqlite?

[dyedgreen]

This might be the issue? Either way we should probably bump the version, so we might as well and see if that fixes the issues you’re seeing 😊

[dyedgreen]

@andykais can you confirm if upgrading SQLite fixes the issue? 😅

[andykais]

unfortunately this has not fixed my issue @dyedgreen

> import {DB} from 'https://deno.land/x/sqlite@v3.4.0/mod.ts'
undefined
> let db1 = new DB('forager.db')
undefined
> db1.query('SELECT COUNT(*) FROM tag')
Uncaught SqliteError: file is not a database
    at DB.prepareQuery (https://deno.land/x/sqlite@v3.4.0/src/db.ts:250:13)
    at DB.query (https://deno.land/x/sqlite@v3.4.0/src/db.ts:150:24)
    at <anonymous>:2:5

expected output from sqlite cli is shown below:

> sqlite3 forager.db
-- Loading resources from /home/andrew/.sqliterc
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> SELECT COUNT(*) FROM tag LIMIT 1;
COUNT(*)
--------
29745   

[andykais]

can this issue be reopened @dyedgreen? This issue is still real. If there are any other debug steps I can take to help please let me know

[raphael10-collab]

I'm having the same problem:

I get this error: SqliteError: file is not a database

Following the indications found here: https://stackoverflow.com/questions/48974135/sqlite-error-file-is-not-a-database I double-checked that the path does not contain .db extension:

export const getInfopiecesDbpath = () {
  const isEnvDevelopment = process.env.NODE_ENV === 'development'

  const infopiecesDbpath = isEnvDevelopment
    ? path.join(app.getAppPath(), "src", "data", "infopieces")
    : path.join(app.getAppPath(), ".webpack", "data", "infopieces")
  return infopiecesDbpath
}

const createInfopiecesTable: any = `
  CREATE TABLE IF NOT EXISTS infopieces (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    infotext TEXT NOT NULL
  );`

const createInfopiecesIndex = `PRAGMA INDEX_LIST(infopieces);`
 
export function initializeInfopiecesDB(db: Database) {
  db.prepare(createInfopiecesTable).run()
  db.prepare(createInfopiecesIndex).get()
}

const insertIP = `
    INSERT INTO infopiecesDb (id, infotext)
    VALUES (ip.id, ip.infotext)
  `

export const insertInfopiece = (ip: IInfopiece) => {
  infopiecesDb.prepare(insertIP).run(ip)
}

let infopiecesDbpath = getInfopiecesDbpath()
let infopiecesDb = require('better-sqlite3-multiple-ciphers')(infopiecesDbpath, { verbose: console.log })
infopiecesDb.pragma("key='secret-key'")

initializeInfopiecesDB(infopiecesDb)

in main :

let infopiecesDbpath = getInfopiecesDbpath()
let infopiecesDb = require('better-sqlite3-multiple-ciphers')(infopiecesDbpath, { verbose: console.log })
infopiecesDb.pragma("key='secret-key'")
initializeInfopiecesDB(infopiecesDb)


ipcMain.handle ("insert-infopiece-intodb", (IpcMainEvent, message) => {
  console.log("ipcMain.hanlde-insert-infopiece-intodb-message: ", message.ip_01)
  insertInfopiece(message.ip_01)
})

I get this error:

Error occurred in handler for 'insert-infopiece-intodb': SqliteError: file is not a database
    at Database.prepare (/home/raphy/Playground/.webpack/main/index.js:652:21)
    at insertInfopiece (/home/raphy/Playground/.webpack/main/index.js:1773:18)
    at /home/raphy/Playground/.webpack/main/index.js:1943:32
    at node:electron/js2c/browser_init:193:579
    at EventEmitter.<anonymous> (node:electron/js2c/browser_init:161:10433)
    at EventEmitter.emit (node:events:390:28) {
  code: 'SQLITE_NOTADB'
}

Other info:

"@types/better-sqlite3": "^7.5.0",
"better-sqlite3": "^7.5.3",
"better-sqlite3-multiple-ciphers": "^7.5.2",
"electron": "17"
 node:  v16.15.0

What am I missing and / or doing wrongly?

[dyedgreen]

@andykais do you have a minimal example to reproduce the issue by any chance? I don’t think we can make progress on debugging the issue otherwise 😅

[jsejcksn]

do you have a minimal example to reproduce the issue by any chance? I don’t think we can make progress on debugging the issue otherwise 😅

@dyedgreen I am seeing the same issue when trying to read SQLite databases created by the Deno CLI — the ones used to back its window.localStorage API.

They are located at paths following this pattern: $DENO_DIR/location_data/$MODULE_LOCATION_HASH/local_storage, and the file CLI utility says this about them:

% file local_storage
local_storage: SQLite 3.x database, last written using SQLite version 3038002

You should be able to reproduce by writing some data to local storage with Deno, and then locating the created database. Simply running this in your terminal will create one:

% deno eval "localStorage.setItem('hello', 'world')"

[dyedgreen]

This does reproduce the problem, I'll see if I can figure out what is not working here!

[dyedgreen]

Aha! The issue with this file (and presumably the others that were reported) is that they use PRAGMA journal_mode = wal, which is not supported by the VFS.

Since WAL is not supported in the VFS, we also build with -DOMIT_WAL, which is why you get the “file is not a database” error rather than “failed to open database” (which you get if you don’t build with that flag).

There is not much we can do to fix this issue; supporting WAL is non trivial in deno / JS since it requires memory-mapping the journal file / mmap support in the VFS.

[jsejcksn]

@dyedgreen Thanks for investigating. Perhaps the error message can be changed to better describe what's happening in this scenario: as written, it is misleading and will likely cause more confusion in the future if left unchanged.

[andykais]

ah of course 🤦 I am using WAL mode, that is why I cannot open it with the wasm implementation

[n8jadams]

I'm writing a script that syncs my music from my jellyfin server, which uses the library.db, which is PRAGMA journal_mode = wal; https://github.com/jellyfin/jellyfin/blob/1b4394199a2f9883cd601bdb8c9d66015397aa52/Emby.Server.Implementations/Data/BaseSqliteRepository.cs#L63 This incompatibility is a dealbreaker and I'll have to rewrite in either node or bun or something else unfortunately.