Restore in-memory database failed by using Online Backup API

[chivincent]

TL; DR

I've tried to restore an in-memory database from an exist sqlite database file, it returns no error but the in-memory db is empty.

However, if I changed :memory: to path/output.db, it works.

Environment

$ go env
GO111MODULE=''
GOARCH='arm64'
GOBIN=''
GOCACHE='/Users/chivincent/Library/Caches/go-build'
GOENV='/Users/chivincent/Library/Application Support/go/env'
GOEXE=''
GOEXPERIMENT=''
GOFLAGS=''
GOHOSTARCH='arm64'
GOHOSTOS='darwin'
GOINSECURE=''
GOMODCACHE='/Users/chivincent/go/pkg/mod'
GONOPROXY=''
GONOSUMDB=''
GOOS='darwin'
GOPATH='/Users/chivincent/go'
GOPRIVATE=''
GOPROXY='https://proxy.golang.org,direct'
GOROOT='/opt/homebrew/Cellar/go/1.22.4/libexec'
GOSUMDB='sum.golang.org'
GOTMPDIR=''
GOTOOLCHAIN='auto'
GOTOOLDIR='/opt/homebrew/Cellar/go/1.22.4/libexec/pkg/tool/darwin_arm64'
GOVCS=''
GOVERSION='go1.22.4'
GCCGO='gccgo'
AR='ar'
CC='cc'
CXX='c++'
CGO_ENABLED='1'
GOMOD='/Users/chivincent/tmp/sqlite-online-backup/go.mod'
GOWORK=''
CGO_CFLAGS='-O2 -g'
CGO_CPPFLAGS=''
CGO_CXXFLAGS='-O2 -g'
CGO_FFLAGS='-O2 -g'
CGO_LDFLAGS='-O2 -g'
PKG_CONFIG='pkg-config'
GOGCCFLAGS='-fPIC -arch arm64 -pthread -fno-caret-diagnostics -Qunused-arguments -fmessage-length=0 -ffile-prefix-map=/var/folders/q2/_yb9rc5x291d47kxfqph69jc0000gn/T/go-build1445517694=/tmp/go-build -gno-record-gcc-switches -fno-common'

$ /opt/homebrew/opt/sqlite/bin/sqlite3 --version
3.46.0 2024-05-23 13:25:27 96c92aba00c8375bc32fafcdf12429c58bd8aabfcadab6683e35bbb9cdebf19e (64-bit)

Database Schema

$ litecli test.db
test.db> create table users (id integer primary key not null, name text not null)
Query OK, 0 rows affected
Time: 0.001s
test.db> select * from users
0 rows in set
Time: 0.000s
test.db> insert into users(id, name) values (1, "Vincent")
Query OK, 1 row affected
Time: 0.002s
test.db> insert into users(id, name) values (2, "Lilly")
Query OK, 1 row affected
Time: 0.002s
test.db> select * from users
+----+---------+
| id | name    |
+----+---------+
| 1  | Vincent |
| 2  | Lilly   |
+----+---------+
2 rows in set
Time: 0.006s

Code (in Go)

package main

import (
	"context"
	"database/sql"
	"errors"

	"github.com/mattn/go-sqlite3"
)

func main() {
	fromDb, err := sql.Open("sqlite3", "test.db")
	if err != nil {
		panic(err)
	}
	if err = fromDb.Ping(); err != nil {
		panic(err)
	}

	toDb, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		panic(err)
	}
	if err = toDb.Ping(); err != nil {
		panic(err)
	}

	fromConn, err := conn(fromDb)
	if err != nil {
		panic(err)
	}
	toConn, err := conn(toDb)
	if err != nil {
		panic(err)
	}

	if err := backupAll(fromConn, toConn); err != nil {
		panic(err)
	}

	rows, err := toDb.Query("SELECT COUNT(*) FROM users;")
	if err != nil {
		panic(err)
	}

	for rows.Next() {
		var count int
		if err := rows.Scan(&count); err != nil {
			panic(err)
		}
		println(count)
	}

}

func conn(db *sql.DB) (c *sqlite3.SQLiteConn, err error) {
	var rawConn *sql.Conn
	if rawConn, err = db.Conn(context.Background()); err != nil {
		return
	}

	err = rawConn.Raw(func(driverConn any) error {
		var ok bool
		if c, ok = driverConn.(*sqlite3.SQLiteConn); !ok {
			return errors.New("failed to get sqlite3 connection")
		}
		return nil
	})

	return
}

func backupAll(src, dest *sqlite3.SQLiteConn) error {
	backup, err := dest.Backup("main", src, "main")
	if err != nil {
		return err
	}
	defer func() {
		if err = backup.Finish(); err != nil {
			panic(err)
		}
	}()

	var done bool
	for !done {
		if done, err = backup.Step(-1); err != nil {
			return err
		}
	}

	return nil
}

$ go run main.go
panic: no such table: users

goroutine 1 [running]:
main.main()
        /****/main.go:43 +0x188
exit status 2

If I changed toDb, err := sql.Open("sqlite3", ":memory:") into toDb, err := sql.Open("sqlite3", "another.db"), it works well.

Code (in C)

I've tried the same logic with sqlite C API, and it works well.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

int backupAll(sqlite3 *src, sqlite3* dest) {
    sqlite3_backup *backup = sqlite3_backup_init(dest, "main", src, "main");
    if (backup) {
        sqlite3_backup_step(backup, -1);
        sqlite3_backup_finish(backup);
    };

    return sqlite3_errcode(dest);
}

int main() {
    const char *fromFilename = "test.db";
    sqlite3 *fromDb = NULL, *toDb = NULL;

    int rc = 0;
    if (sqlite3_open(fromFilename, &fromDb) == SQLITE_OK && sqlite3_open(":memory:", &toDb) == SQLITE_OK) {
            rc = backupAll(fromDb, toDb);

            sqlite3_stmt *stmt;
            sqlite3_prepare_v3(toDb, "SELECT COUNT(*) FROM users;", -1, 0, &stmt, NULL);
            if (sqlite3_step(stmt) == SQLITE_ROW) {
                int count = sqlite3_column_int(stmt, 0);
                printf("count: %d\n", count);
            } else {
                printf("error\n");
            }
    }

    sqlite3_close(fromDb);
    sqlite3_close(toDb);
    return rc;
}

$ gcc -Wall -L/opt/homebrew/opt/sqlite/lib -I/opt/homebrew/opt/sqlite/include main.c -lsqlite3  -v
$ ./a.out
count: 2

[rittneje]

Please note the following:

1. *sql.DB represents a connection pool.
2. Each call to Query/QueryRow/Exec/etc. on *sql.DB may run on a different connection in the pool.
3. When you call db.Conn, the returned connection is considered "in use" until you close it, which means any subsequent requests to the *sql.DB pool will need to open a new connection.
4. If you use :memory:, each connection will refer to a separate in-memory database.

You should instead use the memdb vfs so that different connections in the pool refer to the same in-memory database, and you should configure the pool to never close the last connection.

//  Note that the path you give here is somewhat arbitrary, but it MUST start with a slash to work properly.
db, err := sql.Open("sqlite3", "file:/whatever?vfs=memdb")
if err != nil {
    ...
}
defer db.Close()

db.SetConnMaxIdleTime(0)
db.SetConnMaxLifetime(0)
db.SetMaxIdleConns(1)
db.SetMaxOpenConns(...)

if err := db.Ping(); err != nil {
    ...
}

...

[chivincent]

I've tried to replace :memory: into file:/foobar?vfs=memdb in previous code, and it works.

Thanks a lot.