Sqlite database is widely used and battle tested and thanks to this package it can be integrated with the sql standard library in Go.

In this post i’m going to explore how the online backup and restore features of Sqlite work and the steps needed to leverage them using Golang.

Remember that the good old way of backing up copying the raw database file is a valid option if:

  • you can prevent writes when file is being copied (e.g. copy on application startup).
  • you can ensure backup integrity when a failure occurs during the copy, when you use a shared write lock to perform an online backup.
  • you never need to backup an in-memory database but only ones stored in files.

Online Backup and restore in Sqlite

According to the documentation Sqlite has a set of primitives that makes devs lives easier and allow for online backup of a database:

  • sqlite3_backup_init() create a specific object that manage the backup.
  • sqlite3_backup_step() perform the hardcore work copying a choosen number of memory pages of database into a destination file. This allow to avoid long locks of database and automatic backup-retries in case of race condition.
  • sqlite3_backup_finish() clean up stuff after the backup is done.

The good news is that this commit adds support for backup primitives also in Golang Sqlite driver, so let’s use them!

To perform a backup we need to:

  1. Create 2 connections to source and destination databases (destination can be an empty database).
  2. Access underlying Sqlite driver connection.
  3. Use the set of functions to backup the database.

To restore a previous backup, the steps are identical. Just mirror the source and destination databases :)

1. Create 2 connections to source and destination databases


// get the 2 db objects and related sql connections
// errors are ignored but must be handled in production code
sourceDb, _ := sql.Open("sqlite3", "source.sqlite")
destinationDb, _ := sql.Open("sqlite3", "destination.sqlite")

sourceConnection, _ := sourceDb.Conn(context.Background())
destinationeConnection, _ := destinationDb.Conn(context.Background())

2. Accessing underlying driver connection of a sql connection

The sql package abstracts a large set of methods from DBMS implementations, but some specific functions like the backup ones, can be used only on using Raw method to access underlying driver connection.

The way raw connection can be accesses is a bit hacky, in fact Raw method accepts only a closure with an any param, that must be casted to the sqlite connection type. Specific driver connection object cannot be used outside the clousure :(


// get the underlying sqlite connections of source and destination db
// the only way to have access to both connections at the same time 
// is to nest the 2 closures and cast raw connections to sqlite ones
err := sourceConnection.Raw(func(sourceRawConnection any) error {
    return destinationeConnection.Raw(func(destinationRawConnection any) error {
        sourceSqliteConnection, ok :=  sourceRawConnection.(*sqlite.SQLiteConn)
        if !ok {
            return fmt.Errorf("error when casting source raw connection to sqlite connection")
        }
        destionationSqliteConnection, ok :=  destinationRawConnection.(*sqlite.SQLiteConn)
        if !ok {
            return fmt.Errorf("error when casting source raw connection to sqlite connection")
        }

        // start the backup using the 2 sqlite connections
        return backup(sourceSqliteConnection,destionationSqliteConnection)
        
        
    })
})
if err != nil {
    // handle errors in backup
}

3. Use the set of functions to backup the database

Now that we got the 2 sqlite connections we have to leverage the online backup features of sqlite to:

  1. initialize backup object
  2. perform the backup with step function
  3. ensure resources are cleared with finish

func backup(sourceConnection, destinationConnection *sqlite.SQLiteConn) error{

    // when creating the backup obect, keep in mind that method must be called from destination
    // connection and not the source one.
    // the "main" string identify the main sqlite database and never change
    b, err := destinationConnection.Backup("main", sourceConnection, "main")
	if err != nil {
		return fmt.Errorf("error initializing SQLite backup: %w", err)
	}
    
    // using -1, sqlite perform a backup of the entire database.
    // using a positive integer instead, tells sqlite to copy only a specific number of pages and pause the lock.
    // in that case, a loop that continues to call step and checks for done flag in necessary
	done, err := b.Step(-1)
	if !done {
        // it should never happen when using -1 as step
		return fmt.Errorf("generic error: backup is not done after step")
	}
    if err != nil {
		return fmt.Errorf("error in stepping backup: %w", err)
	}
    
    // remember to call finish to clear up resources
	err = b.Finish()
	if err != nil {
		return fmt.Errorf("error finishing backup: %w", err)
	}
    
    //yup :) we backup our sqlite database
    return nil
}

That’s it. In this way we can also backup an in-memory database to a file and restore it when needed.