CODEWITHSUNDEEP
node.jssqlitebackup
user2957271
user2957271

Reputation: 192

Backup database

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)?

Upvotes: 2

Views: 2753

Answers (2)

YoniXw
YoniXw

Reputation: 585

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

Upvotes: 1

Werlious
Werlious

Reputation: 594

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

Upvotes: 1

Related Questions