db_test.go

package sqlutil

import (
	"context"
	"database/sql"
	"os"
	"testing"
)

func init() {
	DebugMigrations = true
}

func TestOpenDB(t *testing.T) {
	dir, err := os.MkdirTemp("", "")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dir)

	db, err := OpenDB(dir + "/test.db")
	if err != nil {
		t.Fatal(err)
	}
	db.Close()
}

func getTestValue(db *sql.DB, id int) (out string, err error) {
	err = WithReadonlyTx(context.Background(), db, func(tx *sql.Tx) error {
		return tx.QueryRow("SELECT value FROM test WHERE id=?", id).Scan(&out)
	})
	return
}

func checkTestValue(t *testing.T, db *sql.DB) {
	value, err := getTestValue(db, 1)
	if err != nil {
		t.Fatal(err)
	}
	if value != "test" {
		t.Fatalf("got bad value '%s', expected 'test'", value)
	}
}

func TestOpenDB_Migrations_MultipleStatements(t *testing.T) {
	dir, err := os.MkdirTemp("", "")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dir)

	db, err := OpenDB(dir+"/test.db", WithMigrations([]func(*sql.Tx) error{
		Statement("CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, value TEXT)"),
		Statement("CREATE INDEX idx_test_value ON test(value)"),
		Statement("INSERT INTO test (id, value) VALUES (1, 'test')"),
	}))
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	checkTestValue(t, db)
}

func TestOpenDB_Migrations_SingleStatement(t *testing.T) {
	dir, err := os.MkdirTemp("", "")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dir)

	db, err := OpenDB(dir+"/test.db", WithMigrations([]func(*sql.Tx) error{
		Statement(
			"CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, value TEXT)",
			"CREATE INDEX idx_test_value ON test(value)",
			"INSERT INTO test (id, value) VALUES (1, 'test')",
		),
	}))
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	checkTestValue(t, db)
}

func TestOpenDB_Migrations_Versions(t *testing.T) {
	dir, err := os.MkdirTemp("", "")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dir)

	migrations := []func(*sql.Tx) error{
		Statement("CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, value TEXT)"),
		Statement("CREATE INDEX idx_test_value ON test(value)"),
	}

	db, err := OpenDB(dir+"/test.db", WithMigrations(migrations))
	if err != nil {
		t.Fatal("first open: ", err)
	}
	db.Close()

	migrations = append(migrations, Statement("INSERT INTO test (id, value) VALUES (1, 'test')"))
	db, err = OpenDB(dir+"/test.db", WithMigrations(migrations))
	if err != nil {
		t.Fatal("second open: ", err)
	}
	defer db.Close()

	checkTestValue(t, db)
}

func TestOpenDB_Write(t *testing.T) {
	dir, err := os.MkdirTemp("", "")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dir)

	db, err := OpenDB(dir+"/test.db", WithMigrations([]func(*sql.Tx) error{
		Statement(
			"CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, value TEXT)",
			"CREATE INDEX idx_test_value ON test(value)",
		),
	}))
	if err != nil {
		t.Fatal(err)
	}
	defer db.Close()

	err = WithTx(context.Background(), db, func(tx *sql.Tx) error {
		_, err := tx.Exec("INSERT INTO test (id, value) VALUES (?, ?)", 1, "test")
		return err
	})
	if err != nil {
		t.Fatalf("INSERT error: %v", err)
	}

	checkTestValue(t, db)
}

func TestOpenDB_Migrations_Legacy(t *testing.T) {
	dir, err := os.MkdirTemp("", "")
	if err != nil {
		t.Fatal(err)
	}
	defer os.RemoveAll(dir)

	db, err := sql.Open("sqlite3", dir+"/test.db")
	if err != nil {
		t.Fatal(err)
	}
	for _, stmt := range []string{
		"CREATE TABLE schema_migrations (version uint64,dirty bool)",
		"INSERT INTO schema_migrations (version, dirty) VALUES (2, 0)",
		"CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, value TEXT)",
		"CREATE INDEX idx_test_value ON test(value)",
	} {
		if _, err := db.Exec(stmt); err != nil {
			t.Fatalf("statement '%s': %v", stmt, err)
		}
	}
	db.Close()

	migrations := []func(*sql.Tx) error{
		Statement("CREATE TABLE test (id INTEGER PRIMARY KEY NOT NULL, value TEXT)"),
		Statement("CREATE INDEX idx_test_value ON test(value)"),
		Statement("INSERT INTO test (id, value) VALUES (1, 'test')"),
	}

	db, err = OpenDB(dir+"/test.db", WithMigrations(migrations))
	if err != nil {
		t.Fatal("first open: ", err)
	}
	defer db.Close()

	checkTestValue(t, db)
}