Restore/backup from/to shell commands?

(1) By Mark Lawrence (mark) on 2022-06-22 10:02:49 [link] [source]

Is there a way to pass an SQLite database into the CLI via a shell command? Likewise, can I backup a database to a shell command?

The .read and .once commands interpret a "|" in their arguments to provide this kind of functionality for SQL statements, but I don't see the equivalent for .backup, .open, .save or .restore.

I have tried using .open --deserialize '|cat db.sqlite3' as a test but that doesn't seem to result in any change to the current database (or any kind of error for that matter). Any other ideas?

(2) By kuup (kuup22) on 2022-06-22 10:26:45 in reply to 1 [link] [source]

I am not sure if I correctly interpret your question.

One way you can restore / backup  via shell command is like this:

sqlite3 db ".dump" | your_backup_commands

your_restore_commands | sqlite3 db

in both cases nothing else should try to access the database during these actions

(3) By Larry Brasfield (larrybr) on 2022-06-22 13:08:57 in reply to 1 [source]

Is there a way to pass an SQLite database into the CLI via a shell command?

If you have built the dbtotxt utility, it can render a SQLite3 database in a text format known as a "hexdb". Such output could be read into an in-memory database using an invocation such as this (for a *Nix-like OS):

.read "|echo .open -hexdb && ./dbtotxt whatever.sdb"

Likewise, can I backup a database to a shell command?

Have you considered the .dump command?

(4) By Richard Hipp (drh) on 2022-06-22 13:16:52 in reply to 3 [link] [source]

.read "|echo .open -hexdb && ./dbtotxt whatever.sdb"

I wonder, should we add a command-line option to dbtotxt that automatically adds the ".open --hexdb" line to the front? Would that make this magic incantation a little easier to understand?

.read "|./dbtotxt --for-cli whatever.db"

I would use such a command-line option (whatever it might be called) if it existed. For example, when trying to come up with repro cases for problems, I frequently generate a CLI script that starts with a database file in the ".open --hexdb" format. I normally just run "dbtotxt" and then edit the output to add the ".open --hexdb" line. But if I could do it all in one step, that might be nice.

Just a thought....

(5) By Larry Brasfield (larrybr) on 2022-06-22 14:05:59 in reply to 4 [link] [source]

On trunk now.

As I answered the OP, I was wondering if the shell should be able to do what dbtotext does, using the same source with some added munging.

(6) By Richard Hipp (drh) on 2022-06-22 14:12:18 in reply to 5 [link] [source]

I'm open to having some enhancements along those lines. But let's wait until we start the 3.40.0 development cycle before working on them.

(7) By Mark Lawrence (mark) on 2022-06-22 18:19:01 in reply to 1 [link] [source]

To expand a little, consider the case that I have an SQLite database that is not stored on the filesystem. It could be a blob in another database or it could be coming across the network. I'm trying to avoid:

the cpu overhead (of (de-)serializing back and forth to SQL, index creation) each time I read or write; or
the disk overhead of writing/reading to the fs when it's already in memory and/or just going out the network
security complexity of having to write to the filesystem at all

I guess my question is better worded like this: are the sqlite3_(de)serialize() functions ever used by the CLI for I/O? I think that the dbtotext/-hexdb option is probably only a minor improvement over .dump/.read for my use case.

(8) By Larry Brasfield (larrybr) on 2022-06-22 18:42:55 in reply to 7 [link] [source]

There is a --deserialize option for the .open command which. However, with that option given, it still expects to read a named, file-like object. I suppose (without having tried it) that a *Nix FIFO might be read that way.

At present, the CLI does not use the sqlite3_serialize API.

Use cases such as you describe are among the motivations for adding export and import plug-in support to the extensible shell that is in development.