Tables disappeared after Query() without Scan() when using in-memory DB

[tdang-github]

Somehow the tables seem to disappear after a call to Query() or QueryRow() but without Scan(). This happens when using database/sql API with an "in-memory" database but not with vfs for file-based ones.

The test case below fails with sqlite3: SQL logic error: no such table: users upon the 2nd SELECT call. Is there something I did wrong?

func TestQueryInMemory(t *testing.T) {
	//dbUri := memdb.TestDB(t)
	dbUri := "file::memory:"
	db, err := sql.Open("sqlite3", dbUri)
	if err != nil {
		t.Fatalf("Failed to open database: %v", err)
	}
	defer db.Close()

	_, err = db.Exec(`CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)`)
	if err != nil {
		t.Fatalf("Failed to create table: %v", err)
	}
	_, err = db.Exec("INSERT INTO users (name, age) VALUES (?, ?), (?, ?)", "Alice", 30, "Bob", 25)
	if err != nil {
		t.Fatalf("Failed to insert data: %v", err)
	}

	var count int
	//if err = db.QueryRow("SELECT count(*) FROM users").Scan(&count); err != nil {
	if err := db.QueryRow("SELECT count(*) FROM users").Err(); err != nil {
		t.Fatal(err)
	}
	if err := db.QueryRow("SELECT count(*) FROM users").Err(); err != nil {
		t.Fatal(err)
	}
	t.Log("Result:", count)
}

[ncruces]

A little search would've solved the issue: #188

This is a common “problem” with SQLite and databasw/sql.

[tdang-github]

Thanks Nuno. Do you happen to have an explanation as to why we only see the problem when calling Query() without Scan()? The issue is described as intermittent but it was consistent for me. Just curious.

[ncruces]

No, I don't. Wether new connections are added/removed is not something you control. You can set some parameters (max/min/idle) and that's it.

But the solution is to not use :memory: with database SQL. Just use the memdb VFS.

[tdang-github]

Yes, that's why I have memdb.TestDB(t) in that test case. The VFS feature is really neat. Thanks again!