Test Scan Inside TX (go-sqlite3)

tx_test.go

package main

import (
	"database/sql"
	"testing"
	"os"
	_ "github.com/mattn/go-sqlite3"
)

// This test will pass
func TestSqliteScanNoTx(t *testing.T) {
	// setup
	db, err := setup()
	if err != nil {
		t.Fatal(err)
	}

	// insert into the table
	_, err = db.Exec("INSERT INTO foo (id, name) VALUES($1, $2);", "1", "Bar")
	if err != nil {
		t.Error(err)
	}

	// read from the table
	row := db.QueryRow("SELECT name FROM foo WHERE id = $1", "1")

	// read the name field
	var name string
	err = row.Scan(&name)
	if err != nil {
		t.Error(err)
	}
	if name != "Bar" {
		t.Error("wrong value returned")
	}
}

// This test will fail when trying to scan the name
func TestSqliteScanInTx(t *testing.T) {
	// setup
	db, err := setup()
	if err != nil {
		t.Fatal(err)
	}

	// begin a transaction
	tx, err := db.Begin()
	if err != nil {
		t.Fatal(err)
	}

	// insert into the table
	_, err = tx.Exec("INSERT INTO foo (id, name) VALUES($1, $2);", "1", "Bar")
	if err != nil {
		t.Error(err)
	}

	// read from the table
	row := tx.QueryRow("SELECT name FROM foo WHERE id = $1", "1")

	// read the name field
	var name string
	err = row.Scan(&name)
	if err != nil {
		t.Error(err)
	}
	if name != "Bar" {
		t.Error("wrong value returned")
	}
}

func setup() (*sql.DB, error) {
	os.Remove("txtest.db")
	db, err := sql.Open("sqlite3", "txtest.db")
	if err == nil {
		_, err = db.Exec("CREATE TABLE foo (id VARCHAR(100) NOT NULL, name VARCHAR(255))")
	}
	return db, err
}