2

To backup an SQLite database with Node.js (sqlite3 library) I close the connection, copy database file and re-open the connection. After re-opening the connection all queries cause an SQLITE_MISUSE -error.

I have only 1 connection in the application. What is the problem? How can I backup database if the library (and all other Node.js libraries) don't allow to execute the native C backup API's)?

user4157124
  • 2,809
  • 13
  • 27
  • 42
user2957271
  • 182
  • 3
  • 19
  • "*What is the problem?*" Likely SQLite library itself was unloaded instead of database, or database connection is not re-established. Inconclusive without source code nor error code (error codes are a subset of result codes like [`SQLITE_MISUSE`](https://www.sqlite.org/rescode.html#misuse)). From manual: "*If SQLite ever returns SQLITE_MISUSE from any interface, that means that the application is incorrectly coded and needs to be fixed. Do not ship an application that sometimes returns SQLITE_MISUSE from a standard SQLite interface because that application contains potentially serious bugs.*". – user4157124 Sep 18 '22 at 03:22

2 Answers2

1

There's one way I can think of, by creating another database, attaching it to the current connection, and run a joint insert/select operation between them. This SO answer has some information on copying databases in SQLite, and see here for general information on attaching databases

Werlious
  • 583
  • 6
  • 15
0

I think nowadays you can do it with the backup function.

Information about backup (in C):

And looks like npm package sqlite3 exposed said api:

var db = new sqlite3.Database('live.db');
var backup = db.backup('backup.db');
...
// in event loop, move backup forward when we have time.
if (backup.idle) { backup.step(NPAGES); }
if (backup.completed) { ... success ... }
if (backup.failed)    { ... sadness ... }
// do other work in event loop - fine to modify live.db
...

And from the C page looks like backup.step(-1); will backup it all in one call.

I guess it is very useful if you create a :memory: live database and backup/dump once in a while

Here is a similar answer link that suggest using "better-sqlite3"

db.backup(`my-backup.db`)
  .then(() => {
    console.log('backup complete!');
  })
  .catch((err) => {
    console.log('backup failed:', err);
  });
YoniXw
  • 425
  • 5
  • 12