Calling the backup function throws an exception for encrypted databases.

[nasi-lemak]

Previously, I was able to perform backups by calling the backup function. However, after SQLite3MultipleCiphers fixed the issue utelle/SQLite3MultipleCiphers#158 - "add check to verify compatibility of source and target database in backup operation" in version 1.8.6, I am unable to backup my encrypted database using better-sqlite3-multiple-ciphers. I keep receiving an error saying "backup is not supported with incompatible source and target databases" when I call the backup function. Is there a new way to perform the backup function? Could you please guide me through it?

[m4heshd]

Even though I maintain this library, I don't use it anymore. So I can't give you a very current solution to this other than what I used to practice with my own projects because this was a concerning issue for me as well back in the day. I can't remember exactly why the backup functionality didn't work for me but I do remember that it didn't work and it's the reason why I had to come up with my own solution.

First, you must be aware of how you maintain your connection pool. Assuming you're using mult-threaded connections, make sure to only open one connection per thread. No more than that because that complicates everything including your management of WAL journal files. You should have full control over your connection pool for you to properly close any open connection at any time.

Then all you have to do is manually run the backup process simply with a file copy operation. To do this properly, you need to make sure to force the DB to run a checkpoint. You can manually run a checkpoint but I prefer closing all connections which automatically triggers a checkpoint from the final remaining connection. This also makes sure that there are no open file handles to the DB which makes file operations on the DB easier.

All you need to do before a copy operation is to make sure that the journal files are gone and the only remaining file is the DB itself. Running a checkpoint properly should get rid of those extra files. Check for the journal files and simply copy the DB file over to your preferred location.

This is the method I used for backup operations in all of my commercial projects that use this library and it has never failed. Those applications have been functional for years.

[nasi-lemak]

@m4heshd Thanks for pointing me to the right direction. I have figured out a way the backup the file on my Electron app. The problem I initially faced when performing a copy operation was that if I had database changes, if I only copied the database file, the backup database would not contain the database changes. To get rid of the journal files and save the changes into the database file, I had to close the database connection.

However I wanted to keep my current database connection open, so I went with the following workaround: copy the database file and journal files, then open and close the connection to the backup database file. Here is a sample code snippet for whoever might need this.

    // Copy the database file to the backup location
    fs.copyFileSync(databaseFilePath, backupDirectory + `/backup_database.db`)
    fs.copyFileSync(databaseFilePath + `-wal`, backupDirectory + `/backup_database.db-wal`)
    fs.copyFileSync(databaseFilePath + `-shm`, backupDirectory + `/backup_database.db-shm`)
    let backupDatabase = new Database(backupDirectory + `/backup_database.db`)
    // Open a connection to the encrypted database
    backupDatabase.pragma(`cipher='aes256cbc'`)
    backupDatabase.key(Buffer.from('your-secret-key' ?? ''))
    backupDatabase.pragma('journal_mode = WAL')
    // Close the connection to the encrypted database
    backupDatabase.close()

[m4heshd]

@nasi-lemak There's a very high possibility of that failing because not all OSs treat the open file handles the same way.

As I mentioned earlier, your file operations could throw EBUSY at any moment. I know it doesn't make sense for a copy operation. But it sometimes does because of the locking applied to the journal files when a connection is active. Also, make sure that checks are in place to verify if the journal files are there.