SqliteError: file is not a database

[raphael10-collab]

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?

[m4heshd]

Are you sure the DB is encrypted? Try commenting out the following line and check if it connects.

infopiecesDb.pragma("key='secret-key'");

Also, is this a preexisting DB? Or does your application create one?

[raphael10-collab]

At the very first use the app should create a brand new DB, while afterwards it should open the previously created one

With :

let infopiecesDbpath = getInfopiecesDbpath()

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

I still get qliteError: file is not a database error

[m4heshd]

Did you clear any existing files before you try that? You should try that without any existing files. Let your application create the DB.

At the very first use the app should create a brand new DB, while afterwards it should open the previously created one

If this is the case, you need to write some logic to check if the DB already exists, create one if not, encrypt it and close the DB connection once for the encryption to take effect. Then reopen with the key. This is important. Also try rekey PRAGMA the first time you're encrypting the DB. One more thing, make sure to VACUUM the DB once it's encrypted.

function encryptNewDB(db, key) {
    db.pragma(`rekey='${key}'`);
    db.prepare('VACUUM').run();
    db.close();
}

[raphael10-collab]

I'm missing something or doing something wrongly:

export const encryptNewDB(db: typeof Database, key: string) => {
    db.pragma(`rekey='${key}'`);
    db.prepare('VACUUM').run();
    db.close();
}

https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#new-databasepath-options : Creates a new database connection. If the database file does not exist, it is created.

let Database = require('better-sqlite3-multiple-ciphers')
const infopiecesDb = new Database(infopiecesDbpath, { verbose: console.log })

const secret_key = "my-secret-key"
infopiecesDb.pragma(`key='${secret_key}'`)  // Encrypt the database
infopiecesDb.close() // Close the DB connection once, for the encryption to take effect

encryptNewDB(infopiecesDb, secret_key)

I'm getting this error: Uncaught Exception: TypeError: The database connection is not open at Database.pragma (/home/raphy/Playground/.webpack/main/index.js:321:27)

The error points to this line :

db.pragma(`rekey='${key}'`);

[m4heshd]

It is indeed very odd. Can you create a minimal reproduction repo so I could do a little bit of debugging? Just make sure that I can build and run the minimal application without doing extra work. Time's my enemy at the moment.

[raphael10-collab]

I've created this minimal reproduction repo: https://github.com/raphael10-collab/BetterSqlite3MultipleCiphersElectron.git

Steps to reproduce the error:

0) git clone https://github.com/raphael10-collab/BetterSqlite3MultipleCiphersElectron.git
1) cd BetterSqlite3MultipleCiphersElectron
2) yarn
3) yarn start 

[m4heshd]

Unfortunately I couldn't attend to this sooner. Did you manage to solve this issue?

[raphael10-collab]

No. I didn't. What could we do to solve the issue?

[mikelmi]

You can try to change "pragma legacy" value. For example "DB Browser (SQLCipher)" software uses "SQLCipher 4 defaults" option. Then you can decrypt such database this way:

const db = new Database('/path/to/db');
db.pragma('cipher=sqlcipher');
db.pragma('legacy=4');
db.pragma('key=secret');

[m4heshd]

"DB Browser (SQLCipher)" software uses "SQLCipher 4 defaults" option.

This issue is not related to SQLCipher and legacy mode is only valid for SQLCipher DBs that has an encrypted header. In OP's case, Sqleet is being used.

@raphael10-collab I've finally got to debugging your code and you have a lot of issues in there. Mostly it's a bit messy. The biggest problem you're having at the moment is that you have an already created, unencrypted database in your source and your build process keeps copying it to the .webpack\data directory. Then when your application tries to open it as an encrypted DB which leads to the invalid file error.

First remove the infopieces DB from src\data and try the code in following order.

index.js:

const Database = require('better-sqlite3-multiple-ciphers');
const infopiecesDb = new Database(infopiecesDbpath, {verbose: console.log});
const secret_key = "my-secret-key";

infopiecesDb.pragma(`key='${secret_key}'`);
initializeInfopiecesDB(infopiecesDb);
infopiecesDb.close();

This works perfectly fine and creates a new DB with encryption.

Also webpack parses your SLQ incorrectly if you put them inside template literals ( ` ). So it should look like this.

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

[raphael10-collab]

Thank you @m4heshd

There must be something to fix also in the insert operation:

in App.tsx :

  const sendMsgToDbFunct = () => {
    window.api.send("insert-infopiece-intodb", ip_01)
  }

<button onClick={() => sendMsgToDbFunct()}>SendToDb</button>

in .src/main/index.ts :

import { getInfopiecesDbpath } from '../common/infopiecesDb/initializeInfopiecesDb'
let infopiecesDbpath = getInfopiecesDbpath()
console.log("let infopiecesDbpath = getInfopiecesDbpath(): ", infopiecesDbpath)

import { initializeInfopiecesDB } from '../common/infopiecesDb/initializeInfopiecesDb'
import { insertInfopiece } from '../common/infopiecesDb/sync'

const Database = require('better-sqlite3-multiple-ciphers');
const infopiecesDb = new Database(infopiecesDbpath, {fileMustExist: true; verbose: console.log});
const secret_key = "my-secret-key";

infopiecesDb.pragma(`key='${secret_key}'`);
initializeInfopiecesDB(infopiecesDb);

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

ipcMain.handle("insert-infopiece-intodb", (event, args) => {
  console.log("ipcMain.on-insert-infopiece-intodb-args: ", args)
  infopiecesDb.prepare(insertIP).run(args)
})

infopiecesDb.close();

Output:

raphy@pc:~/BetterSqlite3MultipleCiphersElectron$ yarn start
yarn run v1.22.19
$ electron-forge start
✔ Checking your system
✔ Locating Application
✔ Preparing native dependencies: 2 / 2
✔ Compiling Main Process Code
✔ Launch Dev Servers
✔ Compiling Preload Scripts
✔ Launching Application

Webpack Output Available: http://localhost:9000

let infopiecesDbpath = getInfopiecesDbpath():  /home/raphy/BetterSqlite3MultipleCiphersElectron/src/data/infopieces
PRAGMA key='my-secret-key'
CREATE TABLE IF NOT EXISTS infopieces (id INTEGER PRIMARY KEY AUTOINCREMENT, infotext TEXT NOT NULL);
PRAGMA INDEX_LIST(infopieces);
⠇ Compiling Renderer CodeIssues checking in progress...
⠋ Compiling Renderer Codeasset main_window/index.js 3.39 MiB [emitted] (name: main_window)
asset ../data/infopieces 12 KiB [emitted] [from: src/data/infopieces] [copied]
asset main_window/index.html 386 bytes [emitted]
runtime modules 26.6 KiB 14 modules
modules by path ./node_modules/ 1.24 MiB 37 modules
modules by path ./src/app/ 7.52 KiB
  modules by path ./src/app/*.css 3.2 KiB
    ./src/app/index.css 2.27 KiB [built] [code generated]
    ./node_modules/css-loader/dist/cjs.js!./src/app/index.css 946 bytes [built] [code generated]
  ./src/app/renderer.ts 1.07 KiB [built] [code generated]
  ./src/app/index.tsx 1.39 KiB [built] [code generated]
  ./src/app/components/App.tsx 1.86 KiB [built] [code generated]
webpack 5.73.0 compiled successfully in 2138 ms
✔ Compiling Renderer Code
Issues checking in progress...
No issues found.
No issues found.
ipcMain.on-insert-infopiece-intodb-args:  { id: 0, infotext: "Today has been raining. It's June ...." }
Error occurred in handler for 'insert-infopiece-intodb': TypeError: The database connection is not open
    at Database.prepare (/home/raphy/BetterSqlite3MultipleCiphersElectron/.webpack/main/index.js:652:21)
    at /home/raphy/BetterSqlite3MultipleCiphersElectron/.webpack/main/index.js:1883:18
    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)

If I comment db.close() :

//infopiecesDb.close();

I get SqliteError: no such table: infopiecesDb

/src/common/infopiecesDb/initializeInfopiecesDb.ts :

import { Database } from 'better-sqlite3'

import * as path from 'path'

import { app } from 'electron'

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()
}

reproduction repo: https://github.com/raphael10-collab/BetterSqlite3MultipleCiphersElectron.git

Steps to reproduce the error:

0) git clone https://github.com/raphael10-collab/BetterSqlite3MultipleCiphersElectron.git 1) cd BetterSqlite3MultipleCiphersElectron 2) yarn

[raphael10-collab]

@m4heshd Would you be so kind in telling me what else and how should be fixed?

[m4heshd]

@raphael10-collab Sorry about not being able to assist you with this issue further. Current issue you're having seems to be out of scope for both this library and for me personally since it's not an issue directly with library. I would have to go out of my way with the very little time I have on my hands to debug your code just like before. Perhaps you might have more luck on a platform like StackOverflow?

Also my suggestion here would be to make yourself a lot more familiarized with this particular template you're using as your boilerplate code. This is a very common issue with opinionated code like this. Other than the contributors, users hardly understand the patterns, execution order, limitations, best practices...etc when it comes to opinionated templates like this. Your code involves a lot of pre-processing (one of my personal reasons for avoiding typescript) which means the output which executes might be a lot different from what you actually coded.

So take a few good looks into the template and check if you're doing something it doesn't agree with. I'll try to assist with debugging as soon as I'm able to take a breather.

[SethCohen]

Hi, I'm receiving the same error, but it seems to be somewhat different from the first person. Rather than receiving the error on the first db creation, I receive the error on if using db.pragma("rekey='...'") on an already existing db a second time. e.g.

import Database from 'better-sqlite3-multiple-ciphers';

function encryptNewDB(db, key) {
    db.pragma(`rekey='${key}'`);
    db.prepare('VACUUM').run();
    db.close();
}

const db = new Database(`./test.sqlite`);
encryptNewDB(db, 'test');

If one were to run this code once, it'd successfully create a new db file and no error gets thrown. But if you were to run it again, it would fail at the db.pragma bit and throw:

SqliteError: file is not a database
    at Database.pragma (/home/sethdev/WebstormProjects/sandbox-test/node_modules/better-sqlite3-multiple-ciphers/lib/methods/pragma.js:10:27)
    at encryptNewDB (file:///home/sethdev/WebstormProjects/sandbox-test/src/main.js:4:8)
    at file:///home/sethdev/WebstormProjects/sandbox-test/src/main.js:10:1
    at ModuleJob.run (node:internal/modules/esm/module_job:193:25)
    at async Promise.all (index 0)
    at async ESMLoader.import (node:internal/modules/esm/loader:541:24)
    at async loadESM (node:internal/process/esm_loader:91:5)
    at async handleMainPromise (node:internal/modules/run_main:65:12) {
  code: 'SQLITE_NOTADB'
}

I'm suspecting that maybe the only true error here is just not having a check on if a database already exists or if its actually already encrpyted?

This honestly doesn't actually effect much really as the db file does still gets create and encrypted on the first call and stays existing and still interactable, but in my use-case it was a bit confusing to wonder why such an error was being thrown randomly at times until I pinned it down to just "some of my users are calling my create a new encrypted db function multiple times sometimes". The error essentially just really comes down to relaying the wrong kind of info, which can lead to confusion.

[m4heshd]

Rather than receiving the error on the first db creation, I receive the error on if using db.pragma("rekey='...'") on an already existing db a second time.

This right here is the issue. You cannot rekey an already encrypted DB because SQLite can't even recognize the file in the first place prior to decryption. You're better off using key PRAGMA here instead of rekey because key can do both of these jobs.

[SethCohen]

Yeah, I had already figured out that was the case, haha.

I was just thinking it would be nice for a more informational error (Such as SqliteError: database is already encrypted or something) that would make things more understandable for those who were in my previous situation; Not realizing that the error was not from a database simply not being encrypted, but that an already existing database was already encrypted.

Its probably not possible to implement such a thing though because of the

because SQLite can't even recognize the file in the first place

bit, sadly, aye?

[m4heshd]

Its probably not possible to implement such a thing though because of the

because SQLite can't even recognize the file in the first place

bit, sadly, aye?

Yup. Closing this issue since it's not an actual issue anymore.

[FunctionDJ]

You can try to change "pragma legacy" value. For example "DB Browser (SQLCipher)" software uses "SQLCipher 4 defaults" option. Then you can decrypt such database this way:

const db = new Database('/path/to/db');
db.pragma('cipher=sqlcipher');
db.pragma('legacy=4');
db.pragma('key=secret');

This helped me with my issue where i got the same error message! (But a different cause obviously) Thanks a lot :) I had to run these 3 pragma lines:

db.pragma("cipher=sqlcipher");
db.pragma("legacy=4");
db.pragma("key = '...'");

instead of this code with @journeyapps/sqlcipher:

db.run(`PRAGMA cipher_compatibility = 3`);
db.run(`PRAGMA key = '...'`);

In my case it's the master.db SQLCipher database used by Pioneer DJ Rekordbox 6.

[m4heshd]

@FunctionDJ There's a whole page of documentation on legacy mode usage of SQLite3MC docs, both here and here. Always go through the documentation when you're getting ready to use a new library.

Also I'm gonna add this specific use case to the documentation here since people hardly read the third-party docs and this seems to come up a lot.

[FunctionDJ]

@m4heshd i did actually read that but didn't know what version the database uses because i didn't create it. it's made from a closed-source, proprietary software. at least i searched through issues to find a solution and didn't open a new one xD