From efb324070827bd1fe713b93f80c601551d246579 Mon Sep 17 00:00:00 2001
From: xengineering <me@xengineering.eu>
Date: Sun, 31 May 2026 21:20:48 +0200
Subject: Add migration execution

This adds the execution of the embedded migrations and adds a suitable
unit test.
---
 database.go      | 23 +++++++++++++++++++++++
 database_test.go | 18 +++++++++++++++---
 testing.go       | 50 ++++++++++++++++++++++++++++++++++++++++++++++++++
 3 files changed, 88 insertions(+), 3 deletions(-)
 create mode 100644 testing.go

diff --git a/database.go b/database.go
index c2d69c1..c71ff78 100644
--- a/database.go
+++ b/database.go
@@ -1,6 +1,7 @@
 package main
 
 import (
+	"database/sql"
 	"embed"
 	"fmt"
 	"io/fs"
@@ -43,3 +44,25 @@ func readMigrations() {
 		return nil
 	})
 }
+
+func Transaction(db *sql.DB, f func(*sql.Tx) error) error {
+	tx, err := db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	err = f(tx)
+	if err != nil {
+		return err
+	}
+
+	return tx.Commit()
+}
+
+func Migrate(db *sql.DB, index int) error {
+	return Transaction(db, func(tx *sql.Tx) error {
+		_, err := tx.Exec(migrations[index])
+		return err
+	})
+}
diff --git a/database_test.go b/database_test.go
index fce0c02..b412af0 100644
--- a/database_test.go
+++ b/database_test.go
@@ -3,7 +3,6 @@ package main
 import (
 	"fmt"
 	"os"
-	"path/filepath"
 	"strings"
 	"testing"
 )
@@ -24,8 +23,7 @@ func TestMigrationNumber(t *testing.T) {
 
 func TestMigrationContent(t *testing.T) {
 	for i, migration := range migrations {
-		name := fmt.Sprintf(migrationFilePattern, i+1)
-		path := filepath.Join(migrationDir, name)
+		path := migrationPath(i)
 
 		expectation := fmt.Sprintf("PRAGMA user_version = %d;", i+1)
 		if !strings.HasPrefix(migration, expectation) {
@@ -42,3 +40,17 @@ func TestMigrationContent(t *testing.T) {
 		}
 	}
 }
+
+func TestMigrations(t *testing.T) {
+	db, _ := emptyDB(t)
+
+	for i := range migrations {
+		path := migrationPath(i)
+		t.Logf("Test execution of %s", path)
+
+		err := Migrate(db, i)
+		if err != nil {
+			t.Fatalf("Failed to execute migration %s: %v", path, err)
+		}
+	}
+}
diff --git a/testing.go b/testing.go
new file mode 100644
index 0000000..d4a8457
--- /dev/null
+++ b/testing.go
@@ -0,0 +1,50 @@
+package main
+
+import (
+	"database/sql"
+	"fmt"
+	"os"
+	"path/filepath"
+	"testing"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+func newSite(t *testing.T) string {
+	site, err := os.MkdirTemp("", "finserv-testing-")
+	if err != nil {
+		t.Fatalf("Error creating test site '%s': %v", site, err)
+	}
+
+	t.Cleanup(func() {
+		os.RemoveAll(site)
+		t.Logf("Removed test site '%s'.", site)
+	})
+
+	t.Logf("Created test site '%s'.", site)
+	return site
+}
+
+func emptyDB(t *testing.T) (*sql.DB, string) {
+	site := newSite(t)
+
+	path := filepath.Join(site, "main.sqlite3")
+
+	db, err := sql.Open("sqlite3", path)
+	if err != nil {
+		t.Fatalf("Failed to open SQLite3 database '%s': %v", path, err)
+	}
+
+	t.Cleanup(func() {
+		db.Close()
+		t.Logf("Removed test database '%s'.", path)
+	})
+
+	t.Logf("Created test database '%s'.", path)
+	return db, path
+}
+
+func migrationPath(index int) string {
+	name := fmt.Sprintf(migrationFilePattern, index+1)
+	return filepath.Join(migrationDir, name)
+}
-- 
cgit v1.3.1