Support for exporting/serializing/deserializing

[fabiospampinato]

It'd be nice if the following got implemented:

• Support for backing up a database into a separate file, something like sqlite3 foo.db ".backup 'foo.db.bak'" interally.
• Support for serializing a database to a Uint8Array, which is like a backup but the file is read and deleted afterwards.
• Support for deserializing a serialized database, which is kinda like :memory: but the buffer is written in the temp file.

[WebReflection]

Support for backing up a database into a separate file

an sqlite3 db backup is a copy ... an fs.copyFile, not a SQL command, unless you want the export as .csv or something else, but you have the sqlite3 CLI for that, no need for this module?

Support for serializing a database to a Uint8Array, which is like a backup but the file is read and deleted afterwards.

not clear ... the db as blob is already usable as Uint8Array and even sqlite on the client. what is that you need here?

Support for deserializing a serialized database, which is kinda like :memory: but the buffer is written in the temp file.

not clear again ... maybe writing down use cases would help ... but it looks more and more you are not interested in this module use case, as you are asking too much blobs stuff around ... may I suggest again not to use this project, if blobs is all you care about?

[fabiospampinato]

an sqlite3 db backup is a copy ... an fs.copyFile, not a SQL command, unless you want the export as .csv or something else, but you have the sqlite3 CLI for that, no need for this module?

Actually that's not how it works, you can't just copy the main database file and expect things to just work. First of all there are multiple files potentially that SQLite uses, but also say you are in the middle of a transaction, you clone the database, the transaction fails, SQLite rolls back the transaction using data that it stored in the journal potentially. In your cloned file the data of this secondary journaling file just doesn't exist, and you copied the file after the transaction started but before the transaction ended, you just got a broken database if you do it like that in general. Like that's just unsafe, in general.

not clear ... the db as blob is already usable as Uint8Array and even sqlite on the client. what is that you need here?

I'm saying to export the entire database as a Uint8Array, like a more programmatic way to do a backup basically.

not clear again ... maybe writing down use cases would help ... but it looks more and more you are not interested in this module use case, as you are asking too much blobs stuff around ... may I suggest again not to use this project, if blobs is all you care about?

This is like the programmatic way to restore a backup kind of, you've got the entire database as a Uint8Array and you make a usable database out of it.

There are all things that better-sqlite3 can do https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md#backupdestination-options---promise .

The need for a reliable way to make a backup seems clear enough, the programmatic APIs I'm not sure when they could be used in practice.

. but it looks more and more you are not interested in this module use case, as you are asking too much blobs stuff around ... may I suggest again not to use this project, if blobs is all you care about?

These suggestions are unrelated to my need to put files somewhere, besides I also have other more relational kind of needs too. In any case I'm kind of writing my own little wrapper, I'm just sharing some little potential things that could be improved or that are missing as I find them.

By the way I was just thinking even if one could make a copy just with fs.copyFile, which one can't, in general, that's not possible for the in-memory database because the path to its temporary file is not exposed currently by this module.

Just throwing out some ideas, it doesn't actually matter to me if these get implemented here or not.

[WebReflection]

Actually that's not how it works

OK, if you have different journal, fair enough, so let me give you a solution:

sqlite3 file.db '.backup file.bck'

that's it? you can do that either via the exported query or directly via CLI ... then you can grab that as blob and read it as whatever typed array you like.

await query`.backup "${raw`backup-${Date.now()}.db`}"`;

[fabiospampinato]

Yeah that's basically it really, or I least I implemented it that way, potentially it could be interesting to stop the module from making mutating queries to the database while a backup is in progress or something, I don't know enough about how the backup process works in practice to know if that would help though. I suppose by using query other requests made from the same process are already queued.

[WebReflection]

potentially it could be interesting to stop the module from making mutating queries to the database

every sqlite3 can connect in the meanwhile, and so would do clusters when the DB is the same ... I don't think better-sqlite or any other can really guarantee that, but AFAIK the backup will execute after any commit happens, or rollback, so I am not sure what issue we are trying to solve here, if plain standard SQLite is the answer ;-)