Expose SQLite's `sqlite3_recover` API in `sqlite3`?

[krassowski]

Feature or enhancement

Proposal:

SQLite 3.40.0 (November 2022) introduced a public recovery API for recovering data from corrupted databases: sqlite3_recover_init(), sqlite3_recover_step(), and sqlite3_recover_finish().

The sqlite3 standard library module already wraps somewhat comparable C APIs, like Connection.backup() wraps sqlite3_backup_* or Connection.blobopen() wraps sqlite3_blob_*, although maybe none of these are as involved as the recover extension.

Recovery currently requires users to shell out to the sqlite3 CLI binary, which is not always available. A GitHub serach for "subprocess.run" "sqlite3" ".recover" demonstrates that Python users often use subprocess to access this functionality, and often have dedicated exception handling for when sqlite3 binary is absent.

Potential API

This could be either a single recover() method, or three methods mirroring the C API. I am not opinionated, and frankly not knowledgeable enough on the topic to advocate for any particular API but for the sake of argument one could imagine mirroring the options of .recover CLI with:

Connection.recover(target, *, lost_and_found=None, ignore_freelist=False, no_rowids=False)

Maybe it could raise sqlite3.DatabaseError on unrecoverable failure.

Build / source availability

Since SQLite 3.41.0 includes sqlite3r.c Makefile target - an alternate amalgamation that bundles sqlite3recover.c and dbdata.c alongside the standard sqlite3.c.

Has this already been discussed elsewhere?

This is a minor feature, which does not need previous discussion elsewhere

Links to previous discussion of this feature:

Relevant SQLite versions:

• SQLite 3.29.0 (2019-10-04) added .recover CLI option
• SQLite 3.40.0 (2022-11-16) added the recovery extension
• SQLite 3.41.0 (2023-02-21) added the sqlite3r.c amalgamation that includes the recovery extension.

Previous discussions (outside of cpython, but still relevant IMO):

• Initially, in August 2022 there was a resistance to add the recovery to public (to discourage silent/automatic use) as seen in this thread applauding .recover CLI
• Perl bindings (DBD-SQLite) explored it back at the time in 2022 ([feature] .recover as a driver function DBD-SQLite/DBD-SQLite#102) but owning to lack of the recovery extension they did not proceed with it
• Rust bindings (rusqlite) explored it back in May 2023 but seemingly did not know about the new sqlite3r.c amalgamation Add Recovery Extension Support rusqlite/rusqlite#1333

I would be interested in some kind of indication if this could potentially be accepted or if the slow adoption elsewhere and absence in the default amalgamation disqualify this extension as of 2026 (which of course can change later on).

[picnixz]

if the slow adoption elsewhere and absence in the default amalgamation disqualify this extension as of 2026

I would suggest that you open a DPO thread with the history you already provided. The minimum SQLite version is however much lower than 3.29, so this API would not be universally enabled. I also think that the lack of support elsewhere would makes this more suitable for a third-party extension instead.

[krassowski]

I would suggest that you open a DPO thread with the history you already provided.

Thank you, I opened https://discuss.python.org/t/expose-sqlites-sqlite3-recover-api-in-sqlite3/