The shell doesn't do binary input or output. There are various SQL functions to convert blob values to/from textual representations (hex/unhex, base64, base85).

The .backup command lets you make a block-level copy of an entire database file.

The .binary command just turns off end-of-line translation (LF to CR+LF) if you're running on Windows.

If you use .mode insert then the sqlite shell emits binary blobs as hex strings like:

INSERT INTO table VALUES(X'1234567890abcdef')

But for this job it may be simpler to write the 50 lines of C that would be required to dump the binary content directly.

The .binary command just turns off end-of-line translation (LF to CR+LF) if you're running on Windows.

Nowhere in the official SQLite documentation states this. If that is the official effect of using this command then the help message should explicitly state that.

.binary on|off Turn binary output on or off. Default OFF

Well in that case the .binary command is entirely misleading, it should be .eol or .crlf on|off for that effect.

From an end user's POV one would think .binary on would turn on actual binary output and not be a dummy function on non-Windows platforms.

You're acting like SQLite invented this nomenclature -- or have you submitted the same complaint about python's open(..., 'wb')?

The default is to use text mode, which may convert 'n' characters to a platform-specific representation on writing and back on reading. Thus, when opening a binary file, you should append 'b' to the mode value to open the file in binary mode, which will improve portability. (Appending 'b' is useful even on systems that don’t treat binary and text files differently, where it serves as documentation.) See below for more possible values of mode.

Or perhaps ios::binary from C++?

I'm not necessarily saying it's a good term, just that for a significant portion of end-users "binary mode" is already well understood to be a dummy function outside of windows.

Might one ask why you are trying to do this - what goal are you ultimately wanting to achieve?

Your request and subsequent frustrations make this feel very much like an XY problem.

There are, for example, many easy ways of inserting binary data from a file directly into a blob in the database (and vice versa) using the CLI.

This isn't what you have asked for, of course, but it is strange that you are trying to get the CLI to return raw disc content as binary data to you - it's not obvious what problem you think this is the best solution for.

Well the aim is to use the sqlite shell to backup the individual pages in a large sqlite db into a read only filesystem such as squashfs,

using inspiration from earlier threads Hot backup database in WAL mode by coping and Use SQLITE_DBPAGE to backup .

But it looks like after this thread was made .binary was made deprecated, which is a step backwards and doesn't solve my backup issue.

But it looks like after this thread was made .binary was made deprecated

It was replaced with .crnl, which performs the same function. Use .crnl off to disable CR/NL output conversion on Windows.

Ah, so you want to only overwrite/update changed blocks (pages) and [my assumption follows] you don't have enough scratch storage to copy the whole database (followed by a filesystem level backup of changed blocks)?

Or are you just trying to backup the entire database but onto a write-once filesystem (much easier).

Thank you, much clearer problem (though I'm afraid I don't have a clever solution at the moment).

NB - I don't think .binary ever did what you want, or think it used to do.

The approach is quite feasible, but the SQLite shell is the wrong tool for it. You'll have to write some code of your own.

Another old thread you might have missed: s3zip.

It seems I have to contradict myself.

If your POSIX-ish system has /dev/fd/*, you can trick the SQLite shell into dumping binary page data, but it's ugly. "Emergency last resort" level ugly.

sqlite3 dbfile "select '' from sqlite_dbpage() where writefile('/dev/fd/1',data)<0;" | insert-stdin-consuming-command-here

Don't say I didn't warn you!

Yes that definitely looks like some ugly black art wizardry, that I am tempted to use. ;-)