How to save in-memory database to file?

[kavir1698]

I do not find the backup function in SQLite.jl. Thank you.

[felipenoris]

This is interesting feature.
https://www.sqlite.org/backup.html

[quinnj]

Yeah, it seems like all the functionality is there via the C library; if someone wants to take a stab at this that'd be great; happy to provide direction. I don't know that I'll have time for a while to get to this.

[felipenoris]

@quinnj , yes I'm glad to help on this one, but it might take some time to get myself available for this.

[daniel-thom]

The implementation could look something like this. If you all are OK with it, I could write tests and documentation and open a PR. I might need some pointers on full error handling. Let me know and I'll be happy to work on it. I need this functionality. I'd be OK with changes to the interface.

function backup(
    dst::DB,
    src::DB;
    dst_name::AbstractString = "main",
    src_name::AbstractString = "main",
    pages::Int = -1,
    sleep::Float64 = 0.25,
)
    if src === dst
        error("src and dst cannot be the same connection")
    end

    num_pages = pages == 0 ? -1 : pages
    sleep_ms = sleep * 1000
    ptr = C.sqlite3_backup_init(dst.handle, dst_name, src.handle, src_name)
    r = C.SQLITE_OK
    try
        while r == C.SQLITE_OK || r == C.SQLITE_BUSY || r == C.SQLITE_LOCKED
            r = C.sqlite3_backup_step(ptr, num_pages)
            @debug "backup iteration: remaining = $(C.sqlite3_backup_remaining(ptr))"
            if r == C.SQLITE_BUSY || r == C.SQLITE_LOCKED
                C.sqlite3_sleep(sleep_ms)
            end
        end
    finally
        C.sqlite3_backup_finish(ptr)
        if r != C.SQLITE_DONE
            e = sqliteexception(src.handle)
            C.sqlite3_reset(src.handle)
            throw(e)
        end
    end
end

[JohnCobbler]

The backup API added in #347 (released in v1.8.0) covers this. To persist an in-memory database to a file:

db = SQLite.DB()  # in-memory
DBInterface.execute(db, "CREATE TABLE t (id INTEGER, name TEXT)")
DBInterface.execute(db, "INSERT INTO t VALUES (1, 'alice'), (2, 'bob')")

SQLite.backup(db, "snapshot.sqlite")  # writes the whole DB to disk

Reopen it as a normal on-disk database with SQLite.DB("snapshot.sqlite"). Signature: SQLite.backup(db::SQLite.DB, path::AbstractString; sleep_ms::Integer=250), where sleep_ms is the retry delay on SQLITE_BUSY/SQLITE_LOCKED. Verified end-to-end on Julia 1.11. Docs PR with this example: #350.