rbn.im

fossil repositories

backing up a SQLite database with Go

open SQLite databases can't just be copied to back them up. while you can use the sqlite3 shell .backup builtin for this, it may be preferrable to do it from inside your Go program.

the way to do this feels a bit hacky, but works nonetheless. given that destDb and srcDb are opened SQLite databases, you can do the following:

func backup(destDb, srcDb *sql.DB) error {
    destConn, err := destDb.Conn(context.Background())
    if err != nil {
        return err
    }

    srcConn, err := srcDb.Conn(context.Background())
    if err != nil {
        return err
    }

    return destConn.Raw(func (destConn interface{}) error {
        return srcConn.Raw(func (srcConn interface{}) error {
            destSQLiteConn, ok := destConn.(*sqlite3.SQLiteConn)
            if !ok {
                return fmt.Errorf("can't convert destination connection to SQLiteConn")
            }

            srcSQLiteConn, ok := srcConn.(*sqlite3.SQLiteConn)
            if !ok {
                return fmt.Errorf("can't convert source connection to SQLiteConn")
            }

            b, err := destSQLiteConn.Backup("main", srcSQLiteConn, "main")
            if err != nil {
                return fmt.Errorf("error initializing SQLite backup: %w", err)
            }

            done, err := b.Step(-1)
            if !done {
                return fmt.Errorf("step of -1, but not done")
            }
            if err != nil {
                return fmt.Errorf("error in stepping backup: %w", err)
            }

            err = b.Finish()
            if err != nil {
                return fmt.Errorf("error finishing backup: %w", err)
            }

            return err
        })
    })
}

the relevant parts of documentation for this are func (*DB) Conn and func (*Conn) Raw from database/sql and func (*SQLiteConn) Backup, func (*SQLiteBackup) Step and func (*SQLiteBackup) Finish from github.com/mattn/go-sqlite3.

lastly, SQLite documentations description of how the C-backup-API is used to find out what goes into the ominous dest and src string parameters of func (*SQLiteConn) Backup (it's main for both, they select the schema inside the SQLite database :).