.key is not encrypting existing database

[cezarsmpio]

Hello everyone, great work on this library. I have the following case, existing databases and first run databases. According to the documentation of .key it says:

Decrypts or encrypts the database using provided key.

But it's not working as expected I guess? It works fine if I run .rekey before.

I currently use typeorm and Electron with an Express server and this is my code:

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

const options: DataSourceOptions & SeederOptions = {
  type: 'better-sqlite3',
  driver: BetterSqliteMultipleCiphers,
  database: databasePath,
  synchronize: isDev,
  logging: true,
  migrationsRun: true,
  logger: isDev ? 'simple-console' : new DbLogger(),
  entities: [
     // list of entities
  ],
  migrations: [],
  subscribers: [],
  seeds: [],
  factories: [],
  prepareDatabase: (db) => {
    if (!process.env.DATABASE_ENCRYPTION_KEY)
      throw new Error('No databse encryption key found');

        const encryptDb = () => {
      db.pragma(`cipher='sqlcipher'`);
      db.pragma('legacy=4');
      db.pragma(`rekey='${process.env.DATABASE_ENCRYPTION_KEY}'`);
      db.close();
      app.relaunch();
      app.quit();
    };

    if (firstRun) {
      return encryptDb();
    }

    const result = db.key(
      Buffer.from(process.env.DATABASE_ENCRYPTION_KEY as string)
    );

    if (result === 0) {
      return encryptDb();
    }
  },
};

const dataSource = new DataSource(options);

The error I get is:

ERROR (12319): [SQLite Database] Error on initializing db {"code":"SQLITE_NOTADB"}
02:56:00.147 › SqliteError: file is not a database
    at Database.prepare

Unfortunately the try...catch I put inside the prepareDatabase method doesn't throw any error, it actually passes, the error happens when it tries to execute a query on the lifecycle of the application.

If I only use:

prepareDatabase: async (db) => {
  if (!process.env.DATABASE_ENCRYPTION_KEY)
    throw new Error('No databse encryption key found');

  db.pragma(`cipher='sqlcipher'`);
  db.pragma('legacy=4');
  db.pragma(`key='${process.env.DATABASE_ENCRYPTION_KEY}'`);
},

This is the error I get:

ERROR (12462): [SQLite Database] Error on initializing db {"code":"SQLITE_NOTADB"}
02:57:13.233 › SqliteError: file is not a database
    at Database.prepare

Is there a recommended way to check if the database is encrypted already? So I can choose to either run .key or .rekey?

Thank you!

[m4heshd]

Sorry for the delayed response.

Unfortunately the try...catch I put inside the prepareDatabase method doesn't throw any error, it actually passes, the error happens when it tries to execute a query on the lifecycle of the application.

This is expected behavior since the pages are decrypted only when you query any data.

Is there a recommended way to check if the database is encrypted already? So I can choose to either run .key or .rekey?

Unfortunately, the only method is to try to decrypt and look for any errors. There's no dedicated way to check the encryption status.

I am closing this issue since no further solutions are available. Feel free to reopen if you have any updates.

[cezarsmpio]

Hey @m4heshd , thanks for your reply!

Shouldn't .key work seamlessly then? It should encrypt/decrypt automagically? Just based on documentation :)

I found a particular solution to migrate existing users to a new database, thank you!

[m4heshd]

Shouldn't .key work seamlessly then? It should encrypt/decrypt automagically? Just based on documentation :)

It follows the behavior of SQLite's own SEE for compatibility.

Just took a look at your code again. Need to verify something. Can you try the same code with key PRAGMA instead of the key() function?

[cezarsmpio]

Hey @m4heshd same behavior either using .key or pragma key.

[m4heshd]

@cezarsmpio Sorry for not responding sooner. Another issue opened recently made me remember what exactly is happening here. Please take a look at https://github.com/m4heshd/better-sqlite3-multiple-ciphers/issues/88#issuecomment-1998680385.