How to save in-memory database to file? #210

Activity

1. felipenoris commented on Mar 10, 2020

   

   felipenoris

   on Mar 10, 2020

   Contributor

   More actions

   This is interesting feature.
   https://www.sqlite.org/backup.html
2. quinnj commented on Mar 20, 2020

   

   quinnj

   on Mar 20, 2020

   Member

   More actions

   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.
3. felipenoris commented on Mar 20, 2020

   

   felipenoris

   on Mar 20, 2020

   Contributor

   More actions

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

   added

   help wanted

   on Oct 7, 2020
5. daniel-thom commented on Apr 20, 2024

   

   daniel-thom

   on Apr 20, 2024

   More actions

   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
6. JohnCobbler

   added a commit that references this issue on Jun 4, 2026

   Document saving an in-memory database with SQLite.backup

   ...

   ee75693
7. JohnCobbler commented on Jun 4, 2026

   

   JohnCobbler

   on Jun 4, 2026

   Contributor

   More actions

   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.
8. quinnj

   added a commit that references this issue on Sep 23, 2026

   Document saving an in-memory database with SQLite.backup (#350)

   ...

   c122eee
9. quinnj commented on Sep 30, 2026

   

   quinnj

   on Sep 30, 2026

   Last edited by quinnj

   Member

   More actions

   SQLite.backup(db, path) (added in v1.8.0) writes a database, including an in-memory one, to a file. The docs now have an example: https://juliadatabases.org/SQLite.jl/dev/#Saving-an-in-memory-database-to-a-file. Thanks all!
10. quinnj

    closed this as completedon Sep 30, 2026